Clever Geek Handbook

Import Excel data for access

My VB.NET application uses import of some excel sheet to access

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

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

The easy way out is to just fix it in excel, but often users forget to do it and the data is skipped.

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

+2


source to share


3 answers


I would do it with

  • In Access, define the table structure (name it YourNewStagingTable

    ) that you want to import into (don't make this summary table), this is just your staging table (usually I just define all fields as TEXT here).
  • use INSERT INTO YourNewStagingTable SELECT Columns FROM SpreadsheetPath

  • Run all patches and data validations in this table
  • Move data from YourNewStagingTable

    toFinalDataTable



If you are still facing data type translation issues, you will have to change the ISAM mappings for the file.

+5


source


You can also import excel data into ms-access by copy and paste. Perhaps this might be a better solution if you process less data.



0


source


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

0


source






More articles:


All Articles