Home » VB.NetRSS

Export To Excel With Column Formatting

Hi

Please...Help save what little hair I have left :)

Needs
I need to export data from SQL Server Express 2008 to Excel.
The Excel file needs to have headers.
I need numbers to be exported as numbers and not stored as text.  This is my problem.


I've Googled, searched and read for hours, but no solution actually seems to work.  The code below is what I have.  Which works except for it storing everything as text.  I need IDField and Quantity to be stored as numbers.  I have an existing XLS template file I'm exporting the data into.  All suggestions are warmly welcomed :)


Edit:  If I put example data under my header in row 2 of the Excel file, it then exports correctly.  So then I'm left with an extra line.  As a hacky solution, anyone know how to delete a row of Excel data from SQL Server?

Cheers

SET @FileName =''
SET @SQL =''

SET @FileName ='Excel 8.0;Database='+ @FileNameTwo +''

SET @SQL ='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
'''
+ @FileName +''',

''SELECT * FROM [Sheet1$]'') SELECT IDField, Code, Quantity FROM tblMyTable'

Exec (@SQL)



 

31 Answers Found

 

Answer 1

One thing you could do is use a template excel  sheet with the headers already in place. Under ssis, I would create a script task that makes a copy of your file and to adjust your connection manager to point to the new file. You can do all of this in code. Then, export  your data  to the new sheet (using the modified connection manager). Be sure to omit your first line if that contains header data.

 

Answer 2

Hi JP

Thanks for the reply.  Excuse my ignorance here.  But I'm already exporting to a template file with headers in it and that doesn't work.  Would doing what you suggest, which I have no clue how to do :), be any different?

Cheers

 

Answer 3

I am going to give you another suggestion... Assuming you want to use openrowset, Export your data  as a csv. Then, open the csv from within excel, specifying the datatype for yoru columns. Another approach that may work is a format file: http://msdn.microsoft.com/en-us/library/ms178129.aspx.

HTH

 

Answer 4

I don't want  to use OpenRowSet.  I want to use anything that works!  I can't export  the data  to a CSV, it has to be an Excel file.

Thanks for the link but BCP can't export column  headers, that I'm aware of, so that won't work either.  :(
 

Answer 5

Then I would suggest using SSIS as the way to export  your data  to excel. You will find that ssis provides a lot of flexibility that is not found in other approaches. Check out this resource: http://www.docstoc.com/docs/9745851/SSIS-Tutorial If you are new to SSIS, this should help.  Here are links for the various tech walkthroughs on ssis.

http://msdn.microsoft.com/en-us/library/bb522504.aspx
 

Answer 6

Thanks again but with SQL Express you can't use SSIS as you can't save  packages.  So I can't use that either.
 

Answer 7

Sorry about that...Missed the fact you were talking about Express.. How comfortable are you with automation? You could spin through a datatable and run the data  into Excel that way. That would give you complete control over the process.
 

Answer 8

Comfortable with anything that works.  But I know nothing about automation.  Can you point me in the right direction so I can check it out?
 

Answer 9

You can do a couple of things here.  First for the header you can either specify hdr=1, if you header exists in your spreadsheet, or you can use UNION ALL to append a header to your result set.  Now for the numbers being treated as text.  Excel should automatically convert any columns with numeric values to numbers automatically.  If it is not, are you sure your column  is comprised of numbers strictly?  You can try using the value() function in your output statement, as shown below.

DECLARE @FileName VARCHAR(100),
		@sql VARCHAR(500)

SET @FileName = ''
SET @SQL = ''

SET @FileName = 'Excel 8.0;hrd=Yes;IMEX=1;Database=' + @FileNameTwo + ''
--hdr is the header,IMEX=1 transforms columns with text and numeric values to text

SET @SQL = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''' + @FileName + ''', 
''SELECT * FROM [Sheet1$]'')  SELECT ''=VALUE('' + IDField + ''), Code, Quantity FROM tblMyTable'
--note you may need a convert the idfield to varchar if it is actually stored as a number.

