csv read by jet engine throws error 'specified cast is not valid'

[learneveryday]

Hi,

I am trying to read data from csv and process the data using c# code and send the data to database. I read csv file using Oledb jet engine connection. It works in my local machine. But does not work in the server, same code, same settings and the same csv file to test both environment. I checked all the oledb jet engine dlls in both machines and both seem to be similar. I am getting the above error when I run it in server.

Any one please help me. Many thanks.

[serkan sendur]

provide your code please.

[learneveryday]

hi Serkan,

here is the fragment of code which I used in the reader function. I believe that the error occurs somewhere in this block.

List<csvData> csvDatas = List<csvData>();
           OleDbConnection oleConnection = new OleDbConnection(string.Format(Resources.OleDBConnection, filePath));
            try
            {
                 
                        OleDbCommand cmd = new OleDbCommand( fileName, oleConnection);
                  
                        oleConnection.Open();
                        OleDbDataReader reader = cmd.ExecuteReader();
                        try
                        {
                            while (reader.Read())
                            {
                               csvData   csvData = new csvData  ();

                                if (reader.GetOrdinal(“ColumnOne”) > -1)
                                {
                                    csvData.col1= reader.GetString(reader.GetOrdinal(“ColumnOne”)));
                                }
                                if (reader.GetOrdinal(“ColumnTwo”) > -1)
                                {
                                    csvData.col2= reader.GetString(reader.GetOrdinal(“ColumnTwo”));
                                }
                                if (reader.GetOrdinal(“ColumnThree”) > -1)
                                {
                                    csvData.col3= reader.GetString(reader.GetOrdinal(“ColumnThree”));
                                }
                                if (reader.GetOrdinal(“ColumnFour”) > -1) 
                                {
                                    csvData.col4= reader.GetString(reader.GetOrdinal(“ColumnFour”));
                                }
                                if (reader.GetOrdinal(“ColumnFive”) > -1) 
                                {
                                    csvData.col5= reader.GetDateTime(reader.GetOrdinal(“ColumnFive”));
                                }
                                if (reader.GetOrdinal(“ColumnSix”) > -1) 
                                {
                                   csvData.col6= reader.GetDateTime(reader.GetOrdinal(“ColumnSix”));
                                }
                                if (reader.GetOrdinal(“ColumnSeven”) > -1) 
                                {
                                   csvData.col7= reader.GetDateTime(reader.GetOrdinal(“ColumnSeven”));
                                }
                                if (reader.GetOrdinal(“ColumnEight”) > -1)
                                {
                                    csvData.col8= reader.GetDouble(reader.GetOrdinal(“ColumnEight”));
                                }
                                csvDatas.Add(csvData);
                            }

                        }
                        catch (InvalidCastException ice)
                        {
		//some code to handle the exception
                        }
                        finally
                        {
                            cmd.Dispose();
                            reader.Close();
                        }
                                      
            }
            catch (OleDbException ole)
            {
//some code
            }
            finally
            {
                oleConnection.Close();
            }
            return csvDatas;
         }

I really appreciate your help.

Thanks.

[kvprajapati]

Try to read all values as a string. Will you attach your data file?

[learneveryday]

Try to read all values as a string. Will you attach your data file?

Hi,

I finally found out the problem that server's date time setting is in the us format. But my machine settings are in the UK format. And the data are in UK format. Therefore the code works in my machine for the csv file but not working in the server for the same file. I tried converting the date using different format also with current culture. None of the format works for server's US setting to work with the uk formatted data.

Please anyone help me.

[kvprajapati]

Use DateTime.TryParseExact method.

string s = "31/12/2009";
   DateTime dt;
   DateTime.TryParseExact(s, "dd/MM/yyyy", null,  System.Globalization.DateTimeStyles.None, out dt);