SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Trying to Bulk Insert from CSV File
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/08/2012 :  13:18:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  13:50:14  Show Profile  Reply with Quote
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.
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/09/2012 :  11:47:30  Show Profile  Reply with Quote
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 ?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:11:03  Show Profile  Reply with Quote
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.
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/09/2012 :  12:22:00  Show Profile  Reply with Quote
" " or just one
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:27:01  Show Profile  Reply with Quote
Just one double-quote
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/09/2012 :  12:45:13  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:50:02  Show Profile  Reply with Quote
That is okay; it has the smarts to process the data correctly in both cases. Give it a try and you will see :)
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/09/2012 :  13:14:58  Show Profile  Reply with Quote
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)
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/09/2012 :  13:31:44  Show Profile  Reply with Quote
It worked.. Ran like a charm..
Thanks for your help Sunita. I really appreciate this.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  13:39:33  Show Profile  Reply with Quote
You are quite welcome, o ye, of little faith
Go to Top of Page

Sarath_Aluri
Starting Member

USA
10 Posts

Posted - 11/16/2012 :  11:20:47  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:29:35  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000