CODEWITHSUNDEEP
c#mysql
Raaigar
Raaigar

Reputation: 13

need to read excel sheet C# and import into mySQL only 3 columns

I am trying to read 3 columns from an excel sheet and import them into a mySQL database. I am very new at C# and need as much detailed help as possible. The MySQL_DB_Management in my using block is a friend's class that basically serves to create my connection string. The code I have thus far is below:

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 Excel = Microsoft.Office.Interop.Excel;
using MySQL_DB_Management;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Class_Code_Discrepancy
{
  public partial class CC_Disc_Fixer : Form
  {
    MySqlDB mysql;
    SqlConnection sql;

    public CC_Disc_Fixer()
    {
        InitializeComponent();
        //create connection strings
        sql = new SqlConnection(@"server=1.1.1.1;" + @"Trusted_Connection=false;" + @"uid=RO_agent;" +
                                @"password=mypass;" + @"database=Depot;");
        mysql = new MySqlDB("1.1.1.1", "platform", "root", "password");
    }

private void Upload_Click(object sender, EventArgs e)
    {           
        string excelconn = @"Provider=Microsoft.Ace.OLEDB.12.0;" +
                                @"Data Source=c:\Notebook Classcode Master 20121024.xlsx;" +
                                @"Extended Properties=Excel 12.0;HDR=NO;FirstRowHasNames=NO";
        OleDbConnection xlconn = new OleDbConnection(excelconn);

        //DataSet xldataset = new DataSet();
        //OleDbDataAdapter xladaptor = new OleDbDataAdapter();
        xlconn.Open();
        OleDbCommand sel = new OleDbCommand();
        sel.Connection = xlconn;
        DataTable dtsheet = xlconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    }

Any help on what I need to do next would be greatly appreciated.

Upvotes: 1

Views: 2193

Answers (1)

nmck
nmck

Reputation: 160

Try looking into a ExcelDataReader library, with that, you can read Excel file into a DataSet just as shown at their page.

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

Then you can iterate through it with foreach loop

foreach (DataTable dt in result.Tables)
{
    foreach (DataRow dr in dt.Rows)
    {
        foreach (DataColumn dc in dt.Columns)
           {
               // do something
           }
    }
}

And if you want to acces only first three columns, you can do it like 

result.Tables[0].Rows[0][0];
result.Tables[0].Rows[0][1];
result.Tables[0].Rows[0][2];

Upvotes: 1

Related Questions