Solved

outputing to pipe-delimited flat file with quoted identifier

Posted on 2011-02-11
10
792 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now