I'm new to MySQL and am having trouble configuring the LOAD DATA INFILE syntax. I want to import .csv's every week into a table with 4 columns: sessionid, libraryid, authclients, and dates. I'm using MySQL Query Browser and will be working on the machine that runs the server. The query I used:

load data infile 'C:/Users/ME/Desktop/testimport.csv'
into table wirelessap ignore 1 lines
fields terminated by ','
lines terminated by '/n'
(libraryid, sessionid, dates, authclients);

Error message(1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ','
lines terminated by '/n'
(libraryid, sessionid, dates' at line 3


Other things I'm wondering about:

The "dates" column is set for DATETIME, however the .csv is not in proper format. For example, one row in the .csv says 11/9/2009 6:15. In correct DATETIME formatting it needs to be 2009-11-09 06:15:00. Does the import fix this automatically or do I need to add something?

When I get sent the .csv's, one column is called "Library name". Rather than manually changing the names to the correct "Library ID", can I change the LOAD DATA INFILE statement to do this? For example, whenever it reads 'BigLibrary' it changes the value to '1'

Is there a free GUI for MySQL that can customize the way I want .csv's to be imported? I've found the EMS Data Import for MySQL and it works great, but unfortunately its a trial version.

Thanks for your help

I'd agree its almost certainly your dates that are the problem. for MySQL your dates need to be in ISO format ie YYYY/MM/DD.

as to an import tool
aside from the PHPMyAdmin theres the MySQL migration tool.

mind you I'm alos suspicious about the order of your statements. looking at the manual MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax the ignore line is out of place

Thanks healdem.

My first problem was this line:
lines terminated by '/n'

I used a forward slash instead of a blackslash, ugh. Once I changed that and changed the dates to correct DATETIME format, the import was successful.

Is there any free software or scripts that can automatically change my .csv's? The dates are always going to be sent to me in this type of format: M/D/YYYY HH:MM. That formatting doesn't fit with any MySQL datatype and it would be tedious changing this myself. EMS Data Import for MySQL was able to do this but like I mentioned, its not free.

if you are having problems with a CSV either you have to get the author to change the format or reprocess the file yourself.

if you reprocess the file yourself then there are numerous language/tools that can do that, but that means you will have to write that reprocess tool yourself. you could use anythign ranging from scriptogn languages like PERL or PHP to conventioanl languages that will read in the file and reformat it.

another approach may be to import the CSV into the db, importing the date value as a string, and then run a query which converts the strign value into a date using the str_to_date function and an appropriate mask

MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

undoubtedly getting the CSV into the right format would be the best alternative.

a much clunkier approach would be to open the CSV in a suitable tool such as Excel or another spreadsheet and run a reformat there, then save the CSV