Enfocus Community

Hi Guys!

I have a use case whereby I need to take a supplied CSV and make an API call based on each line.

I guess I need to either load in each line as either attached XML or private data and then use that to populate my API call...

I am struggling with the first part of this...

A suggestion I has was:
-Use Saxon with an XSLT to transform your CSV file into an XML file
-Use Saxon with a second XSLT to split your new XML file into each listing in your original CSV.
-Pick up each XML as its own Switch dataset/job
-Construct your API call from the necessary elements in the attached XML

I am struggling with this - as I cannot work out how XSLT would work in this context (I am very new to XSLT!).

Any help or suggestions or examples would be much appreciated...

Thanks in advance!

There is an app for that.
CSV2XML will fix it for you.

Ahhh excellent - I will give that a try!!!

Cant get Saxonica working at all - got a simple demo csv and xsl and it's just erroring every time...

My Switch is running on Windows.

Here I use a command line program to split a csv to single lines (https://download.cnet.com/csv-splitter/ ... 10188.html).
And Easy Data Transform (https://www.easydatatransform.com) running as commandline to convert to XML.
Then XML Pickup to get the data to work with in Switch

Let me know if you need a more details about how I do it.

w.bromley wrote: ↑Mon Aug 12, 2024 5:18 pm Ahhh excellent - I will give that a try!!!

Cant get Saxonica working at all - got a simple demo csv and xsl and it's just erroring every time...

Could you please upload the demo csv? I'm curious to see if I can get it working in Saxonica.

Speaking to support it seems like Saxonica often causes issues - they have recommended I try it via the CLI rather than use the actual element!

Could you share the demo CSV?

I cant seem to attach - invalid format - I will paste below!
CSV:
Name,Age,Location
Alice,30,New York
Bob,25,San Francisco
Charlie,35,Los Angeles

XSL:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<!-- Root element of the output XML -->
<xsl:output method="xml" indent="yes"/>

<!-- Template for the root of the CSV XML -->
<xsl:template match="/csv">
<root>
<xsl:apply-templates select="row"/>
</root>
</xsl:template>

<!-- Template for each row in the CSV -->
<xsl:template match="row">
<record>
<xsl:for-each select="cell">
<xsl:element name="field{position()}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</record>
</xsl:template>

</xsl:stylesheet>

It was indeed hard to convert from CSV to XML with Saxonica.
I would use this approach instead (if not Jan or Jimmys suggestions suites you better):

1. Convert CSV to XML with Easy data transform (app coming soon)
2. Split the XML with Saxonica
3. XML pickup

Jan's suggestion was perfect - I have the workflow basically build and already thinking of ways to roll it out to other use cases!!!

Thanks everyone for your help