Trouble BCPing text file dates into date type table columns

Question

Post reply

Trouble BCPing text file dates into date type table columns

stiej1977

Ten Centuries

Points: 1132
More actions
July 6, 2018 at 5:53 am

#414320

Hi,
i'm using BCP to read data from a pipe delimited text file where some columns are dates in dd/mm/yyyy format, and using a format file generated by bcp for the destination table i'm squirting the data into.
i've made the destination sql table from the vendor's data dictionary/documentation that describes the file format, where some of the columns in the file are dates (no times), and so the corresponding column in the table is DATE datatype.
i used this command to create the format file:
bcp db.schema.mytable format nul -t"|" -T -c -f pathtoformatfile.txt -Smysqlinstance
...a sample of the format file produced from the above command (showing two date type columns)....
10  SQLCHAR     0   30  "|"  10 PCODE    Latin1_General_CI_AS
11  SQLCHAR     0   11  "|"  11 PDATE    ""
12  SQLCHAR     0   11  "|"  12 ODATE    ""
13  SQLCHAR     0   1   "|"  13 TYPE     Latin1_General_CI_AS
when i run the bcp command to read the file, use the format file, and insert the data into the corressponding table, i get the followng error.
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification
if i change the table datatypes to varchar from date, then it works (even without reproducing a new format file).
what do i need to do to make it handle the dates in the file and bring them through as dates, into the data type columns in the table?
thanks

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

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

is the source and destination on the same server or could you create a linked server if not?

then insert from select ?

or you could try this as your BCP method Query out, this way you could set a default date or convert the data to always display a date and time

set database=DNMANE

setServer=SERVERNAME

bcp "Select X,Y, DATEFEILED FROM database.dbo.tablename " queryout"MYTABLE.bcp" -E -n -T -S %SERVER%

***The first step is always the hardest *******

stiej1977 Ten Centuries Points: 1132 More actions · Answer 2

thanks, but my source is a text file supplied by a 3rd party, something they produce. so i have no control over the production of that.
i'm not bcp'ing out, i'm bcp'ing in.
i'm playing about with different format files configs but having no luck so far.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

you could use bulk insert instead of BCP

i will send you an example via private message if i can

***The first step is always the hardest *******

stiej1977 Ten Centuries Points: 1132 More actions · Answer 4

thanks if you can, please do. i have started having a play with bulk insert...

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

sent you a PM with a script i created to do a similar task

***The first step is always the hardest *******