Exporting CSV to SQL in C# - how to offset the export by one column

Question

I am using C# to parse a csv file and export to a SQL Server database table. The schema of the database table is almost identical to that of the csv file, with the exception that the table has a Primary Key Identity column as the first column.

The problem: the 2nd column of the database table, which should receive the 1st column of the csv file, is actually receiving the 2nd column of the csv file. The code is assuming that first PK Identity column of the database table is the first column to be written to from the CSV file. In case this is confusing, assume column 1, 2, and 3 of the CSV file have headers called Contoso1, Contoso2 and Contoso3, respectively. The database table's columns 1 through 4 are called Id, Contoso1, Contoso2, and Contoso3, respectively. During the export, the Id column correctly gets populated with the identity id, but then the Contoso1 column of the database table gets populated with the Contoso2 column of the CSV file, and that being off by one column continues on for all 300 columns.

Here is the code. I'm looking for a way to do a one-column offset with this code. If possible, I'd like to avoid hardcoding a mapping scheme as there are 300+ columns.

using System;
using System.Data.SqlClient;
using System.Data;
using Microsoft.VisualBasic.FileIO;

namespace CSVTest
{
    class Program
    {
        static void Main(string[] args)
        {

            string csv_file_path = @"pathToCsvFile";
            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
            Console.WriteLine("Rows count:" + csvData.Rows.Count);
            InsertDataIntoSQLServerUsingSQLBulkCopy(csvData);
        }



        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();
            try
            {
                using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return csvData;
        }


        static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
        {
            using (SqlConnection dbConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Database_Name;Integrated Security=SSPI;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = "TableName";
                    //foreach (var column in csvFileData.Columns)
                        //s.ColumnMappings.Add(column.ToString(), column.ToString());
                    s.WriteToServer(csvFileData);
                }
            }
        }
    }
}

Answer 1

I'm assuming that -

a. only column needs to be skipped, but this can be modified to add multiple columns to skip

b. you know, ahead of time, the zero based index of the column to skip.

With that out of the way, here are the 3 modifications you need to make.

1. Add the variable to store the index to skip

string csv_file_path = @"pathToCsvFile";
//Assuming just one index for the column number to skip - zero based counting. 
//perhaps read from the AppConfig
int columnIndexToSkip = 0;
DataTable csvData = GetDataTabletFromCSVFile(csv_file_path, columnIndexToSkip);

2. Modify the function signature to take the extra int parameter

        private static DataTable GetDataTabletFromCSVFile(string csv_file_path, int columnIndexToSkip)
        {

3. Add the dummy column at that index

                            csvData.Rows.Add(fieldData);
                        }
                    }// end of while (!csvReader.EndOfData) loop

                    if (columnIndexToSkip >= 0)
                    {
                        csvData.Columns.Add("DUMMY").SetOrdinal(columnIndexToSkip);
                    }

I've not tested the import, but the updated csv file looks good to me.