Csv Import Problems

[ddaily]

I've read some of the other threads on this and I'm confused why it should be this hard to import a very simple CSV file.

I have attached a screen shot of the data on the left and the mysql results.

I have tried several variations with the same result.

So what am I missing?

Thanks.

[peterlaursen]

Quote:

I have attached a screen shot of the data on the left and the mysql results.

I don't think you have! 😀

BTW: you want to import to MySQL or some other Database (Access for instance?)

[ddaily]

I couldn't get the file to attach. Not a good day all in all.

Data: '1','hispanic','100'

'2','caucasian','200'

mysql table: racecode [int(11), NOT NULL]

racedsc [tinytext, NULL]

racetot [int(11), NULL]

The import CSV box has:

terminated by ,

optionally enclosed by: '

Escaped by: \

lines terminated by: rn

I've tried different settings, all result in “Error while importing data”

Does the location of the file affect importing?

I did get it imported using another package (but i'm not paying

$65 for an import only program!).

This should be a simple thing and I'm hoping I'm just not seeing the obvious.

Thanks.

[Ritesh]

Quote:

Does the location of the file affect importing?

Yes.

SQLyog uses MySQL's internal LOAD LOCAL INFILE command to import data from a CSV/DELIMITED file.

If you check the HISTORY tab, you will see the corresponding SQL statement. Can you paste the SQL statement out here? Make sure that the path provided in the SQL is correct.

BTW, are you using HTTP Tunneling?

[Ritesh]

You can mail me the csv file at [email protected].

[ddaily]

CSV file mailed.

Here's the history file:

History file attempting to load racetable from a file

fails also if the file is on my local C: drive

/*[9:35:20 AM][ 0 ms]*/ show variables like '%character%'

/*[9:35:20 AM][ 0 ms]*/ Set character_set_connection=latin1

/*[9:35:20 AM][ 0 ms]*/ Set character_set_results=latin1

/*[9:35:20 AM][ 0 ms]*/ Set character_set_client=latin1

/*[9:35:20 AM][ 0 ms]*/ use `fsbo`

/*[9:35:25 AM][ 16 ms]*/ show table status from `fsbo`

/*[9:35:26 AM][ 0 ms]*/ show tables from `fsbo`

/*[9:35:30 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show create table `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show create table `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ select * from `fsbo`.`racetable` limit 0, 50

/*[9:36:33 AM][ 0 ms]*/ show tables from `fsbo`

/*[9:36:33 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:36:34 AM][ 0 ms]*/ load data local infile 'U:/racetabl.asc' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by ''' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

[ddaily]

Data import loaded fine using MySQL-Front, another GUI interface software,

but it's not as good as SQLyog (plus my German isn't good enough to understand the error messages! 😮 ).

What I truly don't understand is why such a simple operation is not working?

I've tried several other settings to no avail.

[peterlaursen]

@ddaily

To attach a -csv you must zip it. For security only some filetypeds are allowed. Common graphics and ZIP.

[peterlaursen]

I tried too and get the same error.

But there is one thing that you do wrong. OPTINALLY shoul not be checked. Optionally is when only strings are enclosed. Here numbers are enclosed too.

I have said it sometimes before. The CSV import tool is written by a C-prgrammer who does not understand that not all people are C-programmers. It is unusable for most people in my opinion! 😛 But who needs CSV when there are so many other import options ?!

EDIT: Sorry I forgot the smiley! But I always thought that the SQLyog CSV-functionality is the most non-intuitive and un-user-friendly I've ever seen. Should not surprise anyone that I think so – it is more than a year I wrote it for the first time here.

But no matter what I do I cant get those simple data to import! So I think something went wrong when “Excel” fill-in was added to the code.

[peterlaursen]

It is a problem with the singlequote character used as enclose-character. These two examples work without problems:

Quote:

1,hispanic,100

2,caucasian,200

“1”,”hispanic”,”100″

“2”,”caucasian”,”200″

So you can “search and replace” in an editor and replace single-quote with double-quote.

I remember we had a l-o-o-o-n-g discussion about a similar issue about a year ago. The problem is that the single-quote character has a special meaning with MySQL. That means that it must be escaped with the -character. Unlike other SQLyog functionality the CSV-tools do not handle this automatically.

Last time I tried to understand this I spent a whole weekend. There was a workaround. But either I can't reconstruct details or it simply is gone.

Ridiculous CSV-tools! 😡

[Ritesh]

I think I got the problem 😛

Your query says:

Quote:

load data local infile 'U:/racetabl.asc' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by ''' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

