Complicated Find & Replace on a CSV

[eighteentee]

Hi all,

Just wondering if anyone could point me in the right direction for editing a CSV. Basically the find & replace dialog in Excel just isn't good enough as I need to do a find and replace to get rid of all the \NOT_CATALOG\ entries. Here's the deal-breaker: these NOT_CATALOG entries largely have different folder names preceeding the actual image name. There are over 9,000 lines in this file and going through by hand isn't an option.

Is there a way to do a wildcard search somehow?

Cheers

[knightrideruk]

Complicated Find & Replace on a CSV

Hi

Download 'ASAP Utilities' and you can then use their comprehensive 'Find and Replace routines'

[Rich Brady]

Hi all,

Just wondering if anyone could point me in the right direction for editing a CSV. Basically the find & replace dialog in Excel just isn't good enough as I need to do a find and replace to get rid of all the \NOT_CATALOG\ entries. Here's the deal-breaker: these NOT_CATALOG entries largely have different folder names preceeding the actual image name. There are over 9,000 lines in this file and going through by hand isn't an option.

Is there a way to do a wildcard search somehow?

Cheers

Why can't you do a find: \NOT_CATALOG\

Replace with: \

Or have I mis-read the OP?

[NormanRouxel]

I use open source Notepad++ as my text editor. This can do a global find replace (whick every editor can).

The magic bit is that it also allows you to use Regular Expressions in these commands so that it's possible to look for and replace patterns rather than exact text.

Tutorals abound on the web regarding Regular Expressions.

[eighteentee]

Why can't you do a find: \NOT_CATALOG\

Replace with: \

Or have I mis-read the OP?

...because if I do that it'll get rid of the filename too and just replace everything with a "\". What I need to do is delete everything BUT the filename in that field.

[eighteentee]

I use open source Notepad++ as my text editor. This can do a global find replace (whick every editor can).

The magic bit is that it also allows you to use Regular Expressions in these commands so that it's possible to look for and replace patterns rather than exact text.

Tutorals abound on the web regarding Regular Expressions.

Nice one Norman - just checking it out now...

[Duncan Rounding]

Replacing \NOT_CATALOG\ will not remove the filename as well.
I also use Notepad++ extensively for all code writing and editing - try it.

[Rich Brady]

I'm certain I have done something similar. Sorry it's not working for you.

[eighteentee]

Replacing \NOT_CATALOG\ will not remove the filename as well.
I also use Notepad++ extensively for all code writing and editing - try it.

Just to clarify, the vast majority of the lines have NOT_CATALOG/foldername/anotherfoldername/etc/etc/ect/image.jpg

all with varying folder names. A search and replace won't do it because the only 'constatant' thing between them all is the NOT_CATALOG folder name, everything else is deifferent line-by-line.

[Rich Brady]

I just did this as a test in Excel. Make sure "Match Case" is NOT checked

[eighteentee]

I just did this as a test in Excel. Make sure "Match Case" is NOT checked

That doesn't work for me; it simply replaces the NOT_CATALOG bit and none of the other stuff (all the different folder names) after it AND BEFORE the filename.

[Mike Hughes]

I assume the image references are all in the same column?

If so, I would do this with a simple macro to loop through all the lines and clear the cell if \NOT_CATALOG\ is found in the contents.

Mike

PS. Make sure you have an end condition to avoid a continouous loop. I've done that before.

Edit: Something like this will do it (untested so save a backup copy before running)

Sub clearnotcatalog()

Dim imageref, endtest As String
Dim n, found As Integer

' clear cell contents if NOT_CATALOG found in col x
' set x in code to the applicable column
' routine stops at first row with empty first column

n = 1
endtest = Cells(n, "A").Value

Do Until endtest = ""

imageref = Cells(n, "X").Value
found = InStr(imageref, "NOT_CATALOG")
If found > 0 Then
Cells(n, "X").Value = ""
End If

n = n+1
endtest = Cells(n, "A").Value

Loop

Cells(1, "A").Select

End Sub

[eighteentee]

I assume the image references are all in the same column?

If so, I would do this with a simple macro to loop through all the lines and clear the cell if \NOT_CATALOG\ is found in the contents.

Mike

PS. Make sure you have an end condition to avoid a continouous loop. I've done that before.

Edit: Something like this will do it (untested so save a backup copy before running)

Will this keep the actual image filename or literally just clear the contents of the cells?

[leehack]

Why don't you give an example of what one line is now and what you want it to be after, something very simple and easy to do seems to be turning into an everest climb.

If you're maintaining the same folder structure, just moving them into the right place, all you need is the replace facility.

[Mike Hughes]

Will this keep the actual image filename or literally just clear the contents of the cells?

As it is above, it will just clear the cell.

If you want to keep the filename only, then you need something like this:

Sub clearnotcatalog()

Dim imageref, endtest As String
Dim n, found, folder, reflength, newlength As Integer

' clear directory paths if NOT_CATALOG found in col x
' set x in code to the applicable column
' routine stops at first row with empty first column

n = 1
endtest = Cells(n, "A").Value

Do Until endtest = ""

imageref = Cells(n, "X").Value
found = InStr(imageref, "NOT_CATALOG")

If found > 0 Then
folder = InStr(imageref, "/")
Do Until folder = 0
reflength = Len(imageref)
newlength = reflength - folder
imageref = Right(imageref, newlength)
folder = InStr(imageref, "/")
Loop

Cells(n, "X").Value = imageref
End If

n = n+1
endtest = Cells(n, "A").Value

Loop

Cells(1, "A").Select

End Sub

This version, looks for NON_CATALOG and if it finds it, repeatedly cuts the path down to remove everything before (and including) a "/" until there none left and then writes that back to the cell

As before, always keep a backup safe.

Mike