Issues creating and writing data to a CSV file using C#

Question

I'm using C# Code in Ranorex 5.4.2 to create a CSV file, have data gathered from an XML file and then have it write this into the CSV file. I've managed to get this process to work but I'm experiencing an issue where there are 12 blank lines created beneath the gathered data.

I have a file called CreateCSVFile which creates the CSV file and adds the headers in, the code looks like this:

writer.WriteLine("PolicyNumber,Surname,Postcode,HouseNumber,StreetName,CityName,CountyName,VehicleRegistrationPlate,VehicleMake,VehicleModel,VehicleType,DateRegistered,ABICode");
        writer.WriteLine("");
        writer.Flush();
        writer.Close();

The next one to run is MineDataFromOutputXML. The program I am automating provides insurance quotes and an output xml file is created containing the clients details. I've set up a mining process which has a variable declared at the top which shows as:

string _PolicyHolderSurname = "";
    [TestVariable("3E92E370-F960-477B-853A-0F61BEA62B7B")]
    public string PolicyHolderSurname
    {
        get { return _PolicyHolderSurname; }
        set { _PolicyHolderSurname = value; }
    }

and then there is another section of code which gathers the information from the XML file:

var QuotePolicyHolderSurname = (XmlElement)xmlDoc.SelectSingleNode("//cipSurname");
        string QuotePolicyHolderSurnameAsString = QuotePolicyHolderSurname.InnerText.ToString();
        PolicyHolderSurname = QuotePolicyHolderSurnameAsString;
        Report.Info( "Policy Holder Surname As String = " + QuotePolicyHolderSurnameAsString);
        Report.Info( "Quote Policy Holder Surname = " + QuotePolicyHolderSurname.InnerText);

The final file is called SetDataSource and it puts the information into the CSV file, there is a variable declared at the top like this:

string _PolicyHolderSurname = "";
    [TestVariable("222D47D2-6F66-4F05-BDAF-7D3B9D335647")]
    public string PolicyHolderSurname
    {
        get { return _PolicyHolderSurname; }
        set { _PolicyHolderSurname = value; }
    }

This is then the code that adds it into the CSV file:

string Surname = PolicyHolderSurname;
        Report.Info("Surname = " + Surname);
        dataConn.Rows.Add(new string[] { Surname });
        dataConn.Store();

There are multiple items in the Mine and SetDataSource files and the output looks like this in Notepad++:

Picture showing the CSV file after the code has been run

I believe the problem lies in the CreateCSVFile and the writer.WriteLine function. I have commented this region out but it then produces the CSV with just the headers showing.

I've asked some of the developers I work with but most don't know C# very well and no one has been able to solve this issue yet. If it makes a difference this is on Windows Server 2012r2.

Any questions about this please ask, I can provide the whole files if needed, they're just quite long and repetitive.

Thanks Ben Jardine

Answer 1

I had the exact same thing to do in Ranorex. Since the question is a bit old I didn't checked your code but here is what I did and is working. I found an example (probably on stack) creating a csv file in C#, so here is my adaptation for using in Ranorex UserCodeCollection:

[UserCodeCollection]
    public class UserCodeCollectionDemo
    {

        [UserCodeMethod]
        public static void ConvertXmlToCsv()
        {
            System.IO.File.Delete("E:\\Ranorex_test.csv");
            XDocument doc = XDocument.Load("E:\\lang.xml");
            string csvOut = string.Empty;
            StringBuilder sColumnString = new StringBuilder(50000);
            StringBuilder sDataString = new StringBuilder(50000);
            foreach (XElement node in doc.Descendants(GetServerLanguage()))
            {
                foreach (XElement categoryNode in node.Elements())
                {
                    foreach (XElement innerNode in categoryNode.Elements())
                    {
                        //"{0}," give you the output in Comma seperated format.
                        string sNodePath = categoryNode.Name + "_" + innerNode.Name;
                        sColumnString.AppendFormat("{0},", sNodePath);
                        sDataString.AppendFormat("{0},", innerNode.Value);
                    }
                }
            }
            if ((sColumnString.Length > 1) && (sDataString.Length > 1))
            {
                sColumnString.Remove(sColumnString.Length-1, 1);
                sDataString.Remove(sDataString.Length-1, 1);
            }
            string[] lines = { sColumnString.ToString(), sDataString.ToString() };
            System.IO.File.WriteAllLines(@"E:\Ranorex_test.csv", lines);
        }
}

For your information, a simple version of my xml looks like that:

<LANGUAGE>
    <ENGLISH ID="1033">
        <TEXT>
            <IDS_TEXT_CANCEL>Cancel</IDS_TEXT_CANCEL>
            <IDS_TEXT_WARNING>Warning</IDS_TEXT_WARNING>
        </TEXT> 
        <LOGINCLASS>
            <IDS_LOGC_DLGTITLE>Log In</IDS_LOGC_DLGTITLE>
        </LOGINCLASS>
    </ENGLISH>
    <FRENCH ID="1036">
        <TEXT>
            <IDS_TEXT_CANCEL>Annuler</IDS_TEXT_CANCEL>
            <IDS_TEXT_WARNING>Attention</IDS_TEXT_WARNING>
        </TEXT> 
        <LOGINCLASS>
            <IDS_LOGC_DLGTITLE>Connexion</IDS_LOGC_DLGTITLE>
        </LOGINCLASS>
    </FRENCH>
</LANGUAGE>