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
10
Medium Priority
?
810 Views
Last Modified: 2012-05-11
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


0
Comment
Question by:sammySeltzer
  • 6
  • 4
10 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 34875108
post a sample from csv file
at least first two line
0
 
LVL 61

Expert Comment

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

Author Comment

by:sammySeltzer
ID: 34875256
Use of "|" is user requirement.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 61

Expert Comment

by:HainKurt
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

by:HainKurt
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

by:sammySeltzer
ID: 34876448
Thanks for all your help HainKurt.

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

Author Comment

by:sammySeltzer
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

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
 
LVL 61

Expert Comment

by:HainKurt
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

Open in new window

0
 
LVL 61

Accepted Solution

by:
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)

Open in new window

0
 
LVL 29

Author Comment

by:sammySeltzer
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question