text string to column

[John Connor 0]

Hello good people.

Last year one of my hard drives started failing and as quickly as I could made a copy of all its content to another hard drive. I used an app called unstoppable copy that basically bypasses all the errors and forces the copy. I also set it to generate a log file. So today I tried importing the log to the original application and found that there’s no option to load/import the log file. So I’m left with a text file that follows the following pattern:

source file location -> target file location - status
E:\Imágenes\deaktop.ini -> G:\Imágenes\deaktop.ini - Copied Successfully

I’d like to take all that data to a table-like document.
I already tried with Excel and found its text to columns command to be too dumb this job.
Or maybe I am the dumb! Please help me!

Daniel

[Terry R]

@John-Connor-0 said in text string to column:

So I’m left with a text file that follows the following pattern:

Can you provide a couple of example lines and this time once inserted in your post, select the lines and click on the </> button above? This will put the lines in a black box, which prevents the data from being mangled by the posting engine. It’s important as your 1 example includes -> which I’m not sure exactly what the character is (maybe it is exactly as you typed or maybe not). By doing this it allows us to copy the examples and test out solutions before giving it to you.

Terry

PS, also include the same example lines again (in a second black box) showing what you’d like the lines to change to.

[John Connor 0]

@Terry-R
Hey thanks for answering so quick. Here’s the example you asked for.

E:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\12Trancision con las imagenes\12. Elegant Slideshows.flv -> G:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\12Trancision con las imagenes\12. Elegant Slideshows.flv - Copied Successfully
E:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\12Trancision con las imagenes\Thumbs.db -> G:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\12Trancision con las imagenes\Thumbs.db - Copied Successfully
E:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\10Recortan al tipo para que tire con el arma\10. Basic Color Keying.flv -> G:\Knowledge\Adobe After Effects\Andrew Cramer - Video Copilot\10Recortan al tipo para que tire con el arma\10. Basic Color Keying.flv - Skipped (Target - The file exists.)
E:\Knowledge\Adobe Illustrator\Lynda.com - Illustrator CS6 One-on-One Complete (all 4 courses)\1 - Illustrator CS6 One-on-One - Fundamentals\4 - Making Line Art\4-7 Drawing a target with the Polar Grid tool.mov - Skipped (Source - The request failed due to a fatal device hardware error.)

[Terry R]

@John-Connor-0 said in text string to column:

Here’s the example you asked for.

That’s good but also need to know what you want it to look like so you can use the data. as you say you can possibly import it into the original app, but what format would that be? Or if importing into Excel, do you possibly want a CSV file (comma separated value)? And if CSV, how many fields do you want it split into and where would the splits be?

Terry

[John Connor 0]

@Terry-R
I just figured out I could do the following:
I’m ok with Excel importing coma separated values. So all I have to do is to replace the current character delimiters with a “,”
But there’s a problem (as usual). As you can see in the example there are files that use the text " - " in both source and target. It is also used as delimiter to separate the status column (Copied Successfully)
I think that a good strategy could be to only replace the last occurrence of the text " - ". Then replace all the " -> " ones and then take the file to Excel.

What do you think? Can that be done?

Thanks so much
Daniel

[John Connor 0]

@Terry-R

source file location -> target file location - status

these two are the delimiters to be replaced for “,”

" -> " and the last occurrence of " - "

[Terry R]

@John-Connor-0 said in text string to column:

I’m ok with Excel importing coma separated values.

Sorry just posting now, before your very latest post. Have a go with what I’ve shown and we can go from there.

I’ve taken a stab at it to create a CSV file with the source location inside quotes, the Destination Drive in a second quote and the success (or failure) result inside a third set of quotes. This is just a rough go, and likely once you spend a bit of time on the data you may well find it needs some modification.

Anyways, use the “Replace” function and as this is a regular expression use the search mode set as “regular expression”
Find What:(?-s)^(.:)(.+?) -> (.:)\2 - (.+)
Replace With:"\1\2","\3","\4"

You can click on Replace All. I suggest running this on a copy of the data.

Come back to us with the results and what problems if any occured so the solution can be better modified.

Terry

[Terry R]

@Terry-R said in text string to column:

Find What:(?-s)^(.:)(.+?) -> (.:)\2 - (.+)
Replace With:“\1\2”,“\3”,“\4”

To give some background this is the description of what I’ve done.
In the Find What field:
(?-s)^ - the DOT (.) character does not include EOL characters, followed by start at the start of the line
(.:) - capture 1 character and the : character
(.+?) - capture some characters, this stops at the “arrow” due to the next sub-code below
-> - find a space then the pseudo arrow and another space
(.:) - same as above, this is the destination drive
\2 - i have assumed the destination folder is the same as the source, only drive letter changes. this way we capture up until the final piece we need, the success or failure result
- find 2 spaces surrounding the - character
(.+) - find the success or failure (right up until the EOL)

The \1, \2, \3 and \4 refer to the respective capture groups in the find what field, those with () surrounding them.
Terry

[John Connor 0]

@Terry-R you are a genius, and my hero. So far it’s working Thanks so much!

[Terry R]

@John-Connor-0 said in text string to column:

So far it’s working Thanks so much!

I’ve just noticed that my solution doesn’t do one of the example lines (sorry missed that on the first look).

Not to worry as these lines weren’t affected by the first regex (regular expression) as there was no pseudo arrow character in these lines. So to edit these lines and have the resulting fields line up with the other altered fields I have the following. Same as before, use Replace and set the search mode to regular rexpression.
Find What:(?-s)^(.:)(.+?) - (Skipped.+)
Replace With:"\1\2","","\3"

Important, use the first solution first, then this afterwards otherwise erroneous results will occur. This one places an empty field where the destination drive letter would otherwise be, thus keeping the success or failure result as the third output field.

Terry