Having issue reading Excel file with comma

[ripalamin]

Hi Guys ,

I am reading excel file with different format from Share Point and validating Date format. I am stuck on a data field containing a comma in my .csv file.  The comma is part of the data so cannot be replaced with another delimiter but as a result the columns are not split correctly.

Here is the Example:

ID	PartName	Value	Month	Date
E4	ZZ_PC_Watmar	318.07	1	10/3/2023
D4	ZE_PC_Watmar	132.96	2	10/3/2023
V5	ZN_PC_Stone	370.38	3	10/3/2023
K1	ZV_PC_Water(Prod,Dev,UAT)	13.61	4	10/5/2023
D1	ZZ_PC_XVH	58.41	4	10/3/2023

[Jondoesflow]

Try this

https://powerusers.microsoft.com/t5/Building-Flows/Setting-up-specific-expression-to-remove-comma-in...

---------------------------------------------------------------------------


If I have helped you with an answer, please accept this post as a solution and thumbs up my reply. Thanks !

Blog - JonDoesFlow
Twitter - JonDoesFlow
YouTube - JonDoesFlow

[AlexEncodian]

@ripalamin 

I tested it out using Encodian's Parse CSV action and it appears to have given me the correct result (see below). Worth trying out although it could also be differences in how the CSV is set up:

[ripalamin]

 

When I try to use it ask me for connection Name and API Key. Does API Key is free? Any suggestion.

[ripalamin]

I have tried your suggestion 

This Flow replace comma from every where and I want comma later to split the column value. How Can i do that?

This flow you have suggest me it replace comma from everywhere but I need comma later to split the column value and get specific Date column data.

[grantjenkins]

Can you add a Compose directly after your Get file content using the following expression and run the flow. Then show the output you get from your Compose. I'm fairly certain we can get what you're after without a third-party solution and without having to use a loop.

base64ToString(outputs('Get_file_content')?['body']?['$content'])

I just wanted to confirm that it looks like that below (with double quotes around the Part Names that contain commas).

[ripalamin]

I have already use Compose Action (ArrayData) in my flow. The output is same as you have.

The problem is when I split data after this step, it split ZV_PC_Water(Prod,Dev) in 2 separate column bcoz of comma. I want only in one column. 

[grantjenkins · Accepted solution]

This is how I'd write the flow to get what you're after. This assumes you only have a single column that could contain commas within the text (in your example, the PartName column).

Below is the CSV data that I'm importing into my flow.

See full flow below. I'll go into each of the actions.

Get file content using path retrieves my CSV file.

Compose converts the CSV data to text using the following expression.

base64ToString(outputs('Get_file_content_using_path')?['body']?['$content'])

Select splits the data by carriage return and new line, then skips the first row (headers). And within the Map, it replaces the commas within the text to ||. Note that it doesn't replace the comma delimiters.

//From
skip(split(outputs('Compose'), decodeUriComponent('%0D%0A')), 1)

//Map
if(contains(item(), '"'), concat(slice(item(), 0, indexOf(item(), '"')), replace(slice(item(), add(indexOf(item(), '"'), 1), lastIndexOf(item(), '"')), ',', '||'), slice(item(), add(lastIndexOf(item(), '"'), 1), length(item()))), item())

Filter array uses the output from Select and removes any empty rows. You normally get an empty row at the end of your array when splitting the data. The expression used is below.

item()

Select Final uses the output from Filter array and the following expressions.

//ID
split(item(), ',')[0]

//Part Name
replace(split(item(), ',')[1], '||', ',')

//Value
split(item(), ',')[2]

//Month
split(item(), ',')[3]

//Date
split(item(), ',')[4]

After running the flow, we should get the following output.

[
  {
    "ID": "E4",
    "Part Name": "ZZ_PC_Watmar",
    "Value": "318.07",
    "Month": "1",
    "Date": "10/03/2023"
  },
  {
    "ID": "D4",
    "Part Name": "ZE_PC_Watmar",
    "Value": "132.96",
    "Month": "2",
    "Date": "10/03/2023"
  },
  {
    "ID": "V5",
    "Part Name": "ZN_PC_Stone",
    "Value": "370.38",
    "Month": "3",
    "Date": "10/03/2023"
  },
  {
    "ID": "K1",
    "Part Name": "ZV_PC_Water(Prod,Dev,UAT)",
    "Value": "13.61",
    "Month": "4",
    "Date": "10/05/2023"
  },
  {
    "ID": "D1",
    "Part Name": "ZZ_PC_XVH",
    "Value": "58.41",
    "Month": "4",
    "Date": "10/03/2023"
  }
]