importing and excel file

How do i import a Excel file into a table i have created in my database in SQL server 2005?

http://support.microsoft.com/kb/321686

They have listed various method choose the one which suits u most

|||

u can do it like this

Try

Dim excelConnectionStringAsString =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)

'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=location;Extended Properties=Excel 8.0"

' Create Connection to Excel Workbook

Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select CompanyName,CompanyContact,Title,AddressLine1,AddressLine2,City,State,Zip,Telephone,Web FROM [Sheet1$]", connection)

connection.Open()

' Create DbDataReader to Data Worksheet

Using drAs Data.Common.DbDataReader = command.ExecuteReader()

' SQL Server Connection String

Dim sqlConnectionStringAsString = System.Configuration.ConfigurationManager.AppSettings(APPSETTING_DBCONNECTION)'"Data Source=.; Initial Catalog=Test;Integrated Security=True"

Dim myconnectionAsNew SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTING_DBCONNECTION))

Dim queryAsString ="Truncate Table FiservSpecific"

myconnection.Open()

Dim cmdAsNew SqlCommand(query, myconnection)

cmd.CommandType = CommandType.Text

cmd.ExecuteScalar()

myconnection.Close()

' Bulk Copy to SQL Server

Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString)

bulkCopy.DestinationTableName ="FiservSpecific"

bulkCopy.WriteToServer(dr)

EndUsing

EndUsing

connection.Close()

EndUsing

|||

they don't have a basic just import the file into the table and then get rid of the excel file, and edit the table via VWD or anything like that?

|||

u can use a DTS package, but i am importing the contents of the excel file directly to the sql server database

|||

DTS package?? well...i have this list of information that is in the excel file, i have a table created on the server...the excel file is on the server's desktop, now all i want to do is to take what is in the excel file and put it in the server?? what is the easiest way?

|||

If u want to do it directly thru sql server, then u can

Go to the database -- > right click and then select import data and u would get wizard select the Datasource thats the excel and just follow the steps.

If u want to do it from a asp.net website just add a upload control to ur site and on click of a button save the file if u want or just directly open the excel file and dump the data to sql server.

hope this helps.

Regards

Karen

|||

which one of these works? it ask me to connect through ODBC?? or a system DSN?? it's a local file so??

|||

these are the steps.

1) write click on the Database

2) select Task - ImportData

3) select microsoft Excel from the drop down list and then browse and select the file u want to import. click next.

4) then select the database, give the username and password if any to access the databse or sql server. click next.

5) select the first option, click next, click on the sheet name of the sheet u want the data from and then in the destination select the table where u wanna put the data too. Click on edit mappings and see going where it has to go .

6) click next and finsh , u can save the package for future use and just replace the file and run the package again.

Hope this helps.

Regards,

Karen

|||

For all those questions and answers about excel import., into a database., using bulkcopy.

1. I had used a webserver model, where user would upload excel files., which would further processed using bulkcopy to make data available with database. ( packet was c# +sqlserver ) as asp.net application. but excel contained worksheets, which has to be parsed sheet after sheet. i did pass the sheet names dynamically and use bulkcopy inside a loop till all sheets were processed. but it so happened the data columns containted uneven datatypes, ( double instead of datetime), the bulkcopy failed after processing first sheet. ( again users are users )

2. I tried using DTS, same, all data was ported by this time into different tables, but some dates were invalid ( because they were not in datetime format )., still i get useless data.

Now my question is., should the data across the worksheet be in same format ? for bulk copy to work ? because i cannot expect users to have formatted data in each and every cell. if one cell has different data, 1st method fails, 2nd method turns those data into dates like 01-01-1900 :00:00:00, how to go about this problem ??

|||

When the user sends in the spread sheet make sure its been formatted as Date and Let them select the date format as with the regional settings for eg, 3/12/2007

Hope this helps.

Regards

Karen