Change the delimiter when creating a CSV file

[Anonymous]

I am querying a SharePoint List and using the Create CSV Table action to generate a CSV file.

However I want the file to be pipe("|") separated.

Is there a way to change the delimiter when creating a CSV file. I would greatly appreciate any advise on this.

[nihkel]

Hi there!

glad to see this post keeps active after all this time 🙂 

I don't entirely disagree with your solution, and it may be appropriate for some cases, but for me (not sure if I mentioned in a previous post) it would be much simpler to convert that kind of data before the delimiter replacement method (ie, when you create the json table) whenever a column may incur in that problem.

Either way, more solutions for the same problem always fuel creativity for someone, so is always welcome.
Thanks for your contribution.

Cheers!

[m_jeppesen]

@nihkel 

Could you please elaborate how you would approach doing the conversion before creating the CSV, and maybe even taking my sample data as an example? 🙂

I've really tried thinking of an easier approach, but I really don't see how to do this before creating the CSV, if some cells really cannot avoid having comma (,) in them - fx. prices and names that could contain a comma (,)

[
    {
        "ID": "1",
        "Name": "John Doe",
        "CreditLimit": "3,000.50",
        "Status": "Active"
    },
    {
        "ID": "2",
        "Name": "Serena Young",
        "CreditLimit": "1,000.00",
        "Status": "Active"
    },
    {
        "ID": "3",
        "Name": "Henry Ford",
        "CreditLimit": "500.00",
        "Status": "Inactive"
    }
]

[nihkel]

(Sorry, I'm on mobile and this just sucks)
Have you considered using an expression in this step? (this is a screenshot from a previous post of mine in the thread).

In this step where you are building the table, for the columns you know will have commas, instead of using the dynamic content for the field coming from SharePoint directly, use an expression that manipulates the data before hand.
If we are talking about commas, use something like a replace expression and remove them. This way what's entered into the table already won't have commas (and therefor be ready for the next step with the separators)

So in the columns you are building (that will have commas) you shoud have:
- Separator (pipe in my scenario)
- Expression handling the dynamic value you want (insted of the dynamic value directly shown in thr screenshot)

- Separator (pipe in my scenario)

If i understood your point correctly, that should do the trick for you.
Let me know if I managed to explain myself or not... 

Cheers

[nihkel]

I managed to get a screenshot that may make things clearer.

Just as I'm formatting the date values that I get for SP in some of these columns, you could "replace out" whatever is coming in in that field that you don't want. In your case, the comma.

Hope this helps,
Cheers

[m_jeppesen]

I totally understand what you are saying (I think 😉), but I can't really implement it in a way, where is doesn't wrap the pipes into the ""

I think (when you have time) you should try doing the way you suggest, because I haven't found a way to make the expressions both formatting the values the way I want, including comma, and not wrap the cell value in " " 🙂

In this example i have a string with a comma that includes an illegal character I want to replace, with an expression - replace ø with o.

Data

[
  {
    "ID": "1",
    "Name": "John, Døe",
    "CreditLimit": "3,000.50",
    "Status": "Active"
  },
  {
    "ID": "2",
    "Name": "Serena, Young",
    "CreditLimit": "1,000.00",
    "Status": "Active"
  },
  {
    "ID": "3",
    "Name": "Henry, Ford",
    "CreditLimit": "500.00",
    "Status": "Inactive"
  }
]

Result

ID|,|Name|,|CreditLimit|,|Status
1|,"|John, Doe|","|3,000.50|",|Active
2|,"|Serena, Young|","|1,000.00|",|Active
3|,"|Henry, Ford|",|500.00|,|Inactive

So the ø is replaced with o, but the cell is still wrapped in "" including the |.

So even though the values are not selected by dynamics values, but inserted with expressions, Power Automate still wrap | in ""

[nihkel]

I tried to create a quick flow with your data.

I didn't get to the separator replacement part, but it should be as mentioned in the post.

Output looks good to me... no?
(note i only touched the name field)

Let me know if this helped. Maybe I can dig a bit deeper next week if it didn't.
I'm not sure if I am missing something here on your issue.

Cheers

[nihkel]

It took me a while, but I finally think I understood you.

You just want to keep the commas. 

It's not really my usecase cause as I explained, for me what I don't want is already forbidden by column validation in SP.

Thinking of a solution to your issue:
- i would try a similar approach to keeping the commas as we are doing for changing the delimiter. Have you tried using the replace expression in the table to replace all commas for some new symbol combination you could look for later? After that you'd do the reverse step and look for the symbol combination and replace it with a comma wherever is found.

I'll test it later if I can.

Cheers