XML Mapping to CSV

RickSharp

Hello, I have a fixed XML table thats coming from a Web Service.

JOHNSON, DANIEL R.
BMRES-550
Bank of America
123456
49,7560.8600

I need to convert the XML data to CSV. The data to be converted has commas. So the below method doesn't work:

using (StreamWriter writer = File.CreateText("C:\\username\\password\\Desktop\\Export.csv"))
{
DataSet ds = new DataSet();
ds.ReadXml(XmlReader.Create(new StringReader(myWebreference.GetWebServiceMethod("username", "password1", "Export", "Parameter4", DateTime.Today, DateTime.Today, ""))));

        writer.WriteLine(string.Join(",", ds.Tables\["Loan"\].Columns.Cast().Select(col => col.ColumnName).ToArray()));
        foreach (DataRow row in ds.Tables\["Loan"\].Rows)
        {
            writer.WriteLine(string.Join(",", row.ItemArray.Cast().ToArray()));

        }
    }

David C Hobbyist

Why not convert comma's to something that wont be in your data like ~ ?

Frazzle the name say's it all
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. John F. Woods

RickSharp

Actually this might be more what I'm looking for:

public class WireEntry
{
public String Name { get; set; }
public String Number { get; set; }
public String BankName { get; set; }
public String ClosingNo { get; set; }

public WireEntry()
{
}

}

foreach (DataRow row in ds.Tables["Loan"].Rows)
{

        // Enter into result set

        WireEntry newEntry = new WireEntry();
        newEntry.Name = row\["Name"\].ToString();
        newEntry.Number = row\["Number"\].ToString();
        newEntry.BankName = row\["BankName"\].ToString();

        result.Add(newEntry);

    }

    return result;

David C Hobbyist

Maybe I am wrong but you still need to strip out the comma's from the incoming data. Or possibly I didn't understand your task. I was thinking string.Replace(",","comma");

Frazzle the name say's it all
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. John F. Woods

PIEBALDconsult

What do you do with the CSV when you're done? Why use CSV? 0) Use a different delimiter (Good). 1) Wrap the values in quotes (better). 2) I'd prefer to save the XML and then use XSLT to form the CSV.

RickSharp

Actually thats a good question. Because I'm not really wanting the end file to be a CSV file. The end file should be in FedWire Format. http://www.frbservices.org/campaigns/remittance/files/fedwire_funds_format_reference_guide.pdf[^] I just figured it would be easier to get to my end result with a CSV file to work with. Basically I'm taking that Web Service. Iterating through the tables. Then ultimately spitting out a file in that format. {1015} blah blah blah {1870} blah blah blah

PIEBALDconsult

Cut out the middlemen (including the DataSet). Look into XSLT to transform the XML to FedWire directly. I can probably help, but I won't read that document until I get home.

RickSharp

Hmmm Ok. Thanks for the brainstorming. Guess i dont need a dataset at all if XLST can give me both displaying the data in a meaningful manner front end while also being able to convert the data and spitting it out to a file, which it sounds like it can. I look into it until you get back to me. :)

jschell

Normal form for "CSV" is to put double quotes around values that have commas. With double quotes the value is also surrounded by double quotes and each double quote in it is then escaped with two double quotes.

jschell

RickSharp wrote:

The end file should be in FedWire Format.

The posted link is not a Protocol structure document. It looks like a mapping document.