CSV => line by line API calls

[w.bromley]

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!

[jan_suhr]

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

[w.bromley]

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...

[JimmyHartington]

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.

[magnussandstrom]

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.

[w.bromley]

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!

[magnussandstrom]

Could you share the demo CSV?

[w.bromley]

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>

[magnussandstrom]

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

[w.bromley]

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