• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1306
  • Last Modified:

Convert RTF to XLS - Freeware

Hi, i'm just looking for a way to convert RTF to XLS files..  If anyone knows an easy customizable freeware way then please say so, otherwise read my attempt below:

I've got a solution at the moment where I have an RTF file with headers and data in tables..  I open this document with wordpad, select all the text and copy and paste it into excel.  This puts each header and each item of data on it's own seperate line.. It's easy up until this point, but the way the text comes out when I paste it in excel is backwards..

I think that the RTF files are automatically generated by crystal reports, so unfortunately I can't get it in any other format..

For instance I could have 23 Pages of info with 7 headers: Membership %, Surname, Firstname, Address, Zip Code, Phone Number, Member No, which represent members that pay 100% membership fees and the next page, has 5 Members that pay 50% membership and 2 members that pay 25% membership.. The data is sorted by percentage and then surname.  On every page, the header information appears (the percentage only appears once on each page unless there are more then one).

Sorry I couldn't think of a better way to explain that.. so I'll try and do a rough diagram.

Membership % - Surname - Firstname - Address - Zip Code - Phone - Member No  (These are the headers)
100% - Bloggs - Joe - 4 Somewhere Lane - 77263 - 555-8192 - MEM6771
- Smith - John - 8 Nowhere St - 21387 - 555-1881 - MEM8811

so it will continue like that will continue for 23 Pages...
On page 24:

Membership % - Surname - Firstname - Address - Zip Code - Phone - Member No  (These are the headers)
50% - Sname - Fname - Add - Zcode - PNum - MemNo
- Sname - Fname - Add - Zcode - Pnum - MemNo
etc etc for 5 people, and then on that same page, the 25% percentiles are put on as well..
so another lot of header information is printed on that same page, rather then starting a new page just because it's a different percentile group..

Membership % - Surname - Firstname - Address - Zip Code - Phone - Member No  (These are the headers)
25% - Smith - Harry - 2 Myplace Lane - 3233 - 911 - MEM721
- Washington - Bill - 8 Nohome Court - 23833 - 555-2039 - MEM8232
so.. when I paste this to excel the tab order is in Reverse so it will come out like this..

100
Membership %
Bloggs
Surname

Now when it gets to a page with more then one percentage (i.e. with 5 50% members and 2 25% members as mentioned above) it comes out like this!

50
25
Membership %
Membership %
Bloggs
Smith
Salizer
Grigor
Lewinsky
Jeffries
Barden
Lastname
Joe
Sally etc etc etc...

So i've got noway to tell which members are 25% and which members are 50%.  I'm trying to set up a macro to go through and do this all automatically and i've basically finished this but it doesn't allow for the different percentile groups, i.e. I might get sent 2 of these a week, so I have to convert them frequently.  When I get to this point I just have to convert them manually..
0
mortar
Asked:
mortar
  • 6
  • 6
  • 2
1 Solution
 
paraghsCommented:
Here is a method, but it will require a few modifications :

1. Start MS Excel.
2. Menu File -> Open.
3. Go to the deirecoty where your RTF file is. Under "Files of type", choose "All Files (*.*).
4. Select your RTF file, click on OPEN.
5. A "Text Import Wizard" window will open within Excel. This is Step 1 of 3. Choose Delimited, click on Next.
6. Under "Delimiters", check "Other", and type "-" (without quotes) in the accompanying box. Make sure that only "Other" is checked, and all other delimiters (Tab, Semicolon, Comma & Space) are unchecked.
7. Click on "Finish".

You have your Excel file with following two errors / anomalies :
1. As your Phone no. field contains "-", and we have used "-" as delimiter, your phone no will be spread across two columns.
2. Your headers will repeat whenever % changes.

To solve the second error, you will have to manually delete the headers.
To solve first error, do this before importing RTF file into Excel :
1. Open RTF in Wordpad, from Edit menu choose replace, and replace "Phone" with "Phone1-Phone2".
2. Although this will correctly display all your data in Excel, you will not get correct phone no. If phone no. is required, you can merge the two columns as under :
Select cell I-2.
Type "=concatenate(f2,"-",g2)"
Drag and copy this formula to all I column cells.

'hope this helps you :)
0
 
mortarAuthor Commented:
Ok I got stuck at the Finish point.. The data didn't import properly..
Following your instructions perfectly to Step 7, It comes out like this... Also too, my bad, it's Oracle Reports.

