Import Excel File Into Access 2010 Table

I need to import the contents of a MS Excel File into an Access 2010 Table.

Attached is code that I have developed that imports most of the data however it doesn't import all of it.

I need to import the contents of columns B through AP and rows 5 through the last row containing data.

The attached code only imports data from columns E through AP.  Changing the Starting Column in the code does not solve the problem.

The Excel File is generated in another application that I don't have control over but remains stable for at least year.

To use the application, start up the 2010 Import  Group - Sections Detail Report.accdb file and follow the instructions.

The Input file is: Input File.xls.

The Message line displays 'Imported' when the run is complete.

The 07_IMPORT_DATA Table contains the data when loading is complete.

How can I get this application to load all of the data in the required columns (B - AP and rows with data.

Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?
You have several logic problems.
1. You should be using column F or L to identify the rows you want to import.  Notice that your import stops at Group Total rather than two rows later because column E is empty for the last two rows.
2. It would be best to not import the totals at all.  If you use row L, you can bypass all totals.
3. Columns B,C, and D only have data at the beginning of a section.  If you wan to import this data, you will need to save non null values for each of these three columns and use the last saved value when the current row is empty.
4. You would not need to use VBA at all to do this import if you simply open the spreadsheet either manually or programatically and remove the first three rows.  Then you could use the column headers in Row 4 such as they are.
5. Another problem is that when I imported the data from the spreadsheet, the first row that imported was row 40 - 67 and then rows 5-39.  It is important to figure out why this happened.  I'm guessing that Excel has some internal physical order that is different from the order you "see".  This could end up being a problem with your group processing logic that you will need to fill all columns B, C, and D.  I am not an Excel expert but you need one who can tell you how to sort the recordset in the "visible" order rather than the "physical" order or the logic will be too fragile to depend on.  You might have to create an Excel macro to drag down the suppressed values so they can be correctly imported and run the macro when you open the spreadsheet prior to starting the import loop..

Once the data is imported using TransferSpreadsheet, you still have a couple of problems with it.
1. Without using VBA, the first 3 columns will be intermittently blank and therefore invalid so you would still need to populate these.
2. You would have the totals rows to get rid of but you can run a delete query to do that.
3. You have an unnormalized schema.  I don't know what you need to do about that because I don't have any idea why you are importing this data and what you will do with it.

All in all, since the VBA loop is currently working, you might as well stick with it and and make the changes I suggested.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

Thanks very much for your quick and detailed response.

Some comments.

Point 1
I tried using Column F.  It did import all of the rows that I want but of course Columns A to E are missing.  In actual fact Column AP contains a concatenation of the data in Columns B to E always preceded with '-Scouts Canada-' which after loading into the table I am 'splitting' to create the data in these columns anyway.

So this may be the solution since I can use it in place of Columns B - E to construct the data I need.

The one issue I have with this solution is that the 'splitting' uses a dash '-' to determine where the splitting is to occur.  I have one of these name (Co-op) that treats its dash as a 'split point'.  See Row 66.

Here is the code snippet for doing this splitting.
   'Step 315-Extract Organization External Hierarchy Data - Column AP.
   Dim WrkExtHierarchyData314 As String
   WrkExtHierarchyData314 = Mid(RS29ExtOrgDetailListingMstr0Lkup003!ORG_EXTERNAL_HIERARCHY, 2, 249)
   Dim WrkExtOrgLevelCorporateName314 As String
   WrkExtOrgLevelCorporateName314 = Split(ParmExtOrgHierarchyKey311, "-")(1)
   Dim WrkExtOrgLevelCouncilName314 As String
   WrkExtOrgLevelCouncilName314 = Split(ParmExtOrgHierarchyKey311, "-")(2)
   Dim WrkExtOrgLevelArealName314 As String
   WrkExtOrgLevelArealName314 = Split(ParmExtOrgHierarchyKey311, "-")(3)
   Dim WrkExtOrgLevelGroupName314 As String
   WrkExtOrgLevelGroupName314 = Split(ParmExtOrgHierarchyKey311, "-")(4)
   Dim WrkExtOrgLevelSectionName314 As String
   WrkExtOrgLevelSectionName314 = Split(ParmExtOrgHierarchyKey311, "-")(5)

Point 2
I would prefer to import the Total Rows but I could live without them.

Pint 3
Am I correct that to do this I would need to do it in the original filed before importing?  This isn't an option.

If it can be done during the import - how?

Point 4
Again, removing the 3 header Rows before importing isn't possible.  How would I do it programmatically during the import processing?

Point 5
So far I haven't had any issues with this.  When I run the code it loads the records in the table in the same sequence in which they appear in the input file.

The problems after the importing I have already dealt with including the un-normalized schema, so they aren’t an issue.

At this point as you have suggested I have the solution other than the issue with the 'splitting' which I will work on.

Bob C.
Fabrice LambertFabrice LambertCommented:
I gave up looking at your code, I guess your main problem is: Merged cells.
Ms Access do not like it, period.

So first, I suggest you to transform your workbook into something more manageable.
Then you'll be able to import your data.

Please, see the attached database, I commented out all your code and provided function importing your data in a new table (so your old table remain untouched).
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob CollisonSystem ArchitectAuthor Commented:
Hi Fabrice,

Thank you for taking a look at this and your solution.

Having reviewed the results I believe that the final output in the 07_IMPORT_DATA Table is not significantly better (if at all) than those resulting from Pat Hartman's suggestion to change the First Cell to F5.

I am not familiar with the methods you are using although I can get the gist of what you are doing I don't see that it is any better than the results using the original code with Pat's F5 suggestion.

Before I could accept it I would need it explained so that I can understand exactly how it works.  Could you please do this?

Bob C.
Fabrice LambertFabrice LambertCommented:
Short version:
I make a temporary copy of the workbook, so I can edit it as I want without affecting the original file.

Long version:
- Clear the table holding data (clearImportTable function).
- Open the workbook.
- Save it to the temporary folder.
- Retrieve the last row and last column holding data.
- Retrieve the data starting from F5 to the last row and last column, data are retrieved in a 2D array.
- Happen a single quote in front of all data, Excel will interpret that as Text data. This will avoid future troubles with MS Access (details below).
- Clear the worksheet.
- Write back data, starting from A1 cell. Effectivelly getting rid of uneeded rows and columns).
- Save and close the workbook.
- Import data with the transfertSpreadSheet function.

