Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT into Excel leaves values as text


INSERT into Excel leaves values as text

Author
Message
smv929
smv929
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 1129

I use TSQL to create Excel files and send them as reports to a list of people. I use the OPENROWSET function to do the insert into the Excel file. The problem is that all values appear as text in the resulting Excel file. You can't even use the values in SUM functions until you convert it to a real value, which you can do manually by selecting the cell, pressing [F2], and then [Enter]. This converts it to a value, but obviously this is not the solution. I cannot figure out how to control the formatting. Must be a simple way. Anyone know? Thanks.

--smv

Sample OPENROWSET to insert into Excel
____________________________________
insert into OpenRowSet ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=Yes; DATABASE=\\server\folder\GMReport-01-25-2006.xls', 'select * from [Sheet1$A460000]') select * from ##OrderInfo where dept = 'abc'




smv929
Andy Robertson
Andy Robertson
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 1

Hi, I've got a quick fix but probably not the real answer to your problem.

If you copy a blank cell and do a paste special / values / add to the range of text that you want as numbers it should sort out your immediate problem. Excel tries to add zero to all of the range and converts the text to values.





sqltung
sqltung
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 585
I checked this out using your method, as well as creating a linked server to the Excel file.

No matter what you do with the data, even using CAST or CONVERT, Excel does not recognize the numbers as numeric data.

You might have to add another step in which you run some VBA code in Excel
that re-formats the columns.
Doug Smith-377834
Doug Smith-377834
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 12
I'm using OpenRowSet to successfully export data from a SQL Server table to Excel as well... however all data is coming down as text.

I've tried formatting the target spreadsheet as numeric to no avail. Is there something specific I need to do to get numbers and dates to export in their native format?

Thank you.

Doug

Daniel Barry
Daniel Barry
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
Short answer Multiply them By 1 (in another formatted sheet)

Too little too late probably, but in another sheet within the same workbook (ie Sheet2) add a link to the sheet with the data:

=If(Len(Sheet1!B2) =0,"",Sheet1!B2*1)

This way if it's text it will show up as text, if it's blank, it will show as blank, if it's numeric, it will be numeric... winner winner chicken dinner....

Good luck!!
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
Why don't you use linked spreadsheets rather than exporting to Excel? If you have to send the same report out every week, or whenever, you could use a linked spreadsheet instead, then your recipients could refresh the data when ever they wanted. You can either make the record source of the spreadsheet a view or table, or you can use Excels query builder to make a query and store it in the Excel file. I have tons of these, and my users love 'em, and they never have to pester me for reports, a big plus.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8959 Visits: 19017
Hi smv

I'm using linked spreadsheets and doing updates, but the principle is probably the same - use a template spreadsheet and ensure that the columns you want numeric, are formatted as the correct numeric type. Provided that the first 8 values in a column are numeric, the driver will continue to write numeric values in the rest. I've got sp's for a) copying/renaming template to target directory and b) linking to a spreadsheet$worksheet, and sample code for doing updates - will post if you wish. It's been used for data migrations and will require tweaking and tidying up if you're working in a production environment.
Incidentally, using this method allows you to mimic a VLOOKUP with considerably more flexibility than Excel offers, e.g. UPDATE FROM with a JOINed source.

Cheers

ChrisM

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
darth_vodka
darth_vodka
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 411
You can't even use the values in SUM functions


well...you can

=SUM(F2:F6*1)

or

=SUM(VALUE(F2:F7))

all you have to do it convert to a number from text in the formulae
bvdrsganesh
bvdrsganesh
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 23
Andy Robertson Thanks for your quick solution to add blank cell. It worked well.
I added blank row with values 0.00 in Number columns as Hidden row. then it TSQL writes into excel in required number format instead of texts.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search