none
Reading Data from a Flat File to a Typed DataSet RRS feed

  • Question

  •  

    Hi All,

     

    I have a query with regards to reading data from a flat file (.csv / .txt(tab delimited)) to a strongly typed dataset.  At the moment I have created the dataset and a table within the dataset which i wish to populate, however I keep gettng errors about incorrect datatypes, and am not too sure how to overcome the error.

     

    I have posted my simple code below:

     

    internal void LoadTypedData(string strFileName)

    {

    typedTenaDataSet tds = new typedTenaDataSet();

    StreamReader sr = new StreamReader(strFileName);

    while (sr.ReadLine() != null)

    {

    string[] items = sr.ReadLine().Split(',');

    if (items[0].ToString().Contains("Number"))

    {

    continue;

    }

    else

    {

    Convert.ToInt32(items[0]);

    tds.Tables["dtCouponInformation"].Rows.Add(items);

    }

    }

    }

     

    The error I get is thrown at the Convert.ToInt32(items[0]); line, which is:

     

    System.FormatException was unhandled
      Message="Input string was not in a correct format."
      Source="mscorlib"
      StackTrace:
           at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
           at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
           at System.Convert.ToInt32(String value)
           at TenaDataLoader.frmMain.LoadTypedData(String strFileName) in C:\Development\2005\Projects\TenaDataLoader\TenaDataLoader\frmMain.cs:line 350
           at TenaDataLoader.frmMain..ctor(String[] args) in C:\Development\2005\Projects\TenaDataLoader\TenaDataLoader\frmMain.cs:line 79
           at TenaDataLoader.Program.Main(String[] args) in C:\Development\2005\Projects\TenaDataLoader\TenaDataLoader\Program.cs:line 50
           at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()


     

    Any help that you could provide for this will be appreciated.

     

    Thanks in advance!!

    Sukh

    Tuesday, March 18, 2008 10:57 AM

Answers

  • ok, but i'm not sure that you can use array of objects to insert data into dataset. maybe if you do it manually, something like

    tds.Tables["dtCouponInformation"].Rows.Add(convert.toint(items[0]), and so on, and so on);

    hope this helps Smile
    Wednesday, March 19, 2008 6:55 AM

