Skip to main content
  • EN

How do i convert a CSV file to a XML?

Hi @rochak127,

Here are some tutorials to get you started:

Hope that helps point you in the right direction,

-Liz

Hi @rochak127,

Here are some tutorials to get you started:

Hope that helps point you in the right direction,

-Liz

Good point. Excellent tutorials :)

 

 

Greetings, I have been following the tutorial and have been able to convert the attached from csv to XML, but the result i am after is alluding me.

 

For starters I am trying to import the stops in sequential order as follows:

 

 

<?xml version="1.0" encoding="utf-8"?> <Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <FileID>21_1_20180612_2141</FileID> <FileSet>O</FileSet> <MasterStartDate xsi:nil="true"/> <ServiceProvider>Martyrs</ServiceProvider> <ServiceProviderID>21</ServiceProviderID> <DepotName>Martyrs Bus Depot</DepotName> <DepotID>1</DepotID> <Schedule> <CalendarGroupName>WED-SD_(17/06/2018)</CalendarGroupName> <CalendarGroupCode>SD02_0010000</CalendarGroupCode> <Route> <RoutePath>i68300034</RoutePath> <RouteNumber>683</RouteNumber> <RouteDir>0</RouteDir> <PathNote xsi:nil="true"/> <Trip> <TripNumber>5056395</TripNumber> <TicketCode>505639</TicketCode> <IsPublic>1</IsPublic> <SchoolService>0</SchoolService> <WCA>0</WCA> <TripNote> <TripNoteText xsi:nil="true"/> <TripNoteCode xsi:nil="true"/> </TripNote> <Stops> <StopID>21301</StopID> <StopSequenceNumber>1</StopSequenceNumber> <IsTimingPoint>1</IsTimingPoint> <ArrivalTime xsi:nil="true"/> <DepartureTime>07:10:00</DepartureTime> <PickUpOnly xsi:nil="true"/> <DropOffOnly xsi:nil="true"/> <StopNote> <StopNoteText xsi:nil="true"/> <StopNoteCode xsi:nil="true"/> </StopNote> </Stops> <Stops> <StopID>21302</StopID> <StopSequenceNumber>2</StopSequenceNumber> <IsTimingPoint>1</IsTimingPoint> <ArrivalTime xsi:nil="true"/> <DepartureTime>07:20:00</DepartureTime> <PickUpOnly xsi:nil="true"/> <DropOffOnly xsi:nil="true"/> <StopNote> <StopNoteText xsi:nil="true"/> <StopNoteCode xsi:nil="true"/> </StopNote> </Stops> <Stops> <StopID>21303</StopID> <StopSequenceNumber>3</StopSequenceNumber> <IsTimingPoint>1</IsTimingPoint> <ArrivalTime xsi:nil="true"/> <DepartureTime>07:30:00</DepartureTime> <PickUpOnly xsi:nil="true"/> <DropOffOnly xsi:nil="true"/> <StopNote> <StopNoteText xsi:nil="true"/> <StopNoteCode xsi:nil="true"/> </StopNote> </Stops> </Trip> </Route> </Schedule> </Header>Any hints would be appreciated :)

 

 

template.csv

 

schedule.csv

 

 

Hi @giarcnomis, assuming that the requirements as:

 

  • Convert the CSV lines to XML documents for each FileID.
  • In a single XML document, the <Stops> element can occur multiple times sequentially.
  • Other elements occur only once for each document.
I think this workflow creates your desired XML.

 

ROOT Template Expression:

 

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FileID>{fme:get-attribute("FileID")}</FileID>
  <FileSet>{fme:get-attribute("FileSet")}</FileSet>
  <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
  <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
  <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
  <DepotName>{fme:get-attribute("DepotName")}</DepotName>
  <DepotID>{fme:get-attribute("DepotID")}</DepotID>
  <Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    <Route>
      <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
      <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
      <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
      <PathNote>{fme:get-attribute("PathNote")}</PathNote>
      <Trip>
        <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
        <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
        <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
        <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
        <WCA>{fme:get-attribute("WCA")}</WCA>
        <TripNote>
          <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
          <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
        </TripNote>
        {fme:process-features("SUB")}
      </Trip>
    </Route>
  </Schedule>
