CSV Import

[Bruce5M]

Hi,

I'm new to SQLyog and MySQL and need to import a .csv file into an existing database/table. The csv file is quote and comma delimited and the fields correspond directly to the table, there are no column headings.

My problem is I cannot get the correct “line terminated by: ” setting. I've tried the default n and I also tried de-selecting it completely. The csv file is on a XP Pro box with about 705,000 records. I can load it into MS Access no problem.

When I try CSV import I set “Fields terminated by: ” to a comma and the “Fields enclosed by: ” is set to a single quote. Variable length is checked but with or without the “line terminated by:” setting I get only 352870 records out of the 705,000+.

I've found no reference in the doc's to the meaning of n for line termination and what values are allowed. When I read a record in my software and print the hex value the line ends with a quote, there is no $0D0A$ ( cr/lf) at the end.

Thanks

Bruce Racht

[CalEvans]

nl ???

=C=

[Bruce5M]

Thanks Cal.

Bruce

[peterlaursen]

you write: ” I can load it into MS Access no problem.”

then export it from Access into MySQL (first create an empty database using sqlyog)

[JerryL]

CalEvans wrote on Mar 14 2004, 08:43 PM:

nl ???

=C=

I have about the same problem as the previous user… but the nl solution doesn't work… neither does n or l or nr or AUTO or any of my other attempts to snag this by trial and error.

PSPAD says the records in my file end with hex B6 which they call &para. My table is large, so I am using WINRAR, but the database doesn't seem to have a problems with that. It also has no trouble restoring an entire database created the same way, or a smaller single table non-compressed table also created by the same software. I'm running out of ideas… and shudder at the though of splitting the table into 6 or 7 noncompressed chunks… ugh! Any thoughts? Is there any documentation for the corect way to enter the line control parameters into the box (abbreviations, hex codes, actual characters?) Oh, I also tried putting a ; at the end of each line and entering that in the line termination box… result was too many fields in first record error message.

[peterlaursen]

did you try 'rn' ('cariage return' followed by 'newline')?

This is DOS/Windows standard for linebreak (Unix/Linux is 'n' only).

You could inspect/view the file in a HEX editor! If you do not know how to do this, just attache a small example (please zip before attaching).

[peterlaursen]

JerryL wrote on Jan 23 2008, 09:22 PM:

I have about the same problem as the previous user… but the nl solution doesn't work… neither does n or l or nr or AUTO or any of my other attempts to snag this by trial and error.

PSPAD says the records in my file end with hex B6 which they call &para. My table is large, so I am using WINRAR, but the database doesn't seem to have a problems with that. It also has no trouble restoring an entire database created the same way, or a smaller single table non-compressed table also created by the same software. I'm running out of ideas… and shudder at the though of splitting the table into 6 or 7 noncompressed chunks… ugh! Any thoughts? Is there any documentation for the corect way to enter the line control parameters into the box (abbreviations, hex codes, actual characters?) Oh, I also tried putting a ; at the end of each line and entering that in the line termination box… result was too many fields in first record error message.

First i will ask you to add to the topic. braching liek this is very confusing (and I would wish the Forums software eitehr supported it better or did not support it at all!) I posted a reply reading

“did you try 'rn' ('cariage return' followed by 'newline')?

This is DOS/Windows standard for linebreak (Unix/Linux is 'n' only).

You could inspect/view the file in a HEX editor! If you do not know how to do this, just attache a small example (please zip before attaching).”

Now I think you understand a HEX editor. anyway a small example/test case is much better than words!

[Author]

Posts