Parse PHP array stored as string and concatenate items to string in Power Query

Issue

I’m doing my first steps with Power Query in Excel. I have exported some data from PHP to a .csv file and want to import them to an excel sheet. This works great with strings and numbers, but I need an improved Power Query statement to read array data.

The PHP string array looks something like:

a:3:{i:0;s:4:"Text";i:1;s:2:"in";i:2;s:5:"array";}

• "a" stands for the array name
• "3" is the number of items in array
• Within the curly brace {} stands the array with
• "i" for the index and
• "s" for the length of the item (string)
• The strings "Text", "in" and "array" are the items in the array, which I want to parse.

My Idea is to parse the PHP array string to a Power Query list and concatenate the items to a comma separated string.

In the end i want to have this string:

"Text, in, array"

I have tried to remove the a, i, s, ; and the numbers to get something like a json string:

= Text.Remove([PHP-array],{"0".."9"}),...

And then parse with:

Json.Document([JSON-string])

But this seems inelegant and prone to error.

Is there a way to parse the PHP array to a list and than serialize it to a comma separated string?

Edit/Update

A new idea is to extract only the strings between quotes " and merge them. It tried it this way:

Table.TransformColumns(#"Column", {{"function", each Text.BetweenDelimiters(_, """", """"), type text}})

But this works only for the first occurence. I would need to repeat it for each item and separate them with comma. But how to do this? Is there a kind of a loop in Power Query?

Solution

In PowerQuery, this works to convert the data from Column1 of Table1 into a new column called Custom

Splits into a list based on "

If the array item contains a ; then convert that to a null

remove all nulls

Recombine remaining list items separated by , (space)

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.Split([Column1], """"), each if not(Text.Contains(_,";")) then _ else null)),", "))
in  #"Added Custom"

Answered By – horseyride

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0