RegEx Question

[stevenlsj789]

Hi I wanted to asked for some help on how to use Regular Expression to Describe these two cell so I can use Reg Ex to parse the data. It's still relatively new to me after going through some crush course available in the forum here. 

The first item is "Beginning G/L Balance, 100 -  Cash" 0.00

And the second item is "07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond  - C" "04C" 0.01 0.00 0.01" 

I have a text file full of these two items above that I am struggling to use RegEx to parse the data. 

What i had tried for the first one was" (\".*),\s+(\d{3}\s+\-\s.*\")+(\(*\d+\.\d{2}\)*)' but apparently it didn't work....

Thanks for your help in advance. 

[MarqueeCrew]

Can you please post data as:

Data vs desired results?

Thanks,

Mark

[stevenlsj789]

@MarqueeCrewThanks for the response. See data below. I wanted to separate them by each column (i.e. Book Date, Trans # and then all the way to Balance PC") So I needed help with the Reg Ex here.

[MarqueeCrew]

Sorry, I'm answering via my phone. I won't see xlsx data until tomorrow. If you have text to show, I can try now.

[stevenlsj789]

@MarqueeCrew Hi Mark- the text file is quite large so I cannot type all here. but it looks like this.

"General Ledger to To Transaction Reconciliation (All Accounts)"

"100 - Cash"

"07/01/2018 through 07/20/2018"
"Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

"Beginning G/L Balance, 100 - Cash" 0.00
07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

[MarqueeCrew]

Sorry, I forgot to look for this today. When you get the text, what are you expected to do with it?

[stevenlsj789]

@MarqueeCrew I wanted to parse the data from this text format below 

"General Ledger to To Transaction Reconciliation (All Accounts)"

"100 - Cash"

"07/01/2018 through 07/20/2018"
"Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

"Beginning G/L Balance, 100 - Cash" 0.00
07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

to include only the following information and i wanted to have the data in individual column (i.e. "Book Date" with the dates in one column, and that applies to all columns to "Balance PC") I wanted to do that so I can play around with the data, right now i am stuck at the data parsing phrase. Thank you!

Header "Book Date" "Trans #" "Trans. Code" "Short Name" "SEC_NAME" "Portfolio" "Debit PC" "Credit PC" "Balance PC"

Description"Beginning G/L Balance, 100 - Cash" 0.00
Data 07/02/2018 20180627054175 "IssueFunds" "1349" "JH Flexible Bond - C" "04C" 0.01 0.00 0.01
07/02/2018 20180628011906 "Buy" "89175VAA1" "TPMT 2018-2 A1 3.25% 03/25/2058" "04" 0.00 866,969.78 (866,969.77)
07/02/2018 20180628011946 "Buy" "15135BAH4" "CENTENE CORP 6.125% 02/15/2024" "04" 0.00 933,778.85 (1,800,748.62)

[MarqueeCrew]

Please email me your availability for Monday or Tuesday at 4:00 Eastern for a WebEx. I will send to your email.

[stevenlsj789]

@MarqueeCrew monday @4PM ET works for me. email address : steven.li@pwc.com

Thanks! 

[GarthMiles]

@stevenlsj789

is this what you're looking for?

it's just my opinion but i'd recommend against writing a complex regex expression and, instead, go with a sequence of tools to do the work. there's significant upside to going this route--perhaps none more important than saving your future self from spending hours supporting/maintaining said work in the event you share it with others.