Importing 1600 extra lines?

[Locopete99]

Hi Guys,

I'm using the below code to import some data.

Its kind of the "quick entry" version of a form when my colleagues have to fill out 10-20 odd forms one after the other.

The only problem is, its importing 1600 extra lines and creating 1600 blank records.

How can i stop this?

DoCmd.TransferSpreadsheet acImport, , "Tbl_NPPR_LOG", "P:\CCT Hub\NPPR Reflection Sheet.XLS", True,

 

[bob fitz]

Perhaps you need to use the "Range" argument as explained in this link: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

 

[CJ_London]

As Bob suggests. The alternative is to delete the blank lines in the workbook

in the workbook if you click the end then home keys it will take you to the bottom right cell of the workbook which has or had data. My guess is this is 1600 lines below where your data currently resides.

I've had this problem when a file has been exported from sql server or similar as an excel file - it exports to the entire capacity of excel i.e. could be a million rows and a huge number of columns.

I always recommend using text files (you can save excel as a .csv file) which gets over a lot of problems like this and the datatyping issue.

 

[The_Doc_Man]

As an alternative, import the data to a temporary table, then use a series of delete queries with WHERE clauses to remove all lines that have blanks, nulls, empty strings, zero values that cannot truly be zero, or whatever else you can identify. Then use INSERT INTO from what is left after the filtering. Sounds ugly, but if you can identify what the "good" lines look like, you can make the "bad" lines go away easily enough. Doing a little quality control before the import makes all the world of difference.

A second alternative is that if you are willing to allow it, you can open the workbook as an Excel application, then step through the collection of rows to test for things you know HAVE to be there in the worksheet and delete any rows that don't pass muster. Then save & close the worksheet and workbook, then do your import after filtering that way. Tomato, tomahto.