>Hi .
>I have a sql database set up and want to import data from an excel file.
>In VFP, I would just do:
>Use MyDbf
>Appe from MyXls type XLS
>
>Anybosy know what is the corresponding codw in C#,Sqlexpress
>i.e. I have a Sql express table and I want to import an XLS file.
>
>Any help appreciated.
>Regards,
>Gerard

To do that you can either directly use SQL server T-SQL or C# code.
T-SQL:

insert myTable ( fieldlist )
  select ... from openrowset(...)

or:

insert myTable ( fieldlist )
  select ... from opendatasource(...)...sheetname$

With SQL 2005 openrowset and opendatasource are disabled by default. You should enable it first (configurator). You can search for samples here in old messages.

With C# code:
-Create an OLEDB connection to excel
-Get a reader from excel
-Create an SQL conection to target SQL table
-Create a bulk copy class
-Adjust column mappings
-Write to server
ie:

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

class xlAppendFromSample
{
   static void Main()
   {
    string strSource = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=c:\\temp\\sample.xls;Persist Security Info=False;Extended Properties=\"Excel 8.0\"";
    string strTarget = "Data Source=.\\SQL2005;Initial Catalog=test;Integrated Security=True";

    OleDbConnection conSource = new OleDbConnection(strSource);
    OleDbCommand cmd = new OleDbCommand("select * from [sheet1$]", conSource);
    conSource.Open();
    OleDbDataReader rdr = cmd.ExecuteReader();
    SqlConnection conTarget = new SqlConnection(strTarget);
    SqlBulkCopy cpy = new SqlBulkCopy(conTarget);
    conTarget.Open();
    cpy.DestinationTableName = "dbo.Location";
    cpy.ColumnMappings.Add(0, "[Name]");
    cpy.ColumnMappings.Add(1, "[CostRate]");
    cpy.ColumnMappings.Add(2, "[Availability]");
    cpy.ColumnMappings.Add(3, "[ModifiedDate]");
    cpy.WriteToServer(rdr);
    conTarget.Close();
    rdr.Close();
    conSource.Close();
    cpy.Close();
  }
}

PS:Test.dbo.Location is a slightly modified copy of AdventureWorks.Production.Location.
In excel sheet column order was same as those fields so 0 maps to name, 1 maps to CostRate etc.
Cetin