It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
Import an Excel File into Access
I've attached an Access database with a Form containing a command button that is to be used to select an Access file for import.
The button contains FileDialog code that works and that I take no credit for having written.
I've attached an example of the type of Excel file I need to import.
You will see it contains 3 columns of data that begins on row 18 of columns C, D and E and ends on row 22 of columns C, D, and E.
At a minimum, via use of the command button already on the form where I select a file for import/transfer, I'm trying to bring
those 3 columns and 5 rows of data into an Access table. I don't care if it goes into a new or existing table.
Ideally though, what I'd like to be able to do is have the first 6 digits of column C (which represents the account number) go into its own column, the remainder of column C go into its own column, the Statistics go into its own column, the Dollar Amount go into its own column, and the date information (in cell D6) go into its own column and appear in every row of data so that the 5-column expected results table will look something like this:
Field1 Field2 Field3 Field4 Field5
111111 Description of Account 111111 10 10.00 25-NOV-2019
222222 Description of Account 222222 5 100.00 25-NOV-2019
333333 Description of Account 333333 100 3000.00 25-NOV-2019
444444 Description of Account 444444 25 250.00 25-NOV-2019
555555 Description of Account 555555 40 80.00 25-NOV-2019
Also, because this will be a recurring task and the number of data records will vary from one file to the next, I should mention that the data will always start on row 18 (columns C through E) and end with the row immediately above the word "TOTAL" in column C.
Test-Database.accdb
Test-File.xlsm
The button contains FileDialog code that works and that I take no credit for having written.
I've attached an example of the type of Excel file I need to import.
You will see it contains 3 columns of data that begins on row 18 of columns C, D and E and ends on row 22 of columns C, D, and E.
At a minimum, via use of the command button already on the form where I select a file for import/transfer, I'm trying to bring
those 3 columns and 5 rows of data into an Access table. I don't care if it goes into a new or existing table.
Ideally though, what I'd like to be able to do is have the first 6 digits of column C (which represents the account number) go into its own column, the remainder of column C go into its own column, the Statistics go into its own column, the Dollar Amount go into its own column, and the date information (in cell D6) go into its own column and appear in every row of data so that the 5-column expected results table will look something like this:
Field1 Field2 Field3 Field4 Field5
111111 Description of Account 111111 10 10.00 25-NOV-2019
222222 Description of Account 222222 5 100.00 25-NOV-2019
333333 Description of Account 333333 100 3000.00 25-NOV-2019
444444 Description of Account 444444 25 250.00 25-NOV-2019
555555 Description of Account 555555 40 80.00 25-NOV-2019
Also, because this will be a recurring task and the number of data records will vary from one file to the next, I should mention that the data will always start on row 18 (columns C through E) and end with the row immediately above the word "TOTAL" in column C.
Test-Database.accdb
Test-File.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This sounds like a project spec. Have you tried this yourself? Do you have any control over the format of the spreadsheet? Is it possible for the data to have either one or zero header rows so that the data begins on line 2 or 1? This is the only layout that will allow you to do this without writing Excel automation code. Another possible way of getting around the non-importable format is if there is a named range. Access allows you to import named ranges.
I didn't download your .xlsm file and I won't because I don't want to run it on my PC. I suggest that you upload an .xlsx file instead so we don't have to worry about having to allow macros to run.
If the spreadsheet can be reformated to look like a table, you can link to the spreadsheet using TransferSpreadsheet (one line of code) and then run an append query that will reformat the columns and append them to your permanent table. If you can't reformat the spreadsheet, then it will require VBA which we can help you with.
I didn't download your .xlsm file and I won't because I don't want to run it on my PC. I suggest that you upload an .xlsx file instead so we don't have to worry about having to allow macros to run.
If the spreadsheet can be reformated to look like a table, you can link to the spreadsheet using TransferSpreadsheet (one line of code) and then run an append query that will reformat the columns and append them to your permanent table. If you can't reformat the spreadsheet, then it will require VBA which we can help you with.
This is easy using an SQL statement to import a block of data from an Excel workbook into Access. I'll post below the response I recently posted in another thread authored by you that had the same/similar request.
"I have had great success using VBA to build Excel-into-Access queries on the fly. It's my preferred method because it allows you to do just about anything you can do with a regular import query. You can pick and chose any block of data from anywhere in the workbook, you pick & choose your columns to import, add additional info to be imported with the imported data, Add criteria, etc. You don't have to open the workbook with code or have it open. And... it is many times faster than line-by-line copy-and-paste - that's the hard way to do it. Do it that way ONLY if forced to because of extenuating circumstances.
The code below shows how I do it. It also shows how to include todays date as part of the query.
strSQL = “INSERT INTO [tblMyAccessTable] ( [TodaysDate], [AccessField1], [AccessField2], [AccessField3] )”
strSQL = strSQL & “ SELECT Date() AS [TodaysDate], [ExcelColumnName1], [Excel ColumnName5], [ExcelColumnName3]”
strSQL = strSQL & “ FROM [Excel 12.0;HDR=Yes;Database=C:\F
You can also include inline functions to separate the data coming in. For example, you can use the function LEFT([ColumnCName],6) to put the left 6 characters into one column, then use MID([ColumnCName],7) to put characters 7 to the end in another Field.
Depending on the version of Excel you're using, you may have to use an earlier Excel version (9?). Be sure to note the square brackets around the Excel file info and sheet/range, and the use of the $ between sheet name and range. This is a sample. You'll need to use your noodle and adjust it to your specifications. I have set the number of rows beyond the actual number of rows, but you can run a cleanup query to delete any unwanted/needed rows, or add a WHERE clause that will filter out the unwanted stuff (i.e. empty key values column).
There are Excel functions that you can use if all you have are named ranges or Excel tables (you can also use TransferSpreadsheet to import a named range from anywhere in the workbook). You'll need to get the Named Range or Excel Table's "RefersTo" sheet name and range to use here in an SQL query.
I've got an Excel and text file demo app that I use to teach and demo all the various ways you can import/export Excel and text files. Let me know if you have a specific problem such as no header row, etc..
Good luck.
"I have had great success using VBA to build Excel-into-Access queries on the fly. It's my preferred method because it allows you to do just about anything you can do with a regular import query. You can pick and chose any block of data from anywhere in the workbook, you pick & choose your columns to import, add additional info to be imported with the imported data, Add criteria, etc. You don't have to open the workbook with code or have it open. And... it is many times faster than line-by-line copy-and-paste - that's the hard way to do it. Do it that way ONLY if forced to because of extenuating circumstances.
The code below shows how I do it. It also shows how to include todays date as part of the query.
Dim strImportTableName as string
Dim strTargetFieldsList as string
Dim strSourceFieldsList as String
Dim blnHasHeaders as Boolean
Dim strExcelFilePath as String
Dim strSheetName as String
Dim strImportRange as String
‘SET VARIABLE VALUES:
strImportTablesName=”tblMyAccessTable”
strTargetFieldsList = “[AccessField1], [AccessFields2], [AccessField3]”
strSourceFieldsList = “[ExcelColumnName1], [Excel ColumnName5], [ExcelColumnName3]”
blnHasHeaders = True
strExcelFilePath = strFilePathFromBrowseFileFunction
strSheetName = “Sheet1”
strImportRange = “A10:E300”
‘BUILD THE IMPORT QUERY:
strSQL = "INSERT INTO [" & strImportTableName & "]"
strSQL = strSQL & "( [TodaysDate], " & strTargetFieldsList & ") "
strSQL = strSQL & "SELECT Date() AS [TodaysDate], " & strSourceFieldsList
strSQL = strSQL & "FROM [Excel 12.0;HDR=" & IIf(blnHasHeaders, "Yes", "No") & ";Database=" & strExcelFilePath & "].[" & strSheetName & "$" & strImportRange & "]"
strSQL = “INSERT INTO [tblMyAccessTable] ( [TodaysDate], [AccessField1], [AccessField2], [AccessField3] )”
strSQL = strSQL & “ SELECT Date() AS [TodaysDate], [ExcelColumnName1], [Excel ColumnName5], [ExcelColumnName3]”
strSQL = strSQL & “ FROM [Excel 12.0;HDR=Yes;Database=C:\F
You can also include inline functions to separate the data coming in. For example, you can use the function LEFT([ColumnCName],6) to put the left 6 characters into one column, then use MID([ColumnCName],7) to put characters 7 to the end in another Field.
Depending on the version of Excel you're using, you may have to use an earlier Excel version (9?). Be sure to note the square brackets around the Excel file info and sheet/range, and the use of the $ between sheet name and range. This is a sample. You'll need to use your noodle and adjust it to your specifications. I have set the number of rows beyond the actual number of rows, but you can run a cleanup query to delete any unwanted/needed rows, or add a WHERE clause that will filter out the unwanted stuff (i.e. empty key values column).
There are Excel functions that you can use if all you have are named ranges or Excel tables (you can also use TransferSpreadsheet to import a named range from anywhere in the workbook). You'll need to get the Named Range or Excel Table's "RefersTo" sheet name and range to use here in an SQL query.
I've got an Excel and text file demo app that I use to teach and demo all the various ways you can import/export Excel and text files. Let me know if you have a specific problem such as no header row, etc..
Good luck.
I don't know. I tried a stripped-down version of that code and it doesn't seem to be doing anything. It doesn't fail when I step through the code but nothing happens, so I'm obviously doing something wrong.
A couple of other things:
Now trying it on a .xlsx file because of the concerns about macros being in a .xlsm file.
I'm using Excel version 15.0.
My sample .xlsx file is very basic and contains header names as you can see. But my production .xlsm files won't be containing any headers, so I don't know if I'm supposed to reference them by column letter or some other way. I've attached a sample database and a sample Excel file I'm trying to import.
Test-Database.accdb
TEST_FILE.xlsx
A couple of other things:
Now trying it on a .xlsx file because of the concerns about macros being in a .xlsm file.
I'm using Excel version 15.0.
My sample .xlsx file is very basic and contains header names as you can see. But my production .xlsm files won't be containing any headers, so I don't know if I'm supposed to reference them by column letter or some other way. I've attached a sample database and a sample Excel file I'm trying to import.
Test-Database.accdb
TEST_FILE.xlsx
I'll return your database file with a working import. I'll use both headers and no-headers so you can see the difference.
OK. I didn't realize how much of a newbie you were. I didn't include the code to run the SQL after the SQL was built:
The db I return will have a working import and a lot more.
CurrentDB.Execute strSQL
The db I return will have a working import and a lot more.
OK Here it is. Added "Option Explicit" to your form module (if you don't know why, google it).
I added some textboxes to your form. Added a file picker. Added an Excel file without headings. Added some error handling. Changed the Excel version to 12. Moved the data block away from range A1 (so you could see it works from anywhere on the worksheet).
Select which file you want to use and click any button and see what happens. It works for me.
TEST_FILE-No-Headings.xlsx
TEST_FILE.xlsx
Test-Database.accdb
I added some textboxes to your form. Added a file picker. Added an Excel file without headings. Added some error handling. Changed the Excel version to 12. Moved the data block away from range A1 (so you could see it works from anywhere on the worksheet).
Select which file you want to use and click any button and see what happens. It works for me.
TEST_FILE-No-Headings.xlsx
TEST_FILE.xlsx
Test-Database.accdb
Hi Mark,
It looks like the query you supplied requires that the data range be known. I'm not sure that it is in this case.
It looks like the query you supplied requires that the data range be known. I'm not sure that it is in this case.
I should mention that the data will always start on row 18 (columns C through E) and end with the row immediately above the word "TOTAL" in column C.
Hey Pat: This isn't a "cure-all". As with any attempt to import anything into Access using any technique, the technique you use and how you set the parameters depends on what the data looks like. You have the same issues with using SQL as you do with linking an Excel file to Access as a linked table. It is best if the block of data is SQL Compliant, but if not, then you'll have to experiment to see if the SQL method will work and how it returns the data
.
I don't think non-programmers realize just how much testing and experimentation we have to do when trying to program an app. They give us just a little bit of information about the problem, then expect what we recommend to just work, even though they may have left out a few necessary details that would change things - and "experts" love to start assuming "what-ifs" since we didn't get the full description of the problem and conditions - and then we are off to the races.....
First, there is the issue of different datatypes below the datatype determination threshold row that causes all those #Num, etc. to show up, etc. (datatype conversion failures). If the "TOTAL" text is in a non-text column, then you may get one of those "#" errors. You can use a conversion function to get rid of those (such as CStr()) which will change, for example, a "#Num" to a blank. What is the datatype of the column that the "TOTAL" text is in? If it's in a text column, he's o.k. I'm not going to assume any problems with the data unless the author indicates a problem.
I'm assuming that there is no data below the last row of the data block containing the "TOTAL row, and that the "TOTAL" text is in a text column. In that case, he can use a number of rows that he knows will cover any possible number of rows and put in a WHERE clause to filter out any unwanted rows such as a filter where the column that always has data in it isn't blank, or you can filter out something like WHERE [Field3]<>'TOTAL'. The developer who knows what the data source looks like will have to make a judgement on whether or not using a range with a larger number of rows more than what may be expected will work, and what filtering or post-import cleanup queries/processing would do the trick.
What this technique DOES do is it gives the developer the ability to pick-and-chose the columns to import, what fields in the Access table to put those column into, the ability to use inline functions in the SELECT clause to perform modifications on the data during import, and the ability to add a filtering WHERE clause. No other import technique does this - but this technique does have its issues - if the data is not SQL Compliant.
I've attached another copy of the authors file which now has more complex operations and creates a querydef in the query window of the Nav Pane that contains the SQL. Running the import process creates the querydef so you can see what the SELECT version of the query sees, and troubleshoot. I've also included a modified Excel file with more columns and rows and the "TOTAL" text in a text column field.
However....there is more than 1 way to skin a cat if the number of rows to import MUST be known, or the data also has a named-range or Excel table available to use...
Test-Database.accdb
TEST_FILE.xlsx
.
I don't think non-programmers realize just how much testing and experimentation we have to do when trying to program an app. They give us just a little bit of information about the problem, then expect what we recommend to just work, even though they may have left out a few necessary details that would change things - and "experts" love to start assuming "what-ifs" since we didn't get the full description of the problem and conditions - and then we are off to the races.....
First, there is the issue of different datatypes below the datatype determination threshold row that causes all those #Num, etc. to show up, etc. (datatype conversion failures). If the "TOTAL" text is in a non-text column, then you may get one of those "#" errors. You can use a conversion function to get rid of those (such as CStr()) which will change, for example, a "#Num" to a blank. What is the datatype of the column that the "TOTAL" text is in? If it's in a text column, he's o.k. I'm not going to assume any problems with the data unless the author indicates a problem.
I'm assuming that there is no data below the last row of the data block containing the "TOTAL row, and that the "TOTAL" text is in a text column. In that case, he can use a number of rows that he knows will cover any possible number of rows and put in a WHERE clause to filter out any unwanted rows such as a filter where the column that always has data in it isn't blank, or you can filter out something like WHERE [Field3]<>'TOTAL'. The developer who knows what the data source looks like will have to make a judgement on whether or not using a range with a larger number of rows more than what may be expected will work, and what filtering or post-import cleanup queries/processing would do the trick.
What this technique DOES do is it gives the developer the ability to pick-and-chose the columns to import, what fields in the Access table to put those column into, the ability to use inline functions in the SELECT clause to perform modifications on the data during import, and the ability to add a filtering WHERE clause. No other import technique does this - but this technique does have its issues - if the data is not SQL Compliant.
I've attached another copy of the authors file which now has more complex operations and creates a querydef in the query window of the Nav Pane that contains the SQL. Running the import process creates the querydef so you can see what the SELECT version of the query sees, and troubleshoot. I've also included a modified Excel file with more columns and rows and the "TOTAL" text in a text column field.
However....there is more than 1 way to skin a cat if the number of rows to import MUST be known, or the data also has a named-range or Excel table available to use...
Test-Database.accdb
TEST_FILE.xlsx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial