Import CSV file to Database table

Hello Meghana,

To split the columns of the CSV files into respective tables in C#, you can use the following code
 

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        // Read the CSV file into a DataTable
        DataTable csvData = GetDataTableFromCsv("Sample1.csv");

        // Create the Emp table
        DataTable empTable = new DataTable("Emp");
        empTable.Columns.Add("EmpId", typeof(int));
        empTable.Columns.Add("EmpDepartment", typeof(string));
        empTable.Columns.Add("EmpName", typeof(string));

        // Populate the Emp table from the CSV data
        foreach (DataRow row in csvData.Rows)
        {
            int empId = Convert.ToInt32(row["Id"]);
            string empDepartment = row["Department"].ToString();
            string empName = row["Name"].ToString();

            empTable.Rows.Add(empId, empDepartment, empName);
        }

        // Create the Department table
        DataTable departmentTable = new DataTable("Department");
        departmentTable.Columns.Add("DeptId", typeof(int));
        departmentTable.Columns.Add("DeptName", typeof(string));

        // Populate the Department table from the Emp table
        var departments = empTable.AsEnumerable()
            .Select(row => row.Field("EmpDepartment"))
            .Distinct();

        int deptId = 1;
        foreach (string deptName in departments)
        {
            departmentTable.Rows.Add(deptId, deptName);
            deptId++;
        }

        // Print the tables
        Console.WriteLine("Emp table:");
        PrintTable(empTable);

        Console.WriteLine("Department table:");
        PrintTable(departmentTable);
    }

    private static DataTable GetDataTableFromCsv(string csvFilePath)
    {
        DataTable csvData = new DataTable();

        using (StreamReader reader = new StreamReader(csvFilePath))
        {
            string[] headers = reader.ReadLine().Split(',');
            foreach (string header in headers)
            {
                csvData.Columns.Add(header, typeof(string));
            }

            while (!reader.EndOfStream)
            {
                string[] fields = reader.ReadLine().Split(',');
                DataRow row = csvData.NewRow();
                for (int i = 0; i < headers.Length; i++)
                {
                    row[i] = fields[i];
                }
                csvData.Rows.Add(row);
            }
        }

        return csvData;
    }

    private static void PrintTable(DataTable table)
    {
        foreach (DataColumn column in table.Columns)
        {
            Console.Write(column.ColumnName + "\t");
        }
        Console.WriteLine();

        foreach (DataRow row in table.Rows)
        {
            foreach (DataColumn column in table.Columns)
            {
                Console.Write(row[column] + "\t");
            }
            Console.WriteLine();
        }
    }
}

 Sample2.csv:

using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    public static void Main()
    {
        // Read the CSV file into a DataTable
        DataTable csvData = GetDataTableFromCsv("Sample2.csv");

        // Create the Emp table
        DataTable empTable = new DataTable("Emp");
        empTable.Columns.Add("EmpId", typeof(int));
        empTable.Columns.Add("EmpName", typeof(string));
        empTable.Columns.Add("EmpAddress", typeof(string));
        empTable.Columns.Add("EmpQualification", typeof(string));

        // Populate the Emp table from the CSV data
        foreach (DataRow row in csvData.Rows)
        {
            int empId = Convert.ToInt32(row["Id"]);
            string empName = row["Name"].ToString();
			string empAddress = row["Address"].ToString();
			string empQualification = row["Qualification"].ToString();

			empTable.Rows.Add(empId, empName, empAddress, empQualification);
    }

    // Create the Qualification table
    DataTable qualificationTable = new DataTable("Qualification");
    qualificationTable.Columns.Add("QualificationId", typeof(int));
    qualificationTable.Columns.Add("QualificationName", typeof(string));

    // Populate the Qualification table from the Emp table
    var qualifications = empTable.AsEnumerable()
        .Select(row => row.Field("EmpQualification"))
        .Distinct();

    int qualificationId = 1;
    foreach (string qualificationName in qualifications)
    {
        qualificationTable.Rows.Add(qualificationId, qualificationName);
        qualificationId++;
    }

    // Print the tables
    Console.WriteLine("Emp table:");
    PrintTable(empTable);

    Console.WriteLine("Qualification table:");
    PrintTable(qualificationTable);
}

private static DataTable GetDataTableFromCsv(string csvFilePath)
{
    DataTable csvData = new DataTable();

    using (StreamReader reader = new StreamReader(csvFilePath))
    {
        string[] headers = reader.ReadLine().Split(',');
        foreach (string header in headers)
        {
            csvData.Columns.Add(header, typeof(string));
        }

        while (!reader.EndOfStream)
        {
            string[] fields = reader.ReadLine().Split(',');
            DataRow row = csvData.NewRow();
            for (int i = 0; i < headers.Length; i++)
            {
                row[i] = fields[i];
            }
            csvData.Rows.Add(row);
        }
    }

    return csvData;
}

private static void PrintTable(DataTable table)
{
    foreach (DataColumn column in table.Columns)
    {
        Console.Write(column.ColumnName + "\t");
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn column in table.Columns)
        {
            Console.Write(row[column] + "\t");
        }
        Console.WriteLine();
    }
}
}

The code above reads the CSV file into a DataTable, creates two new DataTables with the desired column names, populates the tables with the data from the CSV file, and then prints the tables to the console. 

Assuming that the CSV files are in the same format as described in the question. If the CSV files have different column names or different numbers of columns, the code will need to be modified accordingly.
 

Hi Meghana,

https://www.c-sharpcorner.com/article/how-to-read-data-from-an-excel-sheet/

Check this.

public class TableOne
{
    public int EmpId { get; set; }
    public string EmpDepartment { get; set; }
    public string EmpName { get; set; }
}

public class TableTwo
{
    public int EmpId { get; set; }
    public string EmpName { get; set; }
    public string EmpAddress { get; set; }
    public string EmpQualification { get; set; }
}

using CsvHelper;

public static void ImportCsvFile(string filePath)
{
    using (var context = new MyDbContext())
    {
        var tableOnes = new List();
        var tableTwos = new List();

        using (var reader = new StreamReader(filePath))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            tableOnes = csv.GetRecords().ToList();
            tableTwos = csv.GetRecords().ToList();
        }

        context.Customers.AddRange(tableOnes);
        context.SaveChanges();

        context.Customers.AddRange(tableOnes);
        context.SaveChanges();
    }
}

Hello Meghana,

you can use Import wizard of sql server to import csv filr into sql table,

https://www.c-sharpcorner.com/article/how-to-import-data-from-excel-data-into-sql-table/