distal-attribute
distal-attribute
distal-attribute
distal-attribute

"No rows were Imported"

jabucket posted 2 years ago in General
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.
jabucket posted 2 years ago


Here are my control character settings.
jfalch posted 2 years ago
why is "fields escaped by" set to " - the same value as "fields enclosed by" ?
jabucket posted 2 years ago
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 posted 2 years ago
the standard value is \
usually no need to change that
jabucket posted 2 years ago
If I change field escaped by to "\" the error remains.
jfalch posted 2 years ago
any other messages in log pane before the "no rows..." ?
jabucket posted 2 years ago
No, unfortunately.

LOAD DATA LOCAL INFILE.........(insert string).....
/* 0 rows imported in 0.016 seconds. */
SHOW WARNINGS;
jfalch posted 2 years ago
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
jabucket posted 2 years ago
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.
jabucket posted 2 years ago
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 posted 2 years ago
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 ?
jabucket posted 2 years ago
"BLD# COMPL AUTO HHRWP&AUTO DIFFIAL" is a varchar description value that I am trying to insert
jfalch posted 2 years ago
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 ?
jabucket posted 2 years ago
All I see is a space after AUTO. Is that what you are referring to?
ansgar posted 2 years ago
You could upload the csv file somewhere so we are able to analyze it without some forum editor inbetween.
jabucket posted 2 years ago
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 posted 2 years ago
Then, what's the CREATE TABLE code for your table?
jfalch posted 2 years ago
a tip: you could download Sam FranckeĀ“s CSVEd and see what it makes of your .csv file; perhaps this yields some insight.
jabucket posted 2 years ago
I downloaded the utility and it looks like everything is separated the way it should be. Any other ideas?
jfalch posted 2 years ago
sorry, no.
ansgar posted 2 years ago
If you upload the CSV file I would take a second look.
BubikolRamios posted 2 years ago
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 posted 2 years ago
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.
jabucket posted 2 years ago
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 posted 2 years ago


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.
jabucket posted 2 years ago
The numbers are monetary values, so I need the '.' for that. I will try enclosing the values in quotes.
jabucket posted 2 years ago
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.