Importing text

[omletto]

Hi,

This is a bit of a challenge so I understand if people don't have the time. Attached is an extract of a text file I am sent. I then import this into excel and after several hours of cutting and pasting I get it in the format on the spreadsheet attached. Is there a quick way of doing this ???

All help is appreciated.

Running excel 2010.

Regards,

Om

[mrc1386]

Hi - I used to do conversions like these for my old job.

How long do these reports (the text files) typically become? Are they just a few entries like this one, or do they become pages long?

If they are multiple pages long, do they have page breaks in the text file, or is it standard list the whole way down?

[omletto]

Standard the whole way down but up to 200 pages long. It takes hours at the moment.

I hate Text files.

[brokenbiscuits]

I tried, I failed.

See attached for my attempt, got very close based on the data in there, but the 'future' value for Keith Goodenough doesn't come otu where you'd expect it to once imported into Excel.

I think you need to see if you could get the text files delivered to you with a delimiter (for instance "|") between each data set. Then your job would be a whole lot easier.

[mrc1386]

I've got a template which works to convert the sample you posted, but only has 1 problem;

Excel doesn't properly paste accounts with only 1 category of due amount (for example - Kieth Goodenough only has a balance in the 'Future' column).

Could you upload a larger sample which I could test the template upon so that I can get a feel for the consistency of such accounts?

[omletto]

Unfortunately this is the only way I can get the data. Thanks for the help, attached is a larger sample.

R's,

Om

[mrc1386]

Good news - I've created a template that is able to convert your data in under 1 minute

Unfortunately, the file size is 10MB, and I do not have WinZip on my computer to make the file attachable.

So: take THIS attachment, open the OUTPUT worksheet, copy A2:L2 and paste to A3:L5000. Its a lot of logic there, which is making the file size too big to post. You'll need about 5,000 rows to do your data set.

Else I can just post the real thing when i get home in about 5 hours.

Best,

~Coleman

[omletto]

Hi Coleman,

Sorry its been a while, just come back off hols. Thanks for your time.

I have tried what you attached but no joy. After setting line breaks, do i need to do anything else?

R's

Om

[omletto]

Just retried it and it works perfectly. Thanks so much for your help.

Kind regards,

Om.

[omletto]

I can't mark as solved. Is this because the post is too old? If so sorry for delay.

R's

Om