Importing Multiple Csv Files In Sqlyog

[ndirgo]

Hi,

I have a large # of .csv files to import into my database. Is there a way to import multiple files through the SQLyog GUI?

Thanks….. 😮

[peterlaursen]

There is with the ENTERPRISE version!

You can then use the MIGRATION tool with the Microsoft text-ODBC-driver. Simply copy all the files to the same folder, configure the ODBC driver to point to the folder and each file will be identified as a 'table' by the driver.

In case the .csv-files are identical in structure and should be imported to the same MySQL table you just set DML/DLL options accordingly (not to empty table before import). You can test with the ENTERPRISE TRIAL .. but only test, as it is a restriction with this TRIAL that it only lets you import one file per job.

[peterlaursen]

Well .. actually that became a FAQ:

http://www.webyog.com/faq/28_110_en.html

[ndirgo]

peterlaursen wrote on Jun 5 2006, 02:23 PM:

Well .. actually that became a FAQ:

http://www.webyog.com/faq/28_110_en.html

Thanks for the help. I have been trying since yesterday afternoon to get the Migration Tool to work correctly. I used the ODBC driver as you stated and put all of my files in one directory. I get to the screen where it shows me all the files and if I select one, it does not map to my table in the database. I would think I would see the tablename in the dropdown.

The next problem I had today was when I selected one file, it told me the object or database was read-only. I checked the properties of the file and it is R/W.

Any more advice you can give me or turn me to some doc on the Migration Tool?

[peterlaursen]

1)

“and if I select one, it does not map to my table in the database”

I do not understand! You need to explain a little more in detail.

Does the MySQL table exist in advance? Are you sure the structure and the data fit?

2)

“it told me the object or database was read-only”

I think that is because this ODBC-driver does not allow for write.

I do not understand why this is a problem? You cannot use 'triggers' and that is it.

Could you provide a table definition and a sample file with a few data?

[ndirgo]

peterlaursen wrote on Jun 6 2006, 10:56 PM:

1)

“and if I select one, it does not map to my table in the database”

I do not understand! You need to explain a little more in detail.

Does the MySQL table exist in advance? Are you sure the structure and the data fit?

Yes. I have imported one of these files individually into the table through the GUI and it works.

2)

“it told me the object or database was read-only”

I think that is because this ODBC-driver does not allow for write.

I do not understand why this is a problem? You cannot use 'triggers' and that is it.

Could you provide a table definition and a sample file with a few data?

I do not know wh you mean the the ODBC-drive does not allow for write. Here are my steps:

1. I have already set up a table in my MYSQL database. I have tested an import of one file with the correct delimters and it works fine.

2. I open up the Migration Tool and start a new session selecting my database to import into.

3. I think select System/User DSN and select the ODBC Datasource I set up with the Microsoft text driver and select the location where all of my files to import are.

4. Click next to copy tables from datasource.

So far so good, I think.

5. My files show up on the next page, but this is where I am confused. Source is the data I am trying to import and destination should be the table in the database I want to import into, correct? I don't see that table in the drop down (actually no tables) and so my file cannot be mapped to a table to do the import.

Does this help??

[peterlaursen]

“I don't see that table in the drop down (actually no tables)”

did you check the checkbox for the/each table you want to import?

See attached!

I have two .csv-files in a …/My Documents/test/…folder. The ODBC driver points to that folder.

I am importing into a database with four tables named 'capuser','capuser_copy', 'master_projects' and 'table' and the dropdown shows all of them.

Next issue could be the mapping of columns if you do not have a header line in the CSV-file. The driver is not working very 'smart' then. That is why I wrote that you might then want to save and edit a job-file!

… do you have a header line?

BTW: What I meant was that I do not think that the text-ODBC-driver lets you write to the source files (but I never tried actually …)

[ndirgo]

peterlaursen wrote on Jun 7 2006, 02:59 PM:

“I don't see that table in the drop down (actually no tables)”

did you check the checkbox for the/each table you want to import?

Yes and that is when I get the Read error. I have one table in my db called iCDR which I want to import into. For some reason that table does not show up.

See attached!

I have two .csv-files in a …/My Documents/test/…folder. The ODBC driver points to that folder.

I am importing into a database with four tables named 'capuser','capuser_copy', 'master_projects' and 'table' and the dropdown shows all of them.

Next issue could be the mapping of columns if you do not have a header line in the CSV-file. The driver is not working very 'smart' then. That is why I wrote that you might then want to save and edit a job-file!

… do you have a header line?

BTW: What I meant was that I do not think that the text-ODBC-driver lets you write to the source files (but I never tried actually …)

No I do not have a header line. I didn't realize I needed one. how do i create this?

[peterlaursen]

1)

I cannot explain why the table does not show up in the dropdown.

And not the READ ERROR either. But I understand that this READ ERROR occurs when SQLyog tries to read from MySQL.

If it shall be possible for me to work on it I must have a dump of the database structure and a sample CSV-file like yours (could be with 'dummy' data).

I suggest that you try importing into an empty database. Table will be created. You can 'map' from the tool (preferred) or you can ALTER TABLE after import.

But I still would like to see if I can reproduce this READ ERROR. Can you dump the database structure?

2)

The header line is 'practical' because then the driver and the tool will by itself proposes to import into columns named identically to the names in the header and you won't have to type anything.

How did you generate the .CSV's? From what sort of program? Some programs have that option. MS-Excel does for instance.

If you do not have the header line you will need to type the name of each column name. And if you have more files to import into one file it probably will be fastest to create import of a single file with the wizard and add table no. 2+3 etc. by editing the jobfile (it is basically just a 'block' of lines that must be dublicated.

[Author]

Posts