Solved

Trouble Importing Into SQL Server from CSV File

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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