July 21, 2009 at 1:43 pm
BOL says "The column separator can be any 8-bit character." I have tried the BCP "\t" symbol, as well as entering the character code for CHAR(9) using the [Alt] key and the numeric keypad. I haven't been able to find any resource listing other alternatives. Can anyone provide a definitive answer?
July 21, 2009 at 1:46 pm
After re-re-re-reading the entry in BOL, I tried enclosing the character code for [Tab] in double quotes. That finally worked.
July 21, 2009 at 1:53 pm
Try creating the format file and Bulk insert using the format fil e
format file goes like this , adjust as per your requirement (number of columns and columnnumber ) and save as .fmt extension
9.0
9
1SQLCHAR010""1col1SQL_Latin1_General_CP1_CI_AS
2SQLCHAR08""0delimiter1SQL_Latin1_General_CP1_CI_AS
3SQLCHAR010""2col2SQL_Latin1_General_CP1_CI_AS
4SQLCHAR08""0delimiter2SQL_Latin1_General_CP1_CI_AS
5SQLCHAR010""3col3SQL_Latin1_General_CP1_CI_AS
6SQLCHAR08""0delimiter3SQL_Latin1_General_CP1_CI_AS
7SQLCHAR010""4col4SQL_Latin1_General_CP1_CI_AS
8SQLCHAR08""0delimiter4SQL_Latin1_General_CP1_CI_AS
9SQLCHAR010"\r"5col5SQL_Latin1_General_CP1_CI_AS
Then use the bulk insert
BULK INSERT dbo.tbltest FROM 'FILE LOACATION'
WITH (FORMATFILE = 'format file location .fmt', MAXERRORS = 50000)
go
July 21, 2009 at 2:07 pm
This is the output of a T-SQL statement contained within a batch of statements. A BCP format file will not help.
July 10, 2015 at 1:35 pm
Hi...
I'm trying to use SQLCMD to dump a few tables out to CSV files.
Several of them have columns that contain commas, so I'm TRYING to use TAB as a column separator.
I'm attempting this via a batch file... but tab isn't working; here's the line:
"C:\The\Path\To\SQLCMD" -S MYSERVER\INST -d TheDb -Q "SET NOCOUNT ON SELECT * FROM TheTable" -s"\t" -o "MyOutfile.CSV"
...Which isn't working... I finally tried entering it into a CMD box, then replacing the \t with the old "alt-number keypad 9" deal, but that doesn't work either.
(also tried it with and without a space between -s and "\t" ... also tried -s "CHAR(9)" ... but no joy; argh!)
Any ideas what I'm not doing right?
(Running against SQL2008r2 Enterprise)
---
Ok... additional observation (added a few hours after initial post)...
I open the csv file with Notepad++ and I see that YES, there IS a tab character separating the columns.
But like I said above, Excel isn't seeing it right... all the columns from the table end up in the first Excel column.
I think I have an idea what the cause is:
Many or all the columns here are CHAR not VARCHAR datatype; I suspect that is confusing Excel and preventing it from showing the data in multiple columns. It's about quittin' time but I will try to chop all spaces out of the CSV and see if that version gets interpreted correctly by Excel.
---
Hrmmm... did that... I think I *just* realized that Excel won't interpret VALUE1(tab)VALUE2 as 2 values to go into 2 separate cells.
July 14, 2015 at 11:17 am
Well.. the workaround/fix is a snooze (now that I know it). Rename the CSV file to TXT. Then Excel properly interprets the TAB as a column separator.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply