Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
biniman
Starting Member
6 Posts |
 Posted - 2012-02-12 : 06:20:39
|
| Hello All,I am a new comer to Microsoft SQL. In fact I have only just started to learn the basics. However, I have an important piece of data in Excel that I need to move to SQL. The Excel file contains a sizeable amount of data and manually retyping it into SQl database table is almost unthinkable.I have converted the Excel file into a tab separated text file. When I issue this command: bcp test.dbo.DataTransferTest IN "Production.txt" -S "SQLVS" -T -cI get this error message: "SQLState = 22005, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification"I noticed that the date column in my SQL database table is responsible for the error. When I changed the data type from date to varchar, I was able to copy the data into the SQL table. When I try to change from varchar to date, SQL says: "Unable to modify table. Conversion failed when converting date and/or time from character string". But I do need this column to be a date data type. I will appreciate your suggestions. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-12 : 10:26:55
|
| What value(s) is it choking on?You'll need to clean up the Excel file to have valid dates. |
 |
|
|
biniman
Starting Member
6 Posts |
Posted - 2012-02-12 : 11:46:59
|
| Thanks. There was a mix up in locale. The client computer on which the Excel file was created had a date format in dd/mm/yyy and the SQL server had a date format in mm/dd/yyyy. Any cell in the Excel file that had a date such as 15/12/2012 caused the conversion error in my SQL table. I changed the date format in the Excel file to mm/dd/yyy and that was it; problem solved.Thanks a lot for the suggestion and the time to even stop by and read my post. I am counting on you guys to help me out with challenges that are certainly waiting to surface. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-12 : 13:21:19
|
quote:
Originally posted by biniman
Thanks. There was a mix up in locale. The client computer on which the Excel file was created had a date format in dd/mm/yyy and the SQL server had a date format in mm/dd/yyyy. Any cell in the Excel file that had a date such as 15/12/2012 caused the conversion error in my SQL table. I changed the date format in the Excel file to mm/dd/yyy and that was it; problem solved.Thanks a lot for the suggestion and the time to even stop by and read my post. I am counting on you guys to help me out with challenges that are certainly waiting to surface.
see why following an umambiguos date format is important in sql serverhttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
biniman
Starting Member
6 Posts |
Posted - 2012-03-02 : 09:12:41
|
| Thanks once again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-02 : 12:40:46
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Bulk Copy Programme unable to convert data