Click here to Skip to main content
15,906,628 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi i try import csv file to database sql server without header this code work good but with header how can skip the header what the code that delete or ignore to import csv with out header to database in c# windows application my csv format below . thank you all.

001,0000002226,01,2011/03/27,07:07,
001,0000009392,01,2011/03/27,07:12,
001,0000002220,01,2011/03/27,07:17,
001,0000002121,01,2011/03/27,07:19,

What I have tried:

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

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

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.DefaultExt = ".csv";
            ofd.Filter = "Comma Separated (*.csv)|*.csv" ;
            ofd.ShowDialog();
            txtFileName.Text = ofd.FileName;

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnimport_Click(object sender, EventArgs e)
        {

            Cursor = Cursors.WaitCursor;

            DataTable imported_data = GetDataFromFile();  

            if (imported_data == null) return;           

            SaveImportDataToDatabase(imported_data);    

            MessageBox.Show("load data succ.....!");
            txtFileName.Text = string.Empty;
            Cursor = Cursors.Default;


        }

        private DataTable GetDataFromFile()
        {

            DataTable importedData = new DataTable();

            try
            {
                using (StreamReader sr = new StreamReader(txtFileName.Text))
                {


                    string header = sr.ReadLine();
                    if (string.IsNullOrEmpty(header))
                    {

                        MessageBox.Show("no file data");
                        return null;
                    }



                    string[] headerColumns = header.Split(',');
                    foreach (string headerColumn in headerColumns)
                    {
                        importedData.Columns.Add(headerColumn);
                    }



                    while (!sr.EndOfStream)
                    {

                        string line = sr.ReadLine();
                        if (string.IsNullOrEmpty(line)) continue;
                        string[] fields = line.Split(',');
                        DataRow importedRow = importedData.NewRow();

                        for(int i = 0; i < fields.Count(); i++)
                        {
                            
                            importedRow[i] = fields[i];

                        }

                        importedData.Rows.Add(importedRow);
                    }
                }


            }
            catch (Exception e)
            {
                Console.WriteLine("the file could not be read:");
                Console.WriteLine(e.Message);
            }

            return importedData;
        }

        private void SaveImportDataToDatabase(DataTable imported_data)   
        {

            using (SqlConnection conn = new SqlConnection("Data Source=HA-PC\\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"))
            {

                conn.Open();
                foreach (DataRow importRow in imported_data.Rows)   
                {


                SqlCommand cmd = new SqlCommand("INSERT INTO imported_data (device_id,employee_id,status,date,time ) " +
                                                  "VALUES (@device_id,@employee_id,@status,@date,@time)", conn);
                    cmd.Parameters.AddWithValue("@device_id", importRow["device_id"]);
                    cmd.Parameters.AddWithValue("@employee_id", importRow["employee_id"]);
                    cmd.Parameters.AddWithValue("@status", importRow["status"]);
                    cmd.Parameters.AddWithValue("@date", importRow["date"]);
                    cmd.Parameters.AddWithValue("@time", importRow["time"]);
                    cmd.ExecuteNonQuery();
                }

            }
        }

       
    }
}
Posted
Updated 16-Nov-22 17:04pm
v2
Comments
Garth J Lancaster 8-Jun-16 21:15pm    
not exactely sure what you mean - you're code is quite clearly set up to handle a header row

<pre lang="C#">
string header = sr.ReadLine();
if (string.IsNullOrEmpty(header))
{

MessageBox.Show("no file data");
return null;
}



string[] headerColumns = header.Split(',');
foreach (string headerColumn in headerColumns)
{
importedData.Columns.Add(headerColumn);
}

</pre>

are you saying/implying that the header row is optional or may not be in the data file ?

If that IS the case, you may need to
- define a function to test the line for being a header and/or
- define a function to test the line for being data
- define a function to handle the Header
- define a function to handle the Data
- keep a line count

then you could do something like

<pre lang="C#">
int lineCount = 0;
string lineIn = "";
using (StreamReader sr = new StreamReader(txtFileName.txt))
{
while ((lineIn = sr.ReadLine()) != null)
{
if (lineCount == 0 && lineIsHeader(lineIn))
{
// Process Header Line
}
else if lineIsData(lineIn))
{
// Process Data Line
}
else
{
// Error - lineIn Not Header or Data - chuck a wobbly
}
lineCount += 1;
} // while
} // using
</pre>
Garth J Lancaster 8-Jun-16 21:30pm    
btw, if you dont wish to do the grunt-work, there's http://www.filehelpers.net/
Member 11212591 9-Jun-16 16:21pm    
thank you for answer but not solve the problem

1 solution

When you create the DataTable, since your csv file does not have a header record, you need to supply the column names. Since you didn't do this, and then subsequently call the DataTable row fields by their column names, the value returned for each field is null since the DataTable doesn't contain a column by that name.

Here is how I would fix it:
C#
private DataTable GetDataFromFile()
{

    DataTable importedData = new DataTable();
    string header = "device_id,employee_id,status,date,time";

    try
    {
        using (StreamReader sr = new StreamReader(txtFileName.Text))
        {
            if (string.IsNullOrEmpty(header))
            {
                header = sr.ReadLine();
            }

            string[] headerColumns = header.Split(',');
            foreach (string headerColumn in headerColumns)
            {
                importedData.Columns.Add(headerColumn);
            }

            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();
                if (string.IsNullOrEmpty(line)) continue;
                string[] fields = line.Split(',');
                DataRow importedRow = importedData.NewRow();

                for(int i = 0; i < fields.Count(); i++)
                {

                    importedRow[i] = fields[i];

                }

                importedData.Rows.Add(importedRow);
            }
        }


    }
    catch (Exception e)
    {
        Console.WriteLine("the file could not be read:");
        Console.WriteLine(e.Message);
    }

    return importedData;
}
 
Share this answer
 
Comments
Member 11212591 9-Jun-16 16:21pm    
thank you for answer but not solve the problem

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900