outputing to pipe-delimited flat file with quoted identifier

Greetings mates,

There is probably something simple I am overlooking here and I have been looking at this now for over 45 minutes.

My eyes hurt.

I am trying to some data from sql server to .csv file.

The file needs to be pipe-delimited with double-quoted identifier.

Everything seems to work fine except each header column and associated values are not being wrapped around double quotes.

Any ideas where I am going wrong?

According to the commands I looked up -I (dash i) is supposed to enable double quotes as text identifier.

However, I don't see the double quotes when viewing the outputted data.

Any ideas where I overlooking things or screwing them up?

Here is the "mostly working" code.

And thanks in advance

sqlcmd -S crt3 -i c:\TYLER\ENSA.sql -o c:\inetpub\ftproot\PROD\ENSA.csv -h 8192 -s"|" -w 5000 -W -I

Open in new window


LVL 29
sammySeltzerAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
and this is the csv file that I generated using

sqlcmd -S NTWBUCKM5217X\SQLEXPRESS -i d:\hk\ee\sql\test.sql -o d:\hk\ee\sql\test.csv -h 8192 -s"|" -w 5000 -W -I

and this is test.sql

select
id,
dealid '"Deal ID"',      
'"' + PmtFrequency + '"' '"Pmt Frequency"',
PmtAmount '"Pmt Amount"'
from EE.dbo.deals
id|"Deal ID"|"Pmt Frequency"|"Pmt Amount"
--|---------|---------------|------------
1|1200|"Annual"|1000
2|1200|"Annual"|1250
3|1200|"Monthly"|750
4|1300|"Annual"|825

(4 rows affected)

Open in new window

0
 
HainKurtSr. System AnalystCommented:
post a sample from csv file
at least first two line
0
 
HainKurtSr. System AnalystCommented:
why you are using "|"
what happens if you use -s"<TAB>"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sammySeltzerAuthor Commented:
Use of "|" is user requirement.
0
 
HainKurtSr. System AnalystCommented:
if your qry is not too long do this

select
'"' + col1 + '"' col1,
col2,
'"' + col3 + '"' col3,
...
from myTRable(s)
where ...

ie, put " around your varchar values in sql...
0
 
HainKurtSr. System AnalystCommented:
http://msdn.microsoft.com/en-us/library/ms174393.aspx

-I is to enable " in sql
so when -I is used you can run a query like

select "col name" from "mytable"
0
 
sammySeltzerAuthor Commented:
Thanks for all your help HainKurt.

I will run this shortly and report back to you.
0
 
sammySeltzerAuthor Commented:
Hi HainKurt,

Sorry I was unable to test as indicated above due to the maintenance window that shut down my PC and didn't restart till this morning.

I have just finished trying your suggestions and none of the 2 worked.

First, I tried this:

select
'"' + col1 + '"' col1,
col2,
'"' + col3 + '"' col3,
...
from myTRable(s)
where ...

I would get results and in the format that I want.

However, for some reason, Header Columns are not included.


When I try this example below:
-I is to enable " in sql
so when -I is used you can run a query like

select "col name" from "mytable"

I get invalid object name, referring to the table Name.

So, I am sure I am doing something wrong.

Here is the code I attempted to use:

SELECT "[USEDRUG]","[USEAGE]" FROM "[ENSA]" WHERE ID between 559 and 600

Open in new window


This is the inputfile on the sqlcmd code below:
sqlcmd -S crt3 -i c:\TYLER\ENSA.sql -o c:\inetpub\ftproot\PROD\ENSA.csv -h 8192 -s"|" -w 5000 -W -I
0
 
HainKurtSr. System AnalystCommented:
try this

look at the second query, the headers & PmtFrequency  is wrapped with " in the result...
select * from deals

id	dealid	PmtFrequency	PmtAmount
1	1200	Annual	1000
2	1200	Annual	1250

select 
  id, 
  dealid '"Deal ID"',	
  '"' + PmtFrequency + '"' '"Pmt Frequency"',
  PmtAmount '"Pmt Amount"'
from deals

id	"Deal ID"	"Pmt Frequency"	"Pmt Amount"
1	1200	"Annual"	1000
2	1200	"Annual"	1250

Open in new window

0
 
sammySeltzerAuthor Commented:
HainKurt,

Your solution worked really good for me as far as the quoted identifiers.

One thing that was missing is quoting the integers and date data types.

A few more research produced what I call the perfection solution.

I found a funtion called QUOTENAME().

select QUOTENAME(ID, '"') as '"ID"'
...
...
where
...

and it seems to have satisfied all my requirements.

Thanks a lot for all your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.