Problem importing CSV file

[rakesh]

Hi there,

I'm trying to import data into a table, by right clicking on the table > export / import > import data from CSV .

The problem I have is when I do the import, some rows get imported and some don't. I get a message box saying a certain number of warnings. How do I view the actual warning information?

Thanks for your help,

Rakesh

[Shadow]

Issue a SHOW WARNINGS command from SQL window.

[duffbeer]

hi 🙂

i exported a table from my source db to a .csv file (i exported both with and without the column names at the top).

when i tried to “import from a .csv file” i selected the file to import, and executed the command, but nothing happened. it did not add the rows of data from the .csv file to the target table. this same result happened when trying to import both the .csv without the column names, and the .csv with the column names.

i did not select any options like overwrite, ignore, etc… all i wanted to do is append the source data to the target table.

what am i doing wrong?

i am using the enterprise trial version, to see if this app can do what i need.

so far it looks pretty but i still need to get the mission accomplished.

cheers,

duffbeer

[duffbeer]

ok i played with the “Fields” and “Lines” character definitions, and finally got an error! (errors are so much better than having no clue!) 🙂

i got:

Error No. 1064: You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near 'rn' ( 'topicid' ; 'topicimage' ; 'topictext' ; 'counter' ) at li

and that's all it shows!!! arrrrrgh! a partial error explanation is waaaaay worse than a whole error! 🙂

any help will be appreciated muchly. 🙂

cheers,

duffbeer

[Ritesh]

Very strange.

Can you mail me the CSV file? It will be very helpful if you can post screenshots of your CSV Import and Escape Characters window.

[duffbeer]

ok .. here's a couple pics:

the first is what i see after i “successfully” export the table from the source db/table.

image 1:

the second is what i see after i “import” the data from the .csv to the target db/table – in this example, i see no error, but nothing was actually written to the target db/table.

image 2:

please note that the “special character” settings are identical in both the export and import operations.

note also that i “terminated” the field in this example with , and not just , because there are , in many of the actual fields of the topicname, and if i Terminate the Fields with just , this causes a false “field end” wherever there is a ,

i have tried using , and , and even ;,; for the character to Terminate a Field. none of these yield a successful import.

cheers,

duffbeer

[peterlaursen]

I also get the error “error while importing data” with this dataset

'073F017A-8D15-4395-8916-E'|'Kilmé'

'07AFD950-6CB3-4953-9DCA-1'|'For Rita – Evening Take'

'0CEE48E5-6436-4FE3-B98D-A'|'maa'

'0E9C9C09-343F-4FB4-91A8-A'|'For Rita – Morning Take'

'11B8349C-75AB-4D5E-B50F-5'|'Tchai'

'12007753-F49C-4C6E-9715-8'|'Tariq'

'129C402A-A72B-4AC8-B2D6-6'|'Nejmet el Sahara'

'19E68636-889A-4FF9-8512-F'|'Blue Dessert'

'26928406-1FA7-4024-98BE-F'|'Chi Youm'

'2EB5C64D-8C50-45AF-AFBC-5'|'Tlete'

'A6EF2974-BB21-4EDB-8A87-5'|'El Wahha'

'AB93A970-B3C1-41F1-B2FC-F'|'Nour el Nar'

'B86A4270-81D5-4FF3-9C02-9'|'Min Samarkand'

'C343716A-B704-483B-8A16-9'|'Gibran'

'C7413231-8AB0-4645-B47C-1'|'Valse Arabe'

'E4EF30EA-C7A5-4D4F-880D-4'|'Baalbeck'

'FE98C40A-46F7-40AC-8182-8'|'Raml'

And this table definition:

CREATE TABLE `tablename1` (

`id` char(25) default NULL,

`pic` varchar(255) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

Export dialogue box in pic

I tried a lot of different settings and nothing works. Not even if data are exported with SQLyog and imported with the same settings.

Always thought that there was something wrong with this CSV feature. Besides it relates closely to problems with the “copy from clipboard” option previously discussed in the forum.

[Ritesh]

@duffbeer: What are the Create Table… stmts. of nuke_topics and cms_topics?

[Ritesh]

@peterlaursen: Can you mail me the actual .CSV file or a SQL dump of the table?

[Ritesh]

Shadow wrote on Jun 3 2005, 12:44 PM:

Issue a SHOW WARNINGS command from SQL window.

Starting from v4.07, a SHOW WARNINGS button will be provided to easily view the warnings. This feature has already been implemented in the SQLyog v4.07 development tree.

[duffbeer]

ritesh wrote:

@duffbeer: What are the Create Table… stmts. of nuke_topics and cms_topics?

======

ritesh – i don't see any Create Table… stmts. of nuke_topics and cms_topics.

again, i see no errors … when i try to import, there is simply no action, no error, nothing. it's as if the action is totally ignored.

— duffbeer

[Ritesh]

By Create Table…, I mean the DDL statement that is used to create a table. I am attaching an image to depict my requirement.

[peterlaursen]

@Ritesh

You got mail ! 🙄

[Ritesh]

@Peter:

I was able to correctly import data using the following escape characters.

Note: You have to use ' and not '.

[Ritesh]

@duffbeer: I am still waiting for the Create Table… stmt.

[peterlaursen]

I still am not able to import anything!

Data look like (note there are no ” ' “s)

73F017A-8D15-4395-8916-E|Kilmé

7AFD950-6CB3-4953-9DCA-1|For Rita – Evening Take

CEE48E5-6436-4FE3-B98D-A|maa

E9C9C09-343F-4FB4-91A8-A|For Rita – Morning Take

11B8349C-75AB-4D5E-B50F-5|Tchai

12007753-F49C-4C6E-9715-8|Tariq

129C402A-A72B-4AC8-B2D6-6|Nejmet el Sahara

19E68636-889A-4FF9-8512-F|Blue Dessert

26928406-1FA7-4024-98BE-F|Chi Youm

2EB5C64D-8C50-45AF-AFBC-5|Tlete

A6EF2974-BB21-4EDB-8A87-5|El Wahha

AB93A970-B3C1-41F1-B2FC-F|Nour el Nar

B86A4270-81D5-4FF3-9C02-9|Min Samarkand

C343716A-B704-483B-8A16-9|Gibran

C7413231-8AB0-4645-B47C-1|Valse Arabe

E4EF30EA-C7A5-4D4F-880D-4|Baalbeck

FE98C40A-46F7-40AC-8182-8|Raml

with the setting that you propose

I get these warnings

---

---

---

Warning 1265 Data truncated for column 'guid' at row 1

Warning 1261 Row 1 doesn't contain data for all columns

And I get one EMPTY row inserted and nothing more!

I also really don't understand why the escape-char matters here since the “enclose”character does not appear in dthe dataset.

[Ritesh]

I got 17 rows imported correctly.

The data that you just pasted seems to be different from the one that you sent me. It is now enclosed by rather then “

When you click Import, SQLyog will log the correct Load local infile… query in the SQL window. Do you find anything wrong?

[peterlaursen]

Well , yes I can import the data I sent you with the setting that you propose.

But that's not logical at all, I believe!!

1) Why must the escape-sequence be \ when there is nothing to escape here ??

2) Why must the fields be enclosed by ” ' ” when they really are only enclosed by ” ' ” ?

3) I also don't understand why the “lines teminated by” must be ” n “. Using anything else generates an error. The program shoul prevent me from using values that won't work! But maybe a little bit of documentation would sort things out ??

4) Why can't I just use exactly the same vaules in the export-settings dialogue and the import-settings dialogue ?

[Ritesh]

Quote:

Why must the escape-sequence be \ when there is nothing to escape here ??

You need not. Just uncheck the option Escaped By… in the Escape Character dialog box.

Quote:

Why must the fields be enclosed by ” ' ” when they really are only enclosed by ” ' “

' denotes a literal in MySQL's SQL. So you have to escape it with a . Clients like SQLyog should actually do it for your but right now this feature is not implemented.

Quote:

I also don't understand why the “lines teminated by” must be ” n “.

The file which you had sent be had linux line line endings i.e. just n. Thats a good reason of why only n is working.

Quote:

Why can't I just use exactly the same vaules in the export-settings dialogue and the import-settings dialogue

What values did you use to export it? You should be able to use the same settings to correctly export/import data. If you are not then its a bug with SQLyog.

[peterlaursen]

OK … it clarified one point: that there is no automatic MySQL escape-character handling with the CSV-import.

I believe there is one problem more: If you enter more than one (two) characters as escape charater (i.e “your own” escape-character for your own dataset, not the MySQL standard “” escape-character), the .csv written only contains one (the first) of those two characters! Try yourself to enter “!!” or “!?” as escape-character and use a field delimiter that is represented in some char-field in your data..

There are some more very confusing things about it. If you specify “!!” or “!?” the dialogue will remember both. But in the file it is only “!”. So if you don't check the file but just trust the dialogue, you'll get an error! You must delete the last one from the dialogue to make it work. However – if you specify “\” the dialogue will remember only “” (what is actually what is written too) and then you will have to add one more manually to adress the mySQL escaping correctly. Here you also can't trust the dialogue!!

As far as I remember “\” is the program's default (with a fresh installation). Right ? Bad idea, I think! In case you will need BOTH the escape-character with the MySQL server AND an escape-character your own dataset they should be VERY DIFFERENT! Use for instance “^” as the escape-character for the dataset instead.

Well .. if you know and understand all this maybe it's no problem. But don't you see how it can confusing ? It took me 1½ year to get it right (hope I did!)

One last Q: how did you see the Linux-style line-endings – I have a lot of editors, but none of them show it ??

[Ritesh]

Quote:

One last Q: how did you see the Linux-style line-endings – I have a lot of editors, but none of them show it ??

Linux style line-endings contains only whose ASCII value is 10. Open your file with any hex editor and your will see the value.

[peterlaursen]

my problem: all my editors are so “intelligent” that they show a line-break instead! No matter which standard for line-break the file uses (DOS, ANSI, OS/2, Linux or whatever!).

[Author]

Posts