BCP is a command line tool for exporting and importing data from tab-delimited files, and believe me when I say it's fast, it's FAST. When you are trying to insert hundreds of thousands of records into a table in Sql Server, normal inserts can take hours. With BCP, we're talking mere seconds.
There is a drawback, however. The tool is not without it's flaws. The BCP export command will spit out a query to a tab-delimited file, which you can then use to import that data at a later time. But there are times that BCP doesn't quite do its job right. You'll find what I refer to as "tab-drift", where BCP will insert incorrect numbers of tabs, particularly when your data has nulls or empties in some of the columns. What ends up happening is when you import your data, certain rows end up with the columns shifted along, so what was supposed to be in column A ends up in column B, and so on.
Sometimes BCP can correct this if you re-run the BCP command on the original table to re-export the data. Other times, no such luck. In this case, you'll probably need to correct a couple rows by hand. First you need to find the offending rows (please don't let there be too many!), then you have to fix them in the bcp export file.
And how hard is this? Have you ever opened a tab-delimited file before? If not, there's not much to look at. A lot of text, a lot of space. Nothing else.
Here we go in notepad...
In Visual Studio...
You need to be able to see the tabs. You can do this quite easily by using a good text editor, like Scite, or if you have Visual Studio, use the following command ctrl+E, S
What you'll get now is much more usable...
Each of those little blue arrows are tabs. Tabs indicate the separator between columns. If you need to drop in null / blank columns, just drop in additional tabs as needed and you should be all fixed up.
To turn off viewing tabs, just hit the same ctrl+E, S key again.
Hope this helps. It definitely saved me.
No comments:
Post a Comment