"convert" data with logfile structure to spreadsheet structure

**boarders paradise** · 01-04-2011, 03:23 AM

Hi folks.

This must be one of the top 10 classic Excel problems:
How to automate the import of non-table-data into an Excel table?

I have a logfile listing my mobile phone's calls and SMS. Each entry is separated with a "----------" line.

Please Login or Register  to view this content.

Now what I want to do is import all the data into a table.
The result should look like the attached image.

What must I do?
Thank you so much.

**JBeaucaire** · 01-04-2011, 04:52 AM

Try this:

Please Login or Register  to view this content.

Edit the InitialFilename string to the path where your files are most likely to be.

Also, I set the filter to text files, you can edit that to whatever extension your data file usually has.

**boarders paradise** · 01-04-2011, 06:12 AM

Thank you so much, JBeaucaire!
OMG, I just can't believe that you actually wrote some code just for my problem!!

I am incredibly thankful and will try this out in a moment and will post the results.

But does this mean, that the only way this can be done is in a programming language? I cannot resort to standard Excel manipulations somehow?

**martindwilson** · 01-04-2011, 07:41 AM

like this with functions
i havent bothered to hide #VALUE! but it is easily done if required

**JBeaucaire** · 01-04-2011, 12:47 PM

My macro is actually creating the table for you using worksheet functions, I just put the formula into a macro.

This is the formula I put in B2 and copy down and across as far as needed, then I remove the formulas and flatten the values:

=SUBSTITUTE(INDEX($A:$A, ((ROW(A1)-1)*15)+COLUMN(B1)),B$1&"=","")

Try that...just put your data in column A, then that formula in column B starting at B2, then copy the cell...the rest of the data reformats.

Lastly, the macro version just deletes the column A raw data.

**boarders paradise** · 01-04-2011, 05:12 PM

thank you! I gave rep points to both of you.

It will take me a bit to familiarize myself with the concepts, but I will definitely give feedback here once I have figured it out. Thank you so much.

**JBeaucaire** · 01-05-2011, 03:08 AM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**boarders paradise** · 01-10-2011, 02:06 AM

thank you JBeaucaire and martindwilson.
you really had clever ideas. As far as I can see this does exactly what I need.
I always mark as "solved".
Just give me a little time to look through your instructions and help.
Thank you.

(EDIT: deleted some confused stuff I wrote before putting my specs on :-)