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. seems it is not taking data under correct column order. Any change please?

[#3]

Select Labels as None when loading the file.

[#4]

Replace @1 with F1:

LOAD F1 as ADD
FROM
C:\Add.xls
(biff, no labels, table is [Sheet1$]);


ResTest:
Load *,
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
Resident Test1;

DROP Table Test1;

[#5]

Tried but still showing the same. when I am using None it is showing as column 'A'. Is there anything incorrect?

EDIT: for me in the wizard it is selecting .xlsx

Is this the problem?

[#6]

It's not a problem.

Try this

In the script

use

yourfield as ADD

insteadof @1 as ADD

otherwise

Post your snip

[#7]

Yes, this is the problem!

Try this:

LOAD A as ADD
FROM
C:\Add.xls
(biff, no labels, table is [Sheet1$]);


ResTest:
Load *,
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
Resident Test1;

DROP Table Test1;

[ThornOfCrowns]

You require 6 fields in your data:

Add1,Street,City,County,Country,Postcode

Your data sample:

Aquatyure House, 13-90 Bath Road, Slough, US, SL1 3SA

5 fields

Fortress House, Skyliner Way, Bury St. Edmunds, BR, IP32 7YA

5 Fields

78 Gay Street, Bath,  U87 2PD

3 fields- and the postcode is wrong

PO Box 6850 , Reigate, UK, RH2 7WB

4 fileds

Payments, 156-198 St. John Street, London, US, EC1V 4PY

5 fields - country is wrong

Hutchinson House, 5 Hester Road, Batersea , London, BR, SW11 4AN

6 fields, country is wrong, city and county are incorrect

V. Gates Limited, PO Box 7574, London, UK, NW4 2YT

5 fields

Springfields Business Recovery & Insolvency, &^ Hinckley Road, Leicester, US, LE3 0RD

5 fields - gibberish before Hinkley Road - possibly 76? and country is wrong

Royal Crescent Road, Southampton, UK, SO14 3TT

4 fields

3 Gate Road, Glenloortamley, Newtonabbey, County Antrim, UK, BT36 8DY

6 fields, possible switch of Add1 / Street

A S B Law, Innovis House, 108, High Street, Crawley, West Sussex, IU8 1AS

7 fileds - postcode is wrong

A W Champion Ltd, Champion House, Burlington Road, New Malden, Surrey, OI9 4NB

6 fields - postcode is wrong

KIUYRFtlaw District Council, , Hundred Acre Lane, Worksop, Nottinghamshire, S81 0TS

6 fields, blank space in field 2 throws off all other fields, missing country

Of 13 rows, 3 have the right amount of entries, but none of those three will give you correct data in the fields. Other rows have junk data too, which is why I said you have more to worry about than string slicing. However, you will make your life and ours nuch easier by making sure you're not trying to force a square data peg into a round hole.