Hi,
I have approximately 27000 lines in an Excel file that I want to enter into a pre-defined table. Currently I am doing this by calling a stored procedure which inserts each Excel row into the table.
Unfortunately, this takes approximately 12 minutes. I heard there are other ways to bulk import from the Excel file itself. Any pointers on how to do this?
Thanks,
Tim
I have approximately 27000 lines in an Excel file that I want to enter into a pre-defined table. Currently I am doing this by calling a stored procedure which inserts each Excel row into the table.
C#:
// Open Excel Attachment
var app = new Microsoft.Office.Interop.Excel.Application();
Workbooks wbs = app.Workbooks;
wbs.Open("C:\\JDEWOData\\" + fileAttachment.Name);
Worksheet worksheet = app.ActiveSheet;
Range usedRange = worksheet.UsedRange;
bool bHeaderRow = true;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=source;Initial Catalog=FPY;User ID=PartsetupUser;Password=xxxxxxxxx";
conn.Open();
SqlCommand TrunkCommand = new SqlCommand("TRUNCATE TABLE tblJDEWOData",conn);
TrunkCommand.ExecuteNonQuery();
//Iterate the rows in the used range
foreach (Range row in usedRange.Rows)
{
String[] rowData = new String[row.Columns.Count - 1];
for (int i = 0; i < row.Columns.Count; i++)
rowData[i] = Convert.ToString(row.Cells[1, i + 1].Value2);
if (!bHeaderRow)
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.ins_tblJDEWOData";
command.Parameters.Add("WONum", SqlDbType.Int).Value = int.Parse(rowData[0]);
command.Parameters.Add("WOType", SqlDbType.Char,2).Value = rowData[1].Trim();
command.Parameters.Add("OpSeqNum", SqlDbType.Decimal).Value = rowData[2].Trim();
command.Parameters[2].Precision = 20;
command.Parameters[2].Scale = 7;
command.Parameters.Add("CellName", SqlDbType.VarChar, 20).Value = rowData[3].Trim();
command.Parameters.Add("BusinessUnit", SqlDbType.Char,30).Value = rowData[4].Trim();
command.Parameters.Add("PartNum", SqlDbType.VarChar,20).Value = rowData[5].Trim();
command.Parameters.Add("QtyCompleted", SqlDbType.Decimal).Value = rowData[6].Trim();
command.Parameters[6].Precision = 20;
command.Parameters[6].Scale = 7;
command.Parameters.Add("QtyReceived", SqlDbType.Decimal).Value = rowData[7].Trim();
command.ExecuteNonQuery();
command.Parameters[6].Precision = 20;
command.Parameters[6].Scale = 7;
}
bHeaderRow = false;
}
wbs.Close();
conn.Close();
}Unfortunately, this takes approximately 12 minutes. I heard there are other ways to bulk import from the Excel file itself. Any pointers on how to do this?
Thanks,
Tim
