Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    23

    Unanswered: loading comma delimited files

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    23

    oops...my bad?

    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

  4. #4
    Join Date
    Mar 2010
    Posts
    23

    Back at it

    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

  5. #5
    Join Date
    Mar 2010
    Posts
    23

    Even more interesting

    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

  6. #6
    Join Date
    Mar 2010
    Posts
    23

    Ahhhh.....perseverance pays

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •