question

nevada1978 avatar image
nevada1978 asked

Using SQLDecimal in Format File for Bulk Insert

I am trying to execute a bulk insert in SQL using a non-XML format file. As long as decimals are not involved there is no problem. As soon as I input SQLNUMERIC or SQLDECIMAL, the errors begin. Below is a very simple example. First, is the create table script and then the data. Create Table create table example ( stat numeric(7,2), average decimal (7,2) ); Sample Data 123.45,12345.678 124.45,12345.987 Format File 11.0 2 1 SQLNumeric 1 19 "," 1 stat "" 2 SQLdecimal 1 19 "\r\n" 2 average "" Bulk insert example from 'C:\Users\myname\Desktop\ example.txt' WITH (FORMATFILE = 'Q:\ exampleformat.txt' ); Msg 4824, Level 16, State 1, Line 6 Cannot bulk load. Invalid data type for column number 1 in the format file "Q:\ exampleformat.txt". Does anyone have any ideas as to why even the most simple of examples fails? BTW: if I change it sqlchar and 0 for prefix, i get the following error. Msg 4824, Level 16, State 1, Line 6 Cannot bulk load. Invalid data type for column number 1 in the format file "Q:\ exampleformat.txt".
ssmsbulk-insertformat
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.