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

[preetioc]

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?

[Marcy Nunns]

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

[preetioc]

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)

Stack(Sample xml data in csv).csv.zip   312 B 1 version

[Marcy Nunns]

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.  

[preetioc]

How do I add this as a step in script?

Can you give some idea??

[Marcy Nunns]

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.  

[James Culpovich]

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.