Solved

rtf extraction to excel

Posted on 2011-02-14
7
313 Views
Last Modified: 2012-05-11
I have an rtf doc and would like to extract the information into an excel file arranged in an orderly fashion according to the titles in the rtf doc. I've attached a sample doc at ee-stuff as its an rtf doc and can't be uploaded here. I have however attached a sample of how the data should be captured in excel - I did this manually to show what data should go where.  SampleConversion.ods

Many thanks

Jamie
0
Comment
Question by:fraser77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34887213
Writing a VBA subroutine to open a rtf file, process its contents and save it to an excel sheet in itself isn't that difficult. The majority of the work will be in the processing of the rtf contents into the names/addresses etc, this off course depends largely on the rtf structure.

To get you started, you can use these code fragments :

To open an arbitrary file and process its contents line by line (change the debug.print line to process the text).
[If the rtf files are generated from ms word, a lot of garbage is added]
Open "d:\temp\test.rtf" For Input As #1
While Not EOF(1)
    Line Input #1, buffer
    Debug.Print buffer
Wend
Close #1

Open in new window


to place text in a worksheet cell
worksheets("Sheet2").Range("B6")="something"
worksheets("Sheet2").cells(7,3)= "Else"

Open in new window

0
 

Author Comment

by:fraser77
ID: 34887435
HI,

Thanks for the suggestions. I'm afraid I'm not that advanced to know how to do this. Can you provide a solution in its completed format? I wouldnt have a clue how to start doing a vb script or where to put it.

Thanks,

Jamie
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34888973
Sure.
Would you be able to post a zipped sample rtf file, that would get me a starting point for the actual processing.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:fraser77
ID: 34889641
Yes the sample RTF is attached at ee-stuff.com. You just have to paste in the url of this question to find out where it is (sorry if you already know this).

Thanks a lot,

Jamie
0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 500 total points
ID: 34898396
I see...
I have not used ee stuff before, thanx for showing me !


I have looked into the provided rtf file and regret to have to tell you that parsing this rtf file will simply not be an option.
The file is not structured enough to be able to automatically extract fields and place them correctly.

eg: consider the following texts :

DavidPearsonAccountantdpearson99@yahoo.co.uk07871444444
CatherineNeerAssociateConsultantCNeer@hotmail.com
DavidSeedDirecotrDavidSeed@Pers.com
CarolineDememrDirectorcaroline@davies@me.com

if you want to be able to automatically determine which characters represent a name and which a job title / email address / telephone number, you simply need the rtf file to have a better structure.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35221187
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to consolidate .ods files in one sheet 30 43
script to read text file 34 31
Permutacion of 2 numbers COUNT 8 21
Error 1004 Excel 2013 11 16
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question