Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# outputing to pipe-delimited flat file with quoted identifier

Posted on 2011-02-11
Medium Priority
810 Views
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.

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


0
Question by:sammySeltzer
• 6
• 4

LVL 61

Expert Comment

ID: 34875108
post a sample from csv file
at least first two line
0

LVL 61

Expert Comment

ID: 34875151
why you are using "|"
what happens if you use -s"<TAB>"
0

LVL 29

Author Comment

ID: 34875256
Use of "|" is user requirement.
0

LVL 61

Expert Comment

ID: 34875302
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

LVL 61

Expert Comment

ID: 34875548
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

LVL 29

Author Comment

ID: 34876448
Thanks for all your help HainKurt.

I will run this shortly and report back to you.
0

LVL 29

Author Comment

ID: 34888790
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


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

LVL 61

Expert Comment

ID: 34889837
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

0

LVL 61

Accepted Solution

HainKurt earned 2000 total points
ID: 34889867
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)

0

LVL 29

Author Comment

ID: 34890297
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
###### Suggested Courses
Course of the Month8 days, 4 hours left to enroll