Merge column

[#1]

Hello All,

I have a table as below in excel:

Name	Tel1	Tel2	Tel3
A	123456789
B	123456789	456789321
C	123456789	456789323
D	123456789	456789324	54784562
E	123456789		54784568
F	123456789		54784569
G	123456789
H	123456789

And I need to add another column like to merge the tables as shown below:

Name	TEL
A	123456789
B	123456789
B	456789321
C	123456789
C	456789323
D	123456789
D	456789324
D	54784562
E	123456789
E	54784568
F	123456789
F	54784569
G	123456789
H	123456789

Can you please advise me on the best method to do this?

Many Thanks,

Hasvine

[tresesco]

Try concatenating them loading separately like:

Load

          Name,

          Tel1 as TEL

From <>;

Load

          Name,

          Tel2 as TEL

From <>;

Load

          Name,

          Tel3 as TEL

From <>;

[tamilarasu]

Hi Hasvine,

You can also use cross table. Kindly check the attached file.

Data:
CrossTable(Tel, Data)
LOAD Name,
Tel1,
Tel2,
Tel3
FROM

Excel.xlsx
(ooxml, embedded labels, table is Sheet1);

DROP Field Tel;

RENAME Field Data To Tel;

[#4]

Hello Tamil,

I am trying to load it as below but I dont seem to get it right:

Can you help me with this?

Note that my [Téléphone bureau] = Tel1

                    [Téléphone domicile] = Tel2

                    [Tél mobile] = Tel3

Data:

CrossTable(Tel, Data)

LOAD Titre,

     Nom,

     Prénom,

     Service,

     Titre1,

     [Téléphone bureau],

     [Téléphone domicile],

     [Tél mobile],

     [Adresse de messagerie],

     CONCESSION

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Tel;

RENAME Field Data To Tel;

[tamilarasu]

Hasvine,

Something like below.

Data:

CrossTable(Téléphone, Data,7)

LOAD Titre,

     Nom,

     Prénom,

     Service,

     Titre1,

     [Adresse de messagerie],

     CONCESSION,

     [Téléphone bureau],

     [Téléphone domicile],

     [Tél mobile],

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Téléphone;

RENAME Field Data To Téléphone;

[ganeshsvm · Accepted solution]

Hello Hasvine,

Tamil Nagaraj did give you a proper solution, according to your data, you can use the following script:

First, Identify the primary field in your table, suppose if, "Titre" is the PK, then,

Table1:

LOAD Titre,

     Nom,

     Prénom,

     Service,

     [Adresse de messagerie],

     CONCESSION

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

Table2:

CrossTable(Tel, Data)

LOAD Titre,

     [Téléphone bureau],

     [Téléphone domicile],

     [Tél mobile]

FROM

[..\Annuaire\SHLMR_ANNUAIRE.xls]

(biff, embedded labels, table is [shlmr plateau caillou$]);

DROP Field Tel;

RENAME Field Data To Tel;

Then, you can Join or concatenate both table or just leave it which will associate itself with key field.

Regards,

Ganesh

[#7]

Hello everyone,

Thank you all for your help. 🙂

The script of Ganesh working great.

Thanks,

Hasvine