RaqForum 22 No.
1 Reply  •  701 View  • 
     ...

Extract data from uncertain rows

 

1. Methods

To retrieve one extract unit from uncertain number of rows, we need to think from the following two points:

1. From the uncertain number of rows, find out the method of distinguishing each extract unit according to the rule.

2. Combine the distinguished data into an extract unit and then retrieve it.

The rules may be different for different data types, but there is always a rule to distinguish each extract unit.

 

2. Examples

There is a text file info.txt, part of which is shown as follows:

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

There is the contact information of four people. The first four lines of each contact information are department, company name, address, and zip code respectively. From the fifth line on, the information is about contact details, which may be telephone or fax, and the number maybe two or more. Now the calculation task is to convert the information of the text file into Excel according to the actual content of the data. And the format is:

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

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

 

The SPL script is:

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 Read the data of info.txt.

A2 Group telephone, fax, and other information.

A3 Retrieve the even-numbered rows of the grouping result.

A4 Combine the odd and even rows of data as one record. Each record is separated by a comma and concatenated into strings (odd rows are other information, and even rows are telephone and fax information).

A5 Export the results to info.csv.


 
 •  701 View  •