Read multiple text files into a Spreadsheet

[fatti]

I need to read the contents of 100 or more log files into a worksheet.

Each file is named differently (7 digit number). All are contained in the same folder.

Each file contains a set of information in a single column.

I would like to copy that information into a speadsheet so that the contents of the first file are placed in the first column, second sheet into the second column.

Now I can read a single file into the first column and assume I just need a loop to increment there the data starts but its the reading the second file name onwards im having an issue with.

I could probably use the "dir /B /O:N >list.text" function at the DOS prompt to generate the files to read but not sure if this is the way forward.

Has anyone done somethng like this before? I cant imagine this is anything out of the ordinary but have Googled until my fingers bled and found nothing that fits.

Any assistance you can offer would be much appreciated.

[Tinbendr]

Sorry, I didn't have time to finish this. You can record a macro converting the text to columns.

Please Login or Register  to view this content.

[fatti]

Mate this is awesome and worked straight off the bat with the exception of like you said to convert the text to columns as its placed the contents of each log file into the first cell on each column so its a big chunk of the way there.

Now converting that data to seperate cells in the same column is giving me issues as im not sure Excel recognises the delimiting character. You see the log files were just one big column of data anyway seperated by line breaks so they are easily readable.

The issue is Excel doesnt recognise this line break and has replaced that with a box with a ? in it. If i could replace this with a comma I would be set but it wont let me paste one of those characters into the Replace box. If I use the CODE function to determine what the character is it says its number 13 but when I use number 13 and do a replace the character is still there and inserts the comma beforehand as well.

Any ideas?

[Tinbendr]

Try this.

Please Login or Register  to view this content.