Remove Unwanted Rows from CSV

ModernMusician · July 25, 2024, 6:19am

Hi guys,

I’m relatively new to working with Make, previously we’ve had an external contractor working on things for us, but we’re trying to use them less and I thought this seemed like it should be an easy enough thing to achieve.

We get a stockfeed from one of our suppliers in CSV format via email. Previously we’ve just needed to import the whole thing into our ecomm platform, so the scenario was just Gmail (Watch emails) and SFTP (Upload a file).

The CSV is fairly simple. 6 columns: Brand, Item Number, UPC, Description, RRP and Stock Level, in that order.

Problem: The boss now wants us to only stockfeed certain brands from this supplier. Is there a way to do one of the two following things:

Only let through rows where the text in the first column matches a brand we want?

or

Delete rows where the text in the first column matches a brand we don’t want?

Thanks!

samliew · July 25, 2024, 6:20am

Welcome to the Make community!

The normal way is to iterate the rows and then create a filter to only allow the ones you want through, then aggregate back into CSV.

If you need further assistance:

Could you provide a sample CSV with example items/rows you want to remove?

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

ModernMusician · July 25, 2024, 7:09am

Hi, thank you for the reply.

I’ve attached a demo CSV with placeholder information instead of the actual data. An example of what we’d like to do is only let BRAND1 and BRAND2 through to our stockfeed and remove BRAND3 and BRAND4.

I had a crack at iterating, filtering and aggregating, but evidently that’s above my pay grade!

If we do end up needing to contact our usual external contractor for this one we can, we’re just trying to minimise it where we can.

DummyStockfeed.csv (209 Bytes)

samliew · July 25, 2024, 7:28am

Welcome to the Make community!

Yes, that is possible. You’ll need a minimum of one operation:

You can use the built-in function replace to remove lines starting with BRAND3 or BRAND4 with an empty string.

/(?:BRAND3|BRAND4)[^\n]+(\n|$)/g

Proof https://regex101.com/r/opuCLs/1

Input

Output

Give it a go and let us know if you have any issues!

Module Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.
Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

Click to Expand Module Export Code

JSON - Copy and Paste this directly in the scenario editor

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 67,
                    "module": "util:ComposeTransformer",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "value": "Brand,Item Number,UPC,Description,RRP, Stock Level\nBRAND1,BB0001,,PRODUCT 1,74.95,Low\nBRAND2,BB0002,,PRODUCT 2,74.95,Out of Stock\nBRAND3,BB0003,,PRODUCT 3,74.95,Medium\nBRAND4,BB0004,,PRODUCT 4,74.95,Low"
                    },
                    "metadata": {
                        "designer": {
                            "x": 640,
                            "y": -977,
                            "name": "CSV Input"
                        },
                        "restore": {},
                        "expect": [
                            {
                                "name": "value",
                                "type": "text",
                                "label": "Text"
                            }
                        ]
                    }
                },
                {
                    "id": 68,
                    "module": "util:ComposeTransformer",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "value": "{{replace(67.value; \"/(?:BRAND3|BRAND4)[^\\n]+(\\n|$)/g\"; emptystring)}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 882,
                            "y": -974,
                            "name": "Replaced CSV",
                            "messages": [
                                {
                                    "category": "last",
                                    "severity": "warning",
                                    "message": "A transformer should not be the last module in the route."
                                }
                            ]
                        },
                        "restore": {},
                        "expect": [
                            {
                                "name": "value",
                                "type": "text",
                                "label": "Text"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}