Multi delimmited file

1

I have a file that has multiple delimiters

"ui#"|"zip_code","first_name"."last_name"!"address_1"^"address_2":"city""state";"street_number"/"address1_key" "429874227"|"00501","John"."Guenther"!"4291 Chestnut Rdg"^"":"Holtsville""NY";"4291"/"5010001913"
"429874227"|"00501","JACK"."Guenther"!"4291 Chestnut Rdg"^"":"Holtsville""NY";""/"5010001913" "40944614"|"01001","Robert"."Aurnhammer"!"PO Box 2"^"":"Agawam""MA";"2"/"10010002024"
"40944614"|"01001","BOB"."Aurnhammer"!"PO Box 2"^"":"Agawam""MA";""/"10010002024" "431976766"|"01001","Robert"."Aurnhammer"!"PO Box 2"^"":"Agawam""MA";"2"/"10010002024"
"431976766"|"01001","BOB"."Aurnhammer"!"PO Box 2"^"":"Agawam"$"MA";""/"10010002024"

I am attempting to use bulk insert with a format file but have been unsuccessful.
Here is the Format file I created, where am I going wrong?

10.0
10
1 SQLCHAR 0 100 "|" 1 UI# ""
2 SQLCHAR 0 100 "," 2 Zip_Code SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "." 3 first_name SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "!" 4 last_name SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "^" 5 address_1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 ":" 6 address_2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "$" 7 city SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 ";" 8 state SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "/" 9 street_number SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "0x0a" 10 address1_key ""

2

In most cases, it looks like the $ delimiter between city and state is missing.

3

Thanks, that was in my cut. The real data is OK with delimter.
I run the bulk command but get 0 records added

BULK INSERT [dbo].Chandra5b FROM 'D:\Orders And Counts\sbamford\Chandra\T5_Sample.txt'
WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='D:\Orders And Counts\sbamford\Sample_test5_format.fmt',ROWTERMINATOR='0x0a' ,FIRSTROW=2)

4

Typically you use \n for char(10) in format files, but 0x0A should be the same value.

The 100 lengths on the non-char columns are very odd too. Typically the len is much shorter, since the value is an int or date or decimal or etc.

5

I used the 0x0a because it is line feed only on the data.
I tried to create the table as all varchar(100) at first. still loads 0 records.
Not sure why. I thought the quotes messed me up so I removed them, still nothing.
Even made the file all pipe and changed format file to all pipe, still not records loading.
My instinct is that my format file is wrong.
Is there a better way to load a data file with multiple delimiters?

6

No, not that I know of.

Format files are a royal pita to get working, but once they work, they work very well.

7

The easiest way with files that are delimited weirdly is to create a one column staging table. Load the data into it and parse it using SOL to load the target table.