Bulk insert missing first row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk insert missing first row

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-03-03 : 10:45:53

I'm losing the first record when bulk inserting files, and I can't see what the problem is.

I don't know if pasting the contents of the file will work but I've included them

I've been testing it pout using openrowset
select *
FROM OPENROWSET (BULK 'C:\Test.csv',
FIRSTROW=2,
FORMATFILE='c:\eod_analyytics.XML') AS t1

this is a test file

BusinessDate,ClientEntityId,CMAEntityId,EntityName,Seniority,Currency,Tenor,RestructuringType,Bid,Offer,UpfrontBid,UpfrontOffer,Delta,Reference,MidDailyHigh,MidDailyLow,AggregationQuoteTimeStamp,AggregationWindow,AggregationContribututors,AggregationContribututedQuotes,AggregationMaximumSources,AggregationMaximumQuotes,DailyContributors,DailyContributedQuotes,DailyMaximumSources,DailyMaximumQuotes,CumPDF,HazardRate,PV01,RecoveryRate,CMAShortTermImpliedRating,CMALongTermImpliedRating,ObservedDerivedIndicator
25/02/2009 16:30:00,,7701,"Abu Dhabi, City of",Senior,USD,0.5,MR,186.06,243.06,,,,,,,,,,,,,,,,,1.64,2.9,0.57,25,AA+,AA-,D
25/02/2009 16:30:00,,7701,"Abu Dhabi, City of",Senior,USD,0.5,MR,186.06,243.06,,,,,,,,,,,,,,,,,1.64,2.9,0.57,25,AA+,AA-,D

and this is the xml file

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=","" MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=""," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="22" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="24" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="25" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="26" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="27" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="28" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="29" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="30" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="31" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="32" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="33" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="250" COLLATION="Latin1_General_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="BusinessDate" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="ClientEntityId" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="CMAEntityId" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="EntityName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="Seniority" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="Currency" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="Tenor" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="RestructuringType" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="Bid" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="Offer" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="UpfrontBid" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="UpfrontOffer" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="Delta" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="14" NAME="Reference" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="15" NAME="MidDailyHigh" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="16" NAME="MidDailyLow" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="17" NAME="AggregationQuoteTimeStamp" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="18" NAME="AggregationWindow" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="19" NAME="AggregationContribututors" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="20" NAME="AggregationContribututedQuotes" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="21" NAME="AggregationMaximumSources" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="22" NAME="AggregationMaximumQuotes" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="23" NAME="DailyContributors" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="24" NAME="DailyContributedQuotes" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="25" NAME="DailyMaximumSources" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="26" NAME="DailyMaximumQuotes" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="27" NAME="CumPDF" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="28" NAME="HazardRate" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="29" NAME="PV01" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="30" NAME="RecoveryRate" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="31" NAME="CMAShortTermImpliedRating" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="32" NAME="CMALongTermImpliedRating" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="33" NAME="ObservedDerivedIndicator" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

I don't know if there are any unusual characters in the string I should look for that won't survive cutting and pasting.

any suggestions would be appreciated.

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:51:32

quote:
Originally posted by Sean_B

select *
FROM OPENROWSET (BULK 'C:\Test.csv',
FIRSTROW=2,
FORMATFILE='c:\eod_analyytics.XML') AS t1

Why FIRSTROW=2 ?
This is the reason. Either change to = 1 or remove the line.

E 12°55'05.63"
N 56°04'39.26"

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-03-03 : 10:55:23

I'm ignoring the header row, these are files that I'm getting from another company.

The problem appears to be the newline character on the first row.

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 10:58:00

What is the source?
Maybe it's not "\r\n" as row terminator, maybe it's only "\r" or only =\n" ?

E 12°55'05.63"
N 56°04'39.26"

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-03-03 : 11:03:04

I'm using \r\n in the fornmat file and it seems to work, the problem is in ignoring the first row. I'm not sure what the source system is of the files.

Sean

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-03-03 : 11:20:42

the problem is that because of the format file the bilk insert command is expecting the first row to have the same layout but it doesn't have a delimiter of ,"

Sean

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 11:28:24

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources