split a comma-separated xml string

[Neurokahuna]

Hi Everyone,
I really tried to work this out for myself before posting, but I genuinely need some help from those VB.NET specialists in the know.

Basically, I have this xml file. It is a series of quotes by our suppliers to provide parts on a number of projects for that day.

For each company, they may supply a quote on a project or they may not (i.e. it would be a null value on a 'project', or xmlelement in this case). See Company 7 for an example.

The number of companies that tender to supply parts on these projects (xmlelements) will differ each time a tender is sent out.

  <?xml version="1.0" encoding="utf-8" ?> 
- <Quotes>
- <CoQuote>
  <CoID>1,2,3,4,5,6,7,8,9,10,11,12,13,14</CoID> 
  <CoName>Company 1,Company 2,Company 3,Company 4,Company 5,Company 6,Company 7,Company 8,Company 9,Company 10,Company 11,Company 12,Company 13,Company 14</CoName> 
  <QuoteV_2>11,9,23.3,37.7,18.2,19.3,,24.8,11.5,2.9,7.8,22.6,16.3,10.1</QuoteV_2> 
  <QuoteV_1>3.2,2.6,5.9,8.8,4.4,4.8,,6.2,3.7,1.8,2.9,5.6,4,2.7</QuoteV_1> 
  <QuoteN_2>10.9,9.3,22.8,36.7,18.8,18.8,,24.6,11.6,2.9,7.7,23.2,16.7,10</QuoteN_2> 
  <QuoteQ_1>10.9,9.3,22.8,36.7,18.8,18.8,,24.6,11.6,2.9,7.7,23.2,16.7,10</QuoteQ_1> 
  <QuoteAB_3>15,12,17,51,17,21,,17,11,2.9,6.5,19,19,11</QuoteAB_3> 
  <QuoteAT_2>16.04,10.64,22.17,41.54,25.37,18.36,,24.29,11.74,3.88,9.66,22.36,16.81,10.01</QuoteAT_2> 
  </CoQuote>
  </Quotes>

Now I can load it into a datagridview via a dataset easily enough, but it splits each element across as a new column.

I basically need to convert this data to a datagridview in the format shown below.

The structure of the DatagridView is: Company ID, Company Name, Quote 1(project V_2), Quote 2(project V_1), etc.....

Each company is on a new datarow (for each loop)

CoID CoName QuoteV_2 QuoteV_1 QuoteN_2 QuoteQ_1 QuoteAB_1 QuoteAT_2
1 Company 1 11 3.2 10.9 10.9 15 16.04
2 Company 2 9 2.6 9.3 9.3 12 10.64
3 Company 3 23.3 5.9 22.8 22.8 17 22.17
4 Company 4 37.7 8.8 36.7 36.7 51 41.54
5 Company 5 18.2 4.4 18.8 18.8 17 25.37
6 Company 6 19.3 4.8 18.8 18.8 21 18.36
7 Company 7
8 Company 8 24.8 6.2 24.6 24.6 17 24.29
9 Company 9 11.5 3.7 11.6 11.6 11 11.74
10 Company 10 2.9 1.8 2.9 2.9 2.9 3.88
11 Company 11 7.8 2.9 7.7 7.7 6.5 9.66
12 Company 12 22.6 5.6 23.2 23.2 19 22.36
13 Company 13 16.3 4 16.7 16.7 19 16.81
14 Company 14 10.1 2.7 10 10 11 10.01
-I apologise if I have formatted this incorrectly.

What I need is some way of delimited this xml file into this format.

Like I said, I have tried, but just don't know how to structure this in VB.NET or even C#.

If someone could please give me a leg up, I will name my first child after you.

I have trawled the web for weeks trying to find something similar to absolutely no avail. In fact, I do not even know if my Subject heading is correct.

Please find enclosed a spreadsheet that shows the xml file content and output sought in a cleaner format.

Thanks for everything.

Neurokahuna

 

[Tom]

Sort of defeats the purporse of having an xml file that defines each field if your squeeze a comma seperated format into each of the fields...

 

[Neurokahuna]

Hi Tom,

I couldn't agree more, but that is what I get and that is what I have to work with.

TBH, I have not seen anything like this 'type' of structure before in an xml file. Sort of justifies why there are virtually no solutions on the web to handle this issue.

Neurokahuna

 

[Tom]

I dont see much choice except to load it into a temp dataset and loop thru each record to break apart the data while loading it into a proper dataset

 

[Neurokahuna]

Yeah Tom,

I was hoping to avoid that at all costs.

For what it is worth, the xml data is being passed into an excel spreadsheet and works fine using this VBA code:-

'start from Cell A1 in worksheet
vOffset = 0
hOffset = 0
                    
Set xmlDoc2 = xmlDoc.documentElement.childNodes(0)'start at first project element

For i = 0 To 5' array to support all 6 projects
  QuoteValue = xmlDoc2.childNodes(i).Text
  CoName = xmlDoc2.childNodes(i).nodeName
  QuoteValArr = Split(QuoteValue, ",") 'Used to split quote values'
                        
  Worksheets("Quotes").Cells(vOffset - 1, hOffset + i) = CoName 'iterate through each company name and add to next row'
                        
  For j = 0 To UBound(QuoteValArr)
    Worksheets("Quotes").Cells(j + vOffset, hOffset + i) = QuoteValArr(j)
  Next

Next

However, the excel spreadsheet has other non-related problems so the challenge for me is to convert this vba into vb.net or c#.

Even if I have to convert it to another format and then load that into a dataset/datagridview, it would be fine.

Keep the ideas coming though., They are helping anyway.

Cheers,

Neurokahuna

 

[Tom]

Well once you get it into a dataset, you can export to an excel workbook without the need of automating excel

 

[Neurokahuna]

Sorry Tom,

I should have made myself clearer.

We already have it working in Excel but we need to move away from Excel to an application. The VB.net app is jsut an interim solution until we move it to a web-based application...

FTR, that VBA code is what is in the excel file already and works perfectly. We just need to convert that VBA to VB.net.

Any ideas how we can do that, by any chance?

Cheers,

Neurokahuna

 

[Tom]

I downloaded your Excel example to take a closer look. Wow I cant believe how butchered this format is. Sheesh and I thought some of my client formats were bad... Not only is the info concatenated, each field is from a completly different record and all of this is put into a single cell in Excel. Really my absolute best suggestion would be to have this format changed (although I know with clients sometimes thats impossible). Is this coming from an outside source?

I have a few thoughts but not sure which may be optimal with this type of file. Bit busy today & tomorrow but I have time the rest of the week after that to try a couple ideas if you can wait that long.

Couple of questions,

01) Does the data need to be imported & saved to a database?
02) If so what type of database are you planning on using?
03) Can you provide a blank copy of the db to include the tables needed to save the info?
04) Are the field datatypes as follows, CoId Int, CompanyName VarChar, all others decimal?
05) On average how large will these files be, how many records per file? (speed maybe an issue for larger files specially if you are looking ahead to eventually transfering to a web database)
06) Can you provide a larger sample file to work with?
07) I see that you may be using an older version of VB, any chance of updating?

You can PM me for an email address if its info you dont want to post publicly.