INSERT into Excel leaves values as text

Question

INSERT into Excel leaves values as text

smv929

SSCrazy

Points: 2889
More actions
February 1, 2006 at 2:32 pm

#93250

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andy Robertson SSC Eights! Points: 935 More actions · Answer 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 Hall of Fame Points: 3962 More actions · Answer 2

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 SSC Rookie Points: 35 More actions · Answer 3

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 Valued Member Points: 60 More actions · Answer 4

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 SSCoach Points: 16108 More actions · Answer 5

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 SSC Guru Points: 186127 More actions · Answer 6

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

darth_vodka Default port Points: 1489 More actions · Answer 7

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 SSC Enthusiast Points: 112 More actions · Answer 8

Andy RobertsonThanks 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.