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:
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();
}
}
}
}
}