When importing a flat file like a CSV file you may get the error message unknown column type conversion(s) in the SQL Server import and export wizard.
This is because the CSV file columns default to a string. You will need to define the datatypes of each column in the CSV file and then run the import. It is important that the datatypes of the source and destination match. In some cases the SQL import export wizard will be able to convert the datatypes but in many cases it will not be able to.
The following shows how to define the datatypes:
1. Open SQL import and export wizard and choose the flat file source from data source drop down list
In this example the destination is a database table called employee. The following is the datatypes for the employee table.
In order to make the datatypes match change it from SQL Import and SQL Wizard Advanced properties menu DataType.
For this example the EmployeeID needs to be changed to an Integer and the modifieddate to Date.
You can also choose the Suggest Types… button and press OK for the software to detect the datatypes. The Suggest Types… can be inaccurate at times so please check the datatypes after using this option.
3. After setting the datatypes choose next. Choose the destination. In this example it is a SQL Server table.
4. Choose next and select the destination table.
5. Choose next and you should see all green ticks in the left column. If you see a yellow icon or red icon then you will have to go back and rectify the errors.
6. Click finish and if everything is configured correctly then you should see the success screen.
If you have problems:
- Make sure the destination and source datatypes match or is compatible
- Make sure data is not truncated ie the string datatypes in the source should be equal or less than the destination
- You may need to use text qualifiers (usually quotation marks) in your source so that there is no conlflict between your delimiter and data characters. eg. ‘text1, text2’ this would be taken as a single column, where as without the quotes it would be two columns where the delimiter is a comma.