I've been trying to get fresh files loaded into my newly set-up database (MySQL 5.1.44 - I'm like a kid with a shiny new nickel) using the following command line compilation (would this be considered a script?)

LOAD DATA INFILE 'c:\progamfiles\mysql\databases\hospcrcsv\fy2005\h osp_2005_alpha.csv' INTO TABLE rpt_alpha
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

I keep getting an error message the the c:\program file, etc is not found. I don't know how to make it any clearer.

Also,
1) Is that the right call out for a comma delimited file?
2) I probably need some kind switch for date format as well but I've never seen a reference to one...sources for date format qualifier switches.

Suggestions? I'm running XP.

Thanks in advance
Hittman
Andy Bruce

is what you typed what you reposted here
'cos if so in my books
c:\progamfiles\blah di blah
isn't the same as
c:\programfiles\blah di blah

I called myself checkin it but I could be wrong (I didn't copy and paste)...will run it in the morning and get back on that.

Otherwise did the syntax look correct? I took it from the documentation but could not find anything on comma delimited switches. Do you call that a switch? So I thought it was my switch possibly because I was expecting it to be.

I also wasn't absolutely certain a .csv file absolutely qualified as a .txt file...I don't speak assembly. Couldn't find a definitive answer on that either.

Thanks again,
andy

This is a precise copy of what I have tried...and what gave me the clearest error method.

LOAD DATA LOCAL INFILE 'C:/MySQL/HospCRCSV/FY2005.csv' INTO TABLE rpt_alpha
FIELDS TERMINATED BY “,”
LINES TERMINATED BY “\r\n”;

and the error message:

Error 2 (HY000):FIle 'C:/MySQL/HospCRCSV/FY2005.csv' not found. (Errcode 2)

I've tried several iterations of quotation marks and front/back slashes mixed with including the root directory signature C:. I have not tried double slashes so I guess that's next before I move the files to the SQL directory and run it again.

confused.

thanks.
HAB

I continue to tweak the file structure and syntax...now the comma delimited data files and MySQL are in the same directory so apparently I have recognilla... problem is now that she sees the file, she says she doesn't know it.

fickle, fickle

I would guess my best bet now is to convert the .csv file to a .txt file... but before i do that, I thought I'd check in.

this is the precise syntax I last used.

LOAD DATA LOCAL INFILE
“C:/Program Files/MySQL/Databases/FY2005/hosp_2005_ALPHA.csv” INTO TABLE rpt_alpha”
FIELDS TERMINATED BY “,”
LINES TERMINATED BY “\r\n”;

and the Error message - Error 1049 (42000): Unknown Database 'n”;'

Also doesn't appear to like the "n" line return switch I used either

I used to be confused but now I'm just a little aggravated.

Thanks to all...
Hittman
Andy Bruce

Very Fun...I just loaded my first .csv file into a table !!!

This seems to be a common question so I will "publish" my findings.
I am working in XP.

MySQL 5.1.44 has a flat file engine! To know if your version has it, simply type:

SELECT ENGINE, support from information_schema.engines, order by 1; (and check to see if CSV is listed and supported)

---if so, den type:

LOAD DATA INFILE '/yourpath/your.csv' (single quotes, precise flat file path and name, leave off C: and reverse the slash from DOS)
into table yourtablename
fields terminated by ',';

How sweet it is! And you will get a roughly equivalent message to this:

QUERY OK, 1068847 rows affected (44.73 sec)
Records 1068847, Deleted 0, Skipped 0, Warnings 0

I can't wait to move on to the larger tables...may do dem rat now!

Hittman