How do I import and Merge data

[Tim L]

I have exported 4 tables (my 'core' tables) from my database using TransferText\Export Delimited.

I now need to import the file(s) back into the same tables (either as back up or as part of the process to distribute the data - i.e: multiple users require the data (and records could be updated by the different users) but we only have sneakernet).

When I try and import the text file(s) using TransferText\ImportDelimited I get the error message:

Field 'F1' doesn't exist in destination table '<tableName>.'

<DatabaseName> was unable to complete the append operation. The destination table must contain the same fields as the table you are pasting from.

The tables have only been exported, not edited, they should therefore have same number of fields... Also, there is no mention of a field 'F1' in my text file (I never defined one called that).

I tried using RunCommand\Import, but this just starts the dialogue boxes that ask for the file name and then the rest of the wizard, which, causes errors due to duplicate key values, but still manages to produce the correct data in the table (i.e: merges new data in - but, I presume, could also overwrite current data if there are duplicate entries).

I would like the import process to be automated but do not know how to acheive it, can anyone help?

Tim.

ps: A similar post to this was previously made to the 'Macro' forum but has since been deleted as I felt that it was in the wrong forum.

 

[chenn]

If this is a one time only thing, it may be easiest to use the "Bulldog" approach. Import the file into a new table, and then run an append query to put the data into the correct table.

If you run code to do this and use it multiple times, you have to use the "Finesse" approach. One problem that I've noticed with excel, is that blank space causes problems. What I mean is best decribed by example: If your data has three fields, Address1, Address2 and Zip and the user puts a blank space into undefined column "F" access thinks that there is actually data in that field and trys to import it. One way I get around this is to save the file as text and import the text file into the database. Of course you have to first create an import specification.

Have fun.

 

[Tim L]

Unfortunately the transfer of data is going to be a regular occurence, weekly for example. It will also be done by staff that are not necessarily au fait with importing and exporting the data.

I can draw up a flow chart and set of instructions to follow, but this is hardly ideal.

The data can be exported without any problems, and apart from messages warning of duplicate keys, can be reimported using the "Bulldog" approach.

If this approach could be automated, and the error messages switched off (which I believe is the easy bit...) then the users could transfer the files between the computers with only the need to click a button or two on a forms to carry out the data transfer operation. (Provided they placed the data in the right folder....)

Any clues as to how I'd go about the "Finesse" solution?

Tim

 

[chenn]

Since this will be done regularly, I would advise against the "bulldog" approach.

Finesse:

First, you would need a function to automate excel and save the file as a text file. This may be the hardest part of everything and depending on your coding experience, it may be best / easiest to have the user save the file as text in the first place.

Access could easily import the file through a couple lines of code.

You are correct though, the user would have to have the files saved in the correct place, unless of course you wanted to access the common dialog box/api and have the user browse to the files....

 

[Tim L]

Excel? Er, it's not been used at the moment, should it be? All parties will be using Access 2000 to manipulate and retrieve the data...

If Excel has crept in due to my mentioning of the Export\Text Delimited then apologies, I chose that for a few reasons, but none of them to do with using Excel: 1) It was there and seemed like a good idea; 2) it produces files that if not actually compact that can be zipped down considerably in the case of them getting too big (unlikely): 3) If really necessary the data could even be manipulated manually (unlikely, but having the opportunity is a comforting thought).

Being able to "access the common dialog box/api and have the user browse to the files...." would be nice, is it hard to code? This would provide that extra bit of functionalilty that would make the program more user friendly.

So, how do I go about coding all this then?

Tim

 

[chenn]

Here goes...

Using a delimited file is the easiest way to go about importing data. First you have to manually import the file to create an import specification - be sure to save it! Access will not prompt you to save the spec so it is up to you. Before you press the "Finish" button, press the "Advanced" button and save the spec. As far as importing the file, here is some code to get you started....

'first turn off warnings
DoCmd.setwarnings false

DoCmd.TransferText acImportDelim, "My Import Specification", "My Table", "c:\Location of file"

'turn them back on
docmd.setwarnings true

As far as the common dialog api, there is a wonderful article on the msdn site.

 

[Tim L]

Hi chenn, thanks for that, something for me to get my teeth into. Unfortunately I am now moving home so will not be able to much about the database for the next couple of weeks, be assured though that I will return to it and let you know how I get on.

Thanks again,

Tim.