Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.
VB parsing thru XML
I am attempting to go thru an XML document and extract values and place them in a text file. The resulting format should be:
Name,North,East,Elevation,
Should the ATT() have no value, I need to replace the blank with a comma (serves as placeholder essentially).
I haven't started writing the part to write it out to the file as I can't this bit of code to work. Right now, my For Next loop is counting by 2's. ARGH! I pulled the framework from MSDN, so I have done some research, but this being my first time parsing thru XML, I am struggling.
This is the code(I am attaching the XML file that it should go thru to this post):
test.xml
Name,North,East,Elevation,
Should the ATT() have no value, I need to replace the blank with a comma (serves as placeholder essentially).
I haven't started writing the part to write it out to the file as I can't this bit of code to work. Right now, my For Next loop is counting by 2's. ARGH! I pulled the framework from MSDN, so I have done some research, but this being my first time parsing thru XML, I am struggling.
This is the code(I am attaching the XML file that it should go thru to this post):
Private Sub ReadXML(FileLocation As String, FileName As String)
Dim output As StringBuilder = New StringBuilder()
Dim fileContents As String
fileContents = My.Computer.FileSystem.ReadAllText(foFile.Text)
' Create an XmlReader
Using reader As XmlReader = XmlReader.Create(New StringReader(fileContents))
Dim attName() As String = {"Name", "North", "East", "Elevation", "Code", _
"Bridge_No", "C_Stab_ft", "Desc", "DiaHt_in", "Dia_in", "DiaWid_in", "Dim_ft", _
"E_Stab_ft", "GrateDimFt", "HDWL_Ht_ft", "Height_ft", "LegLen_ft", "LegWid_ft", _
"Length_ft", "MilePostNo", "N_Stab_ft", "NoBoxes", "Number", "OpenHt_ft", "OpenWid_ft", _
"PaintPanel", "S_Stab_ft", "SigDirectn", "Spread_ft", "Stamping", "Stand_Mast", _
"Type", "TypeDesc", "W_Stab_ft", "Photo"}
'Dim attValue(30) As String
'Check to see if CSV has been created with same name and delete if YES
If My.Computer.FileSystem.FileExists(FileLocation & FileName & ".csv") Then _
My.Computer.FileSystem.DeleteFile(FileLocation & FileName & ".csv")
'Create blank CSV file with same name
Using sw As IO.StreamWriter = New IO.StreamWriter(FileLocation & FileName & ".csv")
'Read the XML
While Not (reader.EOF)
For cCount As Integer = 0 To attName.GetUpperBound(0) Step 1
MsgBox(cCount)
reader.ReadToFollowing(attName(cCount))
MsgBox(attName(cCount) & " = " & reader.ReadElementContentAsString)
If reader.ReadElementContentAsString = "" Then
output.AppendLine(",")
Else
output.AppendLine(reader.ReadElementContentAsString())
End If
cCount = cCount + 1
Next
sw.WriteLine(output.ToString)
End While
End Using
End Using
End Sub
test.xml
Who is Participating?
Specific to you for loop, if your intent is not to loop by two, remove the count = count + 1. The for loop automatically advances you loop variable each time through.
Hi prostang;
1. Are these the columns name of of all the column in each row?
2. In the XML file I only see the first five names from the above list. Does that mean that all the other values in the roe will just get a place holder?
1. Are these the columns name of of all the column in each row?
Dim attName() As String = {"Name", "North", "East", "Elevation", "Code", _
"Bridge_No", "C_Stab_ft", "Desc", "DiaHt_in", "Dia_in", "DiaWid_in", "Dim_ft", _
"E_Stab_ft", "GrateDimFt", "HDWL_Ht_ft", "Height_ft", "LegLen_ft", "LegWid_ft", _
"Length_ft", "MilePostNo", "N_Stab_ft", "NoBoxes", "Number", "OpenHt_ft", "OpenWid_ft", _
"PaintPanel", "S_Stab_ft", "SigDirectn", "Spread_ft", "Stamping", "Stand_Mast", _
"Type", "TypeDesc", "W_Stab_ft", "Photo"}
2. In the XML file I only see the first five names from the above list. Does that mean that all the other values in the roe will just get a place holder?
foFile. Text is a text field that the populated whdn the user browses for a file.
1. This array contains all the names that i need to pull values for if they exist.
2. Yes. Every point will have either a placeholder or a value.
1. This array contains all the names that i need to pull values for if they exist.
2. Yes. Every point will have either a placeholder or a value.
Hi prostang;
Assuming my last post was correct then the below code will create the csv file.
Assuming my last post was correct then the below code will create the csv file.
'' Using a string for the column names
Dim attName As String = "Name, North, East, Elevation, Code, " & _
"Bridge_No, C_Stab_ft, Desc, DiaHt_in, Dia_in, DiaWid_in, Dim_ft, " & _
"_Stab_ft, GrateDimFt, HDWL_Ht_ft, Height_ft, LegLen_ft, LegWid_ft, " & _
"Length_ft, MilePostNo, N_Stab_ft, NoBoxes, Number, OpenHt_ft, OpenWid_ft, " & _
"PaintPanel, S_Stab_ft, SigDirectn, Spread_ft, Stamping, Stand_Mast, " & _
"Type, TypeDesc, W_Stab_ft, Photo"
'' Load the XML document from the file system
Dim xdoc As XDocument = XDocument.Load("C:\Working Directory\test.xml")
'' Query the XML document using Linq to XML
Dim rows As List(Of String) = (From nodes In xdoc.Root.Descendants("Point")
where nodes.Element("Grid") IsNot Nothing
select nodes.Element("Name").Value & ", " &
nodes.Element("Grid").Element("North").Value & ", " &
nodes.Element("Grid").Element("East").Value & ", " &
nodes.Element("Grid").Element("Elevation").Value & ", " &
nodes.Element("Code").Value &
", , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,"
).ToList()
'' Add the header row to the data
rows.Insert(0, attName)
'' Write the csv file out to the file system. Change the file path and name
File.WriteAllLines("C:\Working Directory\test.csv", rows)
Fernando,
The attName array is a listing of names the program needs to search the XML for values. They are columns, but I don't need the column names.
I should have included an xml that has some of the attributes I am searching for. Some points will have a Dia_In, some will have Type or Photo. Many will have nothing. Here is an example point:
I get stuck when the attribute does not exist and VB kicks out an error.
Thanks.
Mike
The attName array is a listing of names the program needs to search the XML for values. They are columns, but I don't need the column names.
I should have included an xml that has some of the attributes I am searching for. Some points will have a Dia_In, some will have Type or Photo. Many will have nothing. Here is an example point:
<Point>
<ID>0000001E</ID>
<Name>508</Name>
<Code>FSM</Code>
<Grid>
<North>577112.17021714943</North>
<East>458476.36877272319</East>
<Elevation>2257.3845080350752</Elevation>
</Grid>
<SurveyMethod>KeyedIn</SurveyMethod>
<Classification>Normal</Classification>
<Features>
<Feature Name="FSM">
<Attribute>
<Name>Dia_in</Name>
<Value>4</Value>
<Type>Numeric</Type>
</Attribute>
<Attribute>
<Name>Stamping</Name>
<Value>STA 880</Value>
<Type>Text</Type>
</Attribute>
<Attribute>
<Name>Photo</Name>
<Value>img004.jpg</Value>
<Type>Photo</Type>
</Attribute>
</Feature>
</Features>
</Point>
I get stuck when the attribute does not exist and VB kicks out an error.
Thanks.
Mike
Hi Mike;
OK, you gave us a test XML file can you also post a sample of what the output should look like when you have parsed through the XML? Also is the order of the array attName the same order of the fields to be placed in the csv file/
OK, you gave us a test XML file can you also post a sample of what the output should look like when you have parsed through the XML? Also is the order of the array attName the same order of the fields to be placed in the csv file/
This isn't from the example. I apologize that the data isn't matching, but this should give you an idea on how it should work out. The commas are always placed in the file whether or not there was data found. The point # is first so if you look at point 508, you can see there was data found in a couple of attributes, but not all of them.
347,1878540.5851293748,152
349,1878715.8557355061,152
507,1893677.9504663907,150
508,1893408.9047631526,150
509,1892347.4896092878,150
510,1891951.0057258238,150
511,1891290.7785108836,150
512,1891229.7332092323,150
513,1890944.2477674128,150
514,1891801.6093613887,150
347,1878540.5851293748,152
349,1878715.8557355061,152
507,1893677.9504663907,150
508,1893408.9047631526,150
509,1892347.4896092878,150
510,1891951.0057258238,150
511,1891290.7785108836,150
512,1891229.7332092323,150
513,1890944.2477674128,150
514,1891801.6093613887,150
I have this much worked out. This is just to get the code to work if there is attribute data (testing the code basically)
This is the result of the code above:
'' Using a string for the column names
Dim attName As String = "Name, North, East, Elevation, Code, " & _
"Bridge_No, C_Stab_ft, Desc, DiaHt_in, Dia_in, DiaWid_in, Dim_ft, " & _
"_Stab_ft, GrateDimFt, HDWL_Ht_ft, Height_ft, LegLen_ft, LegWid_ft, " & _
"Length_ft, MilePostNo, N_Stab_ft, NoBoxes, Number, OpenHt_ft, OpenWid_ft, " & _
"PaintPanel, S_Stab_ft, SigDirectn, Spread_ft, Stamping, Stand_Mast, " & _
"Type, TypeDesc, W_Stab_ft, Photo"
'' Load the XML document from the file system
Dim xdoc As XDocument = XDocument.Load(foFile.Text)
'' Query the XML document using Linq to XML
Dim rows As List(Of String) = (From nodes In xdoc.Root.Descendants("Point")
Where nodes.Element("Features") IsNot Nothing
Select nodes.Element("Name").Value & "," &
nodes.Element("Grid").Element("North").Value & "," &
nodes.Element("Grid").Element("East").Value & "," &
nodes.Element("Grid").Element("Elevation").Value & "," &
nodes.Element("Code").Value & "," &
nodes.Element("Features").Element("Feature").Element("Attribute").Elements("Value").Value & "," & _
",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
).ToList()
'' Write the csv file out to the file system. Change the file path and name
'File.WriteAllLines(foFile.Text & ".csv", rows)
File.WriteAllLines(foFile.Text & ".csv", rows)
This is the result of the code above:
508,577112.17021714943,458476.36877272319,2257.3845080350752,FSM,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
509,576788.65024140233,458854.801143719,2238.7059517799134,BRC,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
510,576667.8017158322,458962.6973745976,2232.4848325096614,BRC,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
511,576466.56406458211,459047.53021876386,2231.9669755694367,NAIL,1/2IN SPIKE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
512,576447.95742001163,459058.61664242315,2234.0181668427122,FSM,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
513,576360.94128605374,459061.57563107758,2243.4639892471896,PLCAP,CPORTHSTAR,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
514,576622.26561431459,459035.46084343636,2224.2812511606016,IP,0.5,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
515,576670.0206089511,459009.48092426884,2228.6533638582659,PLCAP,NOT/LEGIBLE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
516,576819.18573936622,458880.04783576203,2234.9759549511064,BRC,3,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
517,577273.78482033371,458330.26409554027,2259.83336841588,NAIL,1/4IN SPIKE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Hi Mike;
The following code snippet should do what you need. I removed the attName variable because I did not use it.
The following code snippet should do what you need. I removed the attName variable because I did not use it.
Dim xdoc As XDocument = XDocument.Load("C:\Working Directory\testPoints.xml")
Dim rows = (From nodes In xdoc.Root.Descendants("Point") _
Where nodes.Element("Grid") IsNot Nothing
Select If(nodes.Descendants("Name").Count() > 0, nodes.Descendants("Name").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("North").Count() > 0, nodes.Descendants("North").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("East").Count() > 0, nodes.Descendants("East").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Elevation").Count() > 0, nodes.Descendants("Elevation").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Code").Count() > 0, nodes.Descendants("Code").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Bridge_No").Count() > 0, nodes.Descendants("Bridge_No").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("C_Stab_ft").Count() > 0, nodes.Descendants("C_Stab_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Desc").Count() > 0, nodes.Descendants("Desc").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("DiaHt_in").Count() > 0, nodes.Descendants("DiaHt_in").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Dia_in").Count() > 0, nodes.Descendants("Dia_in").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("DiaWid_in").Count() > 0, nodes.Descendants("DiaWid_in").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Dim_ft").Count() > 0, nodes.Descendants("Dim_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("_Stab_ft").Count() > 0, nodes.Descendants("_Stab_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("GrateDimFt").Count() > 0, nodes.Descendants("GrateDimFt").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("HDWL_Ht_ft").Count() > 0, nodes.Descendants("HDWL_Ht_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Height_ft").Count() > 0, nodes.Descendants("Height_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("LegLen_ft").Count() > 0, nodes.Descendants("LegLen_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("LegWid_ft").Count() > 0, nodes.Descendants("LegWid_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Length_ft").Count() > 0, nodes.Descendants("Length_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("MilePostNo").Count() > 0, nodes.Descendants("MilePostNo").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("N_Stab_ft").Count() > 0, nodes.Descendants("N_Stab_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("NoBoxes").Count() > 0, nodes.Descendants("NoBoxes").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Number").Count() > 0, nodes.Descendants("Number").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("OpenHt_ft").Count() > 0, nodes.Descendants("OpenHt_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("OpenWid_ft").Count() > 0, nodes.Descendants("OpenWid_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("PaintPanel").Count() > 0, nodes.Descendants("PaintPanel").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("S_Stab_ft").Count() > 0, nodes.Descendants("S_Stab_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("SigDirectn").Count() > 0, nodes.Descendants("SigDirectn").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Spread_ft").Count() > 0, nodes.Descendants("Spread_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Stamping").Count() > 0, nodes.Descendants("Stamping").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Stand_Mast").Count() > 0, nodes.Descendants("Stand_Mast").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Type").Count() > 0, nodes.Descendants("Type").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("TypeDesc").Count() > 0, nodes.Descendants("TypeDesc").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("W_Stab_ft").Count() > 0, nodes.Descendants("W_Stab_ft").First().Value.ToString(), " ").ToString() & ", " & _
If(nodes.Descendants("Photo").Count() > 0, nodes.Descendants("Photo").First().Value.ToString(), " ").ToString() _
).ToList()
File.WriteAllLines("C:\Working Directory\test.csv", rows)
Wow. That is a lot of work you put into this. Thank you so much. I have a question about the bit of code you use:
nodes.Descendants("Name").
When I run the script, I am getting the <Type> of Feature and not the <Value>. That being said, I would like to know if it is the "First()" element that is giving this output.
This:
508, 577112.17021714943, 458476.36877272319, 2257.3845080350752, FSM, , , , , , , , , , , , , , , , , , , , , , , , , , , Numeric, , ,
Should be this:
508, 577112.17021714943, 458476.36877272319, 2257.3845080350752,FSM, , , , ,4, , , , , , , , , , , , , , , , , , , ,STA 880, , , , img004.jpg,
nodes.Descendants("Name").
When I run the script, I am getting the <Type> of Feature and not the <Value>. That being said, I would like to know if it is the "First()" element that is giving this output.
This:
508, 577112.17021714943, 458476.36877272319, 2257.3845080350752, FSM, , , , , , , , , , , , , , , , , , , , , , , , , , , Numeric, , ,
Should be this:
508, 577112.17021714943, 458476.36877272319, 2257.3845080350752,FSM, , , , ,4, , , , , , , , , , , , , , , , , , , ,STA 880, , , , img004.jpg,
Please post the part of the XML that has the output you say is not correct from the <Point>....</Point>.
<Point>
<ID>0000001E</ID>
<Name>508</Name>
<Code>FSM</Code>
<Grid>
<North>577112.17021714943<
<East>458476.36877272319</
<Elevation>2257.3845080350
</Grid>
<SurveyMethod>KeyedIn</Sur
<Classification>Normal</Cl
<Features>
<Feature Name="FSM">
<Attribute>
<Name>Dia_in</Name>
<Value>4</Value>
<Type>Numeric</Type>
</Attribute>
<Attribute>
<Name>Stamping</Name>
<Value>STA 880</Value>
<Type>Text</Type>
</Attribute>
<Attribute>
<Name>Photo</Name>
<Value>img004.jpg</Value>
<Type>Photo</Type>
</Attribute>
</Feature>
</Features>
</Point>
<Point>
<ID>0000001E</ID>
<Name>508</Name>
<Code>FSM</Code>
<Grid>
<North>577112.17021714943<
<East>458476.36877272319</
<Elevation>2257.3845080350
</Grid>
<SurveyMethod>KeyedIn</Sur
<Classification>Normal</Cl
<Features>
<Feature Name="FSM">
<Attribute>
<Name>Dia_in</Name>
<Value>4</Value>
<Type>Numeric</Type>
</Attribute>
<Attribute>
<Name>Stamping</Name>
<Value>STA 880</Value>
<Type>Text</Type>
</Attribute>
<Attribute>
<Name>Photo</Name>
<Value>img004.jpg</Value>
<Type>Photo</Type>
</Attribute>
</Feature>
</Features>
</Point>
<Point>
Thank you Fernando. I am going to take your code and modify it so it will parse thru all the points in a file and "fetch" the values for every attribute available. Each point may have different attributes, therefore different values for different places in the columns. Here is an example of different points:
I am going to accept this as an answer, but I hope you will be available for any questions should I falter.
Thanks a million!
<Point>
<ID>00000021</ID>
<Name>511</Name>
<Code>NAIL</Code>
<Grid>
<North>576466.56406458211</North>
<East>459047.53021876386</East>
<Elevation>2231.9669755694367</Elevation>
</Grid>
<SurveyMethod>KeyedIn</SurveyMethod>
<Classification>Normal</Classification>
<Features>
<Feature Name="NAIL">
<Attribute>
<Name>TypeDesc</Name>
<Value>1/2IN SPIKE</Value>
<Type>Text</Type>
</Attribute>
</Feature>
</Features>
</Point>
<Point>
<ID>0000003E</ID>
<Name>540</Name>
<Code>TR</Code>
<Grid>
<North>574691.49179702345</North>
<East>461405.06147049746</East>
<Elevation>2194.4894200096419</Elevation>
</Grid>
<SurveyMethod>KeyedIn</SurveyMethod>
<Classification>Normal</Classification>
<Features>
<Feature Name="TR">
<Attribute>
<Name>Desc</Name>
<Value>ANGLE IRON STAMPED 50</Value>
<Type>Text</Type>
</Attribute>
<Attribute>
<Name>Photo</Name>
<Value>img037.jpg</Value>
<Type>Photo</Type>
</Attribute>
</Feature>
</Features>
</Point>
I am going to accept this as an answer, but I hope you will be available for any questions should I falter.
Thanks a million!
Not a problem Mike, glad to help.
When posting a new question about this solution post a message here as well so that an email gets sent to me this way I don't miss the follow up question.
When posting a new question about this solution post a message here as well so that an email gets sent to me this way I don't miss the follow up question.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
I corrected the issues you posted about last except the column marked as Type seeming there are multiple of them. The issues with the other were that the column names were not node names but node values and as you can see below needed a little more work to get to the values.
Open in new window