importing Excel files - how to add additional columns?

[namliam]

There are also other ways like working with a recordset.....

The simplest and fastest way is to do it with the append query directly, which is fine if you have limited amount of work to do... Though the replace method like I said gets messy because of all the replaces you need to do. I gave you this method because it is the first step to do when you have very limited experience with this type of thing... simply becuase you need not know anything about SQL and/or coding much...

This basicaly is "version 2.0" where we are writing the actual SQL "on the fly" and replacing in the values you need.

Keep in mind for future use the line continuations ( _ ) are maxed at 25 lines, if you go over 25 lines you stop your continuations and either make a new variable or continue by appending to the existing one by doing something like:

        strSQL = strSQL & "More sql here"

Your import code now looks like....

Function import()
    Dim strFile As String
    Dim strSQL As String
    Dim rsC As dao.Recordset
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM DataStaging;"
    DoCmd.RunSQL "DELETE * FROM Data;"
 
'   Set file directory for files to be imported
    strPath = "C:\test\"
'   Tell it to import all Excel files from the file directory
    strFile = Dir(strPath & "*.xlsx*")
 
'   Start loop
    Do While strFile <> ""
        ' Import file
        DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="DataStaging", FileName:=strPath & strFile, HasFieldNames:=False
        Set rsC = CurrentDb.OpenRecordset("Select F1, F2, F3 from DataStaging where F1 = ""Customer""")
        strSQL = " INSERT INTO Data ( F1, F2, F3, filename, customername ) " & _
                 " SELECT DataStaging.F1 " & _
                      " , DataStaging.F2 " & _
                      " , DataStaging.F3 " & _
                      " , """ & strFile & """ AS FileName  " & _
                      " , """ & rsC!F2 & """ AS CustomerName " & _
                 " FROM DataStaging;"
        CurrentDb.Execute strSQL
        ' Loop to next file in directory
        strFile = Dir
        rsc.close
        set rsc = nothing
    Loop

End Function

I hope you can see what is going on here and can work with this...
This is the way you work with text variables.
For Dates: " , #" & strFile & "# AS FileName " & _
For numbers: " , " & strFile & " AS FileName " & _

 

[illusionek]

Many thanks for all your help and above code. I understand some of it and rest I am going to google so I know exactly what's going on there. But when I run this code the CustomerName is not updated properly. Code runs without any problems at all but CustomerName is test for all records, whilst it should be test for file 1.xlsx, test 2 for 2.xlsx and test 3 for 3.xlsx.

Do I need insert somewhere an additional line of code to update records in staging table before inserting to proper table?

 

[namliam]

No, the problem is that the staging table is (supposed to be) a temporary table...
I however wanted you to experience it and notice it yourself
You should also notice that the data from the first 2 excel files are getting duplicated into your perminant table...

What you want to do after running the Insert into query, is delete your temporary table's content.
currentdb.execute "DELETE * FROM DataStaging"

You may also want to change the column names from F1, F2 to something more usable.
And not include the "non-information" of the first 2 lines from your excel file by adding some where clause(s) to your append query.

Also if you always pull the excel files from your same database location, currentproject.Path, will return your database's current folder for you

Also, there is one drawback of having staging tables... something called Bloating.
The records that are added to the table increase the database's size, once deleted this space isnt automaticaly released again. This ultimately can lead to a huge database with nearly no data in it.
2 ways to fix that
1) have a backend database that you can compact on demand. There are quite some samples to be found for this, but is kindoff "version 2.0"
2) have your database be compacted everytime you close it. Quite easy to do. Simply go to File > Options > Current database and tick the "Compact on close" box

 

[illusionek]

Many thanks for pointing out duplicated files, I have not noticed it until now

I will change F1, F2 etc to more meaningful names, it was used for testing only.

Would you be able to recommend anything to read, so I can learn to do all this magic myself instead of bothering others? Currently I am doing online SQL course but by the look of things perhaps I should focus on VBA?

I would like also to say a big THANK YOU for all your help and patience.

The code works perfect now.

 

[namliam]

Would you be able to recommend anything

Nothing in particular, only thing I can recommend is what I made you experience..... Be critical and be inventive, it is all about applying the different piece of the last puzzle in a different way in a new puzzle to make a 3rd puzzle. dont think it makes sence, but its all about creativity, (ab)use the help, (ab)use google, (ab)use the database.

for example dont expect me to have writen the SQL, sure I probably could have... but I didnt, I (ab)used the designer to do my work for me and some imagination