--Exec (@SQL)
PRINT @sql

 

Answer 10

Hi Adam

It all sounds so simple!  'VALUE Solution'  I can't have =VALUE(X) in my cells.  I just have to have the number.  And from what you're saying it sounds like I don't need that as Excel will do what I want automatically.

So I'll make it simple, the proc below does not work for me, does it work for anyone else?  The numbers are exported as text. 

You'll need a dummy XLS file with 3 textual headers in it.

Some points:
* If I don't have a header in the XLS, regardless of the setting of HDR, I get the error "Column name or number of supplied values does not match table definition."
* If I set IMEX=1 I get the errors (setting IMEX=0 produces no error):

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server  "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.Jet.OLEDB.4.0]".

What am I doing wrong here? 


Cheers

CREATE

PROCEDURE XXXXX

AS

BEGIN

SETNOCOUNTON;

DECLARE @FileName VarChar(4000)

DECLARE @FileNameTwo VarChar(4000)

DECLARE @SQL VarChar(4000)

SET @FileName =''

SET @FileNameTwo ='C:\Test.XLS'

SET @SQL =''

SET @FileName ='Excel 8.0;hrd=no;IMEX=1;Database='+ @FileNameTwo +''

SET @SQL ='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', '''+ @FileName +''',

''SELECT * FROM [Sheet1$]'') SELECT 1,2,3'

EXEC(@SQL)

END

 

Answer 11

Bump....

 

Answer 12

Hi Ye11ow

I can get you a little way along: You are using a parameter @FileName in your OPENROWSET statement - it should be @FileNameTwo.

I don't have the Jet provider installed though, so can't test it here.

Ewan
 

Answer 13

Hi Ewan

Thanks for they reply.

@FileName is correct as it's holds the file name C:\Test.xls and the connection string parameters.  The code above works, except that it does not export  the numbers as numbers, it exports them as text.


Cheers
 

Answer 14

Ooops. Sorry about that.

 

Answer 15

Looks like IMEX will not solve your problem: http://support.microsoft.com/kb/194124

Found a test box with Jet and managed to play around with some variations on this. IMEX=2 doesn't work either, headers or not.

Ewan
 

Answer 16

Unless I set IMEX=0 it doesn't work at all for me.

I get the same behaviour if I export  an ADO.net dataset to Excel.  There HAS to be a way of exporting a number as a number.  I can't be the only person in the world who wants to export this way....can I? :)

I've spent days on this now.  I've read about IMEX, MAXSCANROWS.  It all does nothing.  I've read that Microsoft says Excel guesses what the column  format will be... http://support.microsoft.com/kb/316934  Guesses!  Well it isn't doing a very good job is it.

Cheers
 

Answer 17

Is there anything in your column  that is not a number?  What exactly is happening to the numeric value, is it being trimmed, is it null?  You may want to consider using SSIS, as you can more granularly control data  types and formatting  of data.  You can try running the code below to see if any id are not numbers.

select * from mytable where isnumeric(MyId) = 0

I also did a little research and it seems you cannot specify imex =1 for an insert, so you need to have imex set to either 0 or 2. So that is my bad. 


SQL to excel  via SSIS
http://cavemansblog.wordpress.com/2009/04/17/ssis-export-data-from-sql-server-2005-to-excel/
 

Answer 18

Hi Adam

Everything is a number.  100%.  When the data  is exported it's exported as text, with the Excel tag 'Convert To Number'.

Looking at the sproc above, 'XXXXX'.  That doesn't work and it has numbers 1, 2 and 3 as the output.  They're definitely numbers, so should be exported as such?  Does that work for you?

I can't use SSIS as I can't save  packages.


Cheers
 

Answer 19

The numbers are stored as text and not numbers, even though the column  type is numeric.  I think what you are hitting is a limitation of the driver. Sorry about the SSIS reference, I forgot you are using Express.  The only solution I can find is to add a dummy record right underneath your header columns, like all zeros. 

