Help needed urgently.

SomethingMAD · Apr 5, 2023, 3:23 PM

Hello , i want to remove specific things from this line, how do i do it?

I have this

event|email|order_id|order_number|order_name|first_name|last_name|subtotal|discount|line_item_price|tax|total|shipping_address1|shipping_address2|shipping_city|shipping_company|shipping_country|shipping_country_code|shipping_first_name|shipping_last_name|shipping_name|shipping_phone|shipping_province|shipping_province_code|shipping_address_zip|shipping_latitude|shipping_longitude|discount_code|created_at|time

I want this:

email|first_name|last_name|shipping_address1|shipping_country|shipping_phone|shipping_province|shipping_address_zip

Alan Kilborn · Apr 5, 2023, 3:20 PM

@SomethingMAD

Delete this piece of text??:

2.73090084888E11|3784306.0|#11140577-8|margaret|groseclose|32.95|0.0|32.95|0.00|32.95|

SomethingMAD · Apr 5, 2023, 3:24 PM

@Alan-Kilborn

Thanks for the fast reply, I edited it.

I need to do this on multiples lines.

It is a whole database.

PeterJones · Apr 5, 2023, 3:29 PM

@SomethingMAD ,

You’re still a little unclear. Does this mean you want to delete the first, third, fourth, etc field in a |-separated list for every line in the file? If so, please give the full list of field-indexes that need to be deleted.

SomethingMAD · Apr 5, 2023, 3:34 PM

@PeterJones Exactly.

Example:

lori.figaro@gmail.com|5.69272238158E11|4547490.0|#11811391_PRP|LORI|FIGARO|75.85|0.0|75.85|0.00|75.85|2622 AVALON FOREST CT||SPRING|Shipping|United States|US|LORI|FIGARO|LORI FIGARO|7132049425|Texas|TX|77386-2972|30.14205889999999|-95.40070209999999||2018-09-15 19:15:52.000|1537064180000|2018-09-01
stcstout@ruraltel.net|5.6932954939E11|4547618.0|#11811461|Tyna| Stoutimore |31.9|0.0|31.9|0.00|39.85|11 SW 2ND ST||PHILLIPSBURG|Shipping|United States|US|Tyna| Stoutimore |Tyna  Stoutimore |7855432781|Kansas|KS|67661-2609|39.7476562|-99.32758539999999||2018-09-15 22:53:47.000|1537077256000|2018-09-01
annijchaz@msn.com|5.69279643726E11|4547508.0|#11811400_PRP|Annette|Chavez|128.65|0.0|128.65|0.00|163.65|5812 TRAIL CT NW||ALBUQUERQUE|Shipping|United States|US|Annette|Chavez|Annette Chavez|5053071755|New Mexico|NM|87107|35.1414399|-106.6457214||2018-09-15 19:40:47.000|1537065705000|2018-09-01

From the file above I want to have only

email|first_name|last_name|shipping_address1|shipping_country|shipping_phone|shipping_province|shipping_address_zip

PeterJones · Apr 5, 2023, 4:45 PM

@SomethingMAD ,

