2

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 ?

Improve this question

3 Answers 3

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

damn it , i was using a file with already 'converted to numbers' column, I still get the same thing unfortunately. My new connection string is "INSERT INTO [MS Access;Database=" & current_db & "].[" & sheet_name & "] SELECT * FROM [" & sheet_name & "$] " _connection.Open() And i set the column type that i am interested in to text but it's still not getting the data.
it's kinda working now , i've modified the connection string to _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'" But now all my cells stored as numbers in excel are imported in scientific format in acces ( 2205070510 in 2.20507e+009) I don't know how can i attach files here.
IMO, as long as you get the data, you are good. You can always do the conversion from one format to another when you move from staging table to actual table
Excel is notoriously difficult to deal with so we had set up a process where excel sheets were exports to CSV files and then imported into MS Access
I'm having a lot of trouble with the actual conversion now, it seems I can't find a datatype to store my 10 digit number, even cdbl rounds it.
0

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.

Improve this answer

3 Comments

It looks like he is trying to automate a process instead of doing it manually.
Exactly , and besides that I'm doing a lot of querying in the final database after that to generate some reports. That's why I'm making an application to free the users from manually pasting hundreds of files.
you can still automate the process of copy and pasting with the use of macros
0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.