Results 1 to 10 of 10
-
10-14-2011, 03:02 AM #1Registered User
- Join Date
- Jan 2005
- Posts
- 293
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
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
-
10-14-2011, 06:50 AM #2Disabled
- Join Date
- Aug 2011
- Location
- London
- Posts
- 172
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.
-
10-14-2011, 09:37 AM #3Web Hosting Guru
- 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!
-
10-15-2011, 07:55 AM #4Registered User
- Join Date
- Jan 2005
- Posts
- 293
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.
-
10-15-2011, 08:54 AM #5Registered User
- Join Date
- Jan 2005
- Posts
- 293
I would be prepared to pay £100 for the script if someone could develop one ASAP for me.
-
10-15-2011, 01:20 PM #6Web Hosting Guru
- 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
-
10-15-2011, 02:22 PM #7Registered User
- Join Date
- Jan 2005
- Posts
- 293
Have mailed you!
-
10-15-2011, 02:25 PM #8
Since you're now offering pay, moved to employment offers where that's allowed.
Your one stop shop for decentralization
-
10-16-2011, 05:40 AM #9Web Hosting Master
- 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-18-2011, 11:55 AM #10Registered User
- Join Date
- Jan 2005
- Posts
- 293
Thanks Mod. I'm unable to create topics in the wanted section etc, can you look into this?
Similar Threads
-
Finding a key word/phrase in text file?
By P-nut in forum Programming DiscussionReplies: 6Last Post: 06-24-2008, 09:29 PM -
Need someone to import excel file into mysql
By ibda12u in forum Employment / Job OffersReplies: 3Last Post: 07-18-2007, 11:28 PM -
Import Large SQL File
By JmanA9 in forum Hosting Security and TechnologyReplies: 3Last Post: 08-20-2004, 10:47 AM -
how to import a large mysql file into the database?
By lobaloba9 in forum Programming DiscussionReplies: 2Last Post: 08-02-2004, 03:12 PM -
very large text file, viewing/diff
By shasta in forum Hosting Security and TechnologyReplies: 8Last Post: 08-06-2003, 11:13 AM