SQL Server Performance

Import the data from MS Excel to SQL Table ??

Discussion in 'SQL Server 2005 General DBA Questions' started by kasaranenikiran, Jan 28, 2011.

  1. kasaranenikiran New Member

    Hi,
    I have an excel sheet with the Specific Backup Details.
    1.Server Name (Eg: Prod)
    2.Database name ( Eg:ABC)
    3.Backup Job (Eg: Prod Backup)
    4.Backup schedule ( eg: every day 6 AM)
    5.Enabled (Eg:Yes)
    6.Backup Size : (Eg: 10 MB)
    7.Last Backup : ( Eg:1/12/2010)
    8.Backup Method : (Eg: SQL Backup)

    I would like to create a table with the above mentioned columns and import this data from Excel to SQL Tables.
    Could you provide me what datattype to use ??
    I have about 25 server information on 25 sheets seperately in the excel file.
    And, I have the excel on my Desktop . I moved the file to server. But The server doesn't have the Excel software. So, I don't think I could use DTS (Import Export Wizard ) .
    WHat other options can be used to import this data from Excel file to SQL Table ?
    Thanks
    Kay
  2. renek10 New Member

    Hi Kay,Recently I have built a solution in SSIS 2008 32-bits to import an Excel file (2007/2010 version) and to export the content to a textfile:1. The layout of that Excel file had been setted up as a database table, so it contained columnheaders and rows. If the Excel file you have to process has not the layout/structure of a database table you may investigate the functionalities of DataDefractor. I am not so fond of that solution, but maybe it is helpfull.2. If you want to have flexibility regarding the name of the Excel file built into the solution, remember that the connectionstring is different from textfiles and also between Excel versions. Therefor I created three package variables (vcExcelConnectionPart1, vcExcelConnectionPart2 and vcExcelConnectionPart3) to be able to compose the connectionstring.3. The Excel source connector is only available in 32-bits version of SSIS.Cheers!René
  3. satya Moderator

    Why reinventing wheel when you can obtain the information using performance dashboard reports in SQL Server Management Studio?
    Even though if Office components are not installed on the server the SQL native client can handle such an operations.
  4. shabnyc Member

    Hi,
    What are you trying to accomblish by importing this data to SQL table? SQL server provides functionality to acheive this; however, You still can import the spreadsheet to an SQL server table even if the excel software is not installed. If the spreadsheet has the correct excel extention and the structure is = structure of the destination SQL table, the import job should be fine.
    From column names you posted, all datatypes if I am not mistaking should be of type Varchar(200) except number 7.last backup which is of type datetime (change the number 200 properly). Hope that helps
  5. satya Moderator

    It is better to create a table with required columns & relevant data types rather than on fly with ambiguous data type sizes.
  6. yuanyelss New Member

    If the Excel file you have to process has not the layout/structure of a database table you may investigate the functionalities of DataDefractor...
  7. Madhivanan Moderator

    [quote user="yuanyelss"]If the Excel file you have to process has not the layout/structure of a database table you may investigate the functionalities of DataDefractor...[/quote]
    What did you mean by DataDefractor?
  8. satya Moderator

    Are you referring to the third party tool?
    The main objective with SSIS is to provide what is required, then why using third party tool.
  9. renek10 New Member

    With DataDefractor I was referring to the third party tool. I aggree that you should try to solve a problem with native functionalities, because when using a third party tool you are bound to it and it is not easy to substitute. I noticed an interesting reaction / alternative: OPENDATASOURCE.
  10. calico New Member

    Hi
    may be you can try
    SELECT * INTO .......FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c: emp......XLS;Extended Properties=Excel 8.0'
    )...[feuil1$]

Share This Page