Multiple Files With Import External Data Tool?

[smine]

i have 50 .csv files in a subdirectory. i want to import them all into a single table (it already exists in database, but if it is easier to let import create a new table that is okay). i cannot figure out how to do this, each file creates a new table. is it possible to import all into a single table? i am using Ultimate 8.22

[peterlaursen]

I do not know what SQLyog version you are using, but since version  8.2 there is an option to apply settings to all tables with a single click.  This was actually implemented first of all to make it easy to import a large number of CSV's from one folder in one operation

The settings you need to use are basically to 'import into existing table' and 'dont overwrite'.  Then data for each CSV will be appended to the same table (the text ODBC-driver identifies a file of supported type as a 'table').

Did this help?

[smine]

thanks. i am using 8.22 and 8.3 beta 1. i found the settings you describe (in Select tables > Advanced > DDL/DML Options). but i still have problems.

• i cannot find “don't overwrite” option[*]when i click “Select All” to select all tables, the Destination is automatically filled with a table name based on the CSV filename. i change it for one table then click “Apply changes to all tables” but the Destination does not change. do i have to manually change every Destination table name?[*]if i do set the Destination of each file, the import appears to work for the first table, but other tables fail with these errors (slightly edited)

DBMS Information: TEXT
Importing table schema: Discrepancies… Successful…

Importing table schema: Discrepancies… Successful…

Importing table foreign keys: Discrepancies… Successful…

Importing table foreign keys: Discrepancies… Successful…

Importing table data: Discrepancies…

993 rows transferred!

Successful…

Importing table data: Discrepancies…

ERROR: 1054, Unknown column 'F1' in 'field list'

Table:Discrepancy_Counts_By_Status_20091110.csv

Sql:REPLACE INTO `qa_test`.`Discrepancies` (`F1` ,`F2` ,`F3` ,`F4` ,`F5` ,`F6` ,`F7` ,`F8` ,`F9`) VALUES ('date', 'RStatus', 'Product', 'Project', 'DevAssign', 'QAAssign', 'Subject', 'Modified', 'Sent'),…

(note that i am not Mapping any fields and i am not using WHERE for any of the tables). i must be missing something. i read the Help file but i guess it didn't help me enough

[smine]

i don't know if this is a different problem, but before i call it a bug i will check with you. when i Import External Data and go to ODBC Text Setup > Options > Define Format, when i click Ok i get a popup Error. maybe i don't know how to use this dialog box?

[peterlaursen]

Let me reply to the last question first.  This error is returned from the ODBC driver – not from SQLyog. 

We will provide more guidance on where to find the options in the wizard.  However you should be able to specify a *folder*and all supported files in the folder will be recognized as tables

[Khushboo]

Hi Smine,

Quote:

1. i cannot find “don't overwrite” option

You can find an option in Import External Data –> Advanced Option –> Keep Target Data, if you check this then on duplicate data, Import will generate INSERT statements to add data into existing table, means it will not overwrite any data.(see the screen-shot)

Quote:

2. when i click “Select All” to select all tables, the Destination is automatically filled with a table name based on the CSV filename. i change it for one table then click “Apply changes to all tables” but the Destination does not change. do i have to manually change every Destination table name?

Yes, you need to manually change every Destination table name.

Right now Import External Data doesn't have any option to select a “Common Destination Table” for all files. But its a valid feature request and we will discuss about this.

Quote:

3. if i do set the Destination of each file, the import appears to work for the first table, but other tables fail with these errors (slightly edited)

ERROR: 1054, Unknown column 'F1' in 'field list'

The error occurs because there is difference between the fields of table created by 1st .CSV file and the fields which are present 2nd .CSV file.

1. Please use “Keep Target Data option” instead of “Update with Source Details” option.

2. You can check the Import External Data –> Column Mapping section to see the difference in the fields of .CSV files.(see the screen shot)

3. Please try to import two or three files in different tables and provide us with the table structures of the created tables. This will help us to get the exact problem which you are facing.

Thank You.

Regards,

Khushboo

[Khushboo]

Hi,

We added the feature request “Common Destination Table for all files” in our issue tracker:

http://code.google.com/p/sqlyog/issues/detail?id=1245

We will discuss this and prioritize accordingly.

Thanks & Regards,

Khushboo

[smine]

'Khushboo' wrote on '23:

The error occurs because there is difference between the fields of table created by 1st .CSV file and the fields which are present 2nd .CSV file.

i am baffled. when i try to import the CSV files then i get the error described in item 3 above (and the table name i specified to import into is ignored and a table name based on the input filename is generated). but if i simply rename the files then the import succeeds! here are the actual filenames

FAIL

Discrepancy_Counts_By_Status_20091109.csv

Discrepancy_Counts_By_Status_20091110.csv

SUCCEED

Discrepancy_Counts_By_Status_20091109.csv

xDiscrepancy_Counts_By_Status_20091110.csv

SUCCEED

xDiscrepancy_Counts_By_Status_20091109.csv

Discrepancy_Counts_By_Status_20091110.csv

SUCCEED

Discrepancy_Counts_By_Status_20091109.csv

Discrepancy_Counts_By_Status_20091110x.csv

SUCCEED

Discrepancy_Counts_By_Status_20091109x.csv

Discrepancy_Counts_By_Status_20091110.csv

i do not modify the file in any way, i simply rename it. how is this possible?!?

[peterlaursen]

no clue! 

[Author]

Posts