Complicated Find & Replace on a CSV

[Chunkford]

...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.

To extract just the file name, create a new column next to the column with the image file paths and paste this code in to it.

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

Then change the cell references in red to where the image file paths is located on the same row. Drag it down and hey presto you have your file names

[Mike Hughes]

Ha, yes, but he only wants to do this if NOT_CATALOG is in the path. My second bit of code posted above should do it.

Mike

[Chunkford]

"/"

I think the OP has these the wrong way round Mike Not sure tho but it's the other way round for a file path

[Mike Hughes]

I thought so too, but was just giving him the code that does what he asked for.

It's written to leave stuff alone unless it find's what's being looked for. Less harmful that way.

Mike

[Chunkford]

Ha, yes, but he only wants to do this if NOT_CATALOG is in the path. My second bit of code posted above should do it.

Mike

Ha, i see. This should do it then

=IF(ISNUMBER(SEARCH("NOT_CATALOG",A1)),MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)),A1)

Remember to change the cell references in red

[Chunkford]

I thought so too, but was just giving him the code that does what he asked for.

It's written to leave stuff alone unless it find's what's being looked for. Less harmful that way.

Mike

Fair point Mike, will remember that one

[Mike Hughes]

It's certainly shorter than mine.

I wonder if either of them will work?

Mike

PS.

Fair point Mike, will remember that one

Macros that overwrite stuff can be lethal. At least your way with a new column leaves the original data alone. Mine goes in and hacks it to bits, hence my tendency for caution. Three times bitten and all that stuff.

[Chunkford]

Macros that overwrite stuff can be lethal. At least your way with a new column leaves the original data alone. Mine goes in and hacks it to bits, hence my tendency for caution. Three times bitten and all that stuff.

There's been many of time that i've ran a macro and forgotten to save it before hand . The magic 'ctrl z' can't help you there

[leehack]

A find and replace will only replace if it finds what you tell it to, otherwise it does nothing. Either a jackhammer is being used to open up an M&M here or I have seriously lost the plot. A find and replace finds what you specify and changes it to what you tell it to when it finds that. It doesn't touch anything without the string. I'm still at a loss as to why a CTRL+H is not the perfect solution here?

[Golf Tee Warehouse]

Lee, I think the reason the standard Find & Replace is not suitable is because the OP is trying to remove more then just \NOT_CATALOG\ and is trying to remove the whole file path except the filename, although this was not made clear in the original post.

[leehack]

Ah that makes a bit more sense now. Excel formulas it would be for me too then. I'd count the length of the string when not_catalog is present, also the location of the last "/" and then extract from the string all characters from the (location+1) position.

[fergusw]

My method of choice would either be as Norman suggested already; to use regular expressions in notepad++, or it would be to write a simple VBS script which opened the csv, read each line, found the field in question, performed the replace on an (IF "/NOT_CATALOG/" exists) condition, and then spit the results back out.

example here: http://www.dbforums.com/visual-basic...text-file.html

e.g.

dim fso, readfile, contents, writefile, work 
dim FullFileName, ParentDirName, Filename, NewFilename 
set fso = CreateObject("Scripting.FileSystemObject") 
FullFileName="c:\myCSVFile.csv" 
NewFilename="c:\myNewCSV,csv"
set readfile = fso.OpenTextFile(FullFileName, 1, false) 
set writefile = fso.CreateTextFile(NewFileName, 2) 
Do until readfile.AtEndOfStream = True 
     contents = readfile.ReadLine 
If (Instr(contents,"NOT_CATALOG") > 0) then
           contents = Replace(["here you would have the relevant instrRev or Instr functions to find the "/" required to replace from"])
End if
     writefile.writeLine contents 
loop 
readfile.close 
writefile.close
set fso=nothing

[Chunkford]

The title of this thread is defiantly living up to it's name LOL

[Chunkford]

use regular expressions

They scare me I have nightmares about them

[NormanRouxel]

I read this as you have data in an Excel column like:

\NOT_CATALOG\mydir\mysubdir\durble.jpg


Where the mydir\mysubdir bit could vary a lot.

And you want:

durble.jpg

Here's how to do it using Notepad++

In Excel, copy / paste the column containing these files to the clipboard.

Paste into Notepad++.

We should now have a list of filenames like.

\NOT_CATALOG\mydir\mysubdir\durble.jpg
\NOT_CATALOG\mydir\mysubdir2file\wurble.jpg

Now in NotePad++ click Control/F and select Replace.

Set Find what to \\NOT_CATALOG\\.*\\
Set Replace with blank
Tick Regular Expression

Click Replace All.

All should change to strip the everything but the filename. E.g.

durble.jpg
wurble.jpg

Now copy to clipboard, go back into Excel, click the first entry in the filename column and do Paste.

Repeat for any other Excel columns that might contain such filenames.

NOTES:

If you want to add a sub-folder path to these resulting file names then set

Replace with to SubFolder\

And we'll end up with

SubFolder\durble.jpg
SubFolder\wurble.jpg

TECHNICAL NOTE:

I'm doing this on individual Excel columns, rather than directly on the .CSV file as the Notepad++ regexp engine does not support shortest length matching and we cannot risk this on lines that might contain more than one \NOT_CATALOG\ filename as we'd inadvertantly gobble up all the text between the first \NOT_CATALOG\ and the final ones filename.