| Author |
Topic |
|
Mamsaac
Starting Member
9 Posts |
 Posted - 2011-05-25 : 14:41:45
|
| The files I receive have "True" and "False". We were doing the process with SSIS, but for several reasons we're trying to experiment with bcp instead for importing the file into the database.If I change the True/False to 1/0, the entire process works, but obviously I want to prevent the overhead of rewriting the file (it's millions and millions of rows every day).When I keep the file the way I receive it (that is, with True/False), the error I get is:SQLState = 22018, NativeError = 0Error = [Microsoft][SQL Native Client]Invalid character value for cast specificationAnyone knows a way around this? Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-05-25 : 15:59:50
|
| or get all your data into your final table with an additional column that is of type bit and run an update statement after your batch import..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-25 : 17:57:53
|
| I hesitate to say this in a SQL forum, but, this is precisely the type of situation where Perl excels. A one line in-place edit command using Perl can replace the True/false in the file to 1/0. Millions of lines? Perl can handle that without breaking a sweat. Perl distributions are available free; Activestate Perl is the most popular. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Mamsaac
Starting Member
9 Posts |
Posted - 2011-05-25 : 18:29:42
|
| Or any programming language.I got yet another issue with BCP, but I feel it fits to a new topic. Thanks for the responses. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-25 : 19:34:38
|
| Yes Tara, I would expect that Powershell would perform just as well. I dont have any experience with Powershell, but I cant imagine that it would be inferior.There are couple of things I like about Perl for this type of application.1. It is optimized for handling large files while managing memory well.2. It is scripted, not compiled, so no source code to maintain, etc.3. The syntax is very compact (which of course may make it harder to read which is why people refer to it as "write once" language)What I would not prefer to do in this case is, for example, write a C# program (with StreamWriter or other managed I/O objects) to do this, for the same reasons as I prefer Perl. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Issue with BCP when trying to import bit values