Splitting a string into a table

Like (0) Share

Report

Posted on 14 Mar 2023 18:46:05 by CMSGuy

170

Here is some sample data that is being returned by a flow for the power app:

HALLE CHRISTOPHE EDWARDSON###80110720###D/E-PROTECTION FROM HAZARDS###46369;HALLE CHRISTOPHE EDWARDSON###80110722###D/E-PROTECT MIN COVER NOT MET###46170;

HALLE CHRISTOPHE EDWARDSON###80110724###D/E-ANODE & ANODE GRND BED CATH###45667;

I split the string by ";" and it seems to work fine as I can see each "record" in the Value from the Split. But what I need to do now is to create a table with these records. So in the above example, I need a table such as:

Name	Qual ID	Qual	Date
HALLE CHRISTOPHE EDWARDSON	80110720	D/E-PROTECTION FROM HAZARDS	46369
HALLE CHRISTOPHE EDWARDSON	80110722	D/E-PROTECT MIN COVER NOT MET	46170
HALLE CHRISTOPHE EDWARDSON	80110724	D/E-ANODE & ANODE GRND BED CATH	45667

What would be the best way to do this? Also, the "Date" field are those Excel Date Values that I believe I can convert at the power automate level, or is there a way to do that here?

Thanks for the information

Categories:

General Questions

All responses (3)

Answers (2)

WK-23070617-0 on 05 Jun 2024 at 14:34:19

Like (0)

Report

Re: Splitting a string into a table

Great, took me a while to figure it out and to adapt it into my solution.
Verified answer

iAm_ManCat 18,206 Most Valuable Professional on 14 Mar 2023 at 20:29:27

Like (1)

Report
Re: Splitting a string into a table

Hey @CMSGuy

Took a bit of experimenting to get it right, but we can essentially double split it and remove blanks, then for each split-split we trim out non-valid characters like line feed and carriage return using Regex matching, and then for the Qual we keep all the Other special characters

Code for that button (which just sets a variable - that I then use as the Items for that gallery):

Set(gblSplitTable, ForAll( RemoveIf(Split(Trim(TextToCheck.Text),";"), Value="") As OuterColumns, With( { InnerColumns: Split(OuterColumns.Value, "###") }, { Name: Concat(Split(Last(FirstN(InnerColumns.Value, 1)).Value, ""), If(IsMatch(Value, "[a-zA-Z0-9 ]"), Value)), QualID: Concat(Split(Last(FirstN(InnerColumns.Value, 2)).Value, ""), If(IsMatch(Value, "[0-9 ]"), Value)), Qual: Concat(Split(Last(FirstN(InnerColumns.Value, 3)).Value, ""), If(IsMatch(Value, "[ a-zA-Z$&+,:;=?@#|'<>.^*()%!/-]"),Value)), Date: If(Len(Concat(Split(Last(FirstN(InnerColumns.Value, 4)).Value, ""), If(IsMatch(Value, "[0-9 ]"),Value)))>0, // Dates as Numbers in Excel use 1899-12-30 as initial to add to // (so subtract 2 from 1900-01-01 [earliest PowerApps Date function value]) Text(DateAdd( Date(1900, 1, 1), Value(Concat(Split(Last(FirstN(InnerColumns.Value, 4)).Value, ""), If(IsMatch(Value, "[0-9]"),Value))) - 2, TimeUnit.Days), "[$-en-US]dd/mm/yy" ), Blank()) } ) ) )

Have also attached a proof of concept app where you can play around with what I've created and transfer what you need to your code 🙂

Cheers,

Sancho

Split Split Regex...
Verified answer

AaronKnox 512 Super User 2024 Season 1 on 14 Mar 2023 at 20:19:45

Like (1)

Report
Re: Splitting a string into a table

Hi @CMSGuy ,

The solution is to ForAll() through your initial Split() and construct the table via Collect(). To convert the Excel date you need to use the DateAdd() function. To prevent a blank line I added the With() to trim the last semicolon. Putting it all together:

With({_OriginalString: "HALLE CHRISTOPHE EDWARDSON###80110720###D/E-PROTECTION FROM HAZARDS###46369;HALLE CHRISTOPHE EDWARDSON###80110722###D/E-PROTECT MIN COVER NOT MET###46170;HALLE CHRISTOPHE EDWARDSON###80110724###D/E-ANODE & ANODE GRND BED CATH###45667;"}, UpdateContext({locStringFromFlow: Left(_OriginalString,Len(_OriginalString) - 1)}) ); Clear(colTransposeStringToATable); ForAll(Split(locStringFromFlow,";"), Collect(colTransposeStringToATable,{ Name:Index(Split(ThisRecord.Value,"###"),1).Value, 'Qual ID':Index(Split(ThisRecord.Value,"###"),2).Value, Qual:Index(Split(ThisRecord.Value,"###"),3).Value, Date: Text( DateAdd(Date(1900,1,1), Index(Split(ThisRecord.Value,"###"),4).Value -2 ),DateTimeFormat.LongDate) } ) )

Hope this helps!

Aaron