CODEWITHSUNDEEP
c#asp.net
Ramakrishna
Ramakrishna

Reputation: 203

best method to choose to save huge data from excel to Sql

I have an Excel file from which I have to extract the required data and save it to a database. I know by using Range we can get a particular range of data. But my data that was to be extracted was a bit large. So can anyone suggest which was the best and simple method to retrieve the data and store the information in a database?

I would like to read the data from A10 to an unknown range. My data will be as follows

enter image description here

As per marked with red after that data should go in to the database column by column I will do that if anyone can suggest the best method to read the remaining columns too.

Upvotes: 2

Views: 1490

Answers (3)

Developer
Developer

Reputation: 8636

If you still need to do it from code there is only one way of doing. As per your question you said that your data will start from A10, first of all get the UsedRange of excel as follows

Microsoft.Office.Interop.Excel.Range xlRange = worksheet.UsedRange;

As there are only 2 columns get the row count and column count of excel ad follows

iRows = xlRange.Rows.Count;
iCols = xlRange.Columns.Count;

Later start your loop as follows

for (int iRow = 10; iRow <= iRows; iRow++)
 {
   for (int iCol = 1; iCol <= iCols; iCol++)
   {
      xlRange = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
      Console.WriteLine(xlRange.Text); // From here do as per you required and insert the required data to the data base. 
      List<string> lstItems = new List<string>(); // Declare this initially 
       lstItems.Add(xlRange.Text.ToString());
       if (lstItems.Count == 10)
       {
         if (xlRange.Text.ToString().Contains("www") || lstItems[9].ToString() == string.Empty)
         {
         }
      }

Upvotes: 0

Russell
Russell

Reputation: 17729

You could use SQL Server Integration Services to import the excel data to a table. A SSIS package can run at scheduled times or be invoked. It uses the spreadsheet as a data source and allows you to map columns.

Upvotes: 2

Pieter
Pieter

Reputation: 3399

Well, you can use NPOI to read in the Excel file and parse it any way you want. We use it to import large Excel files into a SQL database as well. Using NPOI you have complete freedom on how to interpret the data.

Most important thing is that, if you want to do this more often, either the format of the Excel file should not change, or you should have some generic description of the Excel file stored somewhere else which tells your code how to interpret the file. The latter is of course more difficult to do. It depends on your particular use case which is better.

In our case the Excel file has a fixed layout, so our implementation is based on that layout.

Upvotes: 1

Related Questions