none
Generating CSV from DataSet via XML - too much many Columns RRS feed

  • Question

  • Hello,

    I have a situation where I need to use ADO.NET and it's DataSet class and export it's data in CSV format.

    I'm exporting data via XML. First data is read into DataSet then it is converted into XML and after that I convert it into CSV string.

    Problem is that in every CSV row's end are couple of extra fields that shouldn't be there. Has anyone aany ideas why they come there and how I can get rid of them?

    Br

    Michael

    DataTable dtCloned = SetEmptyToNullValuesIntoXML(objDataSet);
    objDataSet = new DataSet();
    objDataSet.Tables.Add(dtCloned);

    // Converting DataSet to XML and XML to CSV file            
    string xmlInput = objDataSet.GetXml();

    string csvOut = string.Empty;
    XDocument doc = XDocument.Parse(xmlInput);
    StringBuilder sb = new StringBuilder(120000);
    foreach (XElement node in doc.Descendants("Table"))
    {
    DateTime dateTime;
    foreach (XElement innerNode in node.Elements())
    {
    //"{0}," give you the output in Comma seperated format.
    if(DateTime.TryParse(innerNode.Value, out dateTime) && innerNode.Value.Length > 5)
    {                        
    innerNode.Value = dateTime.ToString("dd.MM.yyyy");
    }
    sb.AppendFormat("{0},", innerNode.Value);
    }
    sb.Remove(sb.Length - 1, 1);
    sb.AppendLine();
    }

    csvOut = String.Join(",", headers.Select(x => x.ToString()).ToArray());
    csvOut += "\n" + sb.ToString();            

    ....
    // Tool function that will generate empty XML fields into CSV
    private DataTable SetEmptyToNullValuesIntoXML(DataSet objDataSet)
    {
    DataTable dt = objDataSet.Tables[0];
    DataTable dtCloned = dt.Clone();
    foreach (DataColumn dc in dtCloned.Columns)
    {
    dc.DataType = typeof(string);
    }

    foreach (DataRow row in dt.Rows)
    {
    dtCloned.ImportRow(row);
    }

    foreach (DataRow row in dtCloned.Rows)
    {
    for (int i = 0; i < dtCloned.Columns.Count; i++)
    {
    dtCloned.Columns[i].ReadOnly = false;

    if (string.IsNullOrEmpty(row[i].ToString()))
    row[i] = string.Empty;
    }
    }
    return dtCloned;
    }


    • Edited by Michael_laanti Wednesday, August 14, 2019 12:40 PM Clearify
    Wednesday, August 14, 2019 12:34 PM

All replies

  • Hi,

    Here is a simple demo maybe you can refer to.

    First, read data from dataset to xml (xml string).

        string constr = @"connection string";
        DataSet Ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlDataAdapter sda = new SqlDataAdapter("Select * From Animal", conn);
            sda.Fill(Ds, "Table");
        }
        string xstring = Ds.GetXml();

    Then, convert xml to csv.

        string csvOut = string.Empty;
        XDocument doc = XDocument.Parse(xstring);
        StringBuilder sb = new StringBuilder(100000);
        foreach (XElement node in doc.Descendants("Table"))
        {
            foreach (XElement innerNode in node.Elements())
            {
                sb.AppendFormat("{0},", innerNode.Value);
            }
            sb.Remove(sb.Length - 1, 1);
            sb.AppendLine();
        }
        File.WriteAllText(@"D:\new.csv", sb.ToString());

    Hope this can help you.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 2:45 AM
    Moderator
  • This is exactly same code as mine.

    My code's SetEmptyToNullValuesIntoXML can handle empty tags too.

    Thursday, August 15, 2019 6:10 AM
  • Hi,

    After you test this code, does "couple of extra fields" still exist? If so, could you provide the demo image and point it?

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 15, 2019 6:15 AM
    Moderator
  • Hi Michael,

    Can you post an example of the XML string after you get it from the DataSet? And then post the resulting CSV file with the extra fields?

    Then we can visually compare the XML to the CSV and maybe figure out where your extra fields are coming from.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, August 16, 2019 4:51 AM
  • I think that the problem occurs because I had to use external or additional Columns in DataSet which are calculated into this report.

    And of course DataSet's Column count is growing and extra fields are coming to CSV.

    Removing those additional Columns helped the situation.

    The problem arised because DataSet that I was using didn't have enough Columns to Business needs.

    That error was strictly my fault because I didn't noticed it :(

    Thanks to all!

    Friday, August 16, 2019 5:46 AM
  • Glad you got it figured out, Michael!  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, August 18, 2019 1:52 PM