SQLBulkCopy C# Error (Change in Field Value)

Question

0.00/5 (No votes)

See more:

Hello Guys!

I want to Bulk Copy a .CSV file and save it to db. Everything is going fine but the problem I am facing is that the data of one of the column gets slightly changed. A value of "SS13" becomes "13.0000" after it is copied.
Please help me to fix it friends.
Here is my code ..

C#

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
                string sql_select;
                OdbcConnection conn;
                conn = new OdbcConnection(strConnString.Trim());
                conn.Open();
                sql_select = "select * from [" + FileNevCSV.Trim() + "]";
                //Creates the data adapter
                OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
                OdbcCommand myCommand = new OdbcCommand(sql_select, conn);
                //Fills dataset with the records from CSV file
                DataSet ds = new DataSet();
                obj_oledb_da.Fill(ds, "csv");
                OdbcDataReader myReader;
                DeleteAllFromTempStock();
                myReader = myCommand.ExecuteReader();
                SqlBulkCopy stockBulkCopy = new    SqlBulkCopy(ConfigurationManager.ConnectionStrings["companyConnectionString"].ConnectionString);
                stockBulkCopy.DestinationTableName = "TemporaryStock";
                stockBulkCopy.WriteToServer(myReader);
                //closes the connection
                myReader.Close();
                conn.Close();

[edit]code block added [/edit]

Posted 13-Mar-13 10:26am

tipu khan jan

Updated 14-Mar-13 23:36pm

Nelek

v5

Add a Solution

Comments

[no name] 13-Mar-13 16:31pm

Well check the data type of your columns.

tipu khan jan 13-Mar-13 16:36pm

There is nothing to do with DataType because the error remains during run time as i checked it in dataset visualizer before saving it to db...

[no name] 13-Mar-13 16:40pm

Uhm.... no it has everything to do with data type.

tipu khan jan 13-Mar-13 16:50pm

Sir, the datatype is varchar for this column .. but actually the value gets changed when the csv file is read .. i observed it in dataset visualizer ..

R. Giskard Reventlov 13-Mar-13 16:42pm

What data type is the column in the database? Only asking as you have ignored ThePahntomUpvoter for unknown reasons.

[no name] 13-Mar-13 16:47pm

What do you mean "unknown"?.... Phantom! :-)

tipu khan jan 13-Mar-13 16:50pm

Sir, the datatype is varchar for this column .. but actually the value gets changed when the csv file is read .. i observed it in dataset visualizer before saving it to DB..

R. Giskard Reventlov 13-Mar-13 17:01pm

Show us the code.

tipu khan jan 13-Mar-13 17:09pm

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + dirCSV.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
sql_select = "select * from [" + FileNevCSV.Trim() + "]";
//Creates the data adapter
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
OdbcCommand myCommand = new OdbcCommand(sql_select, conn);
//Fills dataset with the records from CSV file
DataSet ds = new DataSet();
obj_oledb_da.Fill(ds, "csv");
OdbcDataReader myReader;
DeleteAllFromTempStock();
myReader = myCommand.ExecuteReader();
SqlBulkCopy stockBulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["companyConnectionString"].ConnectionString);
stockBulkCopy.DestinationTableName = "TemporaryStock";
stockBulkCopy.WriteToServer(myReader);
//closes the connection
myReader.Close();
conn.Close();

[no name] 13-Mar-13 17:07pm

The code you presented would not do what you describe. I am more interested to know what Excel did to your file and why you think that would have nothing to do with it.

tipu khan jan 13-Mar-13 17:13pm

As i put the data first in Dataset just to check it before saving it to DB ... and there it is changed .. shown in my above code.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Espen Harlinn · Answer 1 · 2013-03-13T11:21:00

You might be interested in the TextFieldParser[^]

using (TextFieldParser parser = new TextFieldParser(filename))
{
    parser.CommentTokens = new string[] { "#" };
    parser.SetDelimiters(new string[] { ";" });
    parser.HasFieldsEnclosedInQuotes = true;
 
    // Skip the header
    parser.ReadLine();
 
    while (!parser.EndOfData)
    {
        string[] fields = parser.ReadFields();
        // process the row
        ....
    }
}

This way you can handle the conversion of each field yourself.

Best regards
Espen Harlinn