Forum Discussion
Replace parts of text that meets certain criteria in a text file
Hi Hans_Olsson
Thanks for the pictures. According to the few examples you posted earlier #OBJEKT strings appear to be formatted as below but with copy/paste on the Web we're never sure so please check this first with your actual data
The attached update highly/only relies on those consistent patterns (seems very consistent on your side according to your pictures :))
#OBJEKT<2 spaces>6<2 spaces>"2003"<1 space>"Kastrup 11.1"
#OBJEKT<2 spaces>25<1 space>"001"<1 space>"ŽTA 001"
Hi Hans_Olsson
Clear problem description + expected results :-))). With Get & Transform aka Power Query:
NB: This is highly dependent on the quality & consistency of the text between {}. Also, Power Query is case-sensitive so i.e. #TRANS <> #Trans and so far the In table is filtered on [String] starting with #TRANS (easy to adapt if necessary)
In attached file could you check that the Out table reflects what you expect against the In table
If fine add some more actuals in In > Right-click in Out > Refresh
(optimizations remain possible but I did not look at this specifically)
Lorenzo Thank you very very much. The attached file clearly does the job. There's just one minor glitch. It requires #TRANS to start in position 1. In the actual file the #TRANS-lines begins with 3 blanks and #TRANS starting in pos 4. If I remove the 3 blanks the query works like a dream.
This is the first time I'm in contact with Power Query. I don't manage to bring up the actual query, to see what the code looks like and add the three 3 blanks or even better, make the query look for #TRANS independent of where it starts.
If it's an easy change it would be very much appreciated if you could make it and attach the updated file.
Regardless, big thanks for this!
Doable no problem. Question though: do you want to keep the <spaces> before #TRANS in the Out table? Or do you prefer to remove those leading <spaces> in ALL records, or only on the #TRANS record?
- If possible the preferred solution is to keep the <spaces> before #TRANS in the Out table so beginning <spaces> in the In and Out tables are identical.
Going forward the current files (In table) will be made obsolete and the Out tables will be the new "current" files.Hans_Olsson This is embarrassing but the #TRANS-records are not the only type that needs to be modified. In addition the #OBJEKT 6 and #OBJEKT 25 records needs modification, too.
These are the declarations for the #TRANS "6" and "25" records already processed.
Current content Modify to #OBJEKT 6 "2003" "Kastrup 11.1" #OBJEKT 6 "00002003" "Kastrup 11.1" #OBJEKT 6 "1001-04" "Welcome Hotel" #OBJEKT 6 "00100104" "Welcome Hotel" #OBJEKT 25 "001" "ŽTA 001" #OBJEKT 25 "0001" "ŽTA 0001" #OBJEKT 25 "220" "ŽTA 220" #OBJEKT 25 "0220" "ŽTA 0220" This is the last addition. The case won't continue to grow after this.
I'm truly sorry I didn't provide the full picture from start.