importing Excel files - how to add additional columns?

[illusionek]

Hello!

I am using below Macro for importing Excel files into Access. The code works very well but unfortunately now I need to modify it. My knowledge of VBA is extremely limited (below code I just found and modified a little bit), so I would appreciate help.

I need to add two additional columns for each imported spreadsheet, which are not in the original files.

First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.

Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".

Dim strFile As String
 
    DoCmd.SetWarnings False
    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:="Data", FileName:=strPath & strFile, HasFieldNames:=True, Range:="name"
        ' Loop to next file in directory
        strFile = Dir
    Loop

 

[namliam]

In order to add the customername into the table simply remove the range all together or possibly just add the range A:ZZ or something if you prefer.

Assuming you want the filename because you have multiple files, the easiest/best way to do this is to work with a "Staging" table... A temporary table... Lets call it tblDataStaging

Add a column filename to your data table.
So you do your importspreadsheet to that table.

Now create an append query from your staging table to your permenant table in the designer and hardcode the "filename" as "[Filename]", save this query as qryAppendDataStaging

then after importing your file add these lines:

currentdb.execute replace(currentdb.querydefs("qryAppendDataStaging").SQL, "[FileName]", strFile)
Currentdb.execute "Delete * from tblDataStaging"

You can also do this with update queries which is "a little cleaner" but with lots of files and/or lots of records is MUCH slower.....
Do remember to compact your database after doing this with a staging table to reduce bloating

 

[illusionek]

I am sorry but I cannot really see how to apply above to my problem.

I use above code to import all Excel files from a particular folder into one table in Access, so basically I consolidate them. I did set up a range name in Excel files because often users add new columns to some files. So I wanted to make sure that my Access table does not become a mess because some Excel files had a slightly different format.

Even if I include Customer Name in the range my main problem is that this value is in a single cell i.e. A6. So if I imported a table from Excel with 20 rows, I would have value appearing only in the first row and all other would be blank. I hope this makes sense.

Correct me if I am wrong but if I wanted to use 'staging table', I would need to create separately table for each imported Excel file, add two new columns and then somehow consolidated all tables into one to get desired effect?

If so, it is just way above my abilities ... I was just hoping that there is an option to add couple lines of code to the existing macro to get the job done.

 

[namliam]

No you dont create a table per file that is the point, however you do need to add the filename column, which is why you have the Staging and perminant tables.... Staging table to hold your data temporarily and a perminant one where you have extra column(s)

Do you have only one customer per excel file? or multiple customers ? Could you post a dummy excel file so I can see the layout?
to only solve your Original problem of adding the filename you simply add the one table to your database and alter your code in de way I described....
i.e.

Dim strFile As String
 
    DoCmd.SetWarnings False
    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:=True, Range:="name"
        currentdb.execute replace(currentdb.querydefs("qryAppendDataStaging").SQL, "[FileName]", strFile)
        Currentdb.execute "Delete * from tblDataStaging"
        ' Loop to next file in directory
        strFile = Dir
    Loop

All you need to do in addition is to create the append query as I described.

 

[illusionek]

I am sorry, I am sure I must be missing a very simple trick but I keep getting error message: "Run-time error 3061 Too few parameters. Expected 1"

I guess this is because I added filename in table Data but not DataStaging and then my append query goes crazy because I reference to the column that does not exist in DataSting but then if I add filename column to both tables, it just stays blank, so I am not sure if the problem is with currentdb.execute replace (....) or my append query, which can be found below. I have been trying to amend it in many different ways and it does not seem to be working ;/

Any suggestions?

INSERT INTO Data ( Klient, SKU, Qty, filename)
SELECT DataStaging.Klient, DataStaging.SKU, DataStaging.Qty, filename AS FileName
FROM DataStaging;

 

[namliam]

filename as FileName
That is what is wrong since you didnt use "" that is where you get the parameter problem...

Assuming you are using my replace thing with the execute... this is what you need....

INSERT INTO Data ( Klient, SKU, Qty, filename)
SELECT DataStaging.Klient, DataStaging.SKU, DataStaging.Qty, "[filename]" AS FileName
FROM DataStaging;

 

[illusionek]

Many thanks for your prompt response, I have amended my query but I got following in my filename column in Data table:

[filename]


I have used exactly your code (copy/paste) I even used exactly the same names for tables queries etc, just to make sure it is gonna work the way it should but the above is only result I got.


EDIT:

OK, I found a problem in below code, I had to change "[FileName]" to "[filename]"

Many thanks for your patience and all help!

If this is ok I will upload tomorrow a dummy file to solve the problem with Customer Name.

Once again, many thanks!

currentdb.execute replace(currentdb.querydefs("qryAppendDataStaging").SQL, "[FileName]", strFile)

 

[namliam]

Replace is not case sensitive? Most likely you made a typo someplace or executed the append query manually?

 

[illusionek]

Hi Namlian,

I have attached a print screen with a copy of imported spreadsheet.

Would you be able to suggest how to get customer name in a similar way to File Name?

I have highlighted in yellow imported range 'name'. I have done it because sometimes users insert some additional columns, so I wanted to make sure that I import what I need only. I guess I could import the whole spreadsheet and then use Select to get only required columns but then as I still do not know how to get value of Customer field in new column.

First attachment shows the original file and the second one how I want to get it. There is only one Customer per each imported file but sometime there may be two/or more different files with the same customer.

Grateful for all help!

 

[namliam]

Did you get the filename (and the replace thing) working then?

To copy down the customer name, should be relatively easy using 2 queries....
Assuming you have your entire excel sheet staged.... Something along the lines of:

Dim rsC as dao.recordset
set rsC = Currentdb.Openrecordset( "Select Field2, Field3 from YourStagingTable where Field2 = """Customer""")
Currentdb.execute("Update YourStagingTable set CustomerName = """ & rsC!Field3 & """")

 

[illusionek]

Hello there!

Yeah, I managed to get filename all right. All I had to do is to change "[FileName]" to "[filename]" in your code.

But I really struggle with the one above.

If I just run it as it is, I get error: “Compile error: Expected list separator or )”. I guessed it had something to do with number of “” around Customer. So I changed your line to the following:

set rsC = Currentdb.Openrecordset( "Select Field2, Field3 from YourStagingTable where Field2 = Customer")

but then I get a different error message saying “Run-time error 3061. Too few parameters. Expected 1”

I am sure solutions must be very easy but I have been trying to fix it since you posted it without any luck. I am very new to all this, so all help would be very much appreciated.

 

[namliam]

Yes well there is one " to much it looks like...

Dim rsC as dao.recordset
set rsC = Currentdb.Openrecordset( "Select Field2, Field3 from YourStagingTable where Field2 = ""Customer""")
Currentdb.execute("Update YourStagingTable set CustomerName = """ & rsC!Field3 & """")

 

[illusionek]

Many thanks! I knew the answer was simple, hopefully next time I will find it myself

The code runs now without problems but the result is not exactly as needed. In CustomerName I get only value from first imported spreadsheet and then it repeats for all other tables/imported files as per attached file. So I would like to see Test 1 for first table and then Test 2 but instead Test 1 is for both tables.

Would you be able to help?

 

[namliam]

That is the point of staging it...
1) Stage the file (aka import it into a holding table)
2) Update the missing information as needed
3) Add the "proper" data to your perminant table (using an insert query)
4) Delete the content of the Staging table

This is the most elegant/frequently used way to process this kind of thing, if you want you can also do some VBA, but that (in the current setting) will not guarantee the proper results.... The best way, fastest, most secure way is to follow the steps I outlined above.

Edit: offcourse after 4) you start back at 1)

 

[illusionek]

Stupid me! I did not realize I have to follow the same process as for filename.

I have done everything exactly the same way as for filename but something is not working too well.

I keep getting error message: "Run-time error 3061: Too few parameters. Expected 1"

Dim strFile 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""")
CurrentDb.Execute ("Update DataStaging set CustomerName = """ & rsC!F2 & """")
CurrentDb.Execute Replace(CurrentDb.QueryDefs("qryAppendDataStaging").SQL, "[CustomerName]", CustomerName)
        ' Loop to next file in directory
        strFile = Dir

    Loop

I was playing with this code and I believe I get error message because there is no customername column in DataStaging table. I have this feeling it must have something to do with append query but it is exactly the same as for filename and it works well for filename but not for customer

INSERT INTO Data ( F1, F2, F3, customername )
SELECT DataStaging.F1, DataStaging.F2, DataStaging.F3, "[customername]" AS CustomerName
FROM DataStaging;

Would you have any idea what is happening here?

Again, many thanks for all your help!

 

[namliam]

The general idea of a staging table is you have a temp table with incomplete/partial/mixed data, that you then manipulate into what it needs to be before you send it to your perminant table....
The total code would be something like:

Dim strFile As String
Dim rsC as dao.recordset
 
    DoCmd.SetWarnings False
    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:=True, Range:="name"
set rsC = Currentdb.Openrecordset( "Select Field2, Field3 from DataStaging where Field2 = """Customer""")
Currentdb.execute("Update DataStaging set CustomerName = """ & rsC!Field3 & """")
        currentdb.execute replace(currentdb.querydefs("qryAppendDataStaging").SQL, "[FileName]", strFile)
        Currentdb.execute "Delete * from tblDataStaging"
        ' Loop to next file in directory
        strFile = Dir
    Loop

Can also do it a little differnently also updating the filename into the Staging table instead of inserting it during the Insert into query

 

[illusionek]

I am so sorry but this code still returns error "Run-time error 3061: Too few parameters. Expected 1" as per attached print screen.

I did try to fix it myself but it is above my abilities

I googled this problem and it looks like I need to use single quotes ' to make it work but when I change line in error to

CurrentDb.Execute "Update DataStaging set CustomerName = '" & rsC!F2 & "'"""

I get error message: "Run-time 3075: Syntax error in string in query expression "test'" and test is value which should be displayed as customer name So I am back to square one This is just drives me mad, I am so close but so far.

Would you have any idea how to fix it?


Many thanks.

 

[namliam]

does your stagingtable have a field called "CustomerName"??

If that is not the problem please attach your database and a sample excel sheet and I can have a look for you

using single quotes (') replaces the double quotes ("") thus would be:

CurrentDb.Execute "Update DataStaging set CustomerName = '" & rsC!F2 & "'"

 

[illusionek]

There is no CustomerName in my staging table, I thought I need to hard code it the same way as I did with filename via appendquery. Unless I miss the trick here?

Even if I add CustomerName in staging table, it does not work well. The code runs but then I get 'Name AutoCorrect Save Failures' in Access and the column is not updated properly anyway. I get only 'test' as CustomerName from 1.xlsx file but not others.

Pls find attached my database and imported Excel files.

Many thanks for all help.

 

[namliam]

Well you can "APPEND" in which case you send data from one table to another and put data in there, which works fine if you are doing one column or very limited columns as it gets messy fast. Alternative might be that you can "hardcode" it in VBA...

You can do multiple updates on your file in the staging table, by doing so you prepare your data in the staging table and the append query is a simple 1:1 full copy from staging to perminant table.