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 |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2006-07-04 : 00:28:03
|
| I have a text data file containing numbers whose digits are separated by commas, as shown below:3215343124 1,000,000 N3215314315 2,613,161 Y4145326541 8,166 YIn general, all these values are integers. I want them to be imported into a numeric field in a SQL Server 2000 table. But when I specified "\t" (tab) in the format file it will prompt error "Invalid character value for cast specification". I know one workaround which is to change the SQL server table field to varchar, and change it to money and then to numeric after the bcp import. But is there any workaround without doing these extra steps? I have heard about using pipe (|) as separator but it doesn't seem to work for me. Please advise and thanks a lot!Del Piero |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-04 : 00:55:26
|
| The accepted method IIRC is to import into a staging table, then running an INSERT query to get the data into your 'live' tables. Your staging table can be all VARCHAR fields if you want; you'll just need to explicitly convert the values in your INSERT query. HTH,Tim |
 |
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2006-07-05 : 00:45:02
|
| Thanks. |
|
|
|
|
|
|
BCP in with comma as digit separator