CSV Import With " " field enclosure

[expired user #10339]'s profile image [expired user #10339] posted 7 years ago in Import/Export Permalink

Ok so i have a csv file like this

"STCODE  ","STBARCOD"
"200LJP07.0/0412","00009"
"200LJP06.0/0412","00008"
"200LJP05.0/0412","00007"
"200LJP04.0/0412","00006"
"200LJJ05.0/0412","00005"
"200LJJ04.0/0412","00004"
"200LJJ03.0/0412","00003"

If i import this it gives me an error when trying to import the second field into a column of type int(5) However if i import the same file but without the encapsulating "" then it imports fine. A file like this:

STCODE  ,STBARCOD
200ASP04.0/0412,00012
200ASP05.0/0412,00013
200ASP06.0/0412,00014
200ASP07.0/0412,00015
200ASP08.0/0412,00016
200ASP08.0/0612,00787

Any ideas how to import it with the ""
[expired user #10339]'s profile image [expired user #10339] posted 7 years ago Permalink

NOTE: My import settings are as attached

1 attachment(s):
  • Untitled
ansgar's profile image ansgar posted 7 years ago Permalink

You could experiment with the settings, e.g. uncheck the "optionally" for "fields enclosed by".

Or you can try the "Client parses" method.

[expired user #10339]'s profile image [expired user #10339] posted 7 years ago Permalink

Unticking optionally made no difference. If I try "Client parses" then i get a truncation error on the last line however the data is exactly the same length

ansgar's profile image ansgar posted 7 years ago Permalink

I guess that was a warning, not an error, right?

[expired user #10339]'s profile image [expired user #10339] posted 7 years ago Permalink

SQL Error (1265): Data truncated for column 'Barcode' at row 549

That's the exact error i get. Row 549 is the last row.

ansgar's profile image ansgar posted 7 years ago Permalink

Well you need to either fix your csv file, or loosen the column data types so the csv values will fit without truncation errors.

[expired user #10381]'s profile image [expired user #10381] posted 7 years ago Permalink

Are saving the file on server? try to change the folder for any "white" (the one that is not connected with server or previous SQL data)

[expired user #10339]'s profile image [expired user #10339] posted 7 years ago Permalink

Well you need to either fix your csv file

my csv file is valid. there is nothing wrong with it. i cant see why it wont import

or loosen the column data types so the csv values will fit without truncation errors.

every value in the column that truncates is of the same length and only the last truncates, but just to check i doubled the column size and it still truncates on the last row

dark moor, im sorry i dont understand what your saying

[expired user #10339]'s profile image [expired user #10339] posted 7 years ago Permalink

The csv file is in the format shown in the first post. i dont see why i should have to "fix" a valid csv file to import it with the csv file import utility. Surely it should support all valid csv formats

ansgar's profile image ansgar posted 7 years ago Permalink

You can post the CREATE TABLE code of your table here, and perhaps a bigger chunk of your csv file, so we can help you.

Please login to leave a reply, or register at first.