So your Excel should look like this:

col1     col2     col3
0         0         0

You can then code your openrowset as such.

INSERT INTO openrowset('Microsoft.Jet.OLEDB.4.0' ,   
'Excel 8.0;Database=c:\test.xls;hdr=Yes,IMEX=2','Select * from [Sheet1$]')   
select 1,2,3
 

Answer 20

Thanks Adam, but that is where I was in my my first post.  I can't say to our client, "oh just ignore that dummy line of data".  They won't accept it.  And rightly so. 

This is embarrasing, what a limitation this is to not be able to export  a number as a number.


The 'solutions' I have come up with:

1:  Go with the dummy line of data.  UPDATE the first row of Excel data  (The zeroes) to be the first row of the output.  Then INSERT everything excluding the first row of data.  This should work, but I'm not sure I trust Excel/SQL server  to always export the data as a number even with a dummy line as I have had incosistant results even with a dummy line of data.

2:  Do what I didn't want to do and run this from VB.net and send a dataset to an Excel worksheet.  Full details of how to do that are here http://support.microsoft.com/kb/316934

Thanks all for all your help.  If anyone has a solution to this I'd be very happy to hear it because I don't (can't?!) believe it isn't possible.


Cheers

 

Answer 21

<<This is embarrasing, what a limitation this is to not be able to export  a number as a number.

Well.... this is not technically the case.  SQL is submitting the column  as a number; however, the problem is Excel is treating it as a text  This seems like a driver/Excel limitation to me, not SQL.  Another thing you have to remember is that the Jet driver does not work in a 64 bit SQL Server install. If any of your clients are using the above code on a 64 bit SQL install, the code will simply not run and a error stating they must register the driver will occur.  I hope that someone else can provide support because I am fresh out of ideas.

-Adam

 

Answer 22

OK this is what I did.  In my post above I can't get option 2 to work with SQL server.  So I've gone with option number 1.

Create a linked server  to your existing XLS sheet.  I have 1 worksheet, Sheet1, and 3 columns ColA, ColB and ColC.  My linked server is called 'Final'.  In the XLS, I have 0, A and 0, in the respective columns as I want numeric, text and numeric.

UPDATE column  1 TO BE THE FIRST ROW OF DATA
UPDATE
Final...Sheet1$
SET ColA =(SELECTTOP 1 MyLocation FROM tblLocations ORDERBY Location)
WHERE ColA = 0

UPDATE COLUMN 2 TO BE THE FIRST ROW OF DATA
UPDATE
Final...Sheet1$
SET ColB =(SELECTTOP 1 MyArea FROM tblLocations ORDERBY Location)
WHERE ColB ='A'

UPDATE COLUMN 3 TO BE THE FIRST ROW OF DATA
UPDATE
Final...Sheet1$
SET ColC =(SELECTTOP 1 MyNumber FROM tblLocations ORDERBY Location)
WHERE ColC = 0


NOW INSERT ALL RECORDS FROM OUR TABLE EXCEPT THE TOP1 RECORD BECAUSE THAT WAS UPDATED ABOVE
INSERT
INTO Final...Sheet1$
SELECTTOP (100)PERCENT dbo.tblLocations.MyLocation, dbo.tblLocations.MyArea, dbo.tblLocations.MyNumber
FROM dbo.tblLocations INNERJOIN
(SELECTTOP (1) MyLocation, MyArea, MyNumber
FROM dbo.tblLocations AS tblLocations_1
ORDERBY Location)AS derivedtbl_1 ON dbo.tblLocations.Location <> derivedtbl_1.Location
ORDERBY dbo.tblLocations.Location


This is a lot of messing about, probably not terribly efficient, but right now I have no other choice.  And this seems to be working right now for me.

Cheers




EDIT:  I've altered this slightly by using OPENROWSET instead of a linked server.  So now I'm using something like this for each of the 3 UPDATE statements.

UPDATE

OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\Test.xls;HDR=yes','SELECT * FROM [Sheet1$]') SET ColA = NewValue WHERE ColA = OldValue

 

Answer 23

Is this what you are looking for:
Tools
   Options
      Query Results
         sql  Server
            Results to Grid
               Check the box titled "Include column  headers when copying or saving the results"
               Click the OK button

Note you will probably get the message:
"SQL server  Results to Grid option changes will only be applied to new SQL Server Query Editor windows."
Click OK

 

Answer 24

Hi

The problem wasn't that I couldn't export  column headers, it was that numbers are exported as text (Or Excel reads them as such) when exported from SQL Server.


Cheers

 

Answer 25

Hi Yellow, i have the same problem, i am trying to export  numbers in a Excel Sheet and these are exported as text , i was checking this forum but for me solution is not clear , may be i miss  something, i would appreciate your help  telling me how did you solve the problem?

Thanks a lot


This is basically what i got:


    Select @fn = 'C:\invoice.xls '
    set @provider = 'Microsoft.Jet.OLEDB.4.0'
    set @ExcelString = 'Excel 8.0;Database=' + @fn

exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT id1, id2  FROM  [Invoices$]'')

    SELECT DISTINCT  id1,id2 from Table1 ')

 

