SQL Server Performance

Export to Excel

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, May 17, 2005.

  1. Madhivanan Moderator

    Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

    Create an Excel file named testing having the headers same as that of table columns and use this query

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D: esting.xls;',
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

    To export data from Excel to new SQL Server table,

    select *
    into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D: esting.xls;HDR=YES',
    'SELECT * FROM [Sheet1$]')

    To export data from Excel to existing SQL Server table,

    Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D: esting.xls;HDR=YES',
    'SELECT * FROM [SheetName$]')





    Madhivanan

    Failing to plan is Planning to fail
  2. anshubansal2000 New Member

    Hi Madhivanan,
    I tried this query for last 2 days but couldn't transfer. Now I have given it up. Please tell me can I transfer database from SQL Server 2000 to SQL server Express 2005.

    Also Can I have SQL server 2000 and 2005 on the same machine.

    Please let me know.
    Thanking YOu
    Anshu
  3. Madhivanan Moderator

    This is the script to transfer data to/from Excel to SQL Server and not between SQL Server 2000 to 2005

    Madhivanan

    Failing to plan is Planning to fail
  4. pallavi New Member

    Hello

    I wanted to export the contents of a excel file to sql server table.

    I tried the code that u have given , but there is a problem,

    After using this query it returns the contents of the file, but if ther first few rows have null data in it then it makes all the entries in that column as null

    for example

    consider this excel file

    field name Q1-91 Q2-91 Q3-91 Q4-91 1991Total................ Q4-04 2004total
    field 1
    .
    .
    .
    .
    .
    Field15 8 9 0 8 25
    field16
    .
    .
    .
    .
    Field25


    In the above excel file the values in the column "Q1-91" are present after 16 rows.
    But when i use the Query


    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: est.xls;HDR=YES', 'SELECT * FROM [Statement 1$]') Rowset_1


    it shows all the values in the column Q1-91 as null

    Pls can u tell me why this is happening and what should I do to overcome this problem

    Is there ay other method to get the excel file contents?

    The same thing is happening when i tried to create package for this whole procedure.

    Pls do let me know ur answer for this

    thanks

    Regards!
    Pallavi
  5. Madhivanan Moderator

    Strange
    Are you trying to export data to Excel from SQL Server table?

    Create an Excel file named test in C drive

    Then

    Create table op(data1 int, data2 varchar(10))

    insert into op Select null,'test1' union all Select 8,'test2' union all Select 567,'test3'
    union all Select 90,'test4' union all Select 45,'test5' union all Select 4,'test6'

    Insert into
    OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=F: est.xls;HDR=YES', 'SELECT * FROM [sheet1$]')
    Select * from op

    Now open the file and see if you are able to see the records

    Madhivanan

    Failing to plan is Planning to fail
  6. pallavi New Member

    Actually i want the data from excel file " d: est.xls" into a table "StatementData"

    the columns are the same

    I have just tried selecting the records from excel file, but as they are not in proper format.

    Pallavi
  7. Madhivanan Moderator

    If there are empty values in the Excel file then those will become null in the table
    Do you want them to update with any values?

    Madhivanan

    Failing to plan is Planning to fail
  8. conny New Member

    Happy to know there is a coding for SQL to Excel
    however, I fail to run it.
    1) testing.xls should be in SQL server or my local pc ?
    2) I got this error when I run "
    Server: Msg 7415, Level 16, State 1, Line 1
    Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."
    3) I have installed Access in my local pc

    This is my coding :
    Server: Msg 7415, Level 16, State 1, Line 1
    Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

  9. Madhivanan Moderator

  10. mporlovsky New Member

    Can someone give a little more help on this question. I can extract data out of SQL Server into Excel, but I want to UPDATE the table instead of inserting new.

    So if I have the following before values:



    key style mon val
    1 862 JAN 30000


    After

    key style mon val
    1 862 JAN 30001

    So only value I want to update is in the val column.

    Cheers, Mark
  11. Madhivanan Moderator

    Update T1 Set T1.val=30001 from yourTable T1
    inner join
    (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C: esting.xls;HDR=YES',
    'SELECT * FROM [sheetName$]')) T2 on T1.key=T2.key




    Madhivanan

    Failing to plan is Planning to fail
  12. mporlovsky New Member

    Madhivanan,

    this looks great and I will give it try later, but instead of actually referring to a value 30001, how do I dynamically refer to the value? Do I do something like sheetName$+cell location?

    Update T1 Set T1.val=??????? Cell Location? from yourTable T1
    inner join
    (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C: esting.xls;HDR=YES',
    'SELECT * FROM [sheetName$]')) T2 on T1.key=T2.key

    Thanks
  13. Madhivanan Moderator

  14. cpnz New Member

    I am trying to do something similiar, except the Excel workbook I am using has graphs already set up for the data area.
    Is it possible to overwrite a certain cell range instead of appending new data to the spreadsheet?
    I am trying to graph perfmon data out of a database, so I know i will always have the same number of rows/collumns.

    Any help you can give would be appreciated.
  15. Madhivanan Moderator

  16. sonnysingh Member

    Hi Madhivanan


    I am runnig just simple insert from excel file into table...


    Insert into process_upload1
    Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:csat
    etgearAgent wise Parameter wise Raw format 09 apr 07.xls;HDR=YES',
    'SELECT * FROM [Sheet1$]')


    I am having this following error...


    Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.


    Thnaks in advance..
  17. Madhivanan Moderator

    Make sure the EXCEL is closed at the time of executing that query

    Madhivanan

    Failing to plan is Planning to fail
  18. smileteju New Member

    Hello,
    I tried to follow the example code for importing the data from excel to SQL server.
    I have the excel file on my production server and a different box for sql. Below is my code
    appPath=server.mappath("testdata.xls")
    response.write appPath


    'declare SQL statement that will query the database
    sSQL="Insert into xxxx Select * FROM OPENROWSET('sqloledb','Excel 8.0;Database="& appPath &";HDR=YES;Data Source=xxxxx;','SELECT * FROM [Sheet1$]')"

    But when i try to run the asp code it gives me the following error
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

    Could anyone please help

  19. Madhivanan Moderator

    Make sure the file exists in Server and not at the client

    Madhivanan

    Failing to plan is Planning to fail
  20. semje Member

    Hi there,
    Is there a query that will create the excel file and allow you to create the headings through a stored procedure rather than having to create the file and the headings manually?
    I have tried the following which creates and empty excel file with no headings. How can I get the headings in so I can use the 'insert into openrowset' command? What I'd like to be able to do is use a cursor to create, open, write and close many excel files based upon the output of query and then email the files to different people. I think if I could get the excel file piece to work then the rest as the say is history.DECLARE @RetCode int ,
    @FileSystem int ,
    @FileHandle int,
    @text varchar(200)
    --SELECT @text = 'hello ' + 'Jeff' + char(13) + char(10)
    EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
    RAISERROR ('could not create FileSystemObject',16,1)
    EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , 'c:jeff.xls', 2, 1
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
    RAISERROR ('Could not open File.',16,1)
    Thanks,
    Jeff

Share This Page