Linked Spreadsheet - column datatype problem

[LarryJ]

I am importing data into my database, by first linking a spreadsheet as a table, then reading each row with code to put it into the proper place in the database (multiple tables).

Anyway, one of the columns is a mixture of numbers and text. It just so happens (in this particular spreadsheet) that the first 30 or so rows are numbers. Other spreadsheets, may be just the opposite.

The problem is that the link is defining this column as a number, so that the rows following, which are text, are dropped.

I have formatted the column, in Excel as TEXT, which didn't help. I can't make a table in Access, with the proper column definitions and import the data into this table, b/c the spreadsheet can change.

Does anyone know how I can get around this problem?

TIA

 

[Dugantrain]

Well, the balls to the wall way is just to Insert a row between the column Headers row and the first line of data and type 'x' in each cell of that row, thereby forcing Access to recognize every column as text. I know that you can do this using Excel Automation; I don't know your experience, but cleaning up Spreadsheets via Access VBA is a fine way to learn!

 

[LarryJ]

I actually tried doing that, manually at first, just to see if it would help. And it doesn't.

I moved one of the rows, that has text, so that it's the first row in the spreadsheet (after the column headings), since order doesn't matter. But when I linked to the spreadsheet, it still wants to read that column as numeric.

I am assuming Access is seeing that the majority of X number of rows is numeric, so sets it to a number field.

I am very good with Excel and Access code, so I think I may just go the extra step and read the data directly from Excel, instead of doing the time saving step of linking the spreadsheet and just working with it as an Access database. It'll be a pain re-writing this part of the code, but I don't see any other clean workaround.

Thanks for the comment.

 

[Dugantrain]

Interesting, Larry, I've always read that Access makes guesses based on the first 10 or so lines of data. Well, you're probably better off importing rather than linking anyway; you'll have more control over the data.