Importing Excel sheet into access, phantom lines

[knightwest]

Hi,

I have been importing excel spreadsheets succesfully for a long time now and this new problem I have never seen before. I don't know how to deal with it. I have had a look on here but could find nothing similar, I have not really had a any luck searching google, mainly because I am at wits end to find a suitable search term.

Here's what's happening:
When i upload the sheet as is, it works fine, bar a "data conversion error" for one column which contains mainly numbers but a few cells of letters.

as before, I have inserted a line at the top of the spreadsheet (below the column headings), and put a 1 in for each numeric column and a letter for each alphanumeric and text based column.

Now when i import to access, I get no conversion error, but access has inlcuded about 20'000 blank lines between the headings and the actual data.

I really don't know what is going on.

 

[andy1955]

I'm not clear precisely what your problem is from your description. However, one way to limit what is imported from the spreadsheet is to define a "Name" in the spreadsheet that macthes the cells that you wish to include. Now, assumin you are using the TransferSpreadsheet Action within a Macro in Access, then you enter the name given to the cell range in the Range field on the Macro Sheet.

So:

Transfer Type would be "Import"
Spreadshet Type would be "Microsoft Excel 8-10" (or whatever)
Table Name - the table into which you are importing
File Name - the location of the file and its name (.xls)
Has Field Names - No or Yes depending on which is true (Do you have COl Heads or not)
Range - The NAME of the cell range - e.g. IMPORT_AREA

Hope that is of assistance. If not, try abd describe your issue somewhat more.

A

 

[knightwest]

Problem solved

Basically the problem was this:
Instead of showing the first line of records in access in row 1, it showed it somewhere on row 20000 or something with all rows between 1 and 20000 being blank.
in excel all records started in row 1.

Reading your post helped me sort it though, as i noticed the print range in my spreadsheet was somewhere well off, where no records where.

I changed the printrange to encompass my records, and hey presto, the data import worked!

 

[SkiGeek]

The blank lines show up before your data because the table is most likely sorted in increasing order. Blanks just come first.

I have had the same problem. I bypassed it without setting a range (I didnt want my user to have to do this) by importing the data to a tempTable and then I Delete all records where the the column the will be my primary key is Null. Then I set up sum update and insert queries to move the data from my tempTbl to the permanent table. This way I only took the data I needed, and could do some data validation, manipulation, and conversion during my insert and update. I then deleted the temp table.

Late Reply, but I just joined