Import multiple excel files into single access table

[mandeepdhami4u]

Hello,

I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.

I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.

Cheers,
Mandeep

 

[Uncle Gizmo]

I answered a similar question in 2004! Have A look here.

 

[mandeepdhami4u]

Thanks Uncle Gizmo, your solution will work for me, but have few questions.

1) I am not able to add any new tables in your file with the solution.
2) I am also not able to delete sample 10 tables that is there in your solution.
3) I am using Access 2002 (SP3), will your solution work without any trouble.
4) Will I be able to add and delete tables for your solution (i.e., I will not always have 10 tables....there can be any number of tables not fixed number every time I use your solution).

I have also attached my sample file. There can be many rows and columns in my real database.

Cheers,
Mandeep

 

[Uncle Gizmo]

>>>1) I am not able to add any new tables in your file with the solution<<<
>>>2) I am also not able to delete sample 10 tables that is there in your solution<<<

Sounds like the database is in read only mode, Open windows explorer and have a look at the file properties and see if the read only attribute is set.

 

[Uncle Gizmo]

>>>there can be any number of tables not fixed number every time I use your solution<<<

Change the code where the X = 10 to the total number of tables you want to loop through. You can either do this manually every time you use this code, or you could write a function that counts how many tables there are, if you do this please post the function as I will be able to add it to the solution.

 

[Uncle Gizmo]

>>>There can be many rows and columns in my real database<<<

This code will only work if your imported data has exactly the same structure.

 

[Uncle Gizmo]

I think your best approach would be to conduct a simple test first. Import three spreadsheets into your MS Access database, name the imports 1, 2 and 3. Select one of them and create a new table (structure only) and name it "z".

Then use the MS Access query builder to build a query to append records from one of your tables into this "z" table.

Now open the SQL view of the above query, and you should see an SQL statement that will resemble the one in the code, fit this new SQL statement in the code where appropriate and I think you'll find it will work.

Please bear in mind it was four years ago since I had any dealings with this code, so my Recollection may not be quite correct.

 

[mandeepdhami4u]

Thanks Uncle Gizmo for all your replies.

I am working on the same and will get back to you with what I am able to acheive.

Cheers,
Mandeep

 

[mandeepdhami4u]

Hello Uncle Gizmo,

I am able to run the file that you gave in your attachement with your dummy tables in it. I tried changing the codes so that it picks up all the columns that I have in my table but was not able to succeed.

Can you please help me with changing the codes. I have got many columns in my table (nearly 26 columns).

Cheers,
Mandeep

Here is the code for your quick reference:

Public Sub fConList()
DoCmd.SetWarnings False

Dim qdf1 As QueryDef

Dim strQryName As String
Dim strSQL As String
Dim strSQL_1 As String
Dim strSQL_2 As String
Dim strSQL_3 As String

strSQL_1 = "INSERT INTO Z ( ExpectationDesc ) SELECT ["
strSQL_2 = "].ExpectationDesc FROM "
strSQL_3 = ""


Dim x As Integer


For x = 1 To 10
strQryName = "qryAppend1"
strSQL = strSQL_1 & x & strSQL_2 & x

Set qdf1 = CurrentDb.CreateQueryDef(strQryName)
   
    qdf1.SQL = strSQL
   
    qdf1.Close
    DoCmd.OpenQuery strQryName
    DoCmd.DeleteObject acQuery, strQryName
   
Next x
    DoCmd.SetWarnings True

End Sub

 

[Uncle Gizmo]

Please post the SQL string version of the query that appends the records from table "1" into the "Z" Table.

read through my previous instructions on how to do this.

 

[JamesRichards]

I'm following this pretty well, however, I'm trying to also setup an automated way of looking for the excel info.

Here's what we're doing: We have a website setup to receive RMA reuqests from customers (Return Merchandise Authorization). The info gets exported into an excel file, and setup in a way we can manually import the info into Access. What i would like to do, is setup maybe an automated way to import each Excel sheet into access into it's own table, then consolidate the tables using Uncle Gizmo's idea... possible? Or no?

Any help or ideas would be appreciated.,

James Richards
IT Dept. www.imageWest.tv
800.673.8076
Las Vegas, NV

 

[dSurvivalist]

Hello Uncle Gizmo,

I need to import multiple text files with different names from a folder in my desktop, to a single table. could you share an example for this? i am really in need of this example, so i can learn it while using the sample. thank you.

 

[HGMonaro]

dSurvivalist,

I did a similar thing recently which you might be able to adapt.

I have a macro with the following

1. Set warnings off
2. Check user really wants to proceed (this replaces existing data so I give then a chance to back out)
3. If yes, then delete data in table using a query
4. run the procedure below
5. turn warnings back on


what the prodeure does in broad terms is :
- checks a specific folder for any files Excel that start with the text "File for"
- Imports each file (they are identical layout) into a specific table

the procedure that does the loading...

Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path, Folder As Variant
 
' get the DB path
DB_Path = getPath()
Folder = "Attendance\"
FileCriteria = DB_Path & Folder & "File for*.xls"
 
' create field with path and filename to import MAKE THIS READ ALL files that start with 'FILE FOR'
NextFile = Dir(FileCriteria)
 
' Check we have something to import
If NextFile = "" Then
   MsgBox "No files to import", , "Error"
   Exit Function
End If
ctr = 0
 
' Import each file that meets the criteria 'File for*.xls'
While NextFile <> ""
 
   ' count files imported
   ctr = ctr + 1
 
   ' add the path to the returned filename
   ImportFile = DB_Path & Folder & NextFile
 
   ' Import file into table
   DoCmd.TransferSpreadsheet acImport, , "Attendance Import", ImportFile, True
 
   ' get another file if it exists
   NextFile = Dir()
 
Wend
 
MsgBox ctr & " files imported", , "Attendance Import"
End Function

This GETPATH ('stolen' from this forum I think) function is use to determine the path of the database which is required to locate the appropiate folder (the database creates this folder if it doesn't exist on start up but the files to import must be located in it). This means the database can be moved to different drives without recoding this bit. If you don't want that you can simplify the above procedure and hard code the folder reference.

Function getPath()
Dim DB_Path As Variant
Dim strFullPath As String
Dim i As Integer
 
' get the name of the DB which includes the path
strFullPath = CurrentDb().Name
 
' loop around testing to see where \ occurs then use the left section up to that character
For i = Len(strFullPath) To 1 Step -1
    If Mid(strFullPath, i, 1) = "\" Then
       DB_Path = Left(strFullPath, i)
       Exit For  
    End If
Next
getPath = DB_Path
End Function

 

[dSurvivalist]

well thanks for the codes! though i didn't really understand how to use it and how to specify my data into the code. it gives me an error; Compile error, sub or function not defined.
the problem is, i don't know which parts of the code is to be adapted to retreive my files from my locations into my db file. which also means i am not capable of using these codes and language, but willing to learn.. any help with sharing an example file would be appreciated.

 

[Lightwave]

dSurvivalist

' get the DB path
DB_Path = getPath()
Folder = "Attendance\"
FileCriteria = DB_Path & Folder & "File for*.xls"

is the bit you will need to alter

You can change it for something like

'get the DB path
DB_Path = "c:\data\"
FileCriteria = DB_Path & "File for*.xls"

I've put the path "c:\data\" in here substitute with whatever location your files are in.

Doing this you can ignore the second window of code indicated by HGMonaro and is what he referred to when he said

"If you don't want that you can simplify the above procedure and hard code the folder reference."

 

[dSurvivalist]

I did just like you said, and both with and without the second code, when i run the mod, i have the error "File not Found" now.

i just uploaded the db1 file as i created. how could i handle it?

 

[dSurvivalist]

With this layout i receive error 3011, which is saying "the microsoft jet engine could not find the object c:\users\q\desktop\FTP\VPL111.xls . to make sure if the file exists the name is spelled correctly ...

Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path, Folder As Variant

' get the DB path
DB_Path = "C:\Users\Q\Desktop\FTP\"
Folder = "C:\Users\Q\Desktop\FTP\"
FileCriteria = "C:\Users\Q\Desktop\FTP\" & "VPL*.xls"

' create field with path and filename to import MAKE THIS READ ALL files that start with 'VPL'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
   MsgBox "No files to import", , "Error"
   Exit Function
End If
ctr = 0

' Import each file that meets the criteria 'VPL*.xls'
While NextFile <> ""

   ' count files imported
   ctr = ctr + 1

   ' add the path to the returned filename
   ImportFile = DB_Path & Folder & NextFile

   ' Import file into table
   DoCmd.TransferSpreadsheet acImport, , "Attendance Import", ImportFile, True

   ' get another file if it exists
   NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Attendance Import"
End Function
Function getPath()
Dim DB_Path As Variant
Dim strFullPath As String
Dim i As Integer

' get the name of the DB which includes the path
strFullPath = CurrentDb().Name

' loop around testing to see where \ occurs then use the left section up to that character
For i = Len(strFullPath) To 1 Step -1
    If Mid(strFullPath, i, 1) = "\" Then
       DB_Path = Left(strFullPath, i)
       Exit For
    End If
Next
getPath = DB_Path
End Function

 

[dSurvivalist]

good news! i can now import .xls files all of them to the same table, and they are being appended each time i run the code. i left the Folder here as blank;

' get the DB path
DB_Path = "C:\Users\Q\Desktop\FTP\"
Folder = ""
FileCriteria = "C:\Users\Q\Desktop\FTP\" & "VPL*.xls"

Can i also use this for importing .txt files? i will need to import .txt files everyday have names beginning with V*, B*, E* etc.

 

[Lightwave]

Yes you can

Apologies I'd forgotten about the folder I see the folder variant is referenced further down in the code.

You could probably take folder out but you would have to delete it further down the code as well.

 

[dSurvivalist]

ok here's the real challange for me; these files -since they are text files- do have different field names once they a re imported to the table, since their first lines are different. and i receive an error message and whole opration stops; 2391 field name does not exist in the destination table !

i need to import all the files in the destination folder, yes; but i need them to be appended as records, even the first lines
could you share how i can do it possible within the same code ?