Convert a CSV File into a Multilevel XML File

Problem description & analysis

Below is CSV file csv.csv:

UserID,Number,IPF,PointNumber,Length

37686,4851,2,0,127.9

37686,9978,2,0,0

37686,9994,2,0,0

11111,9994,2,0,0

We are trying to convert the CSV file into a multilevel XML file. Below is the desired result:

<?xml version="1.0" encoding="GBK"?>

<xml>

    <Plant>

        <UserID>11111</UserID>

        <Verification>

            <Detail>

                <Number>9994</Number>

                <IPF>2</IPF>

            </Detail>

        </Verification>

        <PointNumber>0</PointNumber>

        <Length>0.0</Length>

    </Plant>

    <Plant>

        <UserID>37686</UserID>

        <Verification>

            <Detail>

                <Number>4851</Number>

                <IPF>2</IPF>

            </Detail>

            <Detail>

                <Number>9978</Number>

                <IPF>2</IPF>

            </Detail>

            <Detail>

                <Number>9994</Number>

                <IPF>2</IPF>

            </Detail>

        </Verification>

        <PointNumber>0</PointNumber>

        <Length>127.9</Length>

    </Plant>

</xml>

Solution

We write the following script p1.dfx in esProc:

A

1

=file("csv.csv").import@ct()

2

=A1.group(UserID;~.new(Number,IPF):Detail,PointNumber,Length)

3

=A2.new(UserID,create(Detail).record([Detail]):Verification,PointNumber,Length)

4

=file("xml.xml").write(xml(A3,"xml/Plant"))

Explanation:

A1  Import the CSV file as a table sequence.

A2  Group A1’s table sequence by UserID, create a new table sequence Detail using Number and IPF fields of each group, and get the first record of PointNumber and Length fields from each group.

A3  Create a new table sequence based on A2’s table sequence. UserID, PointNumber fand Length fields will remain unchanged, take the Detail table sequence in each record as new record to insert to the Detail field in Verification, the new table sequence.

A4  Parse A3’s table sequence as XML strings according to level mark "xml/Plant" and write them into file xml.xml.

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/62655557/compare-current-line-and-previous-line-of-the-csv-file