Automate The Import Of .txt Files

[thomas7263]

I am looking for a way if possible to automate the import of .txt files into my db using sqlyog. Currently, I go through the manual process but I have about 500 files to import. They all set in the same directory. Is there a way to automate this?

[peterlaursen]

Absolutely YES! 🙂

You will need to use the SQLyog Enterprise version (demo available). You can the use the Migration Tool with the Microsoft text-ODBC-driver. It will recognize each supported file (.csv, .txt and maybe more – don't remember exactly) in the same folder as a 'table'. If the data from all files have identical structure and shall be imported into ONE MySQL table, you may configure the Migration Tool (with the wizard) not to empty the table for each file to import.

Check it and play around with it a little … You must ensure the the text-file format can be read correctly by the ODBC-driver!

If you need it I can paste in the most important pasages from a SJA XML file to use. I have such sample somewhere on my system from a customer importing about 2 GB's of data from around 200 CSV-files at regular intervals. This job ran for some hours on my system 🙂

You may also read: http://www.databasejournal.com/features/my…p/10897_3550146. A few more options have been added to the tool with later versions but it is still basically the same.

And finally be aware of this issue with the driver: http://webyog.com/faq/17_88_en.html

[thomas7263]

Thank you for your help Peter.

This appears to be exactly what I need. However, I am having issues. My files are .txt tab delimited files. I am using the Microsoft text driver. SQlyog is not recognizing the tab delimited format. When importing manually you tell it what the delimiters are. Here I don't see that option. As a result the first row on my 'Source' column shows all of the column headers from my .txt file. I hope this makes sense.

[peterlaursen]

The M$ text-ODBC driver does not have all the options that SQLyog CSV-import have. You have the configuration options available from the Windows ODBC-manager. However it should recognize tab-delimited files when configured correctly. Try the settings -button in ODBC-maanger (see screenshot – in my language (Danish) it is 'indstillinger')

Quote:

SQLyog is not recognizing the tab delimited format

Wrong. It is the ODBC driver that does not recognize! But did you configure it?

[peterlaursen]

Correction: it is the button named 'Definer format' that opens the delimiter setting et. al.

[thomas7263]

Beautiful!

It works. Thanks, this will save me a lot of time. One last question before I pay for the full version of SQLyog Enterprise. The trial version only lets you load one source at a time. In the full version when I change the mapping will it apply to all sources? If not I would have to redo the mapping for all 500 files. Once again your help is truly appreciated.

[peterlaursen]

Quote:

In the full version when I change the mapping will it apply to all sources?

Yes this is a restriction with the demo version. It lets you only import one table at a time.

And I verified this myself recntly with the cutomer data that I wrote about. 196 (to be exact) files imported with one job. It ran for 11 hours on a 1.5 Ghz computer here – while I was sleeping most of the time. 😀

[thomas7263]

peterlaursen wrote on Feb 18 2006, 01:29 PM:

Yes this is a restriction with the demo version.  It lets you only import one table at a time.

And I verified this myself recntly with the cutomer data that I wrote about.  196 (to be exact) files imported with one job.  It ran for 11 hours on a 1.5 Ghz computer here – while I was sleeping most of the time.  😀

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

I hate to bug you again since you have been so helpful but when I get to final stage of the migration wizard I can't find a way to apply my mapping changes to all files. I make the change to the top row but I don't see an 'apply to all' option or a way to copy this mapping down to the other 500 files. Similarly, by default the source and destination are the same. From the looks of it I will have to change the destination to my table for each of the 500 files.

[peterlaursen]

well .. yes this is true.

Maybe we should improve the wizard in this respect.

I don't thin the wizard was designed with this special case (multible CSV/text) in mind.

Couldn't you possible just use the defaults/driver proposal and ALTER TABLE after import?

Find a sample XML attached (the 196 table thing). But 500 is quite a deal.

But hold on .. I am in the proces of unzipping my test data .. 🙂

[peterlaursen]

First .. I am sorry, it seems to be the wrong XML file! This one should be better

Second .. you are right. We need a GUI improvement for 'multible csv/txt import'

Third: I think the easiest will be

1) create an empty table in advance to import to

2) now the Wizard will use the mapping of this table. Choose import into it (not delete for each table!) for first table only

3) generate an XML file using defaults for TARGET for rest 499 of tables

4) Use 'search and replace' in an editor to correct those 499 entries

[peterlaursen]

I produced a cartoon for you. Usually that is no good when Danish people do this, but hopefully this one won't offend anyone! 🙁

Destination: I think the easiest will be to select from GUI

Map: are fetched from table imported to

Advanced: can be searched and replaced from an editor

Where: left empty

@ritesh: GUI issue: For import of more SOURCE tables into ONE DETINATION table with identical maping like here there could be an option to fill in only one row and 'apply to all'.

…. hmmm .. but HOW precisely ?!

[peterlaursen]

@ritesh:

After thinking a little I think the solution to this is to add a SPECIAL wizard for migration of more ODBC-tables into one. Mostly relevant for text-files but not only that.

It will only need one row of fields/buttons in the import-settings windows of the wizard!

[Author]

Posts