Import Error on CSV File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Import Error on CSV File

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-10 : 17:14:53

I'm using SQL Server 2008 on Server 2008 R2. I can't seem to import one file.

First, I've configured the table in SQL server Manager Studio to have the columns & data types that I need. Then, running the 'Import & Export Data tool'. I select flat file, set all of the matching datatypes & proceed. It has worked perfectly for other excel, csv & text files. But one field in one file is giving me a problem. The 'QtyAvail' field is the second row, it has no more than 4 digits & no special characters (just numeric 0-9). I've tried truncating the file to just 20 rows to be SURE that it is perfect & it is. However I continually get the following error. What can I do?

quote:
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "QtyAvail" 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)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "QtyAvail" (14)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "QtyAvail" (14)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "Z:\IT\feeds\evertek.csv" on data row 2.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - evertek_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 20:05:20

whats the datatype you've given for QtyAvail in table? whats the datatype its interpreting from csv?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-11 : 01:17:31

The datatype for this column in the SQL server is 'numeric(18, 0)'. The datatype in the import tool is 'numeric [DT_NUMERIC]'. It also has a precision of 18 & a scale of 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:57:21

quote:
Originally posted by SergioM

The datatype for this column in the SQL server is 'numeric(18, 0)'. The datatype in the import tool is 'numeric [DT_NUMERIC]'. It also has a precision of 18 & a scale of 0

you say The 'QtyAvail' field is the second row

does that mean the column delimiter in this case is carriage return character?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-15 : 16:28:16

Woops, that was a mistake. I meant to say that it was the second column. I made that clarification because I had previously received a file which was improperly tab delimited. As a result, a column which should had one set of data, had another for just one of the rows.

I converted the file into excel & that does import fine. Same with Tab delimited. So the problem is not the actual content, but the file... Do I have any options?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-15 : 22:25:23

first question is are delimiters consistent?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2012-05-16 : 14:57:57

So, I tried truncating the file to three rows & four columns & it still won't import. Now it has an error for 'Product Description', but no longer for 'QtyAvail'!

quote:
Category,QtyAvail,PartNumber,Product Description
VCD,14, HD4850-PCIE-512-BO, ATI Radeon HD 4850 512MB DDR3 PCI Express (PCIe) Dual DVI Video Card w/HDCP Support
VCD,8, 25SGF6HX1RUV, Galaxy GeForce GTS 250 1GB DDR3 PCI Express (PCIe) DVI/VGA Video Card w/HDMI & HDCP Support
VCD,5, R6570-MD2GD3-LP, MSI Radeon HD 6570 2GB DDR3 PCI Express (PCIe) DVI/VGA Low Profile Video Card w/HDMI & HDCP Support

The delimiters are consistent, but every 50 rows or so, there's an added row to mark the header title, probably to make it easier for a person to read. In those rows, everything is left blank, except for the 'Product Description' field.

Could it be that it's the special characters '()/&'? The datatype is set as nvarchar(255).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:14:22

quote:
Originally posted by SergioM

So, I tried truncating the file to three rows & four columns & it still won't import. Now it has an error for 'Product Description', but no longer for 'QtyAvail'!
quote:
Category,QtyAvail,PartNumber,Product Description
VCD,14, HD4850-PCIE-512-BO, ATI Radeon HD 4850 512MB DDR3 PCI Express (PCIe) Dual DVI Video Card w/HDCP Support
VCD,8, 25SGF6HX1RUV, Galaxy GeForce GTS 250 1GB DDR3 PCI Express (PCIe) DVI/VGA Video Card w/HDMI & HDCP Support
VCD,5, R6570-MD2GD3-LP, MSI Radeon HD 6570 2GB DDR3 PCI Express (PCIe) DVI/VGA Low Profile Video Card w/HDMI & HDCP Support

The delimiters are consistent, but every 50 rows or so, there's an added row to mark the header title, probably to make it easier for a person to read. In those rows, everything is left blank, except for the 'Product Description' field.

Could it be that it's the special characters '()/&'? The datatype is set as nvarchar(255).

ok can you try using ssis and putting a row_number transformation in the pipeline. then add a conditional task and specifically put a filter to avoid the header titles in between.
Also is the number of columns differing after each of those header rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources