Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble Importing Into SQL Server from CSV File

Posted on 2011-09-30
5
Medium Priority
?
452 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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