Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INSERT into Excel leaves values as text Expand / Collapse
Author
Message
Posted Wednesday, February 01, 2006 2:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:15 AM
Points: 149, Visits: 1,052

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
Post #255083
Posted Thursday, February 02, 2006 1:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 23, 2006 6:15 AM
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.




Post #255190
Posted Thursday, February 02, 2006 9:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:01 PM
Points: 211, Visits: 556
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.
Post #255349
Posted Wednesday, October 25, 2006 1:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 2:32 PM
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
Post #318073
Posted Tuesday, November 13, 2007 4:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 23, 2009 12:27 PM
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!!
Post #421843
Posted Tuesday, November 13, 2007 5:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 2:39 PM
Points: 1,564, Visits: 2,366
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.
Post #421846
Posted Wednesday, November 14, 2007 2:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 6,742, Visits: 12,834
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
Post #421979
Posted Friday, December 14, 2012 6:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 7:45 AM
Points: 59, Visits: 378
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
Post #1396605
Posted Sunday, March 17, 2013 8:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 2013 5:47 PM
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.
Post #1432072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse