Answered SQL Server Importing Data from an Excel File

tim8w

Well-known member
Joined
Sep 8, 2020
Messages
125
Programming Experience
10+
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.

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
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
Not to sound cynical, but you would not import the data to excel and instead import it directly the way you want to do it now from whatever source feeds Excel.

Why is it in Excel format in the first place?
 

tim8w

Well-known member
Joined
Sep 8, 2020
Messages
125
Programming Experience
10+
Not to sound cynical, but you would not import the data to excel and instead import it directly the way you want to do it now from whatever source feeds Excel.

Why is it in Excel format in the first place?

I have no control of the source. I can ask for different file formats, but that's about all I can get from the team that supplies the data. Currently I am being emailed an Excel file that I need to import into my SQL Server database. I receive the email every fifteen minutes, which is why I am hoping that there is a faster way to do this...
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
That really sucks. But I don't have much advice to give you but maybe others will.

You're obviously not the first to hit this problem, and that's why libraries have been written for exactly this : How to import Excel to SQL table in C# or VB.NET | EasyXLS Guide

While I have no affiliation with the library, nor have I used it, but I would recommend you start reaching out for already available libs which are built for importing massive chunks of data to a SQL server.

I would also suggest looking on Nuget for additional packages. If comfortable with Linq, you can find Excel to Linq, and from Linq to SQL and on the plus side, you will gain some perfs from doing it like that.

See NuGet Gallery | Packages matching excel to sql
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,712
Location
Chesapeake, VA
Programming Experience
10+
Are you required to use your stored procedure? Does the stored procedure let you send in more than one row at a time?
 

tim8w

Well-known member
Joined
Sep 8, 2020
Messages
125
Programming Experience
10+
Are you required to use your stored procedure? Does the stored procedure let you send in more than one row at a time?

No, I can use whatever I want. I have been trying to use SQLBulkCopy, but it seems to require an OleDBReader and I can't get the Provider to work on my 64-bit machine. I am about to try SQLBulkCopy on a CSV file instead, but that will also cause me to have to write a stored procedure so I can pass it the path of the CSV file on my local machine...
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
The library above claims not to require OleDBReaders :
EasyXLS permits you to import Excel files without Excel installed, without OLEDB, without Interop or any other additional software installed.
 
Top Bottom