Import Is Too Hard

[KimWill]

I have been spending too much time trying to get a simple OpenOffice spreadsheet into a mysql table.

3-hours to experiment with different combinations of csv settings it too hard.

I used to be able to do this effortlessly with MySQL-Front (now defunct).

I read the comments on several (admittedly, not all) posts in this forum.

I am trying to update material prices and labor in a spreadsheet (OpenOffice Calc) save as csv then import into my table through SQLyog. I even tried open the csv file in notepad and Crimson Editor see if I could find some formating issues … none.

I will figure this out and I will be fine — but it IS too hard.

There are other MySQL tools out there which do this easier. I do not want a separate tool for every task…

Why can't SQLyog make importing less complicated? believe it or not – I do like SQLyog Enterprise – QueryBulder, copy tables to other db's, backup and restore has saved my butt!

SQLyog could be more user-friendly, less DBA skill required

P.S. I read the FAQ (http://webyog.com/faq/28_73_en.html) – I am not using MS office products.

[peterlaursen]

please attach

* a small sample CSV file (a few rows, a few columns)

* a screenshot of how the same displays in OpenOffice Calc

[peterlaursen]

It took 1-2 minutes for me with OpenOffice Calc 2.2.1. See attached 'cartoon'. But OK this is a very simple example that uses the OO default save settings only (and does not use decimal numbers). In this OOcalc version you define the data seperator and the enclose character for strings when saving by selecting 'edit filter settings' (translated from Danish). If you have decimal number using Continental European decimal notation, I noticed that OO convert them to strings in the CSV if you do not select another seperator character than ','.

There really are lots of different CSV format 'variations' that we need to support! It is CSV that is the problem, not SQLyog I believe. It would be nice if it was simpler, but there there would be situations that could not be handled correctly if we did not provide the options that we do.

We provided the 'Excel Friendly' option for ease of use, but I can tell that I opposed this internally because it DOES NOT WORK with Continental European decimal notation! That illustrates the conflict: making things easy introduces errors!

BTW: I also verified that it work the same with ANSI/UTF8/UTF16 encoded files

[peterlaursen]

additional comment to image #2:

It is of course the settings in the SMALL dialogue box you should notice.

[KimWill]

I do not see my last reply – Maybe after I previewed my post, I exited – oh well.

I wanted to thank PeterLarsen for your prompt reply.

My problem is fixed – Here is what I discovered

1. I cannot import the column names… you can export them but you have to remove the column names from the csv file before you import. I guess everyone else knows this … I did not not. The data was imported but so were the column names and MySQL did not like it

2. I exported a record set from a database type on a production server, added a column and inserted new data using Oo Calc 2.2 then I saved as csv and went to import the data into the prototype server database. Calc does not show or apparently care what the datatypes are.

Once I matched the data-types to the production database everything went fine.

It just seems a bit weird to discover this on a csv import process. The Good News – I am wiser now and I hope this helps others.

Summary – remove column names on your import csv file and make sure your data-types are correct.

Thanks,

Mr. KimWill

[peterlaursen]

1) Did you notice that if the .csv contains one or more header lines with column names and similar information, there is an option to skip 'n' number of lines in the import dialogue?

2) With SQLyog (Enterprise) Migration Tool you can import using the Microsoft text-ODBC-driver (skips with Windows). This one will create the column names and types for you (but of course you will sometimes have to adjust it!)

[Author]

Posts