Your data from your third post probably mismatches your column headers from the first post. (You have the “event” label over the “email” column; things that look like first and last names are in fields 5 and 6, but your #5 and #6 header are order-name and first-name, so header and data will not match in my example – this is the fault of your data)

I will give you a working example, and then you will be expected to generalize it to the actual columns you want.

I counted 30 fields, so the regex requires 30 fields in every row. It will not transform any line that does not have exactly 30 fields. For this example, I will retrieve columns 1, 5, and 6, and 12. I am hoping you are smart enough to expand the “replace” to be able to add more fields, or to change the field numbers – use ${##} to use the ##th field in the replace.

I start with data:

event|email|order_id|order_number|order_name|first_name|last_name|subtotal|discount|line_item_price|tax  |total|shipping_address1|shipping_address2|shipping_city|shipping_company|shipping_country|shipping_country_code|shipping_first_name|shipping_last_name|shipping_name|shipping_phone|shipping_province|shipping_province_code|shipping_address_zip|shipping_latitude|shipping_longitude|discount_code|created_at|time
lori.figaro@gmail.com|5.69272238158E11|4547490.0|#11811391_PRP|LORI|FIGARO|75.85|0.0|75.85|0.00|75.85|2622 AVALON FOREST CT||SPRING|Shipping|United States|US|LORI|FIGARO|LORI FIGARO|7132049425|Texas|TX|77386-2972|30.14205889999999|-95.40070209999999||2018-09-15 19:15:52.000|1537064180000|2018-09-01
stcstout@ruraltel.net|5.6932954939E11|4547618.0|#11811461|Tyna| Stoutimore |31.9|0.0|31.9|0.00|39.85|11 SW 2ND ST||PHILLIPSBURG|Shipping|United States|US|Tyna| Stoutimore |Tyna  Stoutimore |7855432781|Kansas|KS|67661-2609|39.7476562|-99.32758539999999||2018-09-15 22:53:47.000|1537077256000|2018-09-01
annijchaz@msn.com|5.69279643726E11|4547508.0|#11811400_PRP|Annette|Chavez|128.65|0.0|128.65|0.00|163.65|5812 TRAIL CT NW||ALBUQUERQUE|Shipping|United States|US|Annette|Chavez|Annette Chavez|5053071755|New Mexico|NM|87107|35.1414399|-106.6457214||2018-09-15 19:40:47.000|1537065705000|2018-09-01

FIND: ^([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)\|([^\v|]*?)$
- this is 30 copies of a regex which will capture a series of 0 or more characters with literal | characters between them (so 30 fields and 29 separators are required for this to work)
REPLACE = ${1}|${5}|${6}|${12}
- this does fields 1, 5, 6, and 12, separated by literal | characters

with your mismatched example header and example data , this shows up with the nonsensical answer:

event|order_name|first_name|total
lori.figaro@gmail.com|LORI|FIGARO|2622 AVALON FOREST CT
stcstout@ruraltel.net|Tyna| Stoutimore |11 SW 2ND ST
annijchaz@msn.com|Annette|Chavez|5812 TRAIL CT NW

But that’s technically those four columns. It is up to you to have data that matches each other.

This regex “works” for the data you gave and the column numbers I used for my example. If you want different or additional columns, it is up to you to learn from this example, and make it work with your real data. I will not be spending any more effort in this conversation.

As I said to you in your chat message, where you were trying to push on me to give you an immediate answer: this forum is made up of volunteers who are fellow users of Notepad++. You paid $0 for the software; you paid $0 for your account in this forum; you paid $0 for immediate support on this product. Any answer you (or anyone) gets is solely dependent upon the interest level and free time of the individual answering.

I love helping out here, and do try to make sure nearly every question here has a satisfactory answer, if it’s within my knowledge set and spare time, and if the questioner is cooperative and patient. But a given question will not always get an immediate answer – whether it’s from me or someone else – within 5 minutes, or 10 minutes, or an hour, or a day; and some, sadly, never get answered, for one reason or another.

Good luck.

----

Useful References

----

Please note: This Community Forum is not a data transformation service; you should not expect to be able to always say “I have data like X and want it to look like Y” and have us do all the work for you. If you are new to the Forum, and new to regular expressions, we will often give help on the first one or two data-transformation questions, especially if they are well-asked and you show a willingness to learn; and we will point you to the documentation where you can learn how to do the data transformations for yourself in the future. But if you repeatedly ask us to do your work for you, you will find that the patience of usually-helpful Community members wears thin. The best way to learn regular expressions is by experimenting with them yourself, and getting a feel for how they work; having us spoon-feed you the answers without you putting in the effort doesn’t help you in the long term and is uninteresting and annoying for us.

SomethingMAD · Apr 5, 2023, 5:08 PM

@PeterJones

This actually worked perfectly! Exactly what I wanted. I am sorry for messaging u privately and being so impatient.

Thank you!

guy038 · Apr 5, 2023, 8:52 PM

Hello, @somethingmad, @peterjones and All,

@somethingMad, here is an other alternative to the @peterjones’s solution. So, given this INPUT text :

lori.figaro@gmail.com|5.69272238158E11|4547490.0|#11811391_PRP|LORI|FIGARO|75.85|0.0|75.85|0.00|75.85|2622 AVALON FOREST CT||SPRING|Shipping|United States|US|LORI|FIGARO|LORI FIGARO|7132049425|Texas|TX|77386-2972|30.14205889999999|-95.40070209999999||2018-09-15 19:15:52.000|1537064180000|2018-09-01
stcstout@ruraltel.net|5.6932954939E11|4547618.0|#11811461|Tyna| Stoutimore |31.9|0.0|31.9|0.00|39.85|11 SW 2ND ST||PHILLIPSBURG|Shipping|United States|US|Tyna| Stoutimore |Tyna  Stoutimore |7855432781|Kansas|KS|67661-2609|39.7476562|-99.32758539999999||2018-09-15 22:53:47.000|1537077256000|2018-09-01
annijchaz@msn.com|5.69279643726E11|4547508.0|#11811400_PRP|Annette|Chavez|128.65|0.0|128.65|0.00|163.65|5812 TRAIL CT NW||ALBUQUERQUE|Shipping|United States|US|Annette|Chavez|Annette Chavez|5053071755|New Mexico|NM|87107|35.1414399|-106.6457214||2018-09-15 19:40:47.000|1537065705000|2018-09-01

with the regex S/R below :

(?xi)           ^    #  SEARCH regex in 31 lines
( [\w.@]*   )   \|   #  Field  01
( [\dE.]*   )   \|   #  Field  02
( [\d.]*    )   \|   #  Field  03
( \# \w*    )   \|   #  Field  04
( [\w ]*    )   \|   #  Field  05
( [\w ]*    )   \|   #  Field  06
( [\d.]*    )   \|   #  Field  07
( [\d.]*    )   \|   #  Field  08
( [\d.]*    )   \|   #  Field  09
( [\d.]*    )   \|   #  Field  10
( [\d.]*    )   \|   #  Field  11
( [\w ]*    )   \|   #  Field  12
( [\w.: -]* )   \|   #  Field  13 EMPTY
( \w*       )   \|   #  Field  14
( \w*       )   \|   #  Field  15
( [\w ]*    )   \|   #  Field  16
( \w*       )   \|   #  Field  17
( [\w ]*    )   \|   #  Field  18
( [\w ]*    )   \|   #  Field  19
( [\w ]*    )   \|   #  Field  20
( \d*       )   \|   #  Field  21
( [\w ]*    )   \|   #  Field  22
( \w*       )   \|   #  Field  23
( [\d-]*    )   \|   #  Field  24
( [\d.]*    )   \|   #  Field  25
( [\d.-]*   )   \|   #  Field  26
( [\w.: -]* )   \|   #  Field  27 EMPTY
( [\d.: -]* )   \|   #  Field  28
( \d+       )   \|   #  Field  29
( [\d-]*    )   $    #  Field  30

REPLACE $1|$5|$6|$12|$14|$16|$21|$23|$29

We get this OUTPUT text :

lori.figaro@gmail.com|LORI|FIGARO|2622 AVALON FOREST CT|SPRING|United States|7132049425|TX|1537064180000
stcstout@ruraltel.net|Tyna| Stoutimore |11 SW 2ND ST|PHILLIPSBURG|United States|7855432781|KS|1537077256000
annijchaz@msn.com|Annette|Chavez|5812 TRAIL CT NW|ALBUQUERQUE|United States|5053071755|NM|1537065705000

Practically :

Close the Find/Replace/Mark dialog if already opened
Select all the search regex, between (?x) and Field 30 included
Open the Replace dialog ( Ctrl + H )

=> The Find what : zone is immediately populated

Select all the replacement regex and paste it in the Replace with : zone
Uncheck all box options
Check the Wrap around option
Select the Regular expression search mode
Click once on the Replace All button

Of course, as I’m French, just verify if the appropriate columns are written after the global replacement !! Anyway, it shouldn’t be difficult to select the right columns, with the given search regex ;-)

Best Regards,

guy038