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

"No rows were Imported"

User, date Message
Written by jabucket
2 years ago
Category: General
18 posts since Mon, 14 Jan 13
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.
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13


Here are my control character settings.
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
why is "fields escaped by" set to " - the same value as "fields enclosed by" ?
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
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?
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
the standard value is \
usually no need to change that
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
If I change field escaped by to "\" the error remains.
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
any other messages in log pane before the "no rows..." ?
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
No, unfortunately.

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


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.
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
The numbers are monetary values, so I need the '.' for that. I will try enclosing the values in quotes.
Written by jabucket
2 years ago
18 posts since Mon, 14 Jan 13
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.