We help IT Professionals succeed at work.

Why get empty records when doing an Excel import?

SteveL13
SteveL13 asked
on
Why do I sometimes get empty records in a table when I import from an Excel file?  Here is an example of my VBA code for the import:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTEMPBaselineSurveyDataImport", Me.FileList, True
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019
Commented:
Probably because Access fails to Interpret the Excel fields.
An example...if you have a column in Excel that looks like this
1
5
6
77
Two
Three
....
Based on the first rows Access might recognize the column as Number and assign a Numeric Datatype ...but then encounters text...too bad , text doesn't match to the "recognition" ...so dump it anyway..
Take a look at the problematic entries.
If this is the case , then you need Ms Access to Excel Automation to import the data probably everything as text and then manipulate - match to a more appropriate datatype ..(e.g. Iterate all records...if IsNumeric(YourField) =True then ---> make the conversion)

Author

Commented:
But the rows in the Excel file are empty.  There appears to be no data in any of the cells.
there may be someghing there thats not obvious likeva carraige return.

if you specifically delete the  blank rows from excel before the
import do you still get empty records?

Author

Commented:
No.  If I intentionally delete what appears to be empty rows in the Excel file prior to import I get no empty records.
so looks like there is something there.

u
you cld run
docmd.runsql "delete * from yourtable where yourcol is null"
after the transferspreadsheet

Author

Commented:
But then I'll end up with gaps in the auto-number (PK) field in the table.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Well, the way I generally do this is I link the Excel worksheet to Access.

Then I do some analysis of the data to make sure it is all valid.

Then I write an import query that imports the records from the linked table into my production table and filters out all of the blank rows.
Distinguished Expert 2017

Commented:
Since Excel allows empty cells/rows, it "remembers" cells/rows that once contained data.  If you select cells and press the delete key, any visible data will be deleted, HOWEVER, Excel REMEMBERS that they once held data.  You must use the delete command from the ribbon to actually delete rows and columns that are no longer needed.

The best way to avoid issues with imports is to link to the spreadsheet and use an append query to select rows with data and append them.  The query also allows you to do some formatting and validation if you need to.
import to temp table.
do cleanup
append to live table
Distinguished Expert 2017
Commented:
Importing into a temp table just makes database bloat.  Access cannot recover the used space without a compact.  Using a link avoids that issue.  As long as the file name and location doesn't change, there won't be any bloat.  You can simply replace fileA with a new version of fileA and Access will be none the wiser.  If the file name or location changes each time, then you will have to delete the previous link and link to the new file.  This does also cause a small amount of bloat but not as much as importing a file.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, Pat, if you use the tabledef object, you can simply modify the connection property of the linked table and do a refreshlink to avoid having to delete and relink.
Distinguished Expert 2017

Commented:
Dale, I'm not sure if that avoids the small bloat caused by relinking but if it does, then that is clearly superior.
Chief Technology Officer
Commented:
Personally, I find trying to import directly from Excel to be a pain, with all the issues (sampling only the first few rows to determine data type and sting length, etc.)

The attached database and sample Excel file demos my preferred method of converting a file to csv and importing csv.  It avoids most of the issues.  The database system shows how to use Windows file explorer to pick a file and run it thru a simple process to convert to csv, then use an import spec to import into a table.  Data goes in with no issues!

See if it helps...
ExcelToCSVtoAccessImportDemo.accdb
SampleExcelImportFile.xlsx

Author

Commented:
Mark, interesting.  I have to leave for the day but will take a deeper look at this tomorrow.

Author

Commented:
I found Mark's solution to work best for me.  Thank you to the others for contributing.
Mark EdwardsChief Technology Officer

Commented:
Steve:  Glad I could help.  I've found a working example to be the best way to show others how to do VBA programming.

There are numerous other methods for importing files of various kinds into Access.  Some include being able to pick only the columns you want to import and pulling data from anywhere in the workbook without having to open it.  Which method you choose depends on the files involved, the state of your data (SQL Compliant or Non-SQL Compliant), data types in the column, the use of named-ranges and Excel tables, and where it is located in the workbook.  The same goes for exporting TO Excel.