Importing Data From A Csv File.

[Anonymous]

I am using the freeware version of SQLyog to import data from a CSV file into a table in MYSQL. After importing the data, I find that data from ALL the colums of the CSV file is getting imported into the FIRST column of the MySQL table. The other columns of the MySQL table show “NULL” value. What am I doing wrong?

Thanks in advance for any help.

Pramod Kumar

[peterlaursen]

Well it is the settings. The CSV-tool in SQLyog can be somewhat confusing because it has so many possibilities. And it does not escape automatically either.

Could you show us just how one row of data looks like?

And the 'settings' dialogue (the picture to the one that I attach here)

[Anonymous]

Thanks for your fast reply. In the mean time, I have solved the problem by changing the default field seperator in SQLyog import settings to comma. But now there is another problem! Some of the fields in the CSV file have data containing comma. These commas are being interpretted by SQLyog as field seperators and so the data from such fields in the CSV file is being split into several fields in the MySQL table. For example, if a field in the CSV file contains the data “India, China, Denmark” it is being imported into three fields in the MySQL table, with “India” going into one field, “China” in the next field and “Denmark” in the third field.

Perhaps, I need to change the “Fields enclosed by” character in the import setting. I am using an MS Excel CSV file.

As desired by you, I am attaching an image of the import setting tool.

Thanks a lot for your help.

Pramod Kumar

peterlaursen wrote on Oct 30 2005, 12:52 PM:

Well it is the settings.  The CSV-tool in SQLyog can be somewhat confusing because it has so many possibilities.  And it does not escape automatically either.

Could you show us just how one row of data looks like?

And the 'settings' dialogue (the picture to the one that I attach here)

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

[Ritesh]

Can you copy-n-paste 2-3 rows of data in the CSV file?

After looking at the data, I can correctly tell you the delimiter settings.

[peterlaursen]

Yes … that is the problem!

The escape character is for solving that!

If you export to a CSV with SQLyog you can choose an escape character.

For instance if you have the string

Code:

India, China, Denmark

and choose , (comma) as the delimiter character you can choose for instance ^ as esacpe character. Then the CSV file would look like

Code:

row1data,row2data,India^, China^, Denmark

and SQLyog can handle that. The escape character means that the following charcter should be written

But unfortunalety you don't have the chance to use an escape character when exporting form Excel.

There is an option in my Excel 2000 to use ; (semicolon) as seperator when saving to a CSV. It would then look like

Code:

row1data;row2data;India, China, Denmark

and that will work if there are no ;'s in the data! You will need to find a delimiter that is not in use by the data if you shall avoid manual editing!

You can also export as a tab-seperated text-file, and rename the file to csv. The delimiter setting is then t (Platform-independent C-syntax for 'tab') with SQLyog.

If you still have the Excel-file I propose you experiment with the settings for export!

Personally I prefer quite another way of transferring data from Excel to MySQL and NOT to use CSV at all.

Open the Excel-file from MS-Access (if you have it) as a 'datalink' and either

1) export from Access to MySQL using ODBC (the MySQL ODBC-driver must be installed and configured with an instance pointing to the MySQL database where you want it)

or 2) Import into MySQL with the SQLyog Enterprise migration tool (this uses the MS ODBC-driver for Access)

[peterlaursen]

No Ritesh!

This is not a problem with the delimiter setting at all. The problem is that Excel does not allow for use of escape characters with CSV-export.

The CSV-file as it is now (with comma as delimiter as well as in the data) can't be imported correctly at all! No setting can help! You must find a delimiter that is not is use by the data and that Excel can use (the 'tab' would often work). But it is much simpler to open the .xls-file from Access and use ODBC!

[peterlaursen]

Food for thought:

http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

http://bugs.mysql.com/bug.php?id=11203

😮

[Anonymous]

This forum is really great! Or rather the forum members are great. I am getting fast replies to my queries. Thanks for your time and efforts.

As requested by you, I am pasting below two rows of data from the CSV file.

First row:

5 Arequipa Arequipa Cesar Acurio [email protected] [email protected] 054-464299 Justo Ismodes #117 Guardia Civil-Paucarpata Architect 31-Dec-27 Secretary 20-Nov-70 ENG,FRE Bolivia, Ecuador,Chile USA

Second row:

6 Arequipa Arequipa Guillermo Viñas Luisa Balboa [email protected] [email protected] 054-273700 054-253674 054-9651500 A-9 Urb. San José Yanahuara Electronic Engineer 29-Jun-55 Secretary 20-Nov-70 eng USA, Chile, Italy, Germany

In the first row, “ENG,FRE” are in ONE field. Similarly, “USA, Chile, Italy, Germany” are also in one field.

Thanks

Pramod Kumar

Ritesh wrote on Oct 30 2005, 08:38 PM:

Can you copy-n-paste 2-3 rows of data in the CSV file?

After looking at the data, I can correctly tell you the delimiter settings.

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

[Anonymous]

Thanks again for your advice. I will try using a tab seperated text file as suggested by you. Unfortunately, I can not try out the ODBC option because although I have MS Access but my SQLyog is a freeware version.

Pramod Kumar

peterlaursen wrote on Oct 30 2005, 09:01 PM:

Yes … that is the problem!

The escape character is for solving that!

If you export to a CSV with SQLyog you can choose an escape character.

For instance if you have the string

Code:

India, China, Denmark

and choose  , (comma) as the delimiter character you can choose for instance ^ as esacpe character.  Then the CSV file would look like

Code:

row1data,row2data,India^, China^, Denmark

and SQLyog can handle that. The escape character means that the following charcter should be written

But unfortunalety you don't have the chance to use an escape character when exporting form Excel.

There is an option in my Excel 2000 to use ; (semicolon) as seperator when saving to a CSV.  It would then look like

Code:

row1data;row2data;India, China, Denmark

and that will work if there are no ;'s in the data!  You will need to find a delimiter that is not in use by the data if you shall avoid manual editing!

You can also export as a tab-seperated text-file, and rename the file to csv.  The delimiter setting is then t  (Platform-independent C-syntax for 'tab') with SQLyog. 

If you still have the Excel-file I propose you experiment with the settings for export!

Personally I prefer quite another way of transferring data from Excel to MySQL and NOT to use CSV at all. 

Open the Excel-file from MS-Access (if you have it) as a 'datalink' and either

1) export from Access to MySQL using ODBC (the MySQL ODBC-driver must be installed and configured with an instance pointing to the MySQL database where you want it)

or 2) Import into MySQL with the SQLyog Enterprise migration tool (this uses the MS ODBC-driver for Access)

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

[peterlaursen]

And how many columns have we here ?

1) Arequipa Arequipa Cesar Acurio

2) Arequipa Arequipa Guillermo Viñas Luisa Balboa

[peterlaursen]

EDIT: no need that I should repeat myself 😀

[Anonymous]

It worked! It worked!

Thanks for the tip. As suggested by you, I exported the Excel file as a tab delimited text file and renamed it as a csv file. The renamed csv file looks quite a jumble, but when imported into MySQL it seems to be OK. I have yet to check all the fields thoroughly, but prima facie things are OK.

Thank you very much for this clever tip.

Pramod Kumar

peterlaursen wrote on Oct 30 2005, 09:01 PM:

Yes … that is the problem!

The escape character is for solving that!

If you export to a CSV with SQLyog you can choose an escape character.

You can also export as a tab-seperated text-file, and rename the file to csv. The delimiter setting is then t (Platform-independent C-syntax for 'tab') with SQLyog.

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

[Author]

Posts