August 23, 2011 at 7:20 pm
When I export numeric data to excel file from sql server 2008 using Openrowset, it is pasted in string format. Upon opening it showing green icon and the data is shifted to left side rather than right. I know I can correct it by clicking on it. The problem is that the user is opening the resulted file not me. So he/she finds it sloppy.
Can I do something that the numeric data is shown in numeric format only?
August 23, 2011 at 9:44 pm
Can you post the code and sample data?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 24, 2011 at 1:19 am
I am making a variable in which I call the OPENROWSET. @query has the select query from a table. @filepath contains the pre made excel file which will be used by Openrowset.
SET @sqlquery='INSERT INTO OPENROWSET(''Microsoft.JET.OLEDB.4.0'','''+'Excel 8.0;Database='+@filepath +';' + ''' ,''SELECT * FROM [Sheet1$]'') ' + @query
The numeric data I select from the table is pasted in text form. I want it in numeric form.
August 24, 2011 at 2:53 am
You can format the column in Excel before doing the import. In the cell immediately below the column name right click, select format cell and select number. Choose the format with 0 decimal places if your data contain only whole numbers.
I use scheduled job steps and dos del and dos copy to do this. Step 1 deletes the old spreadsheet, step 2 creates the new spreadsheet from the correctly formatted template, next step/s export data to worksheets and the final step emails the spreadsheet.
August 24, 2011 at 5:01 am
I tried two methods. First the native datatype was General. So the numeric data posted becomes text. Second, I converted the datatype of first cell, where the numeric will come, to number format. Now openrowset skips the whole row and prints from the below row in text format.
August 24, 2011 at 6:39 am
I just tested changing my OPENROWSET code from Excel 5.0 (originally wrote it for '97) to Excel 8.0, which you use and it behaves in the way you described - reverts to the General format after the import. I'm using Excel 2003 at the moment and not changing the version in the code from 5.0 to 8.0 was an oversight on my part. It works though. Could you try using Excel 5.0 with your code, to see if it does the same?
January 3, 2015 at 3:58 am
I couldn't found a solution after digging a lot but it works this way!
Save first row data as dummy number after header and then run query it will save numeric data correctly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply