Import the data from MS Excel to SQL Table ?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Import the data from MS Excel to SQL Table ??

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

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é
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.

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

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

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 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?

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.

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.
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$]

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |