Filtering nightmare

[Timoty]

Is there any way that I can import a text file and split the data into three columns without having to go through 65000 lines of text.

It is basically a print off of a directory structure. I have a very large text file that lists the files in folders for each of hundreds of users. I need to divide the information up into three columns so that the below data:

BOB\Songs

2003-07-15 12:10 PM 1,474,564 blue.mp3
2003-07-15 12:09 PM 858,204 orange.mp3
2003-07-15 12:10 PM 1,665,028 yellow.mp3

BOB\songs\happy

2005-08-23 03:01 PM 917,504 jolly.mp3
2003-07-15 12:09 PM 331,005 joker.mp3

BOB\files

2005-08-23 03:01 PM 917,504 writing.doc

ends up looking like this:

BOB \songs 1,474,564 blue.mp3
BOB \songs 858,204 orange.mp3
BOB \songs 1,665,028 yellow.mp3
BOB \songs\happy 917,504 jolly.mp3
BOB \songs\happy 331,005 joker.mp3
BOB \files 917,504 writing.doc

I easily imported the file into Excel, but it is still all in a column. Any advice would be appreciated.

 

[KeithG]

There is a program called Monarch that is used for this type of request. You could probably write a VBA funciton to parse the data.

 

[KeithG]

have you tried the text-to-columns feature in Excell? That may do the trick

 

[Timoty]

I will give both ideas a shot. The text to columns will help. The biggest problem is going to be getting the user's names in a colum by themselves.

 

[Timoty]

Thanks KeithG, the text to columns worked for me with a little tweaking. Saved me about a week of mind-numbing work.

 

[checker]

If you want to stay within access, there are some sample VBA codes here:
http://www.mvps.org/access/_vti_bin/shtml.exe/search.htm

Search for "parse" and they'll list several options for parsing a string into several columns.