</Header>
SUB Template Expression:

 

<Stops>
  <StopID>{fme:get-attribute("StopID")}</StopID>
  <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
  <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
  <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
  <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
  <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
  <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
  <StopNote>
    <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
    <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
  </StopNote>
</Stops>

0684Q00000ArJ1lQAF.png

Greetings, I have been following the tutorial and have been able to convert the attached from csv to XML, but the result i am after is alluding me.

 

For starters I am trying to import the stops in sequential order as follows:

 

 

   <?xml  version="1.0" encoding="utf-8"?>    <Header  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">       <FileID>21_1_20180612_2141</FileID>       <FileSet>O</FileSet>       <MasterStartDate xsi:nil="true"/>       <ServiceProvider>Martyrs</ServiceProvider>       <ServiceProviderID>21</ServiceProviderID>       <DepotName>Martyrs Bus Depot</DepotName>       <DepotID>1</DepotID>       <Schedule>       <CalendarGroupName>WED-SD_(17/06/2018)</CalendarGroupName>       <CalendarGroupCode>SD02_0010000</CalendarGroupCode>       <Route>       <RoutePath>i68300034</RoutePath>       <RouteNumber>683</RouteNumber>       <RouteDir>0</RouteDir>       <PathNote xsi:nil="true"/>       <Trip>       <TripNumber>5056395</TripNumber>       <TicketCode>505639</TicketCode>       <IsPublic>1</IsPublic>       <SchoolService>0</SchoolService>       <WCA>0</WCA>       <TripNote>       <TripNoteText xsi:nil="true"/>       <TripNoteCode xsi:nil="true"/>       </TripNote>       <Stops>       <StopID>21301</StopID>       <StopSequenceNumber>1</StopSequenceNumber>       <IsTimingPoint>1</IsTimingPoint>       <ArrivalTime xsi:nil="true"/>       <DepartureTime>07:10:00</DepartureTime>       <PickUpOnly xsi:nil="true"/>       <DropOffOnly xsi:nil="true"/>       <StopNote>      <StopNoteText  xsi:nil="true"/>      <StopNoteCode  xsi:nil="true"/>       </StopNote>       </Stops>       <Stops>       <StopID>21302</StopID>       <StopSequenceNumber>2</StopSequenceNumber>       <IsTimingPoint>1</IsTimingPoint>       <ArrivalTime xsi:nil="true"/>       <DepartureTime>07:20:00</DepartureTime>       <PickUpOnly xsi:nil="true"/>       <DropOffOnly xsi:nil="true"/>       <StopNote>      <StopNoteText  xsi:nil="true"/>      <StopNoteCode  xsi:nil="true"/>       </StopNote>       </Stops>       <Stops>       <StopID>21303</StopID>       <StopSequenceNumber>3</StopSequenceNumber>       <IsTimingPoint>1</IsTimingPoint>       <ArrivalTime xsi:nil="true"/>       <DepartureTime>07:30:00</DepartureTime>       <PickUpOnly xsi:nil="true"/>       <DropOffOnly xsi:nil="true"/>       <StopNote>      <StopNoteText  xsi:nil="true"/>      <StopNoteCode  xsi:nil="true"/>       </StopNote>       </Stops>       </Trip>       </Route>       </Schedule>    </Header>Any hints would be appreciated :)

 

 

template.csv

 

schedule.csv

 

 

Hi @giarcnomis, assuming that the requirements as:

 

  • Convert the CSV lines to XML documents for each FileID.
  • In a single XML document, the <Stops> element can occur multiple times sequentially.
  • Other elements occur only once for each document.
I think this workflow creates your desired XML.

 

ROOT Template Expression:

 

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FileID>{fme:get-attribute("FileID")}</FileID>
  <FileSet>{fme:get-attribute("FileSet")}</FileSet>
  <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
  <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
  <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
  <DepotName>{fme:get-attribute("DepotName")}</DepotName>
  <DepotID>{fme:get-attribute("DepotID")}</DepotID>
  <Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    <Route>
      <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
      <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
      <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
      <PathNote>{fme:get-attribute("PathNote")}</PathNote>
      <Trip>
        <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
        <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
        <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
        <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
        <WCA>{fme:get-attribute("WCA")}</WCA>
        <TripNote>
          <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
          <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
        </TripNote>
        {fme:process-features("SUB")}
      </Trip>
    </Route>
  </Schedule>
