Microsoft SQL Server 2008
Microsoft SQL Server 2008

SQL 2008 Bulk Insert issues

I am having issues with with csv file that works in sql 2000. I have identified why but not how to solve. If a row does not have data for the right most columns it just puts a '\n\r' without commas as placeholders. Bulk insert then ignores the row terminator.
Here is a sample of a typical file - It is the code snippet

20,00021B,N,12.56,,,,,12.34,12/31/2008,12.34,12/31/2008,12.34,12/31/2008,,,12.34,12/31/2008
20,01595J,N,218.07
20,01596J,N,257.99
20,01597J,N,439.83
20,01598J,N,331.91
20,02332I,N,9716.31
20,02333I,N,1505.44
20,02334I,N,1904.18
20,028030,Y,139.23,136.53,04/30/2008
20,028061,Y,349.39,342.61,04/30/2008
20,030028,Y,121.72,119.36,04/30/2008
20,03049I,N,3371.04
20,03051I,N,2527.22
20,03064I,N,5964.34
20,03065I,N,5964.34
20,038086,N,34.03,,,,,33.15,12/31/2008,33.15,12/31/2008,33.15,12/31/2008,,,33.15,12/31/2008

Open in new window

dkochenourAsked: 2011-03-09

Who is Participating?

LVL 54

Mark WillsTopic AdvisorCommented: 2011-03-10

Right...

Few different approaches that can be tried. One approach could be to read in, adjust commas so they align and rewrite the file just to import again, or, import as a single line and manually unpack, or, get the mainframe guys to fix their export ((best method).

Then it also depends on if column names / header exists in the CSV file (because that can change things a lot).

Then it also depends on if you can run xp_cmdshell as well. Lets assume you cannot, and there is no headings, so we really do have to take "manual" control.

So, just by way of example, lets see if we can do the job "manually" :

-- using the bulk insert to load a staging table

if object_id('tmp_staging_table') is not null drop table tmp_staging_table
go
-- create our staging table afresh
CREATE TABLE tmp_staging_table (line varchar(max))
go

-- load it up
BULK insert tmp_staging_table from 'C:\ECSJOBS\TDG080229041829001.832' with (fieldterminator='\r\n', firstrow=1)
go

-- have a look
select * from tmp_staging_table
go

-- then run through a procedure to unpack it - not included just yet - there is one down below...

-- or try a openrowset of the entire row without having to go into a staging area first, but ready for manual unpacking into a staging table
-- first create a format file depicting a single line... (the three lines between the asterixs) saved as 'c:\single_line.fmt' in the example
/*
8.0
1
1       SQLCHAR       0      8000     "\r\n"   1     line           ""
*/
-- now select from the csv file...
select * FROM OPENROWSET(BULK 'C:\ECSJOBS\TDG080229041829001.832'  , FORMATFILE = 'c:\single_line.fmt',FIRSTROW = 1, MAXERRORS=10) AS A
go

-- right, so if they work, can now try using the above and doing the unpacking manually...

;with cte as
(
      SELECT line, ltrim(SUBSTRING(line, n, CHARINDEX(',', line + ',',n) - n)) AS Fields, row_number() over (partition by line order by n) as field_number
      FROM OPENROWSET(BULK 'C:\ECSJOBS\TDG080229041829001.832' , FORMATFILE = 'c:\single_line.fmt',FIRSTROW = 1, MAXERRORS=10) AS A   -- or tmp_staging_table
      CROSS JOIN (SELECT number n FROM master..spt_values WHERE type = 'P') AS Numbers
      WHERE SUBSTRING(',' + line, n, 1) = ','
      AND n < LEN(line) + 1
) 
select [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18] from 
(select line, fields, field_number from cte) src
pivot
(max(fields) for field_number in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18])) pvt

-- so then we can use the above to load a temp table and then use that table to put away, or, format, or, validate then do the put away.
-- we can also do things like select [1] as CORP_CODE, [2] as ....
-- we could also define the first three/four columns in our format file, "line" doesnt have to be a single column of all columns, just those that might need to be manually unpacked.

Open in new window

LVL 43

Eugene ZCommented: 2011-03-10

what are you using for bulk insert: SSIS; Command line, ?
what an error did you get?

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2011-03-10

Can you post full command that you are using?

LVL 54

Mark WillsTopic AdvisorCommented: 2011-03-10

so what has changed - is it the generation of the CSV file, reading it, what creates the CSV without the placeholders for the detailed rows ?

Do you want a routine to read that file regardless ?

Does it have a header row ?

Why arent the commas there ? Is it a multiformat line ?