Below is part of the data in text file info.txt:

Abbrucharbeiten

ATR Armbruster

Werkstr. 28

78727 Oberndorf 

Tel. 0175 7441784

Fax 07423 6280

Abbrucharbeiten

Jensen & Sohn, Karl

Schallenberg 6A

25587 Münsterdorf

Tel. 04821 82538

Fax 04821 83381

Abbrucharbeiten

Kiwitt, R.

Auf der Heide 54

48282 Emsdetten

Tel. 02572 88559

Tel. 0172 7624359

Abbrucharbeiten, Sand und Kies, Transporte, Kiesgruben, Erdbau

Josef Grabmeier GmbH

Reitgesing 1

85560 Ebersberg

Tel. 08092 24701-0

Fax 08092 24701-24

The file contains four pieces of contact information. The first four lines of each piece of information are department, company name, address and post code. Phone number or fax number, or both, begin from the fifth line. The task is to convert the text data into Excel by different types of information in the following format:

Branche:    Name:     Address:   Place:    contact1:   contact2:

1st row     2nd row   3rd row    4th row   5th row     6th row.....

 

It’s convenient to complete the task using esProc.
You can download esProc installation package and free DSK edition license HERE.

1. Write script txt2csv.dfx in esProc:

	A
1	=file("info.txt").import@i()
2	=A1.group@o(left(~,3)=="Tel"   || left(~,3)=="Fax")
3	=A2.step(2,2)
4	=A2.step(2,1).((~|A3(#)).concat@cq())
5	=file("info.csv").export(A4)

 

A1   Import data of info.txt.

A2   Divide each piece of contact information by phone number, fax number and other information.

A3   Get even-numbered rows in each group.

A4   Concatenate each pair of neighboring odd-numbered row and even-numbered row into one record. Use comma to separate records and join them into a string (odd-numbered rows contain other information and even-numbered rows contain phone number or/and fax number).

A5   Export the result to info.csv.

2.     Execute the esProc script. Below is the result content in info.csv:

Abbrucharbeiten	ATR Armbruster	Werkstr. 28	78727 Oberndorf	Tel. 0175 7441784	Fax 07423 6280
Abbrucharbeiten	Jensen & Sohn, Karl	Schallenberg 6A	25587 MŸnsterdorf	Tel. 04821 82538	Fax 04821 83381
Abbrucharbeiten	Kiwitt, R.	Auf der Heide 54	48282 Emsdetten	Tel. 02572 88559	Tel. 0172 7624359
Abbrucharbeiten, Sand und   Kies, Transporte, Kiesgruben, Erdbau	Josef Grabmeier GmbH	Reitgesing 1	85560 Ebersberg	Tel. 08092 24701-0	Fax 08092 24701-24