</Header>
SUB Template Expression:

 

<Stops>
  <StopID>{fme:get-attribute("StopID")}</StopID>
  <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
  <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
  <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
  <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
  <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
  <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
  <StopNote>
    <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
    <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
  </StopNote>
</Stops>

0684Q00000ArMBdQAN.png

Thankyou @takashi  for your suggestion which has progressed me a little closer towards my goal.

I have included a larger sample of my data which needs to be converted to XML but am finding it to be quite complicated for my level of expertise.

Using your setup, row 2 isn't making it through to the XML for some reason.

If I introduce additional trips then the following code is being inserted where it shouldn't be.

</Header><?xml version="1.0" encoding="UTF-8"?><Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  <FileID>21_1_20180612_2141</FileID>  <FileSet>O</FileSet>  <MasterStartDate/>  <ServiceProvider>Martyrs</ServiceProvider>  <ServiceProviderID>21</ServiceProviderID>  <DepotName>Bus Depot</DepotName>  <DepotID>1</DepotID>  <Schedule>    <CalendarGroupName>WED-SD_(17/06/2018)</CalendarGroupName>    <CalendarGroupCode>SD02_0010000</CalendarGroupCode>    <Route>      <RoutePath>o68300024</RoutePath>      <RouteNumber>683</RouteNumber>      <RouteDir>1</RouteDir>      <PathNote/>

The sample data attached includes multiple trips on multiple schedules.

The schedule.csv needs to be an exact match to the schedule.zip in the zipped folder but it is coming out as per test.zip

schedule.csv

schedule.zip

test.zip

2018-08-08-14-35-32.png

csv2xml.fmw

Feature Type: `Logger_LOGGED'

Attribute(string)                 : `ArrivalTime' has value `'

Attribute(string)                 : `CalendarGroupCode' has value `SD02_0010000'

Attribute(string)                 : `CalendarGroupName' has value `WED-SD_(17/06/2018)'

Attribute(string)                 : `DepartureTime' has value `7:10:00'

Attribute(string)                 : `DepotID' has value `1'

Attribute(string)                 : `DepotName' has value `Bus Depot'

Attribute(string)                 : `DropOffOnly' has value `'

Attribute(string)                 : `FileID' has value `21_1_20180612_2141'

Attribute(string)                 : `FileSet' has value `O'

Attribute(string)                 : `IsPublic' has value `1'

Attribute(string)                 : `IsTimingPoint' has value `1'

Attribute(string)                 : `MasterStartDate' has value `'

Attribute(string)                 : `PathNote' has value `'

Attribute(string)                 : `PickUpOnly' has value `'

Attribute(string)                 : `RouteDir' has value `1'

Attribute(string)                 : `RouteNumber' has value `683'

Attribute(string)                 : `RoutePath' has value `o68300024'

Attribute(string)                 : `SchoolService' has value `0'

Attribute(string)                 : `ServiceProvider' has value `Martyrs'

Attribute(string)                 : `ServiceProviderID' has value `21'

Attribute(string)                 : `StopID' has value `21301'

Attribute(string)                 : `StopNoteCode' has value `'

Attribute(string)                 : `StopNoteText' has value `'

Attribute(string)                 : `StopSequenceNumber' has value `1'

Attribute(string)                 : `TicketCode' has value `505436'

Attribute(string)                 : `TripNoteCode' has value `'

Attribute(string)                 : `TripNoteText' has value `'

Attribute(string)                 : `TripNumber' has value `5054363'

Attribute(string)                 : `WCA' has value `0'

Attribute(string)                 : `_xml_error{0}.col' has value `1'

Attribute(string)                 : `_xml_error{0}.desc' has value `invalid document structure'

Attribute(string)                 : `_xml_error{0}.file' has value `FileID'

Attribute(string)                 : `_xml_error{0}.line' has value `1'