All replies

  • Please check the value when you do the

     

    Convert.ToInt32(items[0]);

     

    i suspect that items[0]  is not a numeric value.

     

     

     

    Tuesday, March 18, 2008 11:36 AM
  • Hi Bermil,

     

    Yes items[0] is not a numeric value, i need to know how to convert it.  I have previously used Convert.ToInt32, without any problems, and cannot understand why it is causing an issue this time?  I also get the error:

     

    Input string was not in a correct format.Couldn't store <"546"> in RunNumber Column.  Expected type is Int32.

     

    Thanks

    Tuesday, March 18, 2008 11:47 AM
  • Hi,

     

    I was trying to replicate your values like the way below but find no error.

     

    string num = "25, abs";

    string[] items = num.Split(',');

    int number = Convert.ToInt32(items[0]);

     

    you'll get an error only if value from item[0] is not equals to 25. did you try to put a break in your convertion part?

    Tuesday, March 18, 2008 11:57 AM
  • Hi Bermil,

     

    Thanks for that, I have tried putting breakpoints into the convertion part of the program, the value that I am seeing for the items[] array is {[0] = "\"546\""}.

     

    Essentially what I am trying to do is:

    object[] item = num.Split(',');

    item[0] = Convert.ToInt32(item[0]);

     

    Any ideas? 

     

    Thanks

    Tuesday, March 18, 2008 12:45 PM
  • Hi,

     

    As long as there is special characters on the value to be converted to int32 you will always get an error. you need to clean up your data before conversion.

     

    you can use the replace method like below if you are sure that throughout your record the characters is just "\".

    string num = "\\25\\, abs";

    string[] items = num.Split(',');

    Convert.ToInt32(items[0].Replace("\\", " "));

     

    else you need to write a method that will clean up your data/values before a conversion is made.

     

     

    Tuesday, March 18, 2008 1:15 PM
  •  Bermil Espina wrote:

    Hi,

     

    As long as there is special characters on the value to be converted to int32 you will always get an error. you need to clean up your data before conversion.

     

    you can use the replace method like below if you are sure that throughout your record the characters is just "\".

    string num = "\"25\", abs";

    string[] items = num.Split(',');

    Convert.ToInt32(items[0].Replace("\"", ""));

     

    else you need to write a method that will clean up your data/values before a conversion is made.

     

     

     

    just a little correction

    Tuesday, March 18, 2008 1:24 PM
  • Hi Bermil,

     

    I feel we are making progress!!  Well more yourself than me!!  At the moment I have the code below, but I got a new error when trying to convert the value:

    internal void LoadTypedData(string strFileName)

    {

    typedTenaDataSet tds = new typedTenaDataSet();

    StreamReader sr = new StreamReader(strFileName);

    while (sr.ReadLine() != null)

    {

    object[] items = sr.ReadLine().Split(',');

    if (items[0].ToString().Contains("Number"))

    {

    continue;

    }

    else

    {

    items[0] = Convert.ToInt32(items[0].ToString().Replace("\"", ""));

    tds.Tables["dtCouponInformation"].Rows.Add(sr.ReadLine().Split(','));

    }

    }

    }

     

    Error being returned is a runtime error: Attempted to access an element as a type incompatible with the array

     

    Any Ideas?

     

    Thanks

    Tuesday, March 18, 2008 1:36 PM
  •  Sukh1983 wrote:

    Hi Bermil,

     

    I feel we are making progress!!  Well more yourself than me!!  At the moment I have the code below, but I got a new error when trying to convert the value:

    internal void LoadTypedData(string strFileName)

    {

    typedTenaDataSet tds = new typedTenaDataSet();

    StreamReader sr = new StreamReader(strFileName);

    while (sr.ReadLine() != null)

    {

    object[] items = sr.ReadLine().Split(',');

    if (items[0].ToString().Contains("Number"))

    {

    continue;

    }

    else

    {

    items[0] = Convert.ToInt32(items[0].ToString().Replace("\"", ""));

    tds.Tables["dtCouponInformation"].Rows.Add(sr.ReadLine().Split(','));

    }

    }

    }

     

    Error being returned is a runtime error: Attempted to access an element as a type incompatible with the array

     

    Any Ideas?

     

    Thanks

     

    items[0] = Convert.ToInt32(items[0].ToString().Replace("\"", ""));

     

    => without convert.toint

     

    items[0] = items[0].ToString().Replace("\"", "");

     

    Tuesday, March 18, 2008 1:50 PM
  • what i am seeing is the issue is around this area.

     

    tds.Tables["dtCouponInformation"].Rows.Add(sr.ReadLine().Split(','));

     

    please try to look at this code i wrote with dataset sample. it might help you construct your code in the else statement portion of your method:

     

    string num = "25, abs";

    string[] items = num.Split(',');

    DataSet ds = new DataSet();

    DataTable dt = new DataTable("MyTable");

    DataColumn dc = new DataColumn("Value");

    dt.Columns.Add(dc);

    DataRow dr = dt.NewRow();

    dr["Value"] = items[0].ToString();

    string myVal = dr["Value"].ToString();

     

    Tuesday, March 18, 2008 2:08 PM
  • Hi Bermil,

     

    Sorry about that I was testing something, the method should look like this:

    internal void LoadTypedData(string strFileName)

    {

    typedTenaDataSet tds = new typedTenaDataSet();

    StreamReader sr = new StreamReader(strFileName);

    while (sr.ReadLine() != null)

    {

    object[] items = sr.ReadLine().Split(',');

    if (items[0].ToString().Contains("Number"))

    {

    continue;

    }

    else

    {

    items[0] = Convert.ToInt32(items[0].ToString().Replace("\"", ""));

    tds.Tables["dtCouponInformation"].Rows.Add(items);

    }

    }

    }

     

    The error: Attempted to access an element as a type incompatible with the array is being thrown at line

    items[0] = Convert.ToInt32(items[0].ToString().Replace("\"", ""));

     

    I know that this error is due to the datatype of the first column being Int32, but the value it is passing in is a string.  What I am unsure about is how to parse the string value as a Int32.  The various different things I have tried that resulted in an error?

     

    The reason I am creating a object array is because some of the items will need to be of type Int32, and others of type string and other of type DateTime?

     

    Thanks

    Tuesday, March 18, 2008 2:19 PM
  • store them all as strings and when you read them convert them to apropriate type. code from post above that i provided should work

    Tuesday, March 18, 2008 2:28 PM
  • Hi Dino,

     

    Thanks for that info but I was hoping to use a Typed DataSet to prevent adding extra code to convert the data into the appropriate type later on.

     

    Thanks

    Tuesday, March 18, 2008 3:49 PM
  • ok, but i'm not sure that you can use array of objects to insert data into dataset. maybe if you do it manually, something like

    tds.Tables["dtCouponInformation"].Rows.Add(convert.toint(items[0]), and so on, and so on);

    hope this helps Smile
    Wednesday, March 19, 2008 6:55 AM
  • Hi Dino,

     

    Thanks for that information.  After a bit more searching on the net, I have decided to use a struct and pass in the values of each datarow to the struct.

     

    Thanks

    Thursday, March 20, 2008 7:59 AM