Link to home
Start Free TrialLog in
Avatar of chraisy
chraisy

asked on

Import multiple Excel files to different Access tables (same database) adding an extra column with the Excel`s filename

Hello, I have hundreds of Excel files that I need to automatically import to separated Tables of the same Access database.

The Excel files have all the same headers.

The Excel files are inside the same folder.

The Excel files are named: distributionDD-MM-YYYY.xlsx  , where only the date is different from each other. If possible, the extra collun=mn will keep only the date and discard the rest of the filename.

I`m using Office 2007.

Can anyone help me, please?

Thanks in advance.

Chris
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

what is the name of the table you want to store the excel files?
Avatar of chraisy
chraisy

ASKER

Any name, but it would be great if each table had also the Excel filename.
Avatar of chraisy

ASKER

And I forgot to mention that I need only Sheet1 to be imported from each Excel file.
that would mean having hundreds of tables to your database... post sample name of the excel files.

start with this code

Sub ImportExcelFiles()
Dim strFileName As String, strFolder As String, xDate as Date, strTable as string
 strFolder = "C:\FolderName\"  ' CHANGE THIS WITH ACTUAL FOLDER PATH
 

strFileName = Dir(strFolder & "*.xlsx")
xDate=xDate = Left(Right(strFileName, 15), 10)
strTable= Left(strFileName, Len(strFileName) - 15)
 While strFileName <> ""
     DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12Xml, strTable, strFolder & strFileName, True, "Sheet1!"
 'add date [ImpDate] column to table
      CurrentDb.Execute "ALTER TABLE  " & strTable & " ADD COLUMN [ImpDate] Date);", dbFailOnError
      CurrentDb.Execute "Update " & strTable & " set ImpDate = #" & xDate & "#"
    strFileName = Dir()
 Wend
 
end sub
Avatar of chraisy

ASKER

Thank you Rey,

a sample Excel name is distribution14-03-2017.xlsx
another is distribution 15-03-2017

I tried pasting and running the code and I have a Run-time error 3293: Syntax error in ALTER TABLE statement. When I click "Debug" it highlights the code: CurrentDb.Execute "ALTER TABLE  " & strTable & " ADD COLUMN [ImpDate] Date);", dbFailOnError
change this line

strTable= Left(strFileName, Len(strFileName) - 15)

with

strTable= Replace(Replace(Left(strFileName,  Len(strFileName) - 5), " ","_"),"-","_")
Access is not a spreadsheet and should not be treated as one.  It is seriously poor practice to import all the Excel files into separate tables  A better solution would be to add a new column to the target table to hold the name of the Excel file.  That way all the data can go into the same table and you can work with the data as a set or piecemeal by using queries with criteria.  Using the single table will save you an enormous amount of work going forward since you won't have to build separate reports and queries for each table.  You can use a single set of objects and use criteria to select only the data you want.

