How to read particular columns of excel data in C# windows application and store it in SQL database

Question

1.00/5 (1 vote)

See more:

Here I want to fetch only particular columns data from excel(.csv) where I have 60 columns and 20 rows from that I need to fetch 6 columns and store it in sql database using c# windows application.

What I have tried:

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using ExcelApp = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)
        {

            ExcelApp.Application excelApp = new ExcelApp.Application();

            if (excelApp == null)
            {
                Console.WriteLine("Excel is not installed!!");
                return;
            }

            ExcelApp.Workbook excelBook = excelApp.Workbooks.Open("filepath");
            ExcelApp._Worksheet excelSheet = excelBook.Sheets[1];
            ExcelApp.Range excelRange = excelSheet.Columns[1];
            ExcelApp.Range excelRange1 = excelSheet.Columns[3];
            ExcelApp.Range excelRange2 = excelSheet.Columns[4];
            ExcelApp.Range excelRange3 = excelSheet.Columns[6];
            ExcelApp.Range excelRange4 = excelSheet.Columns[7];
            ExcelApp.Range excelRange5 = excelSheet.Columns[28];
            ExcelApp.Range findRange;
            string strToFind = "DealerID";
            string strToFind1 = "StockNo";
            string strToFind2 = "VIN";
            string strToFind3 = "Make";
            string strToFind4 = "Model";
            string strToFind5 = "DealerName";


            string valueInColumnA1;
            string valueInColumnC1;
            string valueInColumnD1;
            string valueInColumnF1;
            string valueInColumnG1;
            string valueInColumnAB1;

            findRange = excelRange.Find(strToFind);
            findRange = excelRange1.Find(strToFind1);
            findRange = excelRange2.Find(strToFind2);
            findRange = excelRange3.Find(strToFind3);
            findRange = excelRange4.Find(strToFind4);
            findRange = excelRange5.Find(strToFind5);

            if (findRange == null)
            {
                MessageBox.Show("Do not find value " + strToFind + "in Column A1");

            }
            else
            {
                valueInColumnA1 = excelSheet.Cells[findRange.Column,1].VALUE;
                valueInColumnC1 = excelSheet.Cells[findRange.Column,3].VALUE;
                valueInColumnD1 = excelSheet.Cells[findRange.Column,4].VALUE;
                valueInColumnF1 = excelSheet.Cells[findRange.Column,6].VALUE;
                valueInColumnG1 = excelSheet.Cells[findRange.Column,7].VALUE;
                valueInColumnAB1 = excelSheet.Cells[findRange.Column,28].VALUE;
                MessageBox.Show(strToFind + " is in A1" + findRange.Column + "\n"
                     + "Value in Column A1 is :" + valueInColumnA1 + "\n"
                    + "Value in Column C1 is :" + valueInColumnC1 + "\n"
            + "Value in Column D1 is :" + valueInColumnD1 + "\n"
                 + "Value in Column F1 is :" + valueInColumnF1 + "\n"
                  + "Value in Column G1 is :" + valueInColumnG1 + "\n"
                + "Value in Column AB1 is :" + valueInColumnAB1);
            }

        }
    }
}

S far I have tried this.
Thanks in Advance.

Posted 8-Nov-18 22:18pm

Srilekha Bolamoni

Updated 14-Nov-18 23:48pm

CHill60

v2

Add a Solution

Comments

Richard MacCutchan 9-Nov-18 4:31am

What is the question?

Srilekha Bolamoni 9-Nov-18 4:59am

just I want to fetch particular columns data from excel(I have data in excel.csv file of 60 columns and 20 rows) in c# using windows application please help me if you can because am beginner started learning on my own by approaching websites.
Thanks in advance

Richard MacCutchan 9-Nov-18 5:05am

We cannot help you unless you tell us what the problem is.

Srilekha Bolamoni 9-Nov-18 6:09am

Am not able to fetch particular data columns but am able to read whole data(not by above code I have written it in another way to read this) of 60 columns and 20 rows but I want to read only 6 particular columns and 20 rows in c#

CHill60 9-Nov-18 4:38am

Where is the code that you use to try to store it into a database?
What is your actual problem?

