Excel - Database

[Kuldeep]

Hi All,

Can anyone give me some leads towards uploading the excel file contents into
SQL Database

Regards
Kuldeep

 

[Code Monkey]

This will fill a DataTable with data from an excel spreadsheet.

'excelPath' is the full file path to the spreadsheet ie;

"c\:ExcelSpreadsheet.xls"

<code>
private DataTable myExcelSpreadSheet(String excelPath)
{
DataTable dt = new DataTable("customer");
OdbcConnection conn = new OdbcConnection();
OdbcCommand cmd = new OdbcCommand();
conn.ConnectionString = string.Format(@"Driver={{Microsoft
Excel Driver (*.xls)}};DBQ={0};ReadOnly=0;", excelPath);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
//select data from the Customers sheet. Note that sheet
name has to be in [] and suffixed with a $ sign
cmd.CommandText = @"SELECT * FROM [Sheet1$]";
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
}
</code>

Something to note, you may want to change:

cmd.CommandText = @"SELECT * FROM [Sheet1$]";

to

cmd.CommandText = @"SELECT f1, f2, f3, f6, f9 FROM [Sheet1$]";

if you want to select specific columns in the spreadsheet.

 

[Kuldeep]

Thanks Bro

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

There are a couple of way of doing this, the first is using DTS , if the
excel file format can change and you need to do this more than once it's the
way to go. I posted yesterday the code for doing it, look for it or let me
know and I will post it again.

the other way is reading the Excel in a dataset and then insert the data in
the SQL, this is slower than the other variant, and MUCH less flexible.

here is the code for that:

static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if(dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

foreach(DataRow row in dt.Rows)
{
excelSheets = row["TABLE_NAME"].ToString();
i++;
}

return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}



void ConvertFile( string sourceFile)
{
try
{
if ( System.IO.Path.GetExtension( sourceFile).ToUpper() == ".XLS")
{

string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string srcQuery = "Select * from [" + GetExcelSheetNames(
sourceFile)[0] + "]";

OleDbConnection srcConn = new OleDbConnection( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);

readerExcel = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);