Post reply

Load the data from excelsheet into a table in sqlserver 2000

  • July 21, 2008 at 10:36 am

    #381809

    Hi,

    Can anyone tell me how to Load the data from excelsheet into a table in sqlserver 2000.

    Thx

  • July 21, 2008 at 11:12 am

    #845904

    Right click the destiny server --> All tasks --> Import Data...-->(then, follow the steps)...--> DONE.

  • July 21, 2008 at 11:35 am

    #845911

    you could also attach the spreadsheet as a linked server, and then select from it as well;

    here's an example, which adds the linked server "MySpreadSheet", just change the path and filename from c:\ to the correct spot ON THE SERVER (cannot be on just your machine!)

    EXEC sp_addlinkedserver MySpreadSheet,

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'C:\MySpreadSheet_sumfy07.xls',

    NULL,

    'Excel 5.0;'

    GO

    --see the available tables in the spreadsheet

    EXEC sp_tables_ex @table_server = 'MySpreadSheet'

    --EXEC sp_dropserver MySpreadSheet

    --You can then select from the Excel table using SQL code as is needed do an insert into a sql table.

    --sheet names end in a dollar sign..so if you name the sheet "Invoice", you access it as "Invoice$"

    --examples

    SELECT * FROM MySpreadSheet...Invoice$

    SELECT * FROM MySpreadSheet...Sheet1$

    SELECT * FROM OPENQUERY(MySpreadSheet, 'SELECT * FROM [Sheet1$]')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 21, 2008 at 11:53 am

    #845921

    Thanks

  • July 21, 2008 at 6:38 pm

    #846100

    Or use OPENROWSET

    example :

    select * from OPENROWSET ('Microsoft.jet.oledb.4.0','excel 8.0;database=path','select * from [sheetname$]')

    *note that datatabase=path must not have space in between and sheetname is the name of the sheet inside the excel file with the $ at the end.

    If your using it to populate a table you can use...

    INSERT INTO table_name

    select * from OPENROWSET ('Microsoft.jet.oledb.4.0','excel 8.0;database=path','select * from [sheetname$]')

    you can find additional information regarding this command in this site

    http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

    "-=Still Learning=-"

    Lester Policarpio

Viewing 5 posts - 1 through 4 (of 4 total)

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