1

I have a txt file, here is some sample data:

Id,PriceOne,Company,PriceTwo,PriceThree
11,15.3599997,Japan ltd,12.23,3.1777777
12,,Koyoto ltd,,0
13,86.25,New Wor,-1289519.44,2536.1245627
......

I use this commandText to read the txt file's data into datatable:

 string connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" 
                   + filePath + ";Extensions=asc,csv,tab,txt;";
        try
        {
            using (OdbcConnection odbcConn = new OdbcConnection(connString))
            {
                odbcConn.Open();
                OdbcCommand oleComm = new OdbcCommand();
                oleComm.Connection = odbcConn;

                oleComm.CommandText = string.Empty;
                if (fileName.ToLower().Contains("fairvalue"))
                 oleComm.CommandText = "select * from [" + fileName + "#txt]";
                else
                 oleComm.CommandText = "select * from [" + fileName + "#csv]";
                OdbcDataAdapter adapter = new OdbcDataAdapter(oleComm);
                DataSet ds = new DataSet();
                adapter.Fill(ds, fileName);
                return ds.Tables[0];
            }
       }

but when I debugging the datatable, I got the reslut like below:

Id PriceOne    Company      PriceTwo  PriceThree
11 15.3599997  Japan ltd    12        3
12             Koyoto ltd   0
13 86.25       New Wor      -1289519  2536
......

I don't know why the PriceTwo column and PriceThree columns' value are changed to INT, well, PriceOne column's value is right ?

Could anyone help me? thx.

CharlieShi
  • 888
  • 3
  • 17
  • 43
  • Can you post the complete code? – Amar Palsapure Feb 10 '12 at 08:46
  • Consider using filehelpers - www.filehelpers.com. It's free, and will allow you to specify the column specification on import. – dash Feb 10 '12 at 08:51
  • @Amar Palsapure, I have modified the content and add more code details, above code is what I used to extract data from a txt file(the txt file's data is splited by comma, so the ODBC object can read it by .net) – CharlieShi Feb 10 '12 at 08:51
  • @dash, thx for your advice, but I think it's too late for me to change my code. – CharlieShi Feb 10 '12 at 08:55
  • What is text encoding of csv/txt doc? – KV Prajapati Feb 10 '12 at 08:58
  • 1
    In which case you can try using a schema.ini file. This file will allow you to specify the column types, and has already been covered on SO here - http://stackoverflow.com/questions/115658/when-reading-a-csv-file-using-a-datareader-and-the-oledb-jet-data-provider-how Note that this should work for the ODBC connection too. You can even get windows to write the schema.ini file for you - http://www.devx.com/tips/Tip/12566 – dash Feb 10 '12 at 09:02
  • @dash, thx, you give me the right answer. – CharlieShi Feb 10 '12 at 09:14
  • thx for dash's answer, I have solved the problem by below method: 1. create a file named Schema.ini under the same location where your txt file is in. 2. open the Schema.ini file and add below content: [Mytext.txt] ColNameHeader=True MaxScanRows=0 – CharlieShi Feb 10 '12 at 09:18

1 Answers1

1

check the database table([priceHistory#txt]) in that two columns having what datatype. it must be int datatype.

change the datatype of "PriceTwo" and PriceThree" from integer to decimal(18,2)

it will work fine.

Id PriceOne    Company      PriceTwo (should be decimal)  PriceThree (should be decimal)
11 15.3599997  Japan ltd    12        3
12             Koyoto ltd   0
13 86.25       New Wor      -1289519  253

check it and let me know.

Krishna N
  • 216
  • 1
  • 2
  • 7