How to format date field when importing CSV file into SQL Server table using BCP?

0
votes

I'm using BCP and python to import a .CSV file into a SQL Server table. The .CSV file has a column containing date time data and my table also has a corresponding datetime2 column. The problem is that the date in the .CSV file is of format:

2017-01-13T06:02:14Z

which throws an error while running BCP command. My BCP command looks like this:

bcp mydb.dbo.mytable in {0} -R -e errorfile -c -S mysqlserver.com -U myuser -P 'mypassword'

and the error is:

Invalid character value for cast specification @# 2017-01-13T06:02:14Z

If I change the date in my .CSV file to '2017-01-13 12:33:49', BCP works fine. The error is because BCP tool uses ODBC bulk copy API. Therefore, to import date values into SQL Server, bcp uses the ODBC date format (yyyy-mm-dd hh:mm:ss[.f...]).

What is the way around to fix this issue?

Is there way out to change the date format inside of CSV file itself from 2017-01-13T06:02:14Z to yyyy-mm-dd hh:mm:ss?

1
pythonsql-servercsvbulkinsertbcp
any chance you could use SSIS instead of bcp? – ADyson
No. I have not used SSIS before. I'm using python and calling subprocess to execute my BCP command. – Haris Muzaffar
Not having used it before doesn't mean you can't start to use it. It can assist you with the transformation of data in a way that bcp cannot – ADyson

1 Answers

1
votes

One option would be to import to a staging table as a varchar. Once in a staging table you can do a straight cast to datetime2 and populate mytable