How to pull data from TXT file.

[intradayteam]

okay, I know this can be done, but i dont know how.

I have a huge text file containing data entries like the following.

Executive: Name 1
Mobile: 9824209211
Executive: Name 2
Mobile: 9888888888
Company 1 Name
Member No: S-133/I/L
Category: Food work, Cattle work
Year of Established: 1983
Address: Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
Phone: 22222222
Fax: 33333333
Email: some1@email.com
Product: Some product 1, Some other product 2.


Executive: Name 3
Mobile: 999999999
Designation: Manager
Executive: Name 4
Mobile: 9777777777
Designation: Director
Company 2 Name
Member No: S-134/I/L
Category: other work
Year of Established: 1985
Address: Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
Phone: 44444444
Fax: 55555555
Email: some2@email.com
Website: www.website.com
Product: Some product, Some other product.

1. Above i have differentiated the details of 2 companies by paragraph (enter on keyboard), but original data has no paragraph.
2. As you can see, both the company details have some common things (Executive, Mobile). But second company details have some extra fields like (Designation, Website)
3. Addresses are in various range, some has 2-3 lines and some has 4-5 lines.

What i am willing to do in excel !

[TABLE="******* 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company Name[/TD]
[TD]Executive[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Executive[/TD]
[TD]Designation[/TD]
[TD]Mobile[/TD]
[TD]Member No[/TD]
[TD]Category[/TD]
[TD]Year of Established[/TD]
[TD]Address[/TD]
[TD]Phone[/TD]
[TD]Fax[/TD]
[TD]Email[/TD]
[TD]Website[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Company 1 Name[/TD]
[TD]Name 1[/TD]
[TD][/TD]
[TD]9824209211[/TD]
[TD]Name 2[/TD]
[TD][/TD]
[TD]9888888888[/TD]
[TD]S-133/I/L[/TD]
[TD]Food work, Cattle work[/TD]
[TD]1983[/TD]
[TD]Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
[/TD]
[TD]22222222[/TD]
[TD]33333333[/TD]
[TD]some1@email.com[/TD]
[TD][/TD]
[TD]Some product 1, Some other product 2.[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Company 2 Name[/TD]
[TD]Name 3[/TD]
[TD]Manager[/TD]
[TD]999999999[/TD]
[TD]Name 4[/TD]
[TD]Director[/TD]
[TD]9777777777[/TD]
[TD]S-134/I/L[/TD]
[TD]other work[/TD]
[TD]1985[/TD]
[TD]Some address here line 1
Some address here line 2
Some address here line 3
Some address here line 4
[/TD]
[TD]44444444[/TD]
[TD]55555555[/TD]
[TD]some2@email.com[/TD]
[TD]www.website.com[/TD]
[TD]Some product, Some other product.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


and so on.

I dont know how to pull data in excel from text file, and arrange it like this. I know this is possible.

Any help would be great.

I can even send/upload the ORIGINAL text file, which is less than 1MB.

 

[igold]

Hi intradayteam,

In your example, are the columns you show, (Columns A-P) the complete list of column headings?

igold

 

[intradayteam]

Yes its a complete list. May be one or two more coloumns in rare case. To check that, i have to check text files each and every line.
Lets go ahead assuming, its a complete list of heading.

 

[igold]

Hi intradayteam,


I could probably overcome the multiple addresses and the differing number of fields per record. Unfortunately, I am having trouble overcoming the duplicate field names. In your example the field "Executive" appears more than once within the same record. If it was "Executive" and "Executive1", then I might be able to come up with something. Other than that I am stuck.

If you look at this link, and my post #11, I was able to deal with different fields in different records, but duplicate fields is troublesome.

http://www.mrexcel.com/forum/excel-questions/923837-populate-excel-file-txt-file.html

Regards,

igold

 

[intradayteam]

First of all thanks for all the efforts igold. Here are some improvements.

Somehow i managed to rename it to "Executive1" and "Executive2". It took hours to rename it manually. Here is the new structure of data.

M A Enterprises ~
Member No: M-551/IV/A
Category: Food and vegitables
Year of Established: 1984
Address: Address line 1
Address Line 2
Address Line 3
Address Line 4
Address Line 5
Phone: 11111111, 22222222
Fax: 33333333
Email: somemail@gmail.com
Website:www.somewebsite.com
Executive1: Mr. Ashok Kumar
Designation: Owner
Mobile: 9999999999
Executive2: Rahul Bhai
Designation: Director
Mobile: 3333333333
Product: food product processing
Rawmaterial: Ss Hot Rolled


Total of 17 kind of entries (highlighted in BOLD letters). This is a complete list of column headings.
1st entry is company name, unfortunately it has no heading. Somehow i managed to put "~" sign after company name. So now the fixed format for every entry in my text file is ... Comapny Name<space>~
Address lines are flexible, it can be 3 to 5 lines, but does not exceed 6 or 7 in any of the entries.
Another issue is "Designation" which appears 2 times in some entries. First one comes after "Executive1" and second comes after "Executive2". So there is a hope :D Same thing with "Mobile".

Lastly, As i said earlier, Some fields may remain blank in some entires. Like some companies dont use FAX, in that case FAX should remain empty. Same case with Email, Website etc.

Waiting for some magic.

Once again thanks for all your help.

Regards

 

[igold]

If you are saying that you are always going to have these 17 entries, then I should be able to deal with dupes on the field names (famous last words). If the field names are not going to be constant (these 17) that is where the trouble begins. Additionally is the company name just going to sit on top of each set of data or does it appear like "Company: MA Enterprises ~". The appearance of the colon is what tells the code to parse that line... I may be able to work around that. Is there a field that will always signify the end of a record... For instance in your first example, the last field was "Product", but in this example it is "Rawmaterial".

I am sorry to pepper you with these questions, but I am looking for a way to make the logic in the code work.

Regards,

igold

 

[intradayteam]

@igold

Sent you my data (text format) in PM.

Company Name is there on top of each and every set of data. Like "M A Enterprises ~"

"Rawmaterial" field is not there in every set of data, so lets drop it, its not important. "Product" will be there at the end of each and every record.

So now, We have 16 fields, where set of data starts with a company name (Like this "M A Enterprises ~") and every records ends at "Product" field.

Hope this helps.

Regards

 

[István Hirsch]

Give this a try:

B column is a helper column. Enter the formula in B2, then C2, then copy across the formula in C2. The "Website:" should be followed a space.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	*	*	Company	Member No	Category	Year of Established	Address	Phone	Fax	Email	Website	Executive1	Designation	Mobile	Executive2	Designation2	Mobile2	Product	Rawmaterial	///
2	M A Enterprises ~Member No: M-551/IV/ACategory: Food and vegitablesYear of Established: 1984Address: Address line 1 Address Line 2Address Line 3Address Line 4Address Line 5Phone: 11111111, 22222222Fax: 33333333Email: somemail@gmail.comWebsite: www.somewebsite.comExecutive1: Mr. Ashok KumarDesignation: OwnerMobile: 9999999999Executive2: Rahul BhaiDesignation: DirectorMobile: 3333333333Product: food product processingRawmaterial: Ss Hot Rolled	Company: M A Enterprises ~Member No: M-551/IV/ACategory: Food and vegitablesYear of Established: 1984Address: Address line 1 Address Line 2Address Line 3Address Line 4Address Line 5Phone: 11111111, 22222222Fax: 33333333Email: [email]somemail@gmail.com[/email]Website: www.somewebsite.comExecutive1: Mr. Ashok KumarDesignation: OwnerMobile: 9999999999Executive2: Rahul BhaiDesignation2: DirectorMobile2: 3333333333Product: food product processingRawmaterial: Ss Hot Rolled///	M A Enterprises ~	M-551/IV/A	Food and vegitables	1984	Address line 1 Address Line 2Address Line 3Address Line 4Address Line 5	11111111, 22222222	33333333	somemail@gmail.com	www.somewebsite.com	Mr. Ashok Kumar	Owner	9999999999	Rahul Bhai	Director	3333333333	food product processing	Ss Hot Rolled	*
Sheet

 

[intradayteam]

@ István Hirsch , Thanks for the reply.


Your formula works only if all the data of each entry goes to SINGLE CELL. Which is not the case.
The data is in plain text.

 

[István Hirsch]

@ István Hirsch , Thanks for the reply.


Your formula works only if all the data of each entry goes to SINGLE CELL. Which is not the case.
The data is in plain text.

Then try to upload some units in Excel format, which have all the rows (17?) or which have missing rows or data.