How to insert data from csv to access db if the co

Question

It is using c# programming. I am selecting the csv file and inserting it in to the access db. I have say, 6 columns in the csv file like ID, FName, LName, Address, Zipcode, cell number in the same order. In access, my columns are FirstName, LastName, S_NO, zip_code (in the same order). How i can import this csv file in to access db, if the column names are different as well the order is different. How can i implement this?? Pls suggest. I tried the following code:

enter code here

DataSet da = new DataSet(); try {

            da = this.ConnectCSV(strCSVFile);
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\EmpApp\\EmpData.accdb;";
            OleDbConnection conn = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            conn.Open();
            for (int i = 0; i <= da.Tables["Ss"].Rows.Count - 1; i++)
            {

                for (int j = 1; j <= da.Tables["Ss"].Columns.Count - 1; j++)
                {
                    cmd.CommandText = "Insert  into EMP_DOWNLOAD ( ID,Company_name,month_billed,year,start_date,end_date,Designation"
                      + ")  values(" + (i + 1) + ",'" 
                      + da.Tables["Ss"].Rows[i].ItemArray.GetValue(0) + "',"
                      + da.Tables["Ss"].Rows[i].ItemArray.GetValue(8) + 
                      da.Tables["Ss"].Rows[i].ItemArray.GetValue(9) + ")";                        
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                 }
            }
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            btnUpload.Enabled = false;
        }
enter code here

Answer 1

I would use a Linq statement to break your text apart and create arrays of your parameter objects then loop through the arrays adding the parameters to each insert statement that is sent to the database.

Like this

var csvParams = (from line in System.IO.File.ReadLines("fileName.csv")
            from cell in line.Split(',').AsEnumerable()
            select new[]
            {
                new OleDbParameter("@fn", cell[1]),
                new OleDbParameter("@ln", cell[2]),
                new OleDbParameter("@sn", cell[3]),
                new OleDbParameter("@zc", cell[4])
            });

using( OleDbConnection conn = new OleDbConnection("CONNECTION STRING GOES HERE"))
{
    foreach (var person in csvParams)
    {
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO People(FirstName, LastName, S_NO, zip_code) VALUES (@fn, @ln, @sn, @zc)";
            cmd.Parameters.AddRange( (OleDbParameter[]) person);
            cmd.ExecuteNonQuery();
        }

    }
}

Answer 2

With MS Access, you can run straight from CSV to a table:

INSERT INTO EMP_DOWNLOAD (ID, Forename, LastName, Address, Zip_Code ) 
SELECT  ID, FName, LName, Address, Zipcode
FROM [Text;Database=z:\docs\;HDR=yes].[importfilename.csv]

Answer 3

You can pass null for the filed which are not required..