Re: Unable to load entire CSV file into DB

With minimal changes, I think that your LOAD DATA statement should read,

my $stmt = qq(LOAD DATA LOCAL INFILE '../$cvstoupload'
INTO TABLE statezones FIELDS TERMINATED BY ',' OPTIONALLY 
ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES);
[download]

That takes care of quoting problems. Is your record seperator really "\r"? That seems odd.

I think that the relative path will be taken by mysqld to be relative to the directory of the database you're logged into. Use absolute paths.

I'm not sure how this has worked at all. Regarding the data file, check with a hex dump or something that the record seperators are all consistent and are what you think they are.

This seems like an odd application to do as CGI. Are you cursed with no shell access to the host? If so, I'd change host - this is an insecure thing to have around.

After Compline,
Zaxo

Comment on Re: Unable to load entire CSV file into DB Download Code

Replies are listed 'Best First'.
Re^2: Unable to load entire CSV file into DB by shenme (Priest) on Nov 07, 2004 at 00:34 UTC
This seems like an odd application to do as CGI. Since I'll be implementing something like this real soon, I'd like to offer a scenario where this is the appropriate solution. In a project I'm doing the customer would really like a whiz-bang database interface with GUI, but hasn't quite figured out what they want. They'd like programatic pick-lists of data supplied from sources on other hosts, lots of ease-of-use features, and all those wonder-widgets that make administrative work less painful. But the one group hasn't yet figured out their current processes. In other words, the people who want the fine admin tool aren't ready to define that phase of the project. But the "funding organization" needs the frontend functionality quickly, before this year's budget runs out! The guy with the money needs something now, and I find myself actually able to propose "What is the simplest thing that could possibly work?" in order to get the frontend process up and running for the 'real' users. Until the admin people can define the new admin process, we'll supply them the ability to submit a CSV file, with column name header, as exported from Excel. After they have added/modified/deleted items they'll export and upload the CSV file via CGI. The CGI will perform basic sanity checks and import into the minimal DB driving the initial implementation. Web access remains the most readily available and usable interface for admin people to interact with the implementation host. But since their specification is (cough) evolving, forcing them to use Excel/CSV initially will allow the project to progress, will give them practice defining where data items come from (their process), and will annoy them to the point of wanting to "get specific."	[reply]

Replies are listed 'Best First'.

Re^2: Unable to load entire CSV file into DB
by shenme (Priest) on Nov 07, 2004 at 00:34 UTC

This seems like an odd application to do as CGI.

In a project I'm doing the customer would really like a whiz-bang database interface with GUI, but hasn't quite figured out what they want. They'd like programatic pick-lists of data supplied from sources on other hosts, lots of ease-of-use features, and all those wonder-widgets that make administrative work less painful.

But the one group hasn't yet figured out their current processes. In other words, the people who want the fine admin tool aren't ready to define that phase of the project.

But the "funding organization" needs the frontend functionality quickly, before this year's budget runs out! The guy with the money needs something now, and I find myself actually able to propose "What is the simplest thing that could possibly work?" in order to get the frontend process up and running for the 'real' users.

Until the admin people can define the new admin process, we'll supply them the ability to submit a CSV file, with column name header, as exported from Excel. After they have added/modified/deleted items they'll export and upload the CSV file via CGI. The CGI will perform basic sanity checks and import into the minimal DB driving the initial implementation.

Web access remains the most readily available and usable interface for admin people to interact with the implementation host. But since their specification is (cough) evolving, forcing them to use Excel/CSV initially will allow the project to progress, will give them practice defining where data items come from (their process), and will annoy them to the point of wanting to "get specific."

[reply]