{\rtf\ansi
{\fonttbl{\f1 Courier New}}
{\colortbl;\red0\green0\blue0;}
{\info {\creatim \yr2005 \mo\Apr \dy26 \hr08 \min56 \sec51} {\author Oracle Reports} {\title Company
\viewkind1
\paperw16840\paperh11900
\lndscpsxn
{{\pard \phpg\pvpg\posx0\posy0\absw5000\absh
{\pard \ql \pvpg\phpg\posx713\posy225\absw2904 \absh
{\pard \qr \sl182 \pvpg\phpg\posx11399\posy225\absw4725 \absh
{\pard \ql \pvpg\phpg\posx1959\posy1098\absw1239 \absh      283 {\f1\fs20 \cf1 100\par}}
{\pard \ql \pvpg\phpg\posx720\posy1098\absw1239 \absh      283 {\f1\fs22 \cf1 \b Membership %\par}}

Phone number isn't an actual field, I made it up for the purposes of our experiment, so there won't be any problems with the dash if we can make it to that point!

Any other ideas?
0
 
paraghsCommented:
My method was based on your "rough diagram" :)

Open your rtf file in Wordpad (or MS word), and save it as text file (.txt).
You will be prompted that all formatting will be lost. Click OK.
Now import this plain text file into Excel.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mortarAuthor Commented:
ahh good point ;)

ok, yeh basically comes out the same as though I opened it in Wordpad... It would be good even if it tabbed the different columns, but everything comes out line after line so theres no delimeters.. they like to make it hard on me!!
0
 
paraghsCommented:
Sorry, but I could not get the meaning of your last comment.
Are you not able to convert rtf into txt, and then import it into Excel ?
0
 
turn123Commented:
Hi mortar :-),
Since we haven't heard from you for a couple of days could you please give us an update on the status of this question?
See:  http://www.experts-exchange.com/help.jsp#hi51 Thank you, turn123's friendly update request script.
Offtopic comments about this script to http://www.experts-exchange.com/Applications/Q_21188389.html please :-).
0
 
mortarAuthor Commented:
sorry I haven't responded in a while paraghs..

Ok if I import it like you said, all the text comes out fine, no gibberish or code as before..  The only thing is (let's assume we've got 4 columns and 5 lines of data).. Instead of it coming out like you'd normally think that it would in this excel representation:

(TABLE1)
Membership% Surname           Percent            Address           (The top line corresponds to Cell(1,2 & 3 & 4)
Cell(2,1)         Cell(2,2)            Cell(2,3)             Cell(2,4)
Cell(3,1)         Cell(3,2)            Cell(3,3)             Cell(3,4)
Cell(4,1)         Cell(4,2)            Cell(4,3)             Cell(4,4)
Cell(5,1)         Cell(5,2)            Cell(5,3)             Cell(5,4)

It reads backwards... Now theres nothing wrong with that and the only bad thing is that you can't tell if there's a header seperator.  I'll explain.. The above information would output like this.. No tabs or anything, just data in the first column only!

(TABLE1-OUTPUT)
Cell(5,4)
Cell(4,4)
Cell(3,4)
Cell(2,4)
Cell(1,4)
Cell(5,3)
Cell(4,3) and so on and so forth.
So it's pulling the last line of data on the far right of the page, all the way up to the header then repeats, working right to left.
Now the interesting thing is when there's more then one header on the page (all data is sorted in reverse membership %.  When the 100% is finished it outputs the 50% and 33% and so on and so forth) It still seems to grab the data first and the headers later.. for example:

(TABLE2)
Membership% Surname           Percent            Address           (The top line corresponds to Cell(1,2 & 3 & 4)
Cell(2,1)         Cell(2,2)            Cell(2,3)             Cell(2,4)
Cell(3,1)         Cell(3,2)            Cell(3,3)             Cell(3,4)
Membership% Surname           Percent            Address           (The top line corresponds to Cell(4,2 & 3 & 4)
Cell(5,1)         Cell(5,2)            Cell(5,3)             Cell(5,4)

Now this data would come out like this.

(TABLE2-OUTPUT)
Cell(5,4)   - DATA
Cell(3,4)   - DATA
Cell(2,4)   - DATA
Cell(4,4)   - HEADER
Cell(1,4)   - HEADER
Cell(5,3)   - DATA
Cell(3,3)   - DATA
Cell(2,3)   - DATA
Cell(4,3)   - HEADER
Cell(1,3)   - HEADER

So the only problem with this, is if the first header represents the 100 percentiles, and the second header represents 50 percentiles, the output above, really has no way of showing you, how many were 100 and how many were 50 without looking manually through the RTF?

It seems to be pulling the information based on tab order, so if you compare the following table to table2 above, the tab order is as follows.

(TABLE3)
20     15     10    5                    Top Line 5th
18     13     8      3                    Second Line 3rd
17     12     7      2                    Middle Line 2nd
19     14     9      4                    Fourth Line 4th
16     11     6      1                    Bottom Line 1st

Sorry about the mess but it's the only way I could think to explain it!
0
 
paraghsCommented:
Mortar,

When you open your RTF file, is the data represented as you depicted in your rough sketch, or do you see them one below the other, in a single column ?

The method described by me above will work if your rtf file shows data in following format :
***********************************************************
Membership % - Surname - Firstname - Address - Zip Code - Phone - Member No
100% - Bloggs - Joe - 4 Somewhere Lane - 77263 - 5558192 - MEM6771
- Smith - John - 8 Nowhere St - 21387 - 5551881 - MEM8811
***********************************************************
If you save this rtf file as plain text file, then opening the text file will retain this format.

Is it possible for you to upload the rtf file to somewhere on the net, so that I can have a look at it ?
0
 
mortarAuthor Commented:
I'm out there in two days, i'll strip the file so there's no confidential stuff in it, and i'll post it on my webspace..

Thanks for your persistence :)
0
 
turn123Commented:
Any luck getting the file?

~~~Turn123
0
 
paraghsCommented:
Hello mortar,

can you upload the file ?
0
 
mortarAuthor Commented:
Sorry i've just been so busy I didn't get a chance to do it.. There's not much point anyway because it only takes about 10 minutes to process it manually and it looks like i'll only have to do it once per year, it was more a matter of interest then anything... Thanks for all your help!
0
 
paraghsCommented:
'wish could help you more :(

Thanks for the points, anyway.
0
 
mortarAuthor Commented:
When I get a chance i'll come back and post it up somewhere, just been flat out with more important stuff!!! sorry!! thanks heaps!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now