For Jim: MySQL question

[stanl]

I am trying to perform a LOAD DATA SQL within a script. I am currently testing on a local instance of MySQL but the script will become an essential part of importing multiple (up to 100) csv files into tables daily. If I execute the syntax below from the Workbench, it correctly imports the data (although giving warnings). Warnings possibly because the csv contains only the first half of the fields in the table... but again, the import works.  When run as Connection.Execute(cSQL), I get "You have an error in your SQL Syntax...check the manual"

Code Select Expand


LOAD DATA local INFILE 'c:/reports/test.csv' INTO TABLE TestList CHARACTER SET latin1 FIELDS TERMINATED BY ',' IGNORE 1 LINES;


NOTE: either 'c:/reports/test.csv' or 'c:\reports\test.csv' work in the WorkBench

If I remove the CHARACTER SET latin1, import still successful in Workbench. Couldn't find a way to specify IGNORE WARNINGS if that was an issue.

[Deana]

Shot in the dark....Maybe this will help? http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html

[JTaylor]

Here is an example that works.  To my knowledge, you do have to have data for all the columns or specify the columns like below.

     Load Data Infile 'C:\\wb_test\\stan\\stan.csv' Into Table stan character set 'UTF8' fields terminated by ',' optionally enclosed by '"' LINES TERMINATED BY '\r\n' (ref_num, isbn, item_type, buy_status, availability, title) ;

Not sure how things will be setup for production but make sure you have the Load Local Data option turned on for the database if you are loading files from the client to the server.  By default I do not think it allows "Local" loading.

http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

Jim

[JTaylor]

Guess I should add...it gives me an error in Workbench (6.x) if I don't have the columns specified and the data file doesn't have all the fields.

Also, if the above doesn't help and if you send me the file you are trying to load and a create table statement I'll give it a go here.

Jim

[stanl]

Thanks, but I already went to plan B. Any given csv file should not exceed 2,000 rows, but more important the column headings may not be in the same order (though always 21 columns). So I re-coded to SELECT the csv into a recordset, open a connection to MySQL, then looping through the recordset and adding rows.

But your comment: By default I do not think it allows "Local" loading.

Wouldn't that apply to the Workbench as well as to scripting? So it shouldn't have succeeded from the Workbench in the first place.

[JTaylor]

Yeah...not having the same order kind of complicates things :-)   Although, if it has headings and they are the column names you could build the Load statement off that.   But, as I said before, what you are doing is probably what I would have done to begin with because it is easy, if not the most efficient.

It may be that it ignores that since you are running it from the server and not from a client.   If you tried the same thing remotely it may not work.   Then again, I may not know what I'm talking about  ;D

Jim

[stanl]

Then again, I may not know what I'm talking about  ;D

Jim

Thought never crossed my mind, or wouldn't have asked in the first place. Seems if you are going to be working with WB/ADO it is often better to deal with FIELDS directly rather than depending upon a 'bulk query'.

In my particular case, the MySQL tables have a LOAD_DATE field that is varchar(80). And it wants values like - 2014-03-04.  And the csv file has an entry 2014-03-04.... but when creating a recordset the data is inserted as text but as 3/4/2014.  Lots of little things to fix.

[JTaylor]

Agreed....sounds like you are on your way.

Jim