Solved

outputing to pipe-delimited flat file with quoted identifier

Posted on 2011-02-11
10
794 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 51

Expert Comment

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

Expert Comment

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

Author Comment

by:sammySeltzer
ID: 34875256
Use of "|" is user requirement.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 51

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 51

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 28

Author Comment

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

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

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 51

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 51

Accepted Solution

by:
HainKurt earned 500 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 28

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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