Import multiple excel files into single access table

[Lightwave]

Hi dS

It is undoubtdley possible - in Access 2003/07 you can save import and export specifications that allow you to determine whether the first line is imported and what other columns are imported in what format.

It will require alteration to the code that I can't completly do off the top of my head.

One thing which could be a dead end is if all the different txt files that you want to combine constantly have different ordering.

For example
If 1.txt
has fields
phone/firstname/surname

but 2. txt
firstname/surname/phone

etc
that could make things complicated

I will take a look if I have time this weekend(I probably won't) but I'll find an appropriate thread which has something on it for your background reading.

 

[Lightwave]

I don't know which version of Access you are dealing with but Access 2003

Import specification example is here....

http://www.btabdevelopment.com/main...teanImportSpecification/tabid/98/Default.aspx

This is the instructions for Access 2007

http://www.btabdevelopment.com/main...teanImportSpecification/tabid/99/Default.aspx

The import specification you save will be used with DoCmd.TransferTxt Command

 

[dSurvivalist]

hello Lightwave!

The files i import are completely identical. they begin like below;

VPLHDREAAFB 20100423
VPLISTAN0AT0363700D 786 AB 001.00000
VPLISTAN0AT0364966G9N 3K185 BJ 001.00000
VPLISTAN0AT0365369T1H 19E647 BJ 001.00000
.....
.....
.....

The first line of the texts always begin with the same line but the "20100423" part changes as date.
when the next file begins to import, maybe i can have a chance to create another table and another for each of the text files following? by that way, i could create a macro to run after finishing import of all files and use an append query in it to join the tables. This now seems the best solution, because i will have to add the date fields next to the records in a query to ensure all the lines have the correct date string for the rest of the job.
how can i import each file in different tables and name them accordingly?

 

[dSurvivalist]

I managed to import all the files with using the spec created for these files, by using "SpecName" here below,

DoCmd.TransferText acImport, "VPL", "VPL", ImportFile, True

all the files with given criteria are imported to a single table now. but the first lines of the text files which were represented as field names before, are now dissapeared. i'll try to have them in the table as records as well.

LWave your information has been very helpful, and let me move on to further steps and learn alot... thank you!

 

[pkstormy]

Here's a routine which let's you select a folder and will import all the xls files from that folder into the mdb (all you do is select the folder using the Browse button.)

 

[dSurvivalist]

hi pkstormy,

Your db file seems quite useful, thank you for sharing on the issue. i will study it and try to edit the codes to be able to import .txt files instead.

 

[HGMonaro]

dS, if you want to capture that 1st row of data, change your import specification so that it doesn't expect fieldnames (not sure if you've done that). If that imports ok then do the following:

in the loop retrieving the files in the folder:
- Use a delete query to empty the import table
- Import the file into this table using the import spec
- Use a query to add the date (maybe into another tempory table using a Make Table Query)
- Append this table to another table that will hold all the data

something like that! Just tried the import part and it worked fine (1st row is in data)

 

[Morrel]

I have a problem:

I want to import data from multiple excel sheets into 1 table in my database. The problem is that the excel sheets allready have a connection to another database for various data. The excel sheets are merely for making calculations.

So when i change data in the database it should change in the Excel file but it should change in the other database as well without having to open al the excel sheets.

To sum up the problem.

• I need a solution that can:
• Automatic change data from a database to multiple excel sheets to one other database. this without opening all of the excel files.
• Each excel file should be considered as one record in the new database
• This should happen by using a save button in the excel file

I really suck at programming but if someone could give me a clue how to do this you would be very welcome