Convert Comma Delimited Text File to XML

I need to convert a comma delimited text file to an XML that can be stored in a SQL Server table.

This is a sample of the text file:
There are 7 columns.
I am storing each col value in the values[] array.

AA.P,  20131227,  76.55,  78.95,  76.55,  78.95,  400
AADR,20131227,37.6,37.6,37.4,37.4,15800
AAMC,20131227,900,906.86,900,902,2100
AAU,20131227,1.13,1.16,1.1,1.15,137300
ACCU,20131227,28.35,28.35,28.28,28.3,3300
ACIM,20131227,62.11,62.11,62.11,62.11,300

This is the code I am using to count the lines:
     int intRowCounter1 = 0;
        FileStream fs1 = new FileStream(pstrSourceFile, FileMode.Open, FileAccess.Read);
        StreamReader sr1 = new StreamReader(fs1);

        string[] headers1 = sr1.ReadLine().Split(new char[] { ',' });
        string[] values1;
        string line1;

        while ((line1 = sr1.ReadLine()) != null)
        {
            intRowCounter1++;
        }

This is the code I am using to read each line:

FileStream fs = new FileStream(pstrSourceFile, FileMode.Open, FileAccess.Read);
        StreamReader sr = new StreamReader(fs);

        string[] headers = sr.ReadLine().Split(new char[] { ',' });
        string[] values;
        string line;
       
        //sr.ReadLine(); // Skips first line
        // Run "usp_getEODExtract" for each line

        while (intRowCount < intRowCounter1) // Skips 2 rows out of 1820 in AMEX EOD text file
   
        {
            line = sr.ReadLine();
            values = line.Split(new char[] { ',' });
            values[values.Length - 1] = values[values.Length - 1].Trim(new char[] { '"' });

Where do I go from here?

Thank you
DovbermanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MattiConnect With a Mentor Commented:
MSAccess 2013 does this with it's import/export tools and you can connect to SQL from it.
No need to code just a few mouse clicks.

Matti
0
 
DovbermanAuthor Commented:
I will try and then give you feedback in 2 hours.

Thanks
0
 
DovbermanAuthor Commented:
I tried it on MS Access 2010.  It works perfectly.  I can also save the conversion as a job.

Thanks,
0
 
DovbermanAuthor Commented:
Perfect, Thanks
0
All Courses

From novice to tech pro — start learning today.