How to split address?

[#1]

Hi,

Can anyone help me how to split the address to below columns?

Add1

Street,

City

County

Country

Postcode

Thanks.

[#2]

Thanks.How can I change the above expression if the data is in excel?

[giakoum]

Why, why is wrong with ckipling solution? See above post

[its_anandrjs]

You have to load like below

Load

  Address,

SubField(Address,',',1) as ADD1,

SubField(Address,',',2) sa ADD2,

SubField(Address,',',3) as ADD3,

SubField(Address,',',4) as ADD4,

SubField(Address,',',5) as ADD5,

SubField(Address,',',6) as ADD6

From Location;

Hope this helps

[#5]

Mostly the same - use the "Table files" button to generate a load statement for that workbook and then modify it to look like below:

LOAD

     SUBFIELD([FieldName],',',1) AS ADD1,

     SUBFIELD([FieldName],',',2) AS Street,

     SUBFIELD([FieldName],',',3) AS City,

     SUBFIELD([FieldName],',',4) AS County,

     SUBFIELD([FieldName],',',5) AS Country,

     SUBFIELD([FieldName],',',6) AS Postcode;

FROM

FilePath\FileName.xlsx

(ooxml, embedded labels, table is TableName);

[giakoum]

You need to decide where your data is . We started with a text file, moved on to Oracle and now it is in Excel. Are you experimenting? Logic is the same no matter the source.

[#7]

Sorry if I am not clear. I am not experimenting. I need to show the data both from SQL and excel. That's the reason I send one example as sample attached.

[its_anandrjs]

Check the load script for excel file

LOAD

Add,

SubField(Add,',',1) AS ADD1,

SubField(Add,',',2) AS ADD2,

SubField(Add,',',3) AS ADD3,

SubField(Add,',',4) AS ADD4,

SubField(Add,',',5) AS ADD5,

SubField(Add,',',6) AS ADD6

FROM

(ooxml, embedded labels, table is Sheet1);

EXIT Script

change in script according to your system

Hope this helps

[#9]

Thanks. I tried this. But it seems some fields data is not showing in correct place. Is it possible to solve this?

[its_anandrjs]

I believe check your data then use subfield function it will give correct address.

Hope this helps

[fkeuroglian]

Use subfield , this would generate the correct fileds

what is the problem that you have

Good luck

Fernando