excel - Parse PHP array stored as string and concatenate items to string in Power Query
Get the solution ↓↓↓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 thea
,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?
Answer
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"
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: invalid argument supplied for foreach() laravel
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.