Attribute(string)                 : `_xml_error{0}.type' has value `Fatal Error'

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

Thank you so much @takashi 

 

I have set up my workspace to be the same as yours.

 

0684Q00000ArMBYQA3.png

For some reason I am only getting 1 record from the Output as follows

 

Can you recommend a tutorial that would assist me please? I have already watched those recommended by @rochak127 but your method is different.

 

<?xml version="1.0" encoding="UTF-8"?>
<fme:xml-tables xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:fme="http://www.safe.com/xml/xmltables" xsi:schemaLocation="http://www.safe.com/xml/xmltables output.xsd">
<fme:CSV-table>
<fme:CSV>
<fme:FileID>21_1_20180612_2141</fme:FileID>
<fme:FileSet>O</fme:FileSet>
<fme:MasterStartDate/>
<fme:ServiceProvider>Martyrs</fme:ServiceProvider>
<fme:ServiceProviderID>21</fme:ServiceProviderID>
<fme:DepotName>Bus Depot</fme:DepotName>
<fme:DepotID>1</fme:DepotID>
<fme:CalendarGroupName>WED-SD_(17/06/2018)</fme:CalendarGroupName>
<fme:CalendarGroupCode>SD02_0010000</fme:CalendarGroupCode>
<fme:RoutePath>o68300024</fme:RoutePath>
<fme:RouteNumber>683</fme:RouteNumber>
<fme:RouteDir>1</fme:RouteDir>
<fme:PathNote/>
<fme:TripNumber>5054363</fme:TripNumber>
<fme:TicketCode>505436</fme:TicketCode>
<fme:IsPublic>1</fme:IsPublic>
<fme:SchoolService>0</fme:SchoolService>
<fme:WCA>0</fme:WCA>
<fme:TripNoteText/>
<fme:TripNoteCode/>
<fme:StopID>21301</fme:StopID>
<fme:StopSequenceNumber>1</fme:StopSequenceNumber>
<fme:IsTimingPoint>1</fme:IsTimingPoint>
<fme:ArrivalTime/>
<fme:DepartureTime>7:10:00</fme:DepartureTime>
<fme:PickUpOnly/>
<fme:DropOffOnly/>
<fme:StopNoteText/>
<fme:StopNoteCode/>
</fme:CSV>
</fme:CSV-table>
</fme:xml-tables>

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

The XMLTemplater outputs features holding an XML document as an attribute for each group. In this case, all input features belong to an identical "FileID" group, so naturally just a single feature containing an XML document would be output. That's the result I intended.

 

Check content of the result attribute (called "_result" by default) using the Logger or the Inspector (FME Data Inspector).

 

You can use the Text File writer or the Data File writer to write the XML document into a file.

 

 

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

This is a workspace example containing some optional XML operations and a process to write the resulting XML document into a file.

 

gUpdated] csv-to-xml-2.fmwt (FME 2018.1.0.0)

 

 

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

Oh, your XMLFormatter has output the feature via the Failed port. Probably the XML document was invalid.

 

I would recommend you to check feature attributes on each connection, using breakpoints or Loggers etc. See here to learn about debugging with breakpoints: About Feature Inspection

 

See also these links to learn more about how to create an XML document with the XMLTemplater.

 

 

Hi @giarcnomis, firstly you will have to clarify the hierarchy structure of destination XML document, relationship between the XML structure and schema of the CSV table, and find key attributes to group descendant elements, in order to configure template expressions properly.

I assume as:

  • <Header> is the root element of destination XML document, and "FileID" is the key.
  • <Schedule> can occur one or more times under <Header>, and "CalendarGroupCode" is the key.
  • <Route> can occur one or more times under each <Schedule>, and "RoutePath" is the key.
  • <Trip> can occur one or more times under each <Route>, and "TripNumber" is the key.
  • <Stops> can occur one or more times under each <Trip>.

If my assumption above was correct, this workflow creates desired XML.

ROOT Expression:

