Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to import a large excell file where one column holds a concatenated field ( 12 different fields) which are seperated by a point '.', the other excell columns are fine...
Here is some example of that concatenate fields
AC.1.TR.AUD.1000.5100.1341..........CREDIT |
AC.1.TR.AUD.1000.5100.1411..........CREDIT |
MG.1.TR.BGN.25015.5100.2601.T....56.....51010 |
MG.1.TR.BGN.25015.5100.2601.T....56.....LIVEDB |
MG.1.TR.BGN.25015.5100.2611.20121118....59....51010 |
MG.1.TR.BGN.25015.5100.2611.20140422....59....51010 |
Is there anyway I can import this in an elegant way without having to work with if constructions and mid/lef/right ...
The problem is that they have not made each of the sub fields equally long
Just as an example in the first 2 rows the field 5th field is only 4 signs long , in the others 5
Thanks for your (appreciated) help
Hi Hwallays,
Try loading the column by setting '.' as delimiter.
LOAD
[YourField]
from a.txt (utf8, txt, delimiter is '.' , embedded labels);
//This will split the fields into multiple fileds as per the delimiter, you can do it from the load transformation section as well.
You could use subfield() for it, for example:
...
subfield(YourField, '.', 1) as YourField1,
subfield(YourField, '.', 2) as YourField2
...
- Marcus
Hai Marcus , that did works really well ...so many thanks on this one !
Here 's a view on the result
AC.1.TR.AUD.1000.5100.1211..........CREDIT | AC | 1 | TR | AUD | 1000 | 5100 | 1211 | |||||
AC.1.TR.AUD.1000.5100.1231..........CREDIT | AC | 1 | TR | AUD | 1000 | 5100 | 1231 | |||||
AC.1.TR.AUD.1000.5100.1341..........CREDIT | AC | 1 | TR | AUD | 1000 | 5100 | 1341 | |||||
AC.1.TR.AUD.1000.5100.1411..........CREDIT | AC | 1 | TR | AUD | 1000 | 5100 | 1411 | |||||
AC.1.TR.AUD.1000.5100.1412..........CREDIT | AC | 1 | TR | AUD | 1000 | 5100 | 1412 | |||||
MG.1.TR.BGN.25015.5100.2601.T....51.....51010 | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 51 | |||
MG.1.TR.BGN.25015.5100.2601.T....51.....LIVEDB | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 51 | |||
MG.1.TR.BGN.25015.5100.2601.T....53.....51010 | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 53 | |||
MG.1.TR.BGN.25015.5100.2601.T....53.....LIVEDB | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 53 | |||
MG.1.TR.BGN.25015.5100.2601.T....56.....51010 | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 56 | |||
MG.1.TR.BGN.25015.5100.2601.T....56.....LIVEDB | MG | 1 | TR | BGN | 25015 | 5100 | 2601 | T | 56 | |||
MG.1.TR.BGN.25015.5100.2621.T....51.....51010 | MG | 1 | TR | BGN | 25015 | 5100 | 2621 | T | 51 |