remove three rows BEFORE running CSV Stream

Orbiter

I am working on a CSV import API - but the specific CSV I am importing has 3 rows of notes I need to remove above the header (4 rows to remove total including header row). I did the old way (pre csv stream example video from Xano) and just repeated the way header was removed three additional times to get all 4 rows out before processing and it all works - except crashes with large CSVs - so ... now I want to change the API to use the new XANO CSV stream feature - BUT the stream functionality itself automatically removes the header so I need a "pre-step" that somehow removes the first 3 rows of the file resource BEFORE passing it to CSV STREAM. Any and all thoughts on an approach would be most appreciated. Thx!

Chris Coleman

Hi, @Orbiter. Because CSV Stream is paired with a loop, we can do this pretty easily with some conditional logic.

First, we establish a 'count' variable with a value of 0. This needs to live outside and before your For Each loop.

Inside the loop as the first step, we add 1 to the count variable. We then check that value with a conditional if/then statement, looking to see if 'count' > 3. If it is, we'll proceed with the other loop steps as normal. If it isn't, we will skip to the next iteration of the loop using the appropriate Continue to Next Iteration function.

With this logic, you'll essentially skip the first three items of the CSV stream before actually processing anything.

Orbiter

Hey @Chris Coleman I got super excited when I read this - unfortunately - it doesn't work. 😥

The three records I need to skip are notes ABOVE the header. The CSV Stream seems to always assume first row is ALWAYS the header BEFORE it is ready to iterate on the loop.

So, when I implement your example - stream first grabs the first row which in my case says "Notes:" - then it starts the loop thinking the only field of the csv is called "Notes:"

You can recreate this by making a CSV with:
first row = text "Notes:"
second row = text "abc"
third row = text "def"
fourth row = actual header names for each field

fifth row = the 1st record

Orbiter

I'll send a really nice bottle of scotch to your developer if they could just add this:

Chris Coleman

Understood, sorry about that. After understanding more about the file you're working with, I think the issue is more that it's not valid CSV structure in the first place. The first row should always be the header.

That doesn't necessarily mean we're out of options, but depending on the size of the CSV, the next steps can get a little tricky to execute successfully.

Use a Get File Resource Data on the file resource input so we can work with the raw file data. Update that variable at the .data path using the substr filter to strip out all of the characters from the beginning that shouldn't be there. I've attached the example CSV I was working with to this post if that's helpful; for me, this meant stripping out 24 characters in total from the beginning (substr start position begins at 0). Once that's done, we can create a new file resource using that updated CSV data, and use that with the CSV Stream function.

test_csv.txt

Orbiter

@Chris Coleman This is super encouraging - I’m away from my computer until later this afternoon to hack at this - but I’m curious how you calculated the number 24 based on sample file you provided. The good news here for me is this extra stuff stuck into this CSV above the header is always exactly the same. I just want a little clarity on how you calculate the number to subtract - thx!

Orbiter

So @Ray Deck got me sorted on statechange.ai (best Xano resource EVER) via a split / slice / join - and I learned a cool trick for doing a split based on new line characters. Everything working like butter. On to the next whacky challenge 😎