On possible troubles when importing data from MS Excel:
When importing, MS Access try  to "guess" the best data type for each columns.
This is done by "voting" at the majority of the top 5 rows. This work often, but if the guessing is wrong (Like numeric data when in fact it is text) it result in an import error.
Data arn't imported to the table, and Access create an "import error" table where all rows in error are written.
Enforcing data as text solve the issue, but on the other hand, may require validation and / or convertion.
2. I would prefer to import the Total Rows but I could live without them.
By definition a table contains only a single type of data.  Every row is equal to every other row.  It makes no sense to include total rows interspersed with detail rows.  Totals should be calculated in reports.
4. Again, removing the 3 header Rows before importing isn't possible.  How would I do it programmatically during the import processing?
You remove the three rows using VBA after your procedure opens the spreadsheet.  You then close the spreadsheet and import it using TransferSpreadsheet.  The column headings are not good column names so you might want to delete 4 rows and can the headings, OR put your own column heading names in that row so the TransferSpreadsheet can use proper column names rather than sentences to name the columns.
Sorry got to run.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Fabrice,

Thanks for the explanations.

In this case I don't see the point in what you are suggesting so I am going to go with Pat's suggestions.

Bob C.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Pat,

Thanks for your comments.

2. Total Rows
This data represents the results froman external source with the totals calculated so it would be desireable to use them.

4. Header Rows
Thanks for the suggestion but I don't see the point in removing the 3 Header Rows when I'm already getting the data I need that just requires additional VBA processing.

I'm going with your original suggestion to use Column F5 as the starting cell as fo me it is the simplest solution.

Thanks again.
Bob C.
Bob CollisonSystem ArchitectAuthor Commented:
The selection of Column F instead of E simplified the data being extracted without major changes to the processing.
2. Total Rows
This data represents the results froman external source with the totals calculated so it would be desireable to use them.
Then you do not understand the relational model.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.