Service Virtualization

Expand all | Collapse all

Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

  • 1.  Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 09:18 AM

    I have created a test script. This has 9 steps. In 8 steps a .txt file gets created as output and each .txt file has xml data.

    Last step has result of all 8 steps stored in a csv file which has the xml data .

    The above data is extracted from Sybase DB.

    So,  when I run the ITR the result is stored in csv file and the stored result contains xml data in it. Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?



  • 2.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 09:49 AM

    Can you post a sample of your output file you are wanting to convert?



  • 3.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 11:00 AM
      |   view attached

    Hi Marcy,

    Thank you for quick response.

    This is the sample that I can provide right now-

    Just like the data mentioned below, my output gets pasted in csv file. So, I want to convert this in excel table format. I want to remove tags/ xml format . Please see attachment also..

    Please see if you can help-

    <STUDENTS>

      <STUDENT>

        <StudentID>1</StudentID>

        <Name>John Smith</Name>

        <Marks>200</Marks>

      </STUDENT>

      <STUDENT>

        <StudentID>2</StudentID>

        <Name>Mark Johnson</Name>

        <Marks>300</Marks>

      </STUDENT>

    <STUDENT>

        <StudentID>3</StudentID>

        <Name>Nitin Tyagi</Name>

        <Marks>400</Marks>

      </STUDENT>

    </STUDENTS>

    <STUDENTS>

      <STUDENT>

        <StudentID>1</StudentID>

        <Name>John Smith</Name>

        <Marks>200</Marks>

      </STUDENT>

      <STUDENT>

        <StudentID>2</StudentID>

        <Name>Mark Johnson</Name>

        <Marks>300</Marks>

      </STUDENT>

    <STUDENT>

        <StudentID>3</StudentID>

        <Name>Nitin Tyagi</Name>

        <Marks>400</Marks>

      </STUDENT>

    </STUDENTS>

    <STUDENTS>

      <STUDENT>

        <StudentID>1</StudentID>

        <Name>John Smith</Name>

        <Marks>200</Marks>

      </STUDENT>

      <STUDENT>

        <StudentID>2</StudentID>

        <Name>Mark Johnson</Name>

        <Marks>300</Marks>

      </STUDENT>

    <STUDENT>

        <StudentID>3</StudentID>

        <Name>Nitin Tyagi</Name>

        <Marks>400</Marks>

      </STUDENT>

    </STUDENTS>

    Attachment(s)



  • 4.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 02:06 PM

    The only think I can think of is to do this in a script, or using an external command step to execute a conversion via a command line.  If you do a search on convert csv to xls, there are alot of examples on how to do so.  



  • 5.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 05:22 PM

    How do I add this as a step in script?

    Can you give some idea??



  • 6.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-20-2018 05:49 PM

    You would need to add an "Execute script (JSR-223)" step (Custom Extensions) or if you already have a utility to do the convert, you could add "Execute External Command" step. (External/Subprocesses).  I would put this as the last step of your test case.

     

    Unfortunately I do not know how to code it.  



  • 7.  Re: Can we convert csv containing xml format data into an excel/csv with only column names and data against each column in test script?

    Posted 12-27-2018 10:36 AM

    Forgive me as I am still relatively new to the CA community.

     

    First, I am unsure if having multiple "Students" elements is normal or if it was just repeatedly pasted, but I assumed the latter, and reduced it down to just one set. For that, a line of PowerShell should get you what you are looking for:

     

    [xml](Get-Content -Raw '.\Stack(Sample xml data in csv).csv') | Select-Object -ExpandProperty "Students" | Select-Object -ExpandProperty "Student" | ConvertTo-Csv -NoTypeInformation

     

    This pulls in the information like this:

     

    "StudentID","Name","Marks"
    "1","John Smith","200"
    "2","Mark Johnson","300"
    "3","Nitin Tyagi","400"

     

    And should hopefully get you going in the right direction.