I am importing .csv file data into a SQL Server table using SqlBulkCopy. My .csv file can be of any format as I can't determine the columns in it and the datatypes of it as same column can have decimal and text values, so I need to pass the value as such in the .csv file. But for some reason, some .csv files decimal points getting rounding off.

DataTable csvData = new DataTable();
StreamReader streamreader = new StreamReader(apsrcfile);
string Headline = File.ReadLines(Csvfilepath).ElementAt(0);|
string[] Headerline = Headline.Split(',');
for (int i = 0; i < Headerline.Length; i++)
 {
    csvData.Columns.Add();
 }
string[] lines = File.ReadAllLines(csvfilepath);
foreach (string line in lines)
{
string[] col = line.Split(',');
}
csvData.Rows.Add(col);
streamreader.Dispose();

SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Live"].ConnectionString);
objConn.Open();

SqlBulkCopy objbulkinsert = new SqlBulkCopy(objConn);
objbulkinsert.DestinationTableName = "Mappingtable";

foreach (DataColumn column in csvData.Columns)
{
    objbulkinsert.ColumnMappings.Add(column.ColumnName.ToString(), column.ColumnName.ToString());
}

objbulkinsert.BulkCopyTimeout = 0;
objbulkinsert.WriteToServer(csvData);
objConn.Close();
                    

It works for other files. I don't know whether the .csv file is of problem, but opening it in notepad it shows the decimal point. I will attach the image of .csv file and SQL output I got.

Expected output: as such as .CSV file data in the SQL Server table.

I am importing .csv file data into a SQL Server table using SqlBulkCopy. My .csv file can be of any format as I can't determine the columns in it and the datatypes of it as same column can have decimal and text values, so I need to pass the value as such in the .csv file. But for some reason, some .csv files decimal points getting rounding off.

DataTable csvData = new DataTable();
StreamReader streamreader = new StreamReader(apsrcfile);
string Headline = File.ReadLines(Csvfilepath).ElementAt(0);|
string[] Headerline = Headline.Split(',');
for (int i = 0; i < Headerline.Length; i++)
 {
    csvData.Columns.Add();
 }
string[] lines = File.ReadAllLines(csvfilepath);
foreach (string line in lines)
{
string[] col = line.Split(',');
}
csvData.Rows.Add(col);
streamreader.Dispose();

SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Live"].ConnectionString);
objConn.Open();

SqlBulkCopy objbulkinsert = new SqlBulkCopy(objConn);
objbulkinsert.DestinationTableName = "Mappingtable";

foreach (DataColumn column in csvData.Columns)
{
    objbulkinsert.ColumnMappings.Add(column.ColumnName.ToString(), column.ColumnName.ToString());
}

objbulkinsert.BulkCopyTimeout = 0;
objbulkinsert.WriteToServer(csvData);
objConn.Close();
                    

It works for other files. I don't know whether the .csv file is of problem, but opening it in notepad it shows the decimal point. I will attach the image of .csv file and SQL output I got.

Expected output: as such as .CSV file data in the SQL Server table.

• c#
• sql-server
• csv
• datatable
• sqlbulkcopy

Share Improve this question Follow edited Nov 20, 2024 at 13:06 Gowsi asked Nov 18, 2024 at 17:47 GowsiGowsi 744 bronze badges 13

• 4 What is csvData and have you checked that data is correct in there? – Ralf Commented Nov 18, 2024 at 18:07
• 4 Pls show Mappingtable column data types. – ValNik Commented Nov 18, 2024 at 19:50
• 2 Please don't use images, use formatted text. – Dale K Commented Nov 18, 2024 at 19:51
• 1 Please do not upload images of code/data/errors when asking a question. – Thom A Commented Nov 18, 2024 at 19:53
• 1 Relook at your csv. Not in Excel that already interprets data to show it in tabular style. Just look at it in notepad or any other simple editor. Paste a few lines here instead of the picture as pure text. Then debug your code and look directly into your datatable what data is inside there and if the problematic column has already trimmed the data. WHen you say csv also show how you got from the csv to a datatable. Thats not just simply a given. – Ralf Commented Nov 19, 2024 at 16:28

 |  Show 8 more comments

1 Answer 1

Sorted by: Reset to default 0

Table Design Matters

The table design of the target table and the view of the CSV is unknown. It makes difficult to answer. Column2 in destination table can be DECIMAL or MONEY and it must have 2 decimal digits that are stored to the right of the decimal point. If the data type is DECIMAL without any decimal digits that upload data without decimal digits. This may have happened to you. I assumed the text file(MappingTable.CSV) is something like it

/94HD012,194.37,10
/94HD013,483.37,
/94HD014,100,1
11-0001,112.57,100

The structure of the destination will be

CREATE TABLE [dbo].[MappingTable](
    [Column1] [varchar](50) NULL,
    [Column2] [decimal](12, 2) NULL,
    [Column3] [varchar](50) NULL,
    [Column4] [varchar](50) NULL
)

Finally, it properly works with the following code.

        private void btnSQLBulkCopyCSV_Click(object sender, EventArgs e)
        {
            DataTable dataTable = new DataTable();
            string ConnStr = "Data Source= MyDB; Database=Learning; Integrated Security=SSPI;";
            SqlConnection objConn = new SqlConnection(ConnStr);
            objConn.Open();

            using (StreamReader reader = new StreamReader(@"E:\\Learning\\CSharp\\SampleData\MappingTable.csv"))
            {
                string[] headers = reader.ReadLine().Split(',');
                int columnCount = headers.Count();
                DataColumn[] columns; //
                columns = Enumerable.Range(1, columnCount).Select(x => new DataColumn($"Column{x}")).ToArray();
                dataTable.Columns.AddRange(columns);

                DataRow dataRow1 = dataTable.NewRow();
                for (int i = 0; i < columnCount; i++)
                {
                    dataRow1[i] = headers[i];
                }
                dataTable.Rows.Add(dataRow1);

                while (!reader.EndOfStream)
                {
                    string[] rows = reader.ReadLine().Split(',');
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dataRow[i] = rows[i];
                    }
                    dataTable.Rows.Add(dataRow);
                }
                SqlBulkCopy objbulkinsert = new SqlBulkCopy(objConn);
                objbulkinsert.DestinationTableName = "Mappingtable";

                foreach (DataColumn column in dataTable.Columns)
                {
                    objbulkinsert.ColumnMappings.Add(column.ColumnName.ToString(), column.ColumnName.ToString());
                }

                objbulkinsert.BulkCopyTimeout = 0;
                objbulkinsert.WriteToServer(dataTable);
                objConn.Close();
            }
        }

This was implemented on .Net Framework 4.7.2