Results 1 to 10 of 10
  1. #1

    Large text file of address in word, import into excel

    Basically i have a large document in word, with a huge number of names, addresses and telephone numbers. I want to import all these details into corresponding fields into excel (name, address, tel, postcode) etc.

    copying and pasting is an option, but there must be an easier way!

    most of the data is seperated by a commas. I tried saving it as a text document and importing it into excel that way and its about 50% accurate, with half the stuff in the wrong cells.

    Here's an example:
    ABC Company
    Contract Services, Suite 2, Ground Floor, Jimbo House, 29 LOL Street, INVERNESS, IV1 1xU, Tel: 01962 244490, Fax: 01363 221188, Email: jimbo@company.com
    What seems to be causing the problem, is if a company has a an additional piece of info and others dont. If one company has building name or what ever, it seems to be moved across to the next cell. The business addresses vary from just a simple address down to fully blown out with rooms, buildings etc. The number of addresses in this file is around 5k, so im looking at an easy method of sorting!

    Someone mentioned putting in additional comma in place of the missing info, I havent tried this yet, any suggestions?

    Eg:

    XYZ Company
    Contract Services, Suite 2, Ground Floor, , 29 LOL Street, INVERNESS, IV1 1xU, Tel: 01962 244490, Fax: 01363 221188, Email: jimbo@company.com
    Like that?

  2. #2
    yes, like that - the key problem is that the same field number doesn't always contain the same piece of info :-(

    you could try to parse it a bit, like replace all occurrences of "House," , "Floor,", etc with "House%", "Floor%" to avoid it being treated as separate field, provided that the next field should go along with it, in fact any recognisable "pattern" can be broken up or combined, another thing I could imagine is checking for the first integer after ", ", which in the case above would be 29 LOL Street, and have everything before or some fields before that go into one. But it is going to be nasty

    cheers -
    oz.

  3. #3
    Join Date
    Oct 2005
    Posts
    292
    I'd like to ask that you expand a bit on the format of the text file.

    1. Is it one-line = one record? or 2 lines per record? 3?
    2. If it is multiple lines per record, what is the delimiter per record? (extra newline?)
    3. In your sample, the "Contract Services", is it always the first field?
    4. Are all telephone numbers consistent? (prefixed with "Tel:")?
    5. Is Fax optional? Is it also consistently prefixed with "Fax: ..."?
    6. Is the email also consistently prefixed with "Email: "?
    etc..


    Given the above questions, one might script (a few lines in Perl/Bash) this to output a TAB-delimited file (ala csv, but this time a tab), in a consistent manner (field1=company name, field2=Type of Company, ...); which you can easily import in Excel.

    Cheers!

  4. #4
    1. Its mostly 3 lines per record, including the business name, which in its self is a hyperlink (though not really important). Although depending on the length of the details, it can spam onto 4 lines.

    2. Extra new line (no space between records) seperates the records.

    3. In that instance, it just related to the business address.

    4/5/6. All telephone numbers, fax and email address are prefixed.

    If someone is prepared to write a quick script, I would be prepared to pay a small fee for the help.

  5. #5
    I would be prepared to pay £100 for the script if someone could develop one ASAP for me.

  6. #6
    Join Date
    Oct 2005
    Posts
    292
    @Artificial
    I'd like to help and if I may, I'd like take a look at that text file.

    Cheers!
    Custom Open-Source Software Solutions
    dtady@cpan.org - Python/Django | Perl | Java | Linux | Amazon AWS | DevOps | Docker

  7. #7
    Have mailed you!

  8. #8
    Join Date
    Oct 2002
    Location
    /roof/ledge
    Posts
    28,074
    Since you're now offering pay, moved to employment offers where that's allowed.
    Your one stop shop for decentralization

  9. #9
    Join Date
    Nov 2006
    Location
    Karachi, Pakistan
    Posts
    1,359
    I'd like a shot at this as well. Please email me at abdussamad@abdussamad.com .

  10. #10
    Thanks Mod. I'm unable to create topics in the wanted section etc, can you look into this?

Similar Threads

  1. Finding a key word/phrase in text file?
    By P-nut in forum Programming Discussion
    Replies: 6
    Last Post: 06-24-2008, 09:29 PM
  2. Need someone to import excel file into mysql
    By ibda12u in forum Employment / Job Offers
    Replies: 3
    Last Post: 07-18-2007, 11:28 PM
  3. Import Large SQL File
    By JmanA9 in forum Hosting Security and Technology
    Replies: 3
    Last Post: 08-20-2004, 10:47 AM
  4. how to import a large mysql file into the database?
    By lobaloba9 in forum Programming Discussion
    Replies: 2
    Last Post: 08-02-2004, 03:12 PM
  5. very large text file, viewing/diff
    By shasta in forum Hosting Security and Technology
    Replies: 8
    Last Post: 08-06-2003, 11:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •