Remove field of information in delimited text

Bowe Kurowski

Thank god for this forum.

I have an exported report that I’m turning into a delimited load file. I couldn’t limit the fields exported on the front end so I am having to do it on the back end.

Essentially, I want to delete anything between <field id="“1033806” and <field>. It’s a long text field and not consistent in length so I can’t just block and remove. It’s wreaking havoc on my load file.

Any easy way to do this?

All the best,
-Bowe.

Claudia Frank

Hello @Bowe-Kurowski,

may I ask if you are 100% sure you want it that way?
The reason of my surprise is that it looks like xml content on the first,
but on the other hand I’m missing something.
Are your double quotes correct?

<field id="“1033806”

and I would assume that it would look like (… represent the not needed text)

<field id="1033806" ... ></field>

It could be solved by using a regular expression find and replace but
therefore the data you provided need to be confirmed to be correct
before providing a possible solution.

Cheers
Claudia

Bowe Kurowski

Hi Claudia… Here’s the actual cut and paste -

Typically there is something between the “oldValue” markers. But I have like 12,000 records, so I can’t do it by hand.

This will help a TON! Thank you so much Claudia. I feel like such an idiot with this and can’t figure out the actual syntax to what I know is a very simple solution.

greenzest

From :

<field id="“1003668"” type="“4"” name="“Extracted Text”984849498484" formatstring="984984984984984"><oldValue></oldValue></field>i want this<field id="“1003668"” type="“4"” name="“Extracted Text”787557" formatstring="894984984984984"><oldValue></oldValue></field>and that? <field id="“1003668"” type="“4"” name="“Extracted Text”984849498484" formatstring="984984984984984"><oldValue></oldValue></field>also this<field id="“1003668"” type="“4"” name="“Extracted Text”787557" formatstring="894984984984984"><oldValue></oldValue></field>and more of that

To :

i want thisand that? also thisand more of that

Ctrl+F > Find what : <field id="“1003668"”.*?</field>, Replace with : leave empty
Dont forget to check Regular expression in Search mode, click replace all

Claudia Frank

Hi Bowe,

I would do it like this

given that string is

<field id="“1003668"” type="“4"” name="“Extracted Text”" formatstring=""""><oldValue></oldValue></field>

search and replace with regular expression

find what:(<field id="“1003668")(.*)(<\/field>)
replace with:\1\3

would result in

<field id="“1003668"</field>

but, as said, this results in an incorrect formatted xml string as the > is missing.

To solve this,

find what:(<field id="“1003668")(.*)(>)(.+)(<\/field>)
replace with:\1\3\5

which results in

<field id="“1003668"></field>

Ths id value looks still strange but …

Cheers
Claudia

Bowe Kurowski

Thanks… let me try playing around with this. I’ve tried to use both Claudia’s as well as Greenzest’s initial string in Notepad++, but it’s not finding it. Maybe I need to play around with the syntax. But Greenzest… you are spot on with what I’m trying to do. I just want to be able to cut and remove entire fields!

-Bowe.

Claudia Frank

Hello Bowe,

I’m confused first you said you want to delete everything between A and B,
which from my point of view means you wanna keep A and B and now you’re
saying you wanna delete the whole field ??

What did I miss?

Cheers
Claudia

Bowe Kurowski

Hi Claudia,

You are correct. In re-reading my initial question, I did ask specifically for that. However, I meant that I was looking for the front part of the string and the last part of the string, and then deleting the entire string altogether. I didn’t do a great job on communicating what I was looking for. So your answer was exactly what the question asked. You did not miss anything. It was completely my error in communicating :)

Claudia Frank

:-) thought that I didn’t understand the question, again - but then greenzest
solution should work.

Cheers
Claudia