Answer 26

why not you consider the Excel Macro Report ??

Like this ?

Private Sub GenerateReportCommandButton_Click()
    Dim SQLStatement
    
    Dim MyConn As ADODB.Connection
    Dim MyRS As ADODB.Recordset
    
    Dim Ws As Worksheet
    
    Dim Country As String
    Dim Region As String
    Dim Service As String
    Dim ServiceText As String
    
    Dim Date1 As Date
    Dim Date2 As Date
    
    Dim passDate1 As String
    Dim passDate2 As String
    
    Set MyConn = New ADODB.Connection
    Set Ws = Sheets("AcerUpdateDetails")
    
    Country = Sheets("Input").Range("A5").Value
    Region = Sheets("Input").Range("A9").Value
    Service = Sheets("Input").Range("A12").Value
    ServiceText = Sheets("Input").Range("B12").Value
    
    Date1 = Range("B4").Value
    Date2 = Range("B5").Value
    If Date1 > Date2 Then
        MsgBox "From date Cannot be greater than To date.", vbCritical, "BIOS Update Summary Report"
        Exit Sub
    End If
   
    Ws.Range("B2").Value = Date1
    Ws.Range("B3").Value = Date2
    Ws.Range("B4").Value = Region
    Ws.Range("B5").Value = Country
    Ws.Range("B6").Value = ServiceText
    Ws.Range("B7").Value = Now()
    
    If Country = "All" Then Country = ""
    If Region = "All" Then Region = ""
    If Service = "All" Then Service = ""
       
    Application.ScreenUpdating = False
    Sheets("AcerUpdateDetails").Select
    LASTROW = Range("D65535").End(xlUp).Row
    
    Set MyConn = New ADODB.Connection
    Set Ws = Sheets("AcerUpdateDetails")
    
    con_String = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=UserID;PWD=Password;Initial Catalog=DB;Data Source=SERVER"

    MyConn.Open con_String
    Worksheets("Sheet1").Activate
    ActiveSheet.Rows("11:65500").Select
    Selection.ClearContents
    dest_sheet = "Sheet1"
    Set Ws = Sheets(dest_sheet)
    dest_cell = "A11"
    Worksheets("Sheet1").Activate
    
    Ws.Cells(10, 1) = "ID"
    Ws.Cells(10, 2) = "Model"
    Ws.Cells(10, 3) = "Number"
    Ws.Cells(10, 4) = "SNID"
    Ws.Cells(10, 5) = "Request Created Date/Time"
    Ws.Cells(10, 6) = "Created Month"
    Ws.Cells(10, 7) = "Operating System"
    Ws.Cells(10, 8) = "Option"
    Ws.Cells(10, 9) = "Contact"
    Ws.Cells(10, 11) = "First Name"
    Ws.Cells(10, 12) = "Last Name"
    Ws.Cells(10, 13) = "Address"
    Ws.Cells(10, 14) = "City"
    Ws.Cells(10, 15) = "State"
    Ws.Cells(10, 16) = "Country"
    Ws.Cells(10, 17) = "Email"
    Ws.Cells(10, 18) = "Phone"
      
    Date2 = Date2 + 1
    passDate1 = Format(Date1, "YYYY-MM-DD")
    passDate2 = Format(Date2, "YYYY-MM-DD")
    
    SQLStatement = "Select * From Table1"
    MyConn.CommandTimeout = 0
    Set MyRS = MyConn.Execute(SQLStatement)
    Ws.Range(dest_cell).CopyFromRecordset MyRS
        
    Set MyRS = Nothing
    Set MyConn = Nothing

    Ws.Select
    Application.ScreenUpdating = True
End Sub
 

Answer 27

 Hi oscarmiguel

What you need is a template XLS sheet. 

Put the column  headers into it in row A.

In row B put a line of dummy data.  If the data  is to be numeric, the put a 0, if it's going to be text but an A.


You then need to UPDATE each column in that first row of the Excel sheet.  So you need to order your data your going to insert so the first record you require is first in the recordset.

For example:

 SET @SQL = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
  ''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
  ''SELECT * FROM [Sheet1$]'')
  SET ColumnA = (SELECT TOP (1) AField FROM dbo.tblMyTable ORDER BY IDField) WHERE ID = 0'
 EXEC (@SQL)
Then update the second column.  For example:
 SET @SQL = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
  ''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
  ''SELECT * FROM [Sheet1$]'')
  SET ColumnB = (SELECT TOP (1) AnotherField FROM dbo.tblMyTable ORDER BY IDField) WHERE ID = 0'
 EXEC (@SQL)
And so on.  Once you've repeated this for each column in that first row, you need to export  the rest of the recordset, which will be every row, except the first row as you've already done that.

For example:

	SET @SQL = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
		''Excel 8.0;Database=' + @FileName + ';HDR=yes'',
		''SELECT * FROM [Sheet1$]'')
		SELECT     TOP (100) PERCENT AllMyFields
		FROM         dbo.tblMyTable INNER JOIN
							  (SELECT     TOP (1) AllMyFields													FROM          dbo.tblMyTable AS tblMyTable_1
								ORDER BY IDField) AS derivedtbl_1 ON dbo.tblMyTable.IDField <> derivedtbl_1.IDField
		ORDER BY dbo.tblMyTable.IDField'
	EXEC (@SQL)
And that's it.

Why do we have a postage size window to work with in these forums.  Sorry this post is a mess but I can't format it and I can't get rid of the code box on the first paragraph.

Hope that helps.

Cheers

 

Answer 28

Here is my simple solution. I write the results to file and that will give me a .rpt file. Then I open it in Excel, format, save  it, and I'm done.

 

Answer 29

Abdshall - That's fine if you want to do it manually.  But if you want it to be automated you need another solution.
 

Answer 30

check this link

How to import MS Excel data  to sql  Server table using c#.net

 

Answer 31

Hello,

with this Excel VB.NET and Excel C# library you can easily export DataSet to Excel .

It is much more faster and easier to use than Excel Interop .

First you will need to export  data from database to DataSet.

Then you manually add headers into appropriate worksheets.

Then you use this sample code to export DataSet to Excel:

// Create new ExcelFile.var ef = new ExcelFile();

// Imports all the tables from DataSet to new file.foreach (DataTable dataTable in dataSet.Tables)
{
  // Add new worksheet to the file.var ws = ef.Worksheets.Add(dataTable.TableName);

  // Insert the data  from DataTable to the worksheet starting at cell "A1".
  ws.InsertDataTable(dataTable, "B1", true);
}

// Save the file to XLS format.
ef.SaveXls("DataSet.xls");

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter