"No rows were Imported"

[expired user #6759]'s profile image [expired user #6759] posted 11 years ago in General Permalink
I have a csv file that I am trying to import, but am unable to as I am greeted with an error saying that "No rows were imported". Cntents of the CSV file are as follows:

ZUKEMURA,,,2011/07,135123,99205 - OFFICE OUTPT NEW 60 MIN,1.00,0.00,-30.00,0.00,0.00,0.00,0.00,0.00,0.00
ZUKEMURA,,,2011/08,137336,99213 - OFFICE OUTPT EST15 MIN,1.00,0.00,-30.00,0.00,0.00,0.00,0.00,0.00,0.00
ZUKEMURA,,,2011/09,141956,99213 - OFFICE OUTPT EST15 MIN,3.00,0.00,-10.00,-64.88,0.00,-30.12,0.00,0.00,0.00
ZUKEMURA,,,2012/01,155541,80053 - COMPRE METAB PANEL,3.00,25.00,0.00,-14.87,0.00,-10.13,0.00,0.00,0.00
ZUKEMURA,,,2012/01,155541,83615 - LACTATE DEHYD,3.00,15.00,0.00,-8.50,0.00,-6.50,0.00,0.00,0.00
ZUKEMURA,,,2012/01,155541,85025 - BLD# COMPL AUTO HHRWP&AUTO DIFFIAL,3.00,19.00,0.00,-10.94,0.00,-8.06,0.00,0.00,0.00
ZUKEMURA,,,2012/01,155541,99213 - OFFICE OUTPT EST15 MIN,4.00,105.00,-30.00,-44.88,0.00,-30.12,0.00,0.00,0.00
ZUKEMURA,,,2012/05,168197,99213 - OFFICE OUTPT EST15 MIN,4.00,105.00,-30.00,-45.14,0.00,-29.86,0.00,0.00,0.00
ZUKEMURA,,,2012/08,178731,80053 - COMPRE METAB PANEL,3.00,25.00,0.00,-14.97,0.00,-10.03,0.00,0.00,0.00
ZUKEMURA,,,2012/08,178731,83615 - LACTATE DEHYD,3.00,15.00,0.00,-8.55,0.00,-6.45,0.00,0.00,0.00
ZUKEMURA,,,2012/08,178731,85025 - BLD# COMPL AUTO HHRWP&AUTO DIFFIAL,3.00,19.00,0.00,-11.02,0.00,-7.98,0.00,0.00,0.00
ZUKEMURA,,,2012/08,178731,99213 - OFFICE OUTPT EST15 MIN,3.00,105.00,0.00,-45.14,0.00,-29.86,0.00,0.00,0.00
ZUKERAN,,,2011/08,136477,99213 - OFFICE OUTPT EST15 MIN,1.00,0.00,0.00,-14.98,0.00,0.00,0.00,0.00,0.00
ZUKERAN,,,2012/02,158939,99213 - OFFICE OUTPT EST15 MIN,6.00,109.95,-15.03,-60.11,0.00,-34.81,0.00,0.00,6.82
ZUKERAN,,,2012/03,161192,99214 - OFFICE OUTPT EST 25 MIN,6.00,157.07,-22.16,-88.63,0.00,-46.28,0.00,0.00,46.82
ZUKERAN,,,2012/09,183316,99213 - OFFICE OUTPT EST15 MIN,1.00,109.95,0.00,0.00,0.00,0.00,0.00,0.00,0.00

Any ideas? I have imported several other CSV files into the same database, but different tables.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink


Here are my control character settings.
jfalch's profile image jfalch posted 11 years ago Permalink
why is "fields escaped by" set to " - the same value as "fields enclosed by" ?
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
I believe that was the default, but I suppose I could have changed it accidentally. I am new to the program. What should it be set to?
jfalch's profile image jfalch posted 11 years ago Permalink
the standard value is \
usually no need to change that
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
If I change field escaped by to "\" the error remains.
jfalch's profile image jfalch posted 11 years ago Permalink
any other messages in log pane before the "no rows..." ?
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
No, unfortunately.

LOAD DATA LOCAL INFILE.........(insert string).....
/* 0 rows imported in 0.016 seconds. */
SHOW WARNINGS;
jfalch's profile image jfalch posted 11 years ago Permalink
was the .csv file generated on a windows system ?
especially if not, try to replace "lines terminated by" with \n
if that does not work, try with \r
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
It was generated on a Windows 8 box. \r gave me some progress, but now it is giving me a Row 16 does not contain data for all columns error. The row has the same columns populated or null as the previous 15.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
In the table, all columns are allowed null with the exception of the auto-inc key which is not being imported from the CSV file.
jfalch's profile image jfalch posted 11 years ago Permalink
is there really a line end character in the data for column 6 in the 2 rows shown above with "AUTO
DIFFIAL", or is that just formatting from this forum ?
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
"BLD# COMPL AUTO HHRWP&AUTO DIFFIAL" is a varchar description value that I am trying to insert
jfalch's profile image jfalch posted 11 years ago Permalink
ok; but if you seee the values that you posted above, there is a line break after the second "AUTO". Is this line break only inserted by the forum software, or is there a line break cahracter in the data that you posted ?
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
All I see is a space after AUTO. Is that what you are referring to?
ansgar's profile image ansgar posted 11 years ago Permalink
You could upload the csv file somewhere so we are able to analyze it without some forum editor inbetween.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
I edited the data in the CSV file to contain only one line as follows, but I still get the same error about not contain data for all columns:

ZUKERAN,ESSIE,,,2012/09/19,183316,99213,1.00,109.95,0.00,0.00,0.00,0.00,0.00,0.00,0.00
ansgar's profile image ansgar posted 11 years ago Permalink
Then, what's the CREATE TABLE code for your table?
jfalch's profile image jfalch posted 11 years ago Permalink
a tip: you could download Sam FranckeĀ“s CSVEd and see what it makes of your .csv file; perhaps this yields some insight.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
I downloaded the utility and it looks like everything is separated the way it should be. Any other ideas?
jfalch's profile image jfalch posted 11 years ago Permalink
sorry, no.
ansgar's profile image ansgar posted 11 years ago Permalink
If you upload the CSV file I would take a second look.
BubikolRamios's profile image BubikolRamios posted 11 years ago Permalink
The prob might be that there could be some unvisible characters before that 1 line of yours. Se that in hex.

I know fro sure some editors does that - ad some non visible characters infront of file.

Anyway you have to be 100% sure the csv file is 100% OK, before import, as HeidiSQL does not check that, and messages like: 100 lines imported means same as nothing.
ansgar's profile image ansgar posted 11 years ago Permalink
These invisible characters represent the BOM of UTF16 files or other types. The fact that this is rarely seen is due to the fact that BOM less UTF8 is most popular nowadays. Unfortunately, this format requires the editor to guess the format, which fails in some situations.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
CSVEdit shows no errors in the file. Is there something in particular that I should be looking for? I have uploaded a smaller version of the CSV file at http://macroserve.net/public/test.csv which will generate the error. It is only one line.
BubikolRamios's profile image BubikolRamios posted 11 years ago Permalink


File is OK, otherwise there would be something before 'Z'.


Anyway I suggest you to do something to yor file, like , change

ZUKERAN,ESSIE,null,null,9/19/2012,183316,99213,1,109.95,0,0,0,0,0,0,0

to

"ZUKERAN","ESSIE","null","null","9/19/2012","183316","99213","1","109.95","0","0","0","0","0","0","0"

Then there will be no problems.

Also you will avoid problems if your numbers would be forinstance "109,95" instead of "109.95", etc.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
The numbers are monetary values, so I need the '.' for that. I will try enclosing the values in quotes.
[expired user #6759]'s profile image [expired user #6759] posted 11 years ago Permalink
Success! I ran a macro that I found at http://www.markinns.com/articles/full/export_excel_csvs_with_double_quotes to enclose all of my values in quotes as you suggested. It imported all of the values without any trouble. THANKS!

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