Srilekha Bolamoni 9-Nov-18 4:56am

Actually am beginner learning on my own firstly am not able to fetch particular column data then only I can save it to the database right.

CHill60 9-Nov-18 6:04am

I'm sorry I don't understand your comment.
You have some data from excel - at least it looks like that is what your code does.
You want to save it to a database I guess. But you haven't shown us your code nor have you told us what the problem is

Srilekha Bolamoni 9-Nov-18 6:11am

Actually am able to read excel file which consist of 60 columns and 20 rows but I want to read only 6 particular columns from that and 20 rows(and the above code is written for this but it doesn't work so).

Richard MacCutchan 9-Nov-18 7:50am

See my Solution below.

Srilekha Bolamoni 9-Nov-18 6:22am

Ignore how to save it to the database but firstly give me some advice to fetch particular columns from Excel in c# windows application I have tried the same in above code but it doesn't work.I have given a file path and from that I want to read some columns(i.e only 6 columns from 60 columns)

3 solutions

Solution 1

Quote:
Ignore how to save it to the database but firstly give me some advice to fetch particular columns from Excel in c# windows application

Here are some articles that will show you how to do that
Working with Excel Using C#[^]
Reading Excel From C#[^]
Read Excel File in C# (Example)[^]

Posted 9-Nov-18 0:37am

CHill60

Comments

Maciej Los 9-Nov-18 17:06pm

5ed!

Srilekha Bolamoni 13-Nov-18 1:55am

I have checked all those articles but from that we can read full data but I want specific columns to read.

Solution 2

Your code is not correct in a number of places. You set findRange six times, so it will always contain the very last item. It would be easier to write a loop for each row and select the column entries from each row. Something like:

C#

for (int row = 0; ; ++row)
{
    string field1 = workSheet.Cells[row, 1].Value as string;
    if (string.IsNullOrEmpty(field1))
        break;      // no more entries
    string field3 = workSheet.Cells[row, 3].Value as string;
    string field4 = workSheet.Cells[row, 4].Value as string;
    string field6 = workSheet.Cells[row, 6].Value as string;
    string field7 = workSheet.Cells[row, 7].Value as string;
    string field28 = workSheet.Cells[row, 28].Value as string;
//
// add fields to the database here
//
}

Posted 9-Nov-18 1:50am

Richard MacCutchan

Comments

Maciej Los 9-Nov-18 17:06pm

5ed!

Srilekha Bolamoni 13-Nov-18 1:53am

I didn't get you can you give an a clear idea.

Richard MacCutchan 13-Nov-18 3:32am

I am not sure what more I can say. The above code will extract the fields you want from the Excel sheet. You just need to add the code (where I suggest) for the SQL INSERT statement.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Srilekha Bolamoni · Accepted Answer · 2018-11-14T23:48:00

Am answering my own question
It may helpful to someone. 
 
  public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string csv_file_path = "Give your filepath here";
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
DataView dv = new DataView(csvData);
DataTable dt = dv.ToTable(false, "ColumnName");//which particular columns you want to read
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = ".";
csb.InitialCatalog = "Project";
csb.IntegratedSecurity = true;
string connString = csb.ToString();
using (SqlConnection con = new SqlConnection(connString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "db TableName";
sqlBulkCopy.ColumnMappings.Add("Dealer ID", "DealerID");
sqlBulkCopy.ColumnMappings.Add("Stock", "StockNo");
sqlBulkCopy.ColumnMappings.Add("VIN", "VIN");
sqlBulkCopy.ColumnMappings.Add("Make", "Make");
sqlBulkCopy.ColumnMappings.Add("Model", "Model");
sqlBulkCopy.ColumnMappings.Add("Dealer Name", "DealerName");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colFields = csvReader.ReadFields();
foreach (string column in colFields)
{
DataColumn dtcolumn = new DataColumn(column);
dtcolumn.AllowDBNull = true;
csvData.Columns.Add(dtcolumn);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);

}
}

}
catch (Exception)
{
throw;
}
return csvData;

}
}
}

 
Thanks
Srilekha Bolamoni.
Please accept it as an answer if it helps you.

How to read particular columns of excel data in C# windows application and store it in SQL database

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us