Loading and Splitting Data from Excel

[hopkinsc]

Hi,

i was wondering if it was possible to load in the attached data (Excel) and split the combined columns into seperate fields?

Company Name and Address	Contact
company A	Tel: 123456789
Company House	Fax: 123456789
Company street	email: Company@Company.com
Company City	www.company.com
Compnay Country
Post Code

So i would want the Company Name and Address split into their own fields

Company Name

Address Line 1

Address Line 2

Address Line 3 etc

and then the same with the contact column.

Any suggestions will be appreciated.

Thanks

[hopkinsc]

Does anyone have any suggestions please?

[Andrea_Ghirardello]

You need to add a load from file statement, enable transformation steps and use the transpose feature in the Rotate tab.

See attached sample.

[hopkinsc]

Hi Andrea,

Thanks for your reply although the example is not correct.

In the example you have attached, you have made the field names everything in column A and the values are then everything in Column B

What i actually need is..

Cell A1 (Company Name and Address) - I don't need this (this is the field name, but i need the values below it broken down against different field names)

Cell A2 (Company A) - I want a new field created called  'Company Name' with the value in Cell A2 to be the actual value of the new field.

Cell A3 (Company House) - I want a new field created called 'Address Line 1' with the value of Cell A3 to be the actual value of the new field

and the same with the rest of column A

Column B again is the same..

Cell B1 (Contact) - I don't need this  (this is the field name, but i need the values below it broken down against different field names)

Cell B2 (Tel:123456789) -  I want a new field created called  'Telephone No' with the value in Cell B2 to be the actual value of the new field.

Cell B3 (Fax:123456789) -  I want a new field created called  'FAX No' with the value in Cell B3 to be the actual value of the new field.

etc..

Can anyone help please?

[#5]

Hi,

Can you let me know if there is any fixed seperator used to seprate the details. Like , or : or / e..t.c

Thnaks,

D J

[hopkinsc]

Hi DJ,

There is no seperator in Column A. Column B will have a colon ':' to seperate the values from the descriptions.

eg

Tel:123456789

I suppose the TEL part could be the field name if that's easier but if not i would want to completely ignore the TEL.

Is that possible?

[tresesco]

If you want to keep 123456789 and remove Tel:, try like:

Load

KeepChar(YourfieldwhichcontainTel:123456789likedata , '0123456789') as TelPhNo

[hopkinsc]

Hi Tresesco,

Thanks, i know how to do that, but its the whole load script i don't know how to do.

Thanks

[tresesco]

Hi,

I guess that, this is not what you want(believe your real life issue is something different), but hoping this would give some idea towards the solution. PFA.

[Andrea_Ghirardello · Accepted solution]

Ok...now it's more clear.

Try this