bcp with xml insert

Question

bcp with xml insert

jerome.morris

SSCrazy

Points: 2829
More actions
August 22, 2012 at 2:35 pm

#263214

Hi all I am trying to insert a flat text fixed width in to SQL 2008r2. I have tried but not winning can anybody please look at my attemp and tell me where i am going wrong?
<?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="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="5" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="6" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="7" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="10" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="11" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="12" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="13" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="14" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="15" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="16" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="17" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="18" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="19" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="20" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="21" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="22" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="23" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="24" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="25" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="26" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="27" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="28" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="29" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="30" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="31" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="32" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="33" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="34" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="35" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="36" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="37" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="38" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="39" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="40" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="41" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="42" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="43" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="44" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="45" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="46" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="47" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="48" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="49" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="50" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="51" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="52" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="53" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="54" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="55" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="56" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="57" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="58" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="59" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="60" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="61" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="62" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="63" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="64" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="65" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="66" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="67" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="68" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="69" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="70" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="71" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="72" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="73" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="74" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="75" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="76" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="77" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="78" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="79" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="80" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="81" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="82" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="83" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="84" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="85" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="86" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="87" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="88" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="89" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="90" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="91" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="92" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="93" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="94" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="95" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="96" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="97" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="510" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="98" xsi:type="NativeFixed" LENGTH="4"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="MachineName" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="2" NAME="ModeName" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="3" NAME="FileName" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="4" NAME="JobName" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="5" NAME="UserName" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="6" NAME="TransactionKey" xsi:type="SQLNVARCHAR" LENGTH="5"/>
<COLUMN SOURCE="7" NAME="StartDate_Time" xsi:type="SQLNVARCHAR" LENGTH="25"/>
<COLUMN SOURCE="8" NAME="StartDate" xsi:type="SQLDATE"/>
<COLUMN SOURCE="9" NAME="StartTime" xsi:type="SQLTIME" SCALE="7"/>
<COLUMN SOURCE="10" NAME="StartYYYY" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="11" NAME="StartMM" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="12" NAME="StartDD" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="13" NAME="StartHH" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="14" NAME="StartNN" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="15" NAME="StartSS" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="16" NAME="Spare1" xsi:type="SQLNVARCHAR" LENGTH="6"/>
<COLUMN SOURCE="17" NAME="EndDate_Time" xsi:type="SQLNVARCHAR" LENGTH="30"/>
<COLUMN SOURCE="18" NAME="EndDate" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="19" NAME="EndTime" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="20" NAME="EndYYYY" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="21" NAME="EndMM" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="22" NAME="EndDD" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="23" NAME="EndHH" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="24" NAME="EndNN" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="25" NAME="EndSS" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="26" NAME="Spare2" xsi:type="SQLNVARCHAR" LENGTH="6"/>
<COLUMN SOURCE="27" NAME="RunTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="28" NAME="DelayTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="29" NAME="MachineStopTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="30" NAME="OperatorStopTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="31" NAME="MachineFaultTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="32" NAME="OperatorFaultTime" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="33" NAME="OldPiecesFed" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="34" NAME="OldPiecesCompleted" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="35" NAME="NumMachineStops" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="36" NAME="NumOperatorStops" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="37" NAME="NumDelays" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="38" NAME="Feeder_00_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="39" NAME="Feeder_01_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="40" NAME="Feeder_02_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="41" NAME="Feeder_03_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="42" NAME="Feeder_04_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="43" NAME="Feeder_05_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="44" NAME="Feeder_06_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="45" NAME="Feeder_07_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="46" NAME="Feeder_08_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="47" NAME="Feeder_09_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="48" NAME="Feeder_10_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="49" NAME="Feeder_11_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="50" NAME="Feeder_12_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="51" NAME="Feeder_13_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="52" NAME="Feeder_14_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="53" NAME="Feeder_15_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="54" NAME="Feeder_16_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="55" NAME="Feeder_17_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="56" NAME="Input_Feeder_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="57" NAME="Input_SubFdr_1_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="58" NAME="Input_SubFdr_2_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="59" NAME="Input_SubFdr_3_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="60" NAME="Input_SubFdr_4_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="61" NAME="Input_SubFdr_5_Count" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="62" NAME="Avg_Chassis_Speed" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="63" NAME="Shift" xsi:type="SQLINT"/>
<COLUMN SOURCE="64" NAME="Total_Pcs_Outsorted" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="65" NAME="Total_Pcs_Outsorted_Good" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="66" NAME="Total_Pcs_Outsorted_Maybe" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="67" NAME="Total_Pcs_Outsorted_Bad" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="68" NAME="Total_Pcs_Outsorted_Unk" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="69" NAME="Bin_01" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="70" NAME="Bin_02" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="71" NAME="Bin_03" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="72" NAME="Bin_04" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="73" NAME="Bin_05" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="74" NAME="Bin_06" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="75" NAME="Bin_07" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="76" NAME="Bin_08" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="77" NAME="IST_Bin1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="78" NAME="IST_Bin2" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="79" NAME="IST_Bin3" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="80" NAME="IST_Bin4" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="81" NAME="IST_RunOut" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="82" NAME="Mtr1_NoPrint" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="83" NAME="Mtr2_NoPrint" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="84" NAME="Mtr3_NoPrint" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="85" NAME="Mtr4_NoPrint" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="86" NAME="Edge_Mark1" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="87" NAME="Edge_Mark2" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="88" NAME="Edge_Mark3" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="89" NAME="No_Seal" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="90" NAME="Empty_Cycles" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="91" NAME="Filled_Cycles" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="92" NAME="MidRunTime" xsi:type="SQLNVARCHAR" LENGTH="8"/>
<COLUMN SOURCE="93" NAME="SubShiftExt" xsi:type="SQLNVARCHAR" LENGTH="2"/>
<COLUMN SOURCE="94" NAME="ShiftDateAdjust" xsi:type="SQLNVARCHAR" LENGTH="10"/>
<COLUMN SOURCE="95" NAME="PiecesFed" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="96" NAME="PiecesCompleted" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="97" NAME="Spare3" xsi:type="SQLNVARCHAR" LENGTH="68"/>
<COLUMN SOURCE="98" NAME="ID" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>]
and this query
BULK INSERT ProdDataTB
FROM 'C:\bcp\PRODDATA.txt'
WITH (FORMATFILE = 'C:\bcp\TableFMT.xml');
SELECT * FROM ProdDataTB
The table I want to insert into is ProdDataTB
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

How was this format file generated? I'm making a guess here but I'd suspect that it doesn't really describe the data file. Does the data file have a byte (or 2) in front of every column that holds the length of the data column? Are some of the data columns really in a SQLFloat format, or are they readable text?

What's the data file really like? Fixed width columns in readable text? If the format file was generated based on the target table, you can go back and do it again and don't answer the questions based on the table, answer them based on the input file. For example, you don't have a prefix column and all the data types are SQLCHAR and the columns have a fixed length.

If what I've stated above is the case, you need to correct that and give it another shot. If that's incorrect, what error message are you getting? Can you post a sample of data and the table DDL so we can test any solution someone might provide?

And then again, I might be wrong ...
David Webb

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

Hi there I have changed the file now and its fails on all datetime/date..

This is my new formatfile

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

So, it looks like you have one date column and one time column. Are those the ones that are failing? Can you show us some samples of the values for those columns in the rows that are failing? Can you run the bcp with the -e option and post the error file?

And then again, I might be wrong ...
David Webb

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

Hi David, I am trying now to use the ssis import wizard, but each row is not ending correctly its staggering the data. How do I tell that after x amount of char it the end of a row.

I will post what you asked in a min

Sample Data

APS08 C5_LAKE_WE08075_002_0807500236ADAMSAG 26013Sun Jan 01 07:21:59 2012 01/01/201207:21:59 20120101072159 Sun Jan 01 07:30:42 2012 01/01/201207:30:42 20120101073042 196 27 88 212 88 212 498 483 3 0 800000487000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001147000011470000000000000000000000000000000013463 2 0 0 2 0 0 2 0 0 0 488 245 498 483

APS05 C5_LAKE_WE08066_005_0806600536HUGHEPE 28708Sun Jan 01 07:08:38 2012 01/01/201207:08:38 20120101070838 Sun Jan 01 07:47:25 2012 01/01/201207:47:25 20120101074725 1467 45 295 520 295 520 5476 5457 12 2 1500005467000000000000000000000000000000000000000000000109000042480000000000000000000000000000000000000000000000000000000000000000000000000000000000009894000000000000989400000000000000000000000014090 18 0 0 14 4 11 2 0 0 5 5467 275 5476 5457

FPS03 C5_EAST_CU07703_001_0770300136KACPRAG 12275Sun Jan 01 07:22:38 2012 01/01/201207:22:38 20120101072238 Sun Jan 01 07:48:06 2012 01/01/201207:48:06 20120101074806 400 10 337 781 337 781 528 527 12 1 900000530000005070000000000000000000000000000000000000020000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001038000000000000103800000000000000000000000005184 2 0 0 2 0 2 0 530 46 528 527

APS07 C5_LAKE_WE08076_003_0807600336ADAMSAR 29197Sun Jan 01 07:26:21 2012 01/01/201207:26:21 20120101072621 Sun Jan 01 07:49:12 2012 01/01/201207:49:12 20120101074912 530 44 223 574 223 574 1734 1710 8 0 1200001717000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004205000042050000000000000000000000000000000013883 4 0 0 4 0 0 4 0 0 0 1719 325 1734 1710

jerome.morris SSCrazy Points: 2829 More actions · Answer 5

Hi all, if my file has a format date like mm/dd/yyyy and the server wants yyyy/mm/dd will this create this error ?

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (StartDate).

The file in question is fixed width flat text, this is my new format file

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

Is it possible to cast or covert the data going in so I dont get these errors

Jay

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Hard to tell, since I just did a cut and paste, but I'd expect the records in a fixed format file to end with the same length and the data elements to line up when examined visually. Your file looks like it has a ragged end and the data elements, after a certain point, don't line up, so a format file that expects them to line up won't be effective. Where is this file coming from? Can you ask them to send it to you with a delimiter like a tab or a pipe (|)?

And then again, I might be wrong ...
David Webb