Why get empty records when doing an Excel import?
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, "tblTEMPBaselineSurveyData
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTEMPBaselineSurveyData
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
if you specifically delete the blank rows from excel before the
import do you still get empty records?
ASKER
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"
u
you cld run
docmd.runsql "delete * from yourtable where yourcol is null"
after the transferspreadsheet
ASKER
But then I'll end up with gaps in the auto-number (PK) field in the table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
do cleanup
append to live table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
fair point
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.
Dale, I'm not sure if that avoids the small bloat caused by relinking but if it does, then that is clearly superior.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mark, interesting. I have to leave for the day but will take a deeper look at this tomorrow.
ASKER
I found Mark's solution to work best for me. Thank you to the others for contributing.
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.
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.
ASKER