Using QUOTENAME 1

[gbaughma]

I'm generating a CSV file from a Stored Procedure to email over EFT information.

It's working, except for one issue.

Any routing numbers that come over with a beginning 0 are, of course, stripped off by Excel when the CSV is opened.

I'm using QUOTENAME to put quotes around the fields, like this:

QUOTENAME(dbo.table.RoutingNumber,''"'')

... which works....

but to make Excel see it as a string, I really need to have a leading ' in front of the number. So instead of:
12345678
... I would get
'012345678

... I have tried doing:
QUOTENAME('''' + dbo.table.RoutingNumber,''"'')
... but that didn't work. It's stripping off the '

Can someone point me in the correct way to do this?

Thanks in advance!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[ousoonerjoe]

in your SELECT statement, can you not just do

[RoutingNumber] = '''' + CAST(dbo.table.RoutingNumber AS VARCHAR)

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gbaughma]

Let me try that and see how it works.

Nope. Didn't work. lol


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Ahh... here's the secret to get Excel to treat it as a string.

In the CSV, it needs to say ="012345678"....

So, ousoonerjoe put me on the right track.... it looks like this now:

RoutingNumber = CAST(''='' + QUOTENAME(dbo.table.RoutingNumber,''"'') AS VARCHAR)

... that worked great.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com