Solved

Trouble Importing Into SQL Server from CSV File

Posted on 2011-09-30
5
399 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
Parsing out characters on the right side of hyphen 3 23
all records from previous month 6 45
Getting max record but maybe not use Group BY 2 16
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

14 Experts available now in Live!

Get 1:1 Help Now