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 |
|
ddobbertin
Yak Posting Veteran
51 Posts |
 Posted - 2015-01-15 : 13:42:43
|
| I am producing a tab delimited text file from a query using BCP. The client needs to have empty columns at the end of the file for use later on down the road. So in my BCP QUERYOUT statement I am doing the following:select @cmd = 'bcp "select col1, col2, col3, '''' as col4, '''' as col5 from mytable" queryout "c:\mytextfile.txt" -T -c -S Servername'The issue is my client is telling me that they are trying to import and are seeing null characters in the file instead of empty strings. This causes their import to fail. What am I missing? Instead of '''' as col4 should I use space(1) as col4 maybe? Basically I need col4 and col5 to be empty string values so their import process works correctly. And they can't change their process since they use it for a ton of their other imports. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-15 : 13:44:57
|
| Show us a couple of rows from the output file. And then show us a couple of rows that the client says it needs to look like.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2015-01-15 : 13:56:13
|
| Well the visual difference I don't think will matter much...in my file and the client file they visually are the same (well at least to me). I think the issue is that bcp is outputting an ASCII NUL character between tab delimiters when they are empty. Here are a couple rows from my test file. Not sure if I am supposed to attach this differently, I've never added contents from a text file on here yet. Apologize if it's useless.TITLE FIRST_NAME LAST_NAME ZIP ADDRESS CITY STATE EMAIL PHONE TEST_DRIVE PURCHASE_HORIZON MODEL_INTEREST INTERESTS HAVE_DEALER DEALER_NAME DEALER_ADDRESS DEALER_CITY DEALER_STATE DEALER_ZIP DEALER_PHONE DEALER_CODE First Last 71115 123 Milbank Drive Shreveport LA email@email.COM 7081234567 194 First Last 71105 123 River Road Shreveport LA email@email.COM 7081234567 194 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-15 : 14:09:44
|
| If the files look the same visually, then I would suggest opening the files in a hex editor to see what the differences are. Then you'll know what to use for bcp.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2015-01-15 : 14:24:47
|
| Thanks for the tip...I was opening in regular old notepad...switched to Notepad++ and could see that it was in fact adding the ASCII NUL character. I modified my query to include a NULLIF to remove the ASCII character. Opened in Notepad ++ and they are gone. Not sure if its the right way...but it's a way.Initial query:select @cmd = 'bcp "select col1, col2, col3, '''' as col4, '''' as col5 from mytable" queryout "c:\mytextfile.txt" -T -c -S Servername'With NULLIF to remove the ASCII NUL from the txt file:select @cmd = 'bcp "select col1, col2, col3, NULLIF('''','''') as col4, NULLIF('''','''') as col5 from mytable" queryout "c:\mytextfile.txt" -T -c -S Servername' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-15 : 14:30:15
|
| Sounds like a good solution to me if it works.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ddobbertin
Yak Posting Veteran
51 Posts |
Posted - 2015-01-15 : 14:32:34
|
| Thanks tkizer! Do I need to mark this post as fixed or completed or something? Can I give you credit? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-15 : 15:03:34
|
| Snitz doesn't have those concepts. You can edit the subject by prefixing [RESOLVED] to it. That's what I've seen others do. Saves people from clicking on those.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
[RESOLVED] BCP Adding spaces between empty values?