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.
