BULK INSERT Format File Problems with SQLNUMERICAL in SQL-Server 2005 Express

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:\MData\Order_F.txt'
WITH
(
FORMATFILE = 'E:\MData\Order_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "\r\n" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".

Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

or

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...

Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

or

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

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)".

Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

There are two different ways you can do this, I believe, and both involve a staging table.

The reason you need a staging table is that you need to import the file into char/varchar columns, else (I believe, not tested, though) that your spaces would be converted into zeroes.
Anyhow, imo a staging table is always the preferred destination of bulk loads, never directly into the production table. =:o)

Ok, you can do this with a table that has the correct number of columns, or in a table with just a single large column wide enough for the entire row.

For the first case, the column lengths should be the #chars of data + number of trailing spaces until the next column's data starting position, assuming that's how your 'fixed length' file is supposed to work. You need a format file for this to work (like the one you have), but change it so that all columns are specified as SQLCHAR instead, and the respective length includes the trailing spaces

For the second variant, the actual load is much simpler. It's really easy to bulk the file into a single row table, no formatfile is needed. Once it's loaded, you insert into a 'proper' table with the intended datatypes etc with a INSERT newTable (col1, col2...) SELECT SUBSTRING(col1, 1, n), SUBSTRING(col2, n, n)..... etc
construct. You can also do inline casting if you'd like, The point is that it's here you do the actual separation of the 'one-liner' into it's respective columns.

Hope it helps some
=;o)
/Kenneth

|||What solved this was importing used a SQL type of SQLCHAR *only*, and not as a mix of SQLCHAR and SQLNUMERIC, with the appropriate SQL-Server datatypes. -- Thanks!!!