September 7, 2011 at 12:13 pm
Is there a way to capture improperly formatted source records when all of a source file's records contain one too many fields? I have a source file containing 11 tab-delimited fields of data when there should be 10. The BULK INSERT generates no error file, and the last two fields are concatenated together and the result is inserted into the table's 10th column, which produces bad results downstream. The strange thing is, when there are 13 tab-delimted fields in the source file, the error file is correctly generated.
CREATE TABLE #temptable
(
period VARCHAR(6),
scenario VARCHAR(24),
cost_center VARCHAR(24),
natural_account VARCHAR(24),
channel VARCHAR(24),
product VARCHAR(24),
entity VARCHAR(4),
location VARCHAR(24),
project VARCHAR(24),
amountVC VARCHAR(53)
);
BULK INSERT #temptable FROM 'c:\data.txt'
WITH (CHECK_CONSTRAINTS,KEEPNULLS,ROWTERMINATOR='',ERRORFILE='c:\error.txt');
Content of c:\data.txt:
YTDM4Act12_local_inpAvailSupplyXXX1030OTHHNAXX1394
YTDM4Act12_local_inpApprvdSupplyXXX1030OTHHNAXX1394
YTDM4Act12_local_inpAvailCopackXXX1030OTHHNAXX46
YTDM4Act12_local_inpApprvdCopackXXX1030OTHHNAXX46
YTDM4Act12_local_inpItmPurchXXX1030OTHHNAXX17533
YTDM4Act12_local_inpSpecsWritnXXX1030OTHHNAXX17533
YTDM4Act12_local_inpSpecsSignedXXX1030OTHHNAXX17533
YTDM4Act12_local_inpQRMPXXX10301240XX
YTDM4Act12_local_inpCustCmplFSXXX10301240XX6
YTDM4Act12_local_inpRetComplXXX10301240XX
YTDM4Act12_local_inpUnitProdMMXXX10301240XX
YTDM4Act12_local_inpCCRatioRTXXX10301240XX
YTDM4Act12_local_inpYldVarvStdXXX10301240XX
YTDM4Act12_local_inpYldStdvPerXXX10301240XX
YTDM4Act12_local_inpReworkXXX10301240XX
YTDM4Act12_local_inpDisposalXXX10301240XX
YTDM4Act12_local_inpScrapXXX10301240XX
YTDM4Act12_local_inpRWCostsXXX10301240XX
YTDM4Act12_local_inpIdleLaborXXX10301240XX
YTDM4Act12_local_inpSubIngrdXXX10301240XX
YTDM4Act12_local_inpRecallXXX10301240XX
YTDM4Act12_local_inpWithdrawlsXXX10301240XX
YTDM4Act12_local_inpSignifIncdXXX10301240XX
YTDM4Act12_local_inpTraceExPlXXX10301240XX201205
YTDM4Act12_local_inpTraceExCompXXX10301240XX201110
Any feedback is appreciated.
September 9, 2011 at 7:18 am
You could use FORMATFILE for that. It allows you to specify the definition of the file you want to import. Should work for situations like that.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply