importing excel data into access

Question

My VB.NET application is using importing some excel sheet into access

command.CommandText = "SELECT * INTO [MS Access;Database=" & current_db & "].[" & sheet_name & "] FROM [" & sheet_name & "$]"

The problem is that if I have some cells with the green error message in excel they are not imported in access. And they are actually in the same format they don't have a leading ' but excel it's saying number formatted as text.

An easy way out would be to simply correct this in excel but often users forget to do this and the data is skipped.

How can i force the connection to import a specified column as number ?

Answer 1

You can also import excel data to ms-access by copying and pasting. Perhaps this might be a better solution if there is less data to process.

Answer 2

I would do this by

1. In Access, define the table structure (call it YourNewStagingTable) that you want import into (don't make this your final table - this is purely your staging table (I usually just define all fields as TEXT here).
2. use INSERT INTO YourNewStagingTable SELECT Columns FROM SpreadsheetPath
3. Run all your corrections and data validations on this table
4. Move data from YourNewStagingTable to FinalDataTable

If you still encounter data type translation issues, you will have to alter the ISAM mappings for the file.

Answer 3

I don't think there is a way to tell the ODBC for the Excel engine to force a column into a datatype. It only looks at the first 7 rows and makes an "intelligent" decision as to what the dataype is for that column. When I did massive migrations I had code "force" the data into the right datatype doing tricks like what you mentioned above and then do the insert command. But it does look like this is always an alternative. In short you have to massage your data before getting it into the table.