SQL on Excel - split column based on delimiter

[AppDeveloper1]

Good Day!

We have a need to process an excel with a large dataset (65000+ records) and one column that contains comma-separated information. The comma-separated data in the column need to be converted to unique data. SQL query using Split function is erroring out. 

Any guidance on this SQL query would be helpful.

Source Excel column:

Expected output:

[Agnius]

Can you please provide the details of the error?

Also, would it be a problem to split the column after you've retrieved the data?

[AppDeveloper1]

Thanks for responding!

The error is that Split function is undefined. I see that some of the common SQL functions are not supported in PAD when queried against Excel.

I am open to splitting the column after retrieving it as well. Please share any ideas. Also, know that Excel has more than 65000 records, and reading through each row of data takes hours to finish. The approach of SQL is to be efficient. 

[Agnius]

I assume that you use SQL to retrieve filtered data and so the data table that you retrieve has less than 65.000 records. If so, what you could do is create a new data table with more columns, then loop through your data from SQL, processing each record one at a time. Then use Split text on the column you want to split and Insert row into data table to insert a new row into the new data table, where you use the outputs of Split text to populate two columns, and the values from your original SQL output for other columns.

This would result in what you need and would be okay, assuming that your SQL output is not very large.

It is very likely that SQL queries to Excel are limited. And it's not a limitation of PAD, but a general limitation of the driver for running SQL queries to Excel.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[AppDeveloper1]

As I mentioned earlier, there is no filtering of data and the source remains to have 65000+ records. Hence the proposed approach would not work best for us. Thank you though.

[Agnius]

Well, you could use Retrieve data table column into list to get your column into a list variable. Then use Join text to join the list into a single string with a new line as a delimiter. You could then use Parse text with regular expressions enabled and making sure that it retrieves all matches instead of just the first one to retrieve the values into two separate lists.

The following regex would return the first part: .+(?=,) (everything except for newlines that is followed by a comma) 

The following regex would return the second part: (?<=,\s).+ (everything except for newlines that follows a comma and a whitespace).

You'll end up with two lists instead of one.

The problem with this approach is that you cannot really insert a list into a data table as a column. But you can write a list into Excel, if that's the output you need.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[AppDeveloper1 · Accepted solution]

I resolved this by creating excel macro script to do the operation. This turned out to be efficient and improved performance while handling large set of data.