Automated Access Import Of Linked Tables

[jbergholm]

Sorry, the title should actually read:

Automated Import of Linked Access Tables.

I noted in the forum that this had been discussed back in 2003 but i didn't see any recent mention of it. using the migration toolkit i would like to be able to set up an import of access linked tables. imported tables show up in the wizard whereas linked tables do not seem to.

Actually what's happening is that i'm getting an .xls output from quickbooks, doing some data transformation (date into proper yyyy-mm-dd format) and then exporting the data to .csv in a special folder where it is automatically linked as an access table.

first i tried to suck in the .xls workbook by creating a system data source ODBC connection, but in the migration wizard when i get to the point where i'm supposed to select the workbook, it doesn't show up.

so then i output the .xls to tab-delmited .csv file (some of the data is address data which contains commas). created an ODBC connection for that. the wizard is able to see my .csv files and i'm able to make it “guess” the fields properly but when i press OK to define the format i alway get “failed to save the table attributes of (null) into (null)”. so there i'm stuck.

i have no problem importing the .csv file manually. i just wanted to automate the process so i don't have to do it every month.

any suggestion on automating the process at any point along the line would be most appreciated.

thanks!

john

[peterlaursen]

Can you use this – much simpler – proposal:

You don't need to to link the .CSV's in Access. Simply export from Excel as comma-seperated (semicolon seperated in some countries! http://www.webyog.com/faq/14_88_en.html )

next put all the CSVs in one folder and adress a DSN to that folder from ODBC configuration. The text-ODBC-driver identifies each supported file as a 'table', and the Migration Toolkit lets you import from the folder. Those files (if any) using same SCHEMA can be imported to the same MySQL table if you choose to append data with the Migration Wizard.

This way of doing things is used regularly by some people importing data written by 'low-level' hardware such as industrial PLC's. A few weeks ago I had contact with a customer importing almost 2 GB's of data (about 200 CSV-files) at intervals this way. 100% automatic.

An no … the ODBC driver won't read a Excel docbook!

[jbergholm]

peter, thanks for your quick reply. you suggestion is pretty much what i tried first, except that i made it a tab-delimited .csv file due to the fact that one of the columns holds product descriptions which contain commas. i'll give the semicolon delimiter a try.

-j-

[jbergholm]

peter, from the link you sent me earlier, i found this:

2) Consider using ODBC instead. For instance an Excel data file (.xls -file) can be opened from Microsoft Access as a 'datalink' and you can transfer data using ODBC. Then you even don't have to define the columns in advance if the spreadsheet contains the column names in the upper row. ODBC does that for you. And with the SQLyog ODBC/Migration tool you can adjust the datatypes to your need in the import proces.

this is exactly what i was trying to do but have so far been unable to “see” linked tables in access. any suggestion?

[peterlaursen]

Well this

Quote:

2) Consider using ODBC instead. For instance an Excel data file (.xls -file) can be opened from Microsoft Access as a 'datalink' and you can transfer data using ODBC. Then you even don't have to define the columns in advance if the spreadsheet contains the column names in the upper row. ODBC does that for you. And with the SQLyog ODBC/Migration tool you can adjust the datatypes to your need in the import proces.

.. seems not to be so easy! I can then EXPORT from Access (using MyODBC) not IMPORT to MySQL (Using Access-ODBC). But that won't automate things! I'll have to check up on that!

Didn't the direct text-ODBC-import work with comma-CSV's ?

[peterlaursen]

researched on it. Two conclusions:

1) This http://www.webyog.com/whitepaper/Preparing…__Migration.pdf also applies to 'linked' tables.

2) However, after unhiding the metadata, the ODBC-driver does not find any tables in the database! The ODBC-JETengine chain does not seem to be able to resolve OLE/DDE links.

So you can't neither import or export a linked Access DB. But of course you can IMPORT the data from EXCEL into ACCES, save as a .mdb-file and then both import (with SQLyog Migration Tool) and export (from ACCESS) provided that you have the needed ODBC-driver for it.

But the direct migration of CSV's is easier. There also is a problems with EXCEL (and other spreadsheet programs) that it like to 'format' data. For instance any data that can be interpreted as a DATE, will be!

I wrote up the FAQ accordingly http://www.webyog.com/faq/28_73_en.html

[jbergholm]

thanks for all your help in researching this, peter. as i mentioned, i could not use comma-delimited .csv files but used instead tab-delimited and was never able to make that work with the migration tool. it kept telling me that error that i attached last time…something about not being able to read [null] into [null]. on trying a different file (this would have been a monthly output of sales data) i'd get thru the defining of the format in ODBC but when i get to the point (in the migration tool) of choosing the file i had used to define the format (in the ODBC administrator) it would tell me that no data was being returned from that particular file. so if i attempted to run it on one of the other monthly dumps i was't getting all my fields…only three out of about 15 showed up as F1, F2, F3 and then when i pressed forward and ran it any ways it erred out saying there was an invalid F1 key in the first field.

seems like it should have been a straight forward process. i suspect it's a data issue in the underlying .csv table.

my whole intention was so get dumps from quickbooks so i could do sales reports in crysal reports that i was unable to do directly in QB. in the end i've found an easier if more expensive solution. A $150US ODBC driver that allows me to ready the Quickbook tables directly without all this output and transformation…duh! why didn't i think of this before?!

again, thanks for all your help.

-john-

[Author]

Posts