question

Antony avatar image
Antony asked

Bulk insert with field terminator (";")

Hi SQL Team, I have a call log .csv file that is field terminated with a (;) and text qualified with (") that I need to import into SQL 2005 and need to be able to call it from a stored procedure . However one of the fields can contain the (;) character in the data, which then thows out the rest of the fields on that row. Not ideal. So I have managed to import the CSV file with the field terminator (";") using the 'Import Data...' wizard and then run a routine to strip off the (") on the begining of the first field and the end of the last field.

I have saved my import data routine to an SSIS package in the SQL database, but am struggling to find how to re-use it. So I have now written a bulk insert statement to use (";") as the field terminator??? here is what I have so far;

if exists (select * from sysobjects where id = object_id(N'[dbo].  [fax_log_import_tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)                    
drop table [dbo].[fax_log_import_tmp]                    
GO                    
CREATE TABLE fax_log_import_tmp(                    
[Job_Number] [nvarchar](50) NULL,                    
[Customer_No] [nvarchar](50) NULL,                    
[Job_description] [nvarchar](50) NULL,                    
[Transmitted_pages] [nvarchar](50) NULL,                    
[Status] [nvarchar](50) NULL,                    
[Status_text] [nvarchar](50) NULL,                    
[CSID] [nvarchar](50) NULL,                    
[Date] [nvarchar](50) NULL,                    
[Zone] [nvarchar](50) NULL,                    
[sending_duration] [nvarchar](50) NULL,                    
[fax] [nvarchar](50) NULL                    
) ON [PRIMARY]                    
GO                    
BULK INSERT fax_log_import_tmp                    
FROM 'D:\DBs\Sandbox\Test_Data\Fax_Log.csv'                    
WITH (FIELDTERMINATOR = '"";""')                    

However when I run this, I get the following error message;

Msg 4866, Level 16, State 1, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"

Please help me with the following as I'm a bit of a newbe at this;

a)Where am I going wrong with the Bulk insert statemant???

b)Is this the best way to this??

Thank you in advance.

sql-server-2005ssisbulk-insert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

Do you have header information on the first row by any chance?

How does ";" deal with numeric fields? Your first column is a number by the title, so have you wrapped it in ";"?

More information is required before we can help.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Antony avatar image
Antony answered

Hi RickD, Here is a sample;

"Job Number";"Customer No.";"Job description";"Transmitted pages";"Status";"Status- text";"CSID";"Date";"Zone";"sending duration";"fax"            
"719962";"12876";"974738/30/11752 3t";"0";"1003";"Blocked faxnumber: server-blocklist";"";"2010.03.02 13:06:56";"7";"0";"44114203xxx"            
"720705";"12876";"Resent of Job: 720666;97471338/11759 XLCR Van Fax";"0";"1003";"Blocked faxnumber: server-blocklist";"";"2010.03.03 16:30:18";"7";"0";"44129671xxxx"            

The top line contains the field headers, the second row is a normal record and the 3rd row contains the field deliminator character as part of the data (in the Job description field) (Which is causing me all these problems. . . )

At the moment I'm treating all the fields as text values as I don't need to do anything with them other than get them into a table in the SQL database.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

So you will need to set the FIRSTROW = 2 for a start. please confirm if this solved your problem. Thanks

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.