Import a single spreadsheet. Give the table a generic name.  Do not include anything date specific.  Delete all the imported rows.  Add the additional column.  Add a primary key (autonumber if the data doesn't contain any unique identifier), Add a non-unique index on the new column since you will probably be selecting by that column and the index will help to speed up processing.  Compact the database.

Instead of the Alter and Update queries, use TransferSpreadsheet to link to each table in turn and then run an append query to copy the data from the linked table to the permanent table and include the value for the new column.  As long as this "new" column is ALWAYS going to be a date, make the field a date data type.  If there is a possibility that you might ever need to have a non-date value, then leave it as a text field but use YYYY/MM/DD as the date format so that the data will all sort correctly and you will be able to select groups of records such as all the imported data for a month, quarter, or year.
Avatar of chraisy

ASKER

Thank you Patrick. I will follow your suggestion.
Can you help me with the TranferSpreadshrets code? And how to append the query?
@chraisy
you are already using the transferspreadsheet  code .

did you test the revised code?

you decide if you want to use One table per Excel file or One table for all the excel file
the codes you have now will just need simple tweaking.
TransferSpreadsheet is a DoCmd method so typing
DoCmd.TransferSpreadsheet
will get you intellisense to help complete the command.

To create an append query, Open the QBE and select the source table (the linked spreadsheet).  Select all the columns from the linked table.  Then change the query type to Append and choose the name of the new, permanent table.  Assuming the column names match, Access will automatically populate the append to line.  If any column is left blank, you can choose the target field from the dropdown list.  You will need a calculated field for the table name so ion your form, create a new unbound control.  Set its visible property to No and name it "txtInputFile".  Then in an empty column in your append query, type
Forms!yourformname!txtInputFile

In the code module,  you will populate the txtInputFile as you go through the loop.  then run the TransferSpreadsheet and the append query.  To run a query, you can use
DoCmd.OpenQuery "yourqueryname"

Since this is an action query, Access will help you by telling you that you are about to append x rows and then it will report any errors.  Let us know if you don't want to see the warning messages.
Avatar of chraisy

ASKER

Thank you, Rey. I`m sorry, I was out for a week. Today I will try the new code and I will let you know. Actually, I would prefer to follow Patrick`s suggestion and import all the Excell to the same table, adding the extra date field to each import. All the best!
Actually it is Patricia.
Avatar of chraisy

ASKER

Sorry, Patricia!
Rey, I replaced the code and now it gives me a "runtime error 5", "Invalid procedure, call or argument". After closing (Debug button) the error message, the following line becomes yellow: strTable = Replace(Replace(Left(strFileName, Len(strFileName) - 5), " ", "_"), "-", "_")
what is the value of strFileName when you got the error?

can you create an image of the folder where the excel files are and upload here.

do you want to import all the excel files to a single table?
Avatar of chraisy

ASKER

Hello, Rey,

I pasted the exact code, without changing anything. The filenames are distribution11-04-2017.xlsx or distribution12-04-2017.xlsx, so I suppose the strFileName is 27 , right?

The Excel files have confidential information (names) that I can`t share, but here is one of the Excel files were I changed the names: https://www.dropbox.com/s/p0v61mwiwggexhp/distribution01-04-2017.xlsx?dl=0

Yes, I would prefer to import all the files to the same table, adding the extra date column.
are all files have the same column names? like below

DBD | Code |Name|      Last name|      FS|      Can be assisted together|      Lunch|      Dinner/Breakfast|
Avatar of chraisy

ASKER

Yes, all files have the same column names. And the error message appears quite fast. It seems that directly when I press "run" it appears, without having process anything.
test this sample db
excel file will be imported to "tblDistribution"
db_29017110_ImportExcel.accdb
Avatar of chraisy

ASKER

I have this error now:

Run-time error`2391`: Field `F8` doesn`t exist in destination table `tblDistribution`

And I don`t have any field `F8` on any excel file.
Excel "remembers" cells that used to have data but are now "blank".  That's because it doesn't know if you want the cell to be blank for spacing or if it really isn't needed.

Whenever you want to delete rows or columns from a spreadsheet, you must select them and press delete on the ribbon or right-click menu.  If you press the delete key on the keyboard, the visible contents will be removed but Excel "remembers" that this row/column once contained data and so when Access imports it, Excel gives Access the empty rows/columns also.
oh yes, you do have a blank column after column "G", column  H is used but it is blank
try to import the excel file manually and you will get the same error.


try this one
db_29017110_ImportExcel.accdb
PS - this is one of the reasons that I never append directly from Excel to a table.  I always link to the spreadsheet and then run an append query which selects only the columns I want and ignores any empty rows.
it can be done in codes. the new db I uploaded limits the import to columns A to G
A great deal can be done with code, but why when it doesn't solve the entire problem?  Specifying the range solves the problem of extra columns to the right but doesn't get around resequenced or inserted  columns.  You have to make sure that the range is either the maximum allowed or that it always keeps ahead of the actual row count.  It also doesn't eliminate the empty rows frequently found at the end nor does it allow you to apply sanity checks on the data.  

So again, I never, import data directly into a permanent table.  I link and use an append query.  This isn't completely foolproof but it does  protect against several common errors and when you are dealing with user input, defensive programming is critical.
Avatar of chraisy

ASKER

Thanks, I used the las version, it seems to process, no error messages, but nothing has been imported. The table remains empty after running the form.
Avatar of chraisy

ASKER

Using the past version, I manually deleted more than 30 columns of each Excel workbook, it imports, but after importing 10 files the message apears:
Run-time error `2391`: Field 802 doesn`t exist in destination table `tblDistribution`
Avatar of chraisy

ASKER

The same happens when I try the last version that you sent, Rey:
It runs, 10 new tables appears with importing errors and an error message:
Run-time error `2391`: Field 802 doesn`t exist in destination table `tblDistribution`
oh well, the codes will only work if your excel file have the same columns names and column number.
chraisy,
The code that Rey so kindly created for you works exactly the same way it does now except that you change AcImport to acLink and get rid of the range specification.  Then instead of the update query, you would run an append query to copy the daa from the linked table to the permanent table.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chraisy

ASKER

@Rey

When I press "Link Excel" it imports only one of the many files into a new table and gives me a message: Run-time error `3061`: Too few parameters. Expected 7.

After pressing "Debug" it shows the code and highlights this:
CurrentDb.Execute aSql, dbFailOnError
Avatar of chraisy

ASKER

@Pat

In Rey`s code, I replaced AcImport with acLink and update with append. The result is:

Sub ImportExcelFiles()
Dim strFileName As String, strFolder As String, xDate As Date, strTable As String
 strFolder = "C:\Users\Christian Ysebie\Desktop\April Ell2\lunch\"  ' CHANGE THIS WITH ACTUAL FOLDER PATH
 

strFileName = Dir(strFolder & "*.xlsx")
xDate = xDate = Left(Right(strFileName, 15), 10)
strTable = Replace(Replace(Left(strFileName, Len(strFileName) - 5), " ", "_"), "-", "_")
 While strFileName <> ""
     DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, strTable, strFolder & strFileName, True, "Sheet1!"
 'add date [ImpDate] column to table
      CurrentDb.Execute "ALTER TABLE  " & strTable & " ADD COLUMN [ImpDate] Date);", dbFailOnError
      CurrentDb.Execute "Append " & strTable & " set ImpDate = #" & xDate & "#"
    strFileName = Dir()
 Wend
 
End Sub


When I run this module, it gives an error message: Run-time error `3611`: Cannot execute data definition statements on linked data sources.
@chraisy
do not alter the codes I use in the db. please until I say so.

When I press "Link Excel" it imports only one of the many files into a new table and gives me a message: Run-time error `3061`: Too few parameters. Expected 7.

this is the reason why  I asked you if all your excel files have the same Column Names. Now we know that they are not because of the error you are getting.

also, I am sure that there are more than one excel file that was imported, if that file that gave the error was one the first one to be linked. Check the content of the table tblDistribution.
Avatar of chraisy

ASKER

Dear Rey,

I went again through my excel files and I found that in one of them, the first row (with labels) was missing. I added the heading and tried your solution (the last one with the "Link to Excel" button works perfectly.

Thank you so much and sorry for the mistakes.
Avatar of chraisy

ASKER

Works perfectly.
Avatar of chraisy

ASKER

@Rey,

The code is working perfectly. The only issue is that my filenames are "distributiondd-mm-yyyy.xlsx", but when it updates the date field, the generated dates are wrong.

Example, when I import the file distribution01-04-2017, the date that appears is 04/01/2017.

How can I make the code "understands" that in my filenames the format is dd-mm-yyyy?

Thanks in advance.
look for

xDate = Left(Right(strFileName, 15), 10)

and change with this

xDate = Format(Left(Right(strFileName, 15), 10), "dd\/mm\/yyyy")
I know people like to see dates as mdy or dmy but when naming files, ymd order allows the files to be  sorted into date order when the date of the file name might be different from the create date of the file.
Avatar of chraisy

ASKER

@Rey

Thank you Rey, I changed the line but it keeps updating the wrong date. When I import the file "distribution01-04-2017.xlsx", the date that appears is 04/01/2017.

Only when the filename has a date bigger than 12, i.e. "distribution13-04-2017", then the date appears right : 13/04/2017

@Pat
Thank you, Pat, I will keep this in mind and change the naming procedure from now.
Avatar of chraisy

ASKER

@Rey

I edited your code:

 xDate = Format(Left(Right(strFileName, 15), 10), "dd\/mm\/yyyy")

And used instead:

xDate = Format(Left(Right(strFileName, 15), 10), "mm\/dd\/yyyy")

and it worked!