John Davidson

Message:

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?

---

My Answer:
To achieve your desired result of parsing the PHP array string to a list and then concatenating the items to a comma-separated string in Power Query, you can follow these steps:

1. Load your data from the .csv file into Power Query.
2. Create a custom column to extract the strings between the quotes ".
3. Merge the extracted strings into a single column.
4. Concatenate the strings in the merged column into a comma-separated string.

Here's a step-by-step guide:

1. Load your data into Power Query.
2. Create a custom column to extract the strings between the quotes ":
- Select the column containing the PHP array string.
- Go to the "Add Column" tab and click on "Custom Column".
- In the custom column formula, use the Text.BetweenDelimiters function to extract the strings between the quotes ". For example:
`Text.BetweenDelimiters([YourColumn], """, """)`
- Click OK to create the custom column.

3. Merge the extracted strings into a single column:
- Select the custom column you created in step 2.
- Go to the "Transform" tab and click on "Merge Columns".
- In the merge columns dialog, select the delimiter as "Custom" and enter a comma (,) as the delimiter.
- Click OK to merge the columns into a single column.

4. Concatenate the strings in the merged column into a comma-separated string:
- Select the merged column.
- Go to the "Transform" tab and click on "Concatenate".
- In the concatenate dialog, select the delimiter as a comma (,) and choose "Combine with delimiter" option.
- Click OK to concatenate the strings into a comma-separated string.

After following these steps, you should have a new column with the comma-separated string of the items extracted from the PHP array string.