XML to Pipe Delimited or .tab in VB?

XML to Pipe Delimited OR .tab in VB

Does anyone know how I can import a xml file & export into a Pipe Delimited
or .tab file in VB Having a hard time finding any documentation on this...
any help is greatly appreciated......
thanks...

mniccole
jacksonville beach, fl

Answer this question

XML to Pipe Delimited or .tab in VB?

jgoldb

Hi,

I guess will do the job

Dim dbsXML As New DataSet

Dim Columns As New Collection

Dim Values As New Collection

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim i As Integer

dbsXML.ReadXml(Application.StartupPath & "\" & "XMLFile1.xml")

GetColumns(dbsXML.Tables(0).TableName)

TextBox1.Text = ""

For i = 1 To Columns.Count

TextBox1.Text &= Columns.Item(i)

If i <> Columns.Count Then TextBox1.Text &= ","

TextBox1.Text &= vbCrLf.ToString

Dim i1 As Integer

For i = 0 To dbsXML.Tables(0).Rows.Count - 1

Values = New Collection

GetValues(dbsXML.Tables(0).TableName, i)

For i1 = 1 To Values.Count

TextBox1.Text &= Values.Item(i1)

If i <> Values.Count Then TextBox1.Text &= ","

TextBox1.Text &= vbCrLf.ToString

End Sub

Private Sub GetColumns(ByVal TableName As String)

Dim i As Integer

For i = 0 To dbsXML.Tables(TableName).Columns.Count - 1

Columns.Add(dbsXML.Tables(TableName).TableName & "." & dbsXML.Tables(TableName).Columns(i).ColumnName)

For i = 0 To dbsXML.Tables(TableName).ChildRelations.Count - 1

GetColumns(dbsXML.Tables(TableName).ChildRelations(i).ChildTable.TableName)

End Sub

Private Sub GetValues(ByVal TableName As String, ByVal RecordNumber As Integer)

Dim i As Integer

Dim i1 As Integer

For i = 0 To dbsXML.Tables(TableName).Columns.Count - 1

Values.Add(dbsXML.Tables(TableName).Rows(RecordNumber).Item(i))

For i = 0 To dbsXML.Tables(TableName).ChildRelations.Count - 1

For i1 = 0 To dbsXML.Tables(TableName).ChildRelations(i).ChildTable.Rows.Count - 1

GetValues(dbsXML.Tables(TableName).ChildRelations(i).ChildTable.TableName, i1)

End Sub

It will copy the data into a TextBox1 (don't forget to set MultiLines to True).

after this you can write it to Text File :)

Enjoy

Abdullah Al-Rasheed

Idontcare2623
Can you verify what the correct reference to system.data is
thanks so much for your help, I've totally confused myself...
haven't written any vb in quite a while...
Bandobras
You could use XSLT to do this. If you could provide sample data then I can provide a sample xslt file that would do this.
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr
Thanks..I've totally confused myself today
I need to loop through this until .eof & spit it out into a .tab or pipe file

< xml version="1.0" encoding="utf-8" standalone="yes" >
- 
- <Eligibles>
- <Eligible>
<Participant_Id>001</Participant_Id>
<First_Name>Ed</First_Name>
<Last_Name>Mccon</Last_Name>
<Birthdate>11/21/1900</Birthdate>
<Gender>FEMA</Gender>
<Company_Name>HR Solutions</Company_Name>
<Partner_Company_Code>1101</Partner_Company_Code>
- <Home_Information>
<Address1>474 North Test Rd..</Address1>
<Address2 />
<City>Barn</City>
<State>NH</State>
<Zip_Code>03225</Zip_Code>
<Phone>(603) 888-8888</Phone>
<Email />
</Home_Information>
- <Case>
<Partner_Id>CES</Partner_Id>
<Vendor_ID>879</Vendor_ID>
<Case_Open_Date>11/10/2004</Case_Open_Date>
<Case_Close_Date>1/19/2005</Case_Close_Date>
- <Work_Case_Information>
<Address1 />
<Address2 />
<City />
<State />
<Zip_Code />
- <Phone>
<Number>(800) 888-8000</Number>
<Extension />
</Phone>
<Email>anywhere@metrocast.net</Email>
</Work_Case_Information>
- <Intake>
<Phone_Assess_State_Health_Code>FAIR</Phone_Assess_State_Health_Code>
<Phone_Assess_High_Chol_Code>UNKN</Phone_Assess_High_Chol_Code>
<Phone_Assess_Exercise_Code />
<Phone_Assess_Exercise_Code>>3WK</Phone_Assess_Exercise_Code>
<Phone_Assess_Weight_Code>NO</Phone_Assess_Weight_Code>
<Phone_Assess_Stairs_Code>UNKN</Phone_Assess_Stairs_Code>
<Phone_Assess_SeatBelt_Code>UNKN</Phone_Assess_SeatBelt_Code>
<Phone_Assess_SelfExam_Code>UNKN</Phone_Assess_SelfExam_Code>
</Intake>
- <CompletedActions>
- <Action>
<IdType>CON</IdType>
<Type_Label>Conversation</Type_Label>
<Date_Completed>11/9/2004</Date_Completed>
</Action>
- <Action>
<IdType>ED</IdType>
<Type_Label>Send Books/TipSheets</Type_Label>
<Date_Completed>11/10/2004</Date_Completed>
</Action>
- <Action>
<IdType>CON</IdType>
<Type_Label>Conversation</Type_Label>
<Date_Completed>11/16/2004</Date_Completed>
</Action>
- <Action>
<IdType>LM</IdType>
<Type_Label>Left Message</Type_Label>
<Date_Completed>12/9/2004</Date_Completed>
</Action>
- <Action>
<IdType>LM</IdType>
<Type_Label>Left Message</Type_Label>
<Date_Completed>12/22/2004</Date_Completed>
</Action>
- <Action>
<IdType>EmailC</IdType>
<Type_Label>Emailed Client</Type_Label>
<Date_Completed>1/5/2005</Date_Completed>
</Action>
</CompletedActions>
- <Materials>
<Books />
- <TipSheets>
- <TipSheet>
<TipSheet_ID>284</TipSheet_ID>
<TipSheet_Name>Weight Management</TipSheet_Name>
</TipSheet>
</TipSheets>
<Referrals />
</Materials>
- <StatusUpdates>
- <Goal>
<Goal_Id>2</Goal_Id>
<Goal_Name>Exercise</Goal_Name>
- <StatusUpdate>
<StatusUpdate_Code>18</StatusUpdate_Code>
<StatusUpdate_Name>Determined readiness to start plan</StatusUpdate_Name>
<StatusUpdate_Date>11/10/2004</StatusUpdate_Date>
- <Stage_Readiness>
<Stage_Readiness_Code>3</Stage_Readiness_Code>
<Stage_Readiness_Name>Preparation</Stage_Readiness_Name>
</Stage_Readiness>
</StatusUpdate>
- <StatusUpdate>
<StatusUpdate_Code>2</StatusUpdate_Code>
<StatusUpdate_Name>more frequently</StatusUpdate_Name>
<StatusUpdate_Date>11/16/2004</StatusUpdate_Date>
- <Stage_Readiness>
<Stage_Readiness_Code>4</Stage_Readiness_Code>
<Stage_Readiness_Name>Action</Stage_Readiness_Name>
</Stage_Readiness>
</StatusUpdate>
</Goal>
- <Goal>
<Goal_Id>15</Goal_Id>
<Goal_Name>Change Diet</Goal_Name>
- <StatusUpdate>
<StatusUpdate_Code>18</StatusUpdate_Code>
<StatusUpdate_Name>Determined readiness to start plan</StatusUpdate_Name>
<StatusUpdate_Date>11/10/2004</StatusUpdate_Date>
- <Stage_Readiness>
<Stage_Readiness_Code>3</Stage_Readiness_Code>
<Stage_Readiness_Name>Preparation</Stage_Readiness_Name>
</Stage_Readiness>
</StatusUpdate>
- <StatusUpdate>
<StatusUpdate_Code>4</StatusUpdate_Code>
<StatusUpdate_Name>changes</StatusUpdate_Name>
<StatusUpdate_Date>11/16/2004</StatusUpdate_Date>
- <Stage_Readiness>
<Stage_Readiness_Code>4</Readiness_Code>
<Stage_Readiness_Name>Action</Stage_Readiness_Name>
</Stage_Readiness>
</StatusUpdate>
</Goal>
</StatusUpdates>
<Screenings />
</Case>
</Eligible>

regisphilbin

Hi mniccole,

  I have thee notes about your XML File

- You cannot use ColumnName Phone in a table with a nested table Phone is a clomn type of other nested table.

- in line 130 you started the tag <Readiness_Code> and Ended it </Stage_Readinss_Code>.

- You didn't end the tag <Eligibles>

so I made The following changes:

   - I changes <Phone> (the column not the nested table) to <PhoneNo>
   - I changes <Readiness_Code> to <Stage_Readiness_Code>
   - I added the ending tag for <Eligibles> to the end of you XML document

and here is the XML file after formating it.

< xml version="1.0" encoding="utf-8" standalone="yes" >

<Eligibles>
<Eligible>
  <Participant_Id>001</Participant_Id>
  <First_Name>Ed</First_Name>
  <Last_Name>Mccon</Last_Name>
  <Birthdate>11/21/1900</Birthdate>
  <Gender>FEMA</Gender>
  <Company_Name>HR Solutions</Company_Name>
  <Partner_Company_Code>1101</Partner_Company_Code>
  <Home_Information>
   <Address1>474 North Test Rd..</Address1>
   <Address2 />
   <City>Barn</City>
   <State>NH</State>
   <Zip_Code>03225</Zip_Code>
   <PhoneNo>(603) 888-8888</PhoneNo>
   <Email />
  </Home_Information>
  <Case>
   <Partner_Id>CES</Partner_Id>
   <Vendor_ID>879</Vendor_ID>
   <Case_Open_Date>11/10/2004</Case_Open_Date>
   <Case_Close_Date>1/19/2005</Case_Close_Date>
   <Work_Case_Information>
    <Address1 />
    <Address2 />
    <City />
    <State />
    <Zip_Code />
    <Phone>
     <Number>(800) 888-8000</Number>
     <Extension />
    </Phone>
    <Email>anywhere@metrocast.net</Email>
   </Work_Case_Information>
   <Intake>
    <Phone_Assess_State_Health_Code>FAIR</Phone_Assess_State_Health_Code>
    <Phone_Assess_High_Chol_Code>UNKN</Phone_Assess_High_Chol_Code>
    <Phone_Assess_Exercise_Code />
    <Phone_Assess_Exercise_Code>>3WK</Phone_Assess_Exercise_Code>
    <Phone_Assess_Weight_Code>NO</Phone_Assess_Weight_Code>
    <Phone_Assess_Stairs_Code>UNKN</Phone_Assess_Stairs_Code>
    <Phone_Assess_SeatBelt_Code>UNKN</Phone_Assess_SeatBelt_Code>
    <Phone_Assess_SelfExam_Code>UNKN</Phone_Assess_SelfExam_Code>
   </Intake>
   <CompletedActions>
    <Action>
     <IdType>CON</IdType>
     <Type_Label>Conversation</Type_Label>
     <Date_Completed>11/9/2004</Date_Completed>
    </Action>
    <Action>
     <IdType>ED</IdType>
     <Type_Label>Send Books/TipSheets</Type_Label>
     <Date_Completed>11/10/2004</Date_Completed>
    </Action>
    <Action>
     <IdType>CON</IdType>
     <Type_Label>Conversation</Type_Label>
     <Date_Completed>11/16/2004</Date_Completed>
    </Action>
    <Action>
     <IdType>LM</IdType>
     <Type_Label>Left Message</Type_Label>
     <Date_Completed>12/9/2004</Date_Completed>
    </Action>
    <Action>
     <IdType>LM</IdType>
     <Type_Label>Left Message</Type_Label>
     <Date_Completed>12/22/2004</Date_Completed>
    </Action>
    <Action>
     <IdType>EmailC</IdType>
     <Type_Label>Emailed Client</Type_Label>
     <Date_Completed>1/5/2005</Date_Completed>
    </Action>
   </CompletedActions>
   <Materials>
    <Books />
    <TipSheets>
     <TipSheet>
      <TipSheet_ID>284</TipSheet_ID>
      <TipSheet_Name>Weight Management</TipSheet_Name>
     </TipSheet>
    </TipSheets>
    <Referrals />
   </Materials>
   <StatusUpdates>
    <Goal>
     <Goal_Id>2</Goal_Id>
     <Goal_Name>Exercise</Goal_Name>
     <StatusUpdate>
      <StatusUpdate_Code>18</StatusUpdate_Code>
      <StatusUpdate_Name>Determined readiness to start plan</StatusUpdate_Name>
      <StatusUpdate_Date>11/10/2004</StatusUpdate_Date>
      <Stage_Readiness>
       <Stage_Readiness_Code>3</Stage_Readiness_Code>
       <Stage_Readiness_Name>Preparation</Stage_Readiness_Name>
      </Stage_Readiness>
     </StatusUpdate>
     <StatusUpdate>
      <StatusUpdate_Code>2</StatusUpdate_Code>
      <StatusUpdate_Name>more frequently</StatusUpdate_Name>
      <StatusUpdate_Date>11/16/2004</StatusUpdate_Date>
      <Stage_Readiness>
       <Stage_Readiness_Code>4</Stage_Readiness_Code>
       <Stage_Readiness_Name>Action</Stage_Readiness_Name>
      </Stage_Readiness>
     </StatusUpdate>
    </Goal>
    <Goal>
     <Goal_Id>15</Goal_Id>
     <Goal_Name>Change Diet</Goal_Name>
     <StatusUpdate>
      <StatusUpdate_Code>18</StatusUpdate_Code>
      <StatusUpdate_Name>Determined readiness to start plan</StatusUpdate_Name>
      <StatusUpdate_Date>11/10/2004</StatusUpdate_Date>
      <Stage_Readiness>
       <Stage_Readiness_Code>3</Stage_Readiness_Code>
       <Stage_Readiness_Name>Preparation</Stage_Readiness_Name>
      </Stage_Readiness>
     </StatusUpdate>
     <StatusUpdate>
      <StatusUpdate_Code>4</StatusUpdate_Code>
      <StatusUpdate_Name>changes</StatusUpdate_Name>
      <StatusUpdate_Date>11/16/2004</StatusUpdate_Date>
      <Stage_Readiness>
       <Stage_Readiness_Code>4</Stage_Readiness_Code>
       <Stage_Readiness_Name>Action</Stage_Readiness_Name>
      </Stage_Readiness>
     </StatusUpdate>
    </Goal>
   </StatusUpdates>
   <Screenings />
  </Case>
</Eligible>
</Eligibles>

Now lets take a look at your table structure:
Eligibles Tbale Contains Eligible records with the following structure:

Eligible Table Structure

Column Name	Column Data Type
Participant_Id	Value
First_Name	Value
Last_Name	Value
Birthdate	Value
Gender	Value
Company_Name	Value
Partner_Company_Code	Value
Home_Information	Nested Table
Case	Nested Table

So representing any Eligible record in CSV (Comma Separated Values) file is easy exept for Home_Information and Case because they are Nested Tables not Values

So what we are going to do is read Eligible fields and check if they are values or nested tables if they were values the value will be writen to the CSV file, and if it was nested table it will read this table fields and write them to the CSV file if they were values but if they were nested tables it will read the that nested table fields and so on. Sounds like we have a recursion method it here (a recursion method is a method that calls itself).

and to make it so clear we will write a header line if the CSV file to show column name. for value types it will show the column name and for nested tables it will show it like this (Home_Information.Address1) and for more comlex nesting we can show it like this (Case.Work_Case_Information.Address1) and so on.

if this what you are looking for tell me so i can help you with the code to read your XML and write CSV.

Abdullah Al-Rasheed

Rohit Wadhwa
This file is coming directly from a client, I have no control over this file...which sucks..Do you have any examples of how I could read this in & append or output to a flat file The only fields I actually need out of the crappy file is:

PartnerCaseId|

UserFirstName|

UserLastName|

Address1|

Address2|

City|

State|

Birthdate|

UserGender|

UserStatus|

EmployeeFirstName|

EmployeeLastName|

EmployeeSSN|

CounselorFirstName|

CounselorLastName|

CounselorWorkPhone|

CounselorWorkExtension|

PartnerIssue|

CoverComments|

Goals|

Materials
BinduV
Still says type mismatch....
Not sure what's going on....
Robert Lettan
try this one:

Dim dbsXML As New System.Data.DataSet

i hope this works otherwise you have to make sure the reference to System.Data is added to your project.

have a nice day

Abdullah Al-Rasheed
BlairSh
Thanks so much... I got it.
Have a great one...
N.
Joel Just Joel
You have to make those modifications i told you about because the XML file is not well formated and .NET XML cannot deal with unformated XML documents.

Abdullah Al-Rasheed
Erin McGowan
Thanks so much, I see what you are doing here.
Why can't my app recoginze the

dbsConfig.ReadXml (Application.StartupPath & "\" & "Test.xml")

What references are you using to declare

Dim dbsXML As New DataSet

user-type not defined.
XML to Pipe Delimited or .tab in VB?

Answer this question

XML to Pipe Delimited or .tab in VB?

Hot Topic

Visual Basic

XML to Pipe Delimited or .tab in VB?

XML to Pipe Delimited or .tab in VB?

jgoldb

Idontcare2623

Bandobras

rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

regisphilbin

Rohit Wadhwa

BinduV

Robert Lettan

BlairSh

Joel Just Joel

Erin McGowan