It should be:

Quote:

load data local infile 'U:/racetabl.asc' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by ''' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

You need to escape ' with '

[Ritesh]

I understand that this is little confusing. I have added this in the TO-DO list. We plan to work on the CSV Export/Import feature to make it more intuitive.

[peterlaursen]

Quote:

You need to escape ' with '

You mean that WHEN GENRATING THE CSV the ' should be escaped with .

This is The MySQL escape-character. Not the escape-character that the CSV-import tool uses. The latter handles situations where the 'enclose' or delimiter -character also is present in strings in your data.

@ Ritesh: Nobody understands those half explanations!

But:

It does not work either with 4.2 !

this .csv-file

Code:

'1','hispanic','100'
'2','caucasian','200'

generates the warning as pic shows and numbers are adjusted to 0 (zero) – not matter whether 'optional' is checked or not!

And BTW: maybe the application generating the CSV does not allow for escaping?

Whou told you that if was SQLyog that was used for generating the CSV ?

Much easier: change singlequote to doublequote

[Ritesh]

I think you got it wrong. You will need to the escape character values with whereever required.

Attached screenshot will help in understanding the solution.

The settings will then generate the following query:

Quote:

load data local infile 'U:/racetabl.asc' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by ''' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

[peterlaursen]

You could have written that in the first place!

Now I have been spending no more than 20 hours with aspects of this CSV-tool.

You know how your code reads the user input and what it does with it.

Nobody else has a chance.

[peterlaursen]

Just some additional info for readers:

To read about the use of escaping with MySQL read

http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Further I created a crazy example. Let's say you have the string

Code:

this is ours', he said.

and you export that to a CSV-file with , as field delimiter and ' as field enclose character. This resultis in

Code:

'1','this is ours', he said.','400'

(to follow the database schema here. Impossible to import! Thats is what the SQLyog escape character is for. With the use of for instance ^ as SQLyog escape character when exporting you get

Code:

'1','this is ours^'^, he said.','400'

and you can import with the settings shown in picture.

The SQLyog escape character ( here ^ )takes effect with the SQLyog CLIENT and is NOT sent to the server ( 'stripped' before that ), the MySQL escape character ( ) is always used by MySQL when interpreting strings in a query.

And there is even one detail more: when using LIKE in an SQL-statement there is a parser that evaluates the substring behink the LIKE and strips one sescape character. So to test for whether the character is contained in a the string you must write LIKE '%\\%'. Thus this statement

Code:

Select 'backs\ash' LIKE '%\\%'

returns TRUE. But that is a MySQL issue – not an SQLyog issue!

It seems that we got a FAQ issue here. 😀

@Ritesh: no plan for any change here just over night I think ??

[Ritesh]

Nope.

This requires complete understanding of the issue. We are planning to overhaul the CSV Export/Import system to make it more intuitive. It seems that there are quite a number of issues that we never thought of.

[ddaily]

peterlaursen wrote on Oct 18 2005, 08:33 PM:

You could have written that in the first place!

Now I have been spending no more than 20 hours with aspects of this CSV-tool.

You know how your code reads the user input and what it does with it. 

Nobody else has a chance.

[post=”7643″]<{POST_SNAPBACK}>[/post]

[peterlaursen]

@ ritesh

this CSV

Code:

'1','a'
'2','b'

needs to be escaped like you showed, however this one

Code:

“1”,”a”
“2”,”b”

does not.

This must be because SQLyog internally adds singlequotes (and not doublequotes) around the strings processed ??

According to the MySQL docs http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html there is NO difference at all in how to escape with ' and ” (when not sql_mode = 'ANSI_QUOTES' 😮 – then ” has same meaning ` (backquote) ) So it must be because of the way this input is handled by the SQLyog program ?!

[ddaily]

🙁 Sorry about the quote, I must have punch the reply too soon.

My real message was both methods worked, ' and ” double quotes.

Thanks, now I can get on with really using this cool software.

🙂

[Ritesh]

Quote:

This must be because SQLyog internally adds singlequotes (and not doublequotes) around the strings processed ??

Can you provide the corresponding SQL statement that works?

You dont need to escape “ as the following query works:

Code:

load data local infile 'C:/Documents and Settings/Ritesh/Desktop/racetest.csv' into table `test`.`racetable` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

You only need to escape and ' 😀

[ddaily]

Here's the code for double quotes on the second field only:

example: 1,”hispan”,100

/*[1:29:49 PM][ 0 ms]*/ load data local infile 'C:/foof/racetestdq.csv' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by '”' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

[Author]

Posts