Solved

Trouble Importing Into SQL Server from CSV File

Posted on 2011-09-30
5
432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 40

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 40

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get_systemdrive info from tsql? 1 26
SQL server client app 3 75
Trying to understand why my Index is so large 12 52
store vs query adhoc - no show rows 4 37
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 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