Trying to Bulk Insert from CSV File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Trying to Bulk Insert from CSV File

Author

Topic

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-08 : 13:18:32

Hello,
I am trying to Bulk insert from a CSV file. Here is the table i created in my SQL 2008 database.

create table reports (
CreationTime varchar(50),
Description varchar(50),
JobId varchar(100),
LastModificationTime varchar(50),
NextRecalculation varchar(50),
RemoteRunStart varchar(50),
RemoteStatus varchar(50),
RemoteSystem varchar(50),
RequestedStartTimeInternal varchar(50),
RestartCount varchar(5),
ReturnCode varchar(50),
RunEnd varchar(50),
RunStart varchar(50),
ScheduledStartTime varchar(50),
Status varchar(50),
JobDefinition varchar(50),
OwnerSubject varchar(50),
Queue varchar(50),
ProcessServer varchar(50),
SubmitFrame varchar(50),
JobTimeZone varchar(50),
LastModifierSubject varchar(1000)
)

I am using this statement:

BULK
INSERT reports
FROM 'D:\Drop\110612_Job_47807756_report.csv'
WITH
(
FIRSTROW=4,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

)
GO

Here is the format of data in the file.

"2012/11/02 11:00:24,867 GMT",INT_Agdata_FTPAT_JC,47554390,"2012/11/02 11:00:24,867 GMT","2012/11/10 00:00:00,000 America/New_York",null,null,null,"2012/11/05 07:00:00,000 America/New_York",2,null,"2012/11/05 07:00:22,922 America/New_York","2012/11/05 07:00:04,593 America/New_York","2012/11/05 07:00:03,831 America/New_York",Completed,DINT_Agdata_FTPAT_JC,User.U365,System,System,RM_EVERY_1_DAY,America/New_York,User.Redwood System

I have some values with " " and also those values have , included. like
"2012/11/02 11:00:24,867 GMT"

I tried to use a format file with no luck. Please Advice.. I am working on this from 2 days for now. Need this to be done asap. Advise Please !!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 13:50:14

Bulk insert is really not great at handling CSV files where the separator also exists in the data fields, even if such fields are escaped using double-quotes as is in your case.

I would recommend using SSIS, or even simple Import/Export wizard (which you can launch from SSMS, object explorer, right-click on the database name and select tasks -> import data). You can save that as a package if you need to import frequently or in an automated process.

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-09 : 11:47:30

Hello Sunita,

I tried using IMP/EXP Wizard and this time it is successful but when i checked the data it is not in the right order meaning

"2012/11/02 11:00:24, 867 GMT" column from the csv file was copied into
"2012/11/02 11:00:24" as column 1 and
" 867 GMT" as column 2 in the table.
Is there any way we can specify the format in the IMP/EXP wizard or how can i get through this ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:11:03

On the dialog where you select the datasource (flat file source), there is a place to specify Text Qualifier. Insert double quotes in there as text qualifier.

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-09 : 12:22:00

" " or just one

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:27:01

Just one double-quote

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-09 : 12:45:13

But not all the columns have " in the data. Only the columns with the date has them.

"2012/11/02 11:00:24,867 GMT",INT_Agdata_FTPAT_JC,47554390,"2012/11/02 11:00:24,867 GMT","2012/11/10 00:00:00,000 America/New_York",null,null,null,"2012/11/05 07:00:00,000 America/New_York",2,null,"2012/11/05 07:00:22,922 America/New_York","2012/11/05 07:00:04,593 America/New_York","2012/11/05 07:00:03,831 America/New_York",Completed,DINT_Agdata_FTPAT_JC,User.U365,System,System,RM_EVERY_1_DAY,America/New_York,User.Redwood System

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:50:02

That is okay; it has the smarts to process the data correctly in both cases. Give it a try and you will see :)

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-09 : 13:14:58

I tried doing that but got this error..

- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 1" 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 "Column 1" (14)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (14)" 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 "D:\Drop\110612_Job_47807756_report.csv" on data row 27.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - 110612_Job_47807756_report_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)

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-09 : 13:31:44

It worked.. Ran like a charm..
Thanks for your help Sunita. I really appreciate this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 13:39:33

You are quite welcome, o ye, of little faith

Sarath_Aluri
Starting Member

10 Posts

Posted - 2012-11-16 : 11:20:47

I loaded the data using import.
But i want to Schedule this Job in SQL Agent. So that it runs everyday. I saved the SSIS package when running for the first time and in Agent i am trying to specify the same package to run everyday. It fails with the following error.

Log in Failed for the User. Reason: Password did not match that to the log in provided.

I gave the same account and password in all the places.
Please Help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 13:29:35

It looks like a security issue - but I don't have enough information to suggest anything useful. Can you read through this page and look at the various scenario's described there to see if any of that applies to you and whether their remedies will help? http://support.microsoft.com/kb/918760

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources