Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
Solved
SQL 2008 Bulk Insert issues
Posted on 2011-03-09
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
3
2- +1
12 Comments
Active 1 day ago
what are you using for bulk insert: SSIS; Command line, ?
what an error did you get?
what an error did you get?
Active today
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 ?
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 ?
BULK INSERT ITEM_PROMOS_QUEUE_TST
FROM 'C:\ECSJOBS\TDG08022904182
WITH (FORMATFILE = 'C:\ECSJOBS\PromosPrice.fm
(1 row(s) affected)
FROM 'C:\ECSJOBS\TDG08022904182
WITH (FORMATFILE = 'C:\ECSJOBS\PromosPrice.fm
(1 row(s) affected)
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
10.0
18
1 SQLCHAR 0 2 "," 1 CORP_CODE SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "," 2 IC_ITEM_NO SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "," 3 PROMO_IND SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 30 "," 4 COLUMN_PRICE_AMT ""
5 SQLCHAR 0 30 "," 5 PROMO_PRICE_AMT ""
6 SQLCHAR 0 30 "," 6 PROMO_EXPIRATION_DTE ""
7 SQLCHAR 0 30 "," 7 EF_PRICE_AMT ""
8 SQLCHAR 0 30 "," 8 EF_EXPIRATION_DTE ""
9 SQLCHAR 0 30 "," 9 EH_PRICE_AMT ""
10 SQLCHAR 0 30 "," 10 EH_EXPIRATION_DTE ""
11 SQLCHAR 0 30 "," 11 EL_PRICE_AMT ""
12 SQLCHAR 0 30 "," 12 EL_EXPIRATION_DTE ""
13 SQLCHAR 0 30 "," 13 FE_PRICE_AMT ""
14 SQLCHAR 0 30 "," 14 FE_EXPIRATION_DTE ""
15 SQLCHAR 0 30 "," 15 FG_PRICE_AMT ""
16 SQLCHAR 0 30 "," 16 FG_EXPIRATION_DTE ""
17 SQLCHAR 0 30 "," 17 ST_PRICE_AMT ""
18 SQLCHAR 0 30 "\r\n" 18 ST_EXPIRATION_DTE ""
Active today
OK your format file is likely to be specifying a column for every field in the CSV file. When it encounters a new line instead of the anticipated columns it create the error.
Just as a test, what happens if you specify just a single column terminated by \n\r (copy your format file first - or create a new test one)
Now, is this a legitimate CSV file ? What created it ? Do you have to handle variable length columns ?
Just as a test, what happens if you specify just a single column terminated by \n\r (copy your format file first - or create a new test one)
Now, is this a legitimate CSV file ? What created it ? Do you have to handle variable length columns ?
This file is produced by our mainframe. Yes, we want to insert the row even if it only has 4 columns. It has always left the commas out if it just finds a price, but no govt pricing ehich what the other columns are for.
Active today
You should provide a row terminator character because of different number of columns by row.
Mainframe creates it and yes there are variable length fields. When I pare down the format to the first 4 columns(which are aleways present) and end it with the \rn\ I get
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 17, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 27, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 30, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 43, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 44, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 53, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 56, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 63, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 94, column 4 (COLUMN_PRICE_AMT).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
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)".
which looks like it is complaining about the rows that hve the additioal columns
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 17, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 27, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 30, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 43, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 44, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 53, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 56, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 63, column 4 (COLUMN_PRICE_AMT).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 94, column 4 (COLUMN_PRICE_AMT).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
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)".
which looks like it is complaining about the rows that hve the additioal columns
Active today
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" :
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.
Featured Post
Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video.
If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
- Document Management
- Document Imaging
- Printers and Scanners
- Software-Other
- Images and Photos
- Photos / Graphics Software, OCR, *tiff, *PaperPort, *PDF
Suggested Courses
Course of the Month12 days, 13 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.