Import into SQL Server 2003 from a CSV file Error

▲
1
▼

I've spent so many hours just trying to import CSV, Excel data into SQL Server 2003 using SSMS (2012). I've tried importing as an excel, as a CSV, and a text file all options have presented problems of their own.

The biggest frustration now is when importing a CSV under the Flatfile option. In the Advanced tab I've set my source date column to have [DT_DBTIMESTAMP] this matches my destination's date column's type [DT_DBTIMESTAMP] YET despite all this when I run my import SQL Server errors out and says

• Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard).

How can this fail if BOTH columns are exactly the same type?

Thanks in advance for the help.

sql-server
csv-import
asked on Stack Overflow Jun 7, 2013 by Sabzo

2 Answers

▲
0
▼

It might not be that SQLServer is complaining about losing data when importing but when reading. For instance, the DT_DBTIMESTAMP type expects the format yyyy-mm-dd hh:mm:ss[.fff]. If your data is not formatted correct in any dimension (say your mm > 12 or dd > 31) that may be the problem. Like @Pondlife suggested I'd bring everything into a varchar(max) field and then run TSQL to see if all rows can convert to DBTIMESTAMP data type is a simple convert statement.

answered on Stack Overflow Jun 8, 2013 by sisdog
▲
0
▼

I saw many problems with SQL server import wizard (I'm on version 2008 R2), reading date fields from CSV files, when format is Latin - day comes before the month, like 25/12/2013 (Christmas). Looks like it assume allways MM/DD/YYYY, and there is no clear way to tell to read as DD/MM/YYYY (or I did not find). If I solve, will post. Tks.

answered on Stack Overflow Aug 30, 2013 by Mario Leboute

User contributions licensed under CC BY-SA 3.0