Solved

Trouble Importing Into SQL Server from CSV File

Posted on 2011-09-30
5
389 Views
Last Modified: 2012-05-12
I am attempting to import a CSV file into SQL server, but when I do I get the following errors:


- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column ""description"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
 (SQL Server Import and Export Wizard)
 
Error 0xc020902a: Data Flow Task 1: The "output column ""description"" (26)" failed because truncation occurred, and the truncation row disposition on "output column ""description"" (26)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\Administrator\Desktop\AUTO PARTS DB\part(1).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 - part(1)_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)
 
0
Comment
Question by:nickgross19
5 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 36894190
If is not mandatory for you to use SSIS and you know the file is csv not xls you can use the code below but you need to create the SQL table I called "CSVTest" with same structure as the CSV file first:


--Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
0
 

Author Comment

by:nickgross19
ID: 36894338
I tried using the code, and it worked while i was doing just 2 records, but I tried to put in a full file with multiple records and get errors.  here are some of the errors:

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 5 ("description").
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 5 ("description").
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 2 ("title").
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 4, column 5 ("description").
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36895479
Hey,

If table is not exists in SQL and trying to Import data using SSIS will create on the fly Table in SQL Database.

Please follow the Step to import data in SQL using wizard. In that in one step click the Edit mapping button and increase the size of destination "description" columns.
0
 
LVL 11

Expert Comment

by:SThaya
ID: 36901791
Hi,

  can you please send me your CSV file .
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36904989
The errors you posted above leads me to believe that the 2 rows had "description" and "title" data that could fit in the table.columns you created but not the actual csv file. If you are not sure about the length use varchar(max) from data type or Nvarchar(max) if you know UNICODE chars can exists in any of the fields.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now