I am trying to import data from an excel worksheet (WS) into SQL but I am having a number of issues.
The import export wizard is incorrectly identifying data types.
I expected this process to be a five minute job, I have spent ten times that already. Is there any better alternative to importing Excel data into SQL server?
Any help appreciated.
NB the Excel data is fairly consistently formatted because it is an export from an online database.
The import export wizard is incorrectly identifying data types.
- There are two columns on the WS that contain only a heading, nulls and integer values but the wizard classes then as double. The destination table is set up as integer datatype. I can live with that one.
- There are five columns that each contain a heading a date in the format dd/mm/yyyy hh:ss and also some nulls. Three of these columns the import wizard correctly identifies as datetime, two of them it identifies as VarChar(255). The destination table is set up as datetime, which is causing problems.
- There is an internal notes field in my table currently set to varchar(max), the Excel field is identified as "long text". This field is throwing an error 0xc0204016 SSIS.Pipeline that the output column has a length that is not valid, the length must be between 0 and 4000.
I expected this process to be a five minute job, I have spent ten times that already. Is there any better alternative to importing Excel data into SQL server?
Any help appreciated.
NB the Excel data is fairly consistently formatted because it is an export from an online database.