<Header xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <FileID>{fme:get-attribute("FileID")}</FileID>
    <FileSet>{fme:get-attribute("FileSet")}</FileSet>
    <MasterStartDate>{fme:get-attribute("MasterStartDate")}</MasterStartDate>
    <ServiceProvider>{fme:get-attribute("ServiceProvider")}</ServiceProvider>
    <ServiceProviderID>{fme:get-attribute("ServiceProviderID")}</ServiceProviderID>
    <DepotName>{fme:get-attribute("DepotName")}</DepotName>
    <DepotID>{fme:get-attribute("DepotID")}</DepotID>
    {
        fme:process-features("SCHEDULE")
    }
</Header>

SCHEDULE Expression:

<Schedule>
    <CalendarGroupName>{fme:get-attribute("CalendarGroupName")}</CalendarGroupName>
    <CalendarGroupCode>{fme:get-attribute("CalendarGroupCode")}</CalendarGroupCode>
    {
        fme:process-features("ROUTE",
            ("CalendarGroupCode"),
            (fme:get-attribute("CalendarGroupCode"))
        )
    }
</Schedule>

ROUTE Expression:

<Route>
    <RoutePath>{fme:get-attribute("RoutePath")}</RoutePath>
    <RouteNumber>{fme:get-attribute("RouteNumber")}</RouteNumber>
    <RouteDir>{fme:get-attribute("RouteDir")}</RouteDir>
    <PathNote>{fme:get-attribute("PathNote")}</PathNote>
    {
        fme:process-features("TRIP",
            ("CalendarGroupCode", "RoutePath"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"))
        )
    }
</Route>

TRIP Expression:

<Trip>
    <TripNumber>{fme:get-attribute("TripNumber")}</TripNumber>
    <TicketCode>{fme:get-attribute("TicketCode")}</TicketCode>
    <IsPublic>{fme:get-attribute("IsPublic")}</IsPublic>
    <SchoolService>{fme:get-attribute("SchoolService")}</SchoolService>
    <WCA>{fme:get-attribute("WCA")}</WCA>
    <TripNote>
      <TripNoteText>{fme:get-attribute("TripNoteText")}</TripNoteText>
      <TripNoteCode>{fme:get-attribute("TripNoteCode")}</TripNoteCode>
    </TripNote>
    {
        fme:process-features("STOPS",
            ("CalendarGroupCode", "RoutePath", "TripNumber"),
            (fme:get-attribute("CalendarGroupCode"), fme:get-attribute("RoutePath"), fme:get-attribute("TripNumber"))
        )
    }
</Trip>

STOPS Expression:

<Stops>
    <StopID>{fme:get-attribute("StopID")}</StopID>
    <StopSequenceNumber>{fme:get-attribute("StopSequenceNumber")}</StopSequenceNumber>
    <IsTimingPoint>{fme:get-attribute("IsTimingPoint")}</IsTimingPoint>
    <ArrivalTime>{fme:get-attribute("ArrivalTime")}</ArrivalTime>
    <DepartureTime>{fme:get-attribute("DepartureTime")}</DepartureTime>
    <PickUpOnly>{fme:get-attribute("PickUpOnly")}</PickUpOnly>
    <DropOffOnly>{fme:get-attribute("DropOffOnly")}</DropOffOnly>
    <StopNote>
        <StopNoteText>{fme:get-attribute("StopNoteText")}</StopNoteText>
        <StopNoteCode>{fme:get-attribute("StopNoteCode")}</StopNoteCode>
    </StopNote>
</Stops>

0684Q00000ArJnbQAF.png

You can also use the XMLFormatter to write the XML document into a file. I updated the workspace example posted above.
Oh, your XMLFormatter has output the feature via the Failed port. Probably the XML document was invalid.

 

I would recommend you to check feature attributes on each connection, using breakpoints or Loggers etc. See here to learn about debugging with breakpoints: About Feature Inspection

 

See also these links to learn more about how to create an XML document with the XMLTemplater.

 

 

@takashi

 

Have you tried running your csv-to-xml-2.fmwt with the XMLFormatter added?

 

It returns Failed the same as mine.

 

I'm still not sure how I'm supposed to output all features :(

 

Oh, your XMLFormatter has output the feature via the Failed port. Probably the XML document was invalid.

 

I would recommend you to check feature attributes on each connection, using breakpoints or Loggers etc. See here to learn about debugging with breakpoints: About Feature Inspection

 

See also these links to learn more about how to create an XML document with the XMLTemplater.

 

 

Yes, the csv-to-xml-2 workspace creates an XML document as expected. The template contains your sample data. You can just run it without any modification.

 

Have you tried it?

 

Yes, the csv-to-xml-2 workspace creates an XML document as expected. The template contains your sample data. You can just run it without any modification.

 

Have you tried it?

 

Yes I ran yours but only 1 line is writing into my xml instead of all 6200 contained in the schedule.csv

 

Yes I ran yours but only 1 line is writing into my xml instead of all 6200 contained in the schedule.csv

 

As I mentioned before, the XMLTemplator outputs just a single feature contains an XML document as an attribute. The XML contains all the contents read from the CSV.

 

Did you check the XML file by opening it with a Text Editor?

 

 

Yes I ran yours but only 1 line is writing into my xml instead of all 6200 contained in the schedule.csv

 

This is the XML file written by my workspace "csv-to-xml-2.fmw".

 

21-1-20180612-2141.zip

 

 

This is a workspace example containing some optional XML operations and a process to write the resulting XML document into a file.

 

>Updated] csv-to-xml-2.fmwt (FME 2018.1.0.0)

 

 

I am learning a lot and have met with some success Takashi.

 

Your advice has been invaluable and I really appreciate it.

 

Thanks to you!!

 

 

You can also use the XMLFormatter to write the XML document into a file. I updated the workspace example posted above.
Everything appears to be working well @takashi

 

I have one more question if you don't mind.

 

Time formatting in the xml document appears as 9:24:00 but I need it to show as 09:24:00.

 

Is there a way to make sure it writes as hh🇲🇲ss

 

Thank you

 

 

You can also use the XMLFormatter to write the XML document into a file. I updated the workspace example posted above.
You can use the DateTimeConverter to convert format of the time values, before the XMLTemplater.

 

  • Input Format: %k:%M:%S
  • Output Format: %H:%M:%S

 

You can use the DateTimeConverter to convert format of the time values, before the XMLTemplater.

 

  • Input Format: %k:%M:%S
  • Output Format: %H:%M:%S

 

Hi @takashi

 

 

Is there a way to get Output Format: Text hh🇲🇲ss

 

The output keeps going to the Rejected port when I use the DateTimeConverter

 

I have tried Input 084000 as well as 08:40:00

 

Hi @takashi

 

 

Is there a way to get Output Format: Text hh🇲🇲ss

 

The output keeps going to the Rejected port when I use the DateTimeConverter

 

I have tried Input 084000 as well as 08:40:00

 

  • What is the input time format?
  • How did you set the parameters in the DataTimeConverter?
  • What text does the "fme_rejection_code" attribute in the rejected feature contain?

 

  • What is the input time format?
  • How did you set the parameters in the DataTimeConverter?
  • What text does the "fme_rejection_code" attribute in the rejected feature contain?

 

Originally I was using a csv to input, but excel kept changing my text to time.

 

Now I have switched to xls and have tried format custom h🇲🇲ss

 

 

 

 

 

Hi @takashi

 

 

Is there a way to get Output Format: Text hh🇲🇲ss

 

The output keeps going to the Rejected port when I use the DateTimeConverter

 

I have tried Input 084000 as well as 08:40:00

 

  • In your screenshot of the Feature Information, the ArrivalTime is . It's the reason for the rejection.
  • Also, in the DateTimeConverter parameters, the Output Format is the same as the Input Format. It would do nothing, as you can see the Preview. If those settings are correct, I don't think you need to use the DateTimeConverter.

 

  • In your screenshot of the Feature Information, the ArrivalTime is . It's the reason for the rejection.
  • Also, in the DateTimeConverter parameters, the Output Format is the same as the Input Format. It would do nothing, as you can see the Preview. If those settings are correct, I don't think you need to use the DateTimeConverter.

 

I came to the same conclusion. Thanks

 

Reply


Community Stats

29,731
Posts
112,962
Replies
37,377
Members
Terms & ConditionsCookie settings