Hi All

I am tyring to import excel sheet to Access Table doing this

DoCmd.TransferSpreadsheet acImport, , "TestData", FileName, True

But it created many empty rows in begining

Any idea

regards

Ayaz

Does this happen every time you use this sub? Were the blank records there from a previous test of the sub?

If you delete all the blank records and run the sub again, are there more blank lines? Did you try setting the spreadsheet version in the empty set of commas?

Just some ideas to get started.




mmcdonal

if I run that sub again it will create more empty records with some data in the end

and this is data in excel sheet

SXID PID SubPID Quantity Date User
1 Alt489-3B05 Blank489-3B05 1 13-Jan-07 12
2 Alt489-3B06 Blank489-3B06 1 13-Jan-07 12
3 Alt489-3B05 Blank489-3B05 1 13-Jan-07 122
4 Alt489-3B06 Blank489-3B06 1 13-Jan-07 23
5 Alt489-3B05 Blank489-3B05 1 13-Jan-07 34
6 Alt489-3B06 Blank489-3B06 1 13-Jan-07 45
7 Alt489-3B05 Blank489-3B05 1 13-Jan-07 99
8 Alt489-3B06 Blank489-3B06 1 13-Jan-07 12


this is imported data in Access table from above sheet

SXID PID SubPID Quantity Date User







1 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 12
2 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 12
3 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 122
4 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 23
5 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 34
6 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 45
7 Alt489-3B05 Blank489-3B05 1 13/01/2007 21:48:18 99
8 Alt489-3B06 Blank489-3B06 1 13/01/2007 21:48:18 12

If i delete all these records and run sub again it will create same empty rows

and i didnt try spreadsheet version in the empty set of commas

Are there characters in the empty rows anywhere, something that looks like a small box?

I am thinking that you have deleted records from the excel spreadsheet, and excel still considers them valid rows. And since there is no row ID (which you seem to be importing) then it sorts them to the top.

Try adding an ID to the number of rows BELOW the data in your spreadsheet that are showing at the top of your table. So if there are 10 empty rows at the top of the table after import, then add 10 more IDs in sequence at the bottom of your spreadsheet.

If when you do the import, you have no empty rows, and ten more blank records except for the IDs, then I think the issue is with Excel and not Access or the DoCmd parameters.

I am not sure how to remove empty rows from Excel, but it probably still thinks they are valid. Worse case, copy and paste the spreadhseet data to a new spreadsheet (without copying the empty rows) and try importing that. I think that will resolve it since the new spreadhseet will have no history of those rows containing data.

Did that help?

mmcdonal

Hi Ayaz,

I too have had this problem from time to time with importing excel files into access, and i tend to agree with mmcdonal that this is a issue with excel. I have found that best practice when importing data into access is to use a staging table rather than import directly into a table. If you create a staging table in the same format as you primary table, then import all the data into the staging table, once the data is imported you can then append your primary table, excluding null/blank records and this also allows you to apply any formatting to fields such as Date/Time etc. I have found this to be by far the best way to import data, my database imports xls, csv and txt all over the place, then exports simular, and since implementing staging tables my problems have been greatly reduced. Also ensure you clear the staging tables before you import new data (goes without saying i guess).
I post this as an alternative to trying to resolve bugs in other apps that impact your database.

Regards

Darren

Hi all,

Problem with excel sheet
Records were deleted from the excel spreadsheet, and excel consider those rows valid even it was not visible.
Thanks all
Regards
Ayaz

  I don't think that is a bug in Excel, just that there must be a way around it in Excel.
  I like the idea of the staging table, however, since this accounts for this 'feature' in Excel.


mmcdonal