Importing awkward data

[Dannis]

Hi Guys/Girls

Could anyone tell me how i can import the folowing data into Excel.

just 2 records here loads more (duh), i would like to create a nice table with category, Code, ect.

Thanks All


Category: NL.Dannis.Test

Code: Dannis01

Last Modified: 28/07/2010 11:30:12 By: D, Dannis

Service Type: Dannis.CatchAll

Children Allowed:YES

Resp. Org.:

Resp. Group: NL.Dannis

Resp. Assignee:

Description: NL.Dannis.Test


Properties

Sequence Label

--------------------------------------------------------------------------------

(none)


Workflow Tasks

Sequence Task Assignee

--------------------------------------------------------------------------------

(none)


================================================================================


Category: NL.Dannis.Test

Code: Dannis08

Last Modified: 28/07/2010 11:30:12 By: D, Dannis

Service Type: Dannis.CatchAll

Children Allowed:YES

Resp. Org.:

Resp. Group: NL.Dannis

Resp. Assignee:

Description: NL.Dannis.Test


Properties

Sequence Label

--------------------------------------------------------------------------------

(none)


Workflow Tasks

Sequence Task Assignee

--------------------------------------------------------------------------------

(none)


================================================================================

 

[asa]

Hello Dannis,

I'm pretty sure I saw a tip in the comments to one of Chandoo's articles the other day that said you can use a line break as a field separator for data import.


To do so, where you specify your delimiters, choose "other" and pres ALT-010 (010 on the numpad) to specify a linefeed character.


That said, it seems to me that that would treat the whole file as one record, if it works, and create a new column for each line. those lines would still have the field names and a colon before them...


So, if that won't work, I think you have two options. One: Write (or find) a VBA macro to do it. Two: Import the text just as-is, and massage it into the right form using a combination of text to columns, formulas, and filtering out unwanted data (which could also be done with formulas).. then copy your massaged data and paste it with paste-special--values to eliminate the formulas.


For a manual/formula approach, check out this book excerpt from MrExcel. He steps through it starting on page 465, and it looks like it is pretty much a ready made solution for you:

http://www.mrexcel.com/manyrows.pdf


For a VBA approach, here are links to routines that read a text file and import it. They do not specifically handle multi-line records, so you would have to make a couple modifications that I think would be minor for it to do so.

http://www.cpearson.com/excel/ImpText.aspx

http://www.cpearson.com/excel/ImportBigFiles.aspx


Here's A multi-line routine, but for data formatted differently than yours:

http://www.mrexcel.com/forum/showthread.php?t=568267


Here's a program for doing these kinds of imports, not free. $19 for a one-day license, then works it's way up to $149 for an unlimited license:

http://www.beside.com/sample1.html


If you want to use VBA, and you need more help, I can help you write the routine to parse your records out.


Asa

 

[Jack]

asa you are incredible, that is such a 1st class and complete answer. The first Mr Excel solution is exactly Dannis' situation. Mr Excel certainly is a pro what a classic approach. I really liked this problem as often reporting output from core systems have wrap around output or multiple lines per transaction that this equally applies to.

 

[asa]

Thank you John!


I was impressed with the well-written chapter from MrExcel, too, and it certainly is the kind of practical approach that really is what Excel is all about. Excel excels at the "custom solution" and that chapter not just solves this problem but teaches some great problem-solving skills, if you follow along, that can be applied to various problems.


VBA is great when the task is time consuming by hand and needs to be done repeatedly, or for the rare situation where a non-VBA solution can't be found or is too slow (to calculate, etc.). VBA is also handy if you are a programmer distributing solutions to clients and want to implement some polish to your design in ways that can't otherwise be done.

 

[Dannis]

Thanks asa!

The MrExcel sollution workes great.

I will have a go at the VBA just for the fun of it, if i cant work it out i'll know were to go

Thanks again like John said first class answer!


Dannis