Splitting a .CSV file column in to multiple columns

[mwscott1]

I have a .csv file with column headings "Name". I need to split the data in the "Name" column in to 3 separate columns with headings  CompName, Node, Serial using a space as the delimiter. I also need to remove the .txt if possible. Splitting the data is the main thing.

Sample data

Name

660026-6   660026-6 0KGSA42446.txt

OWNER6-PC   765726DT 1YQNZV1.txt

RCR-CTYPGV1   RL685229A CTYPGV1.txt

[#2 · Accepted solution]

Hi Melvin,

have a look at subfield, something like the below should work for you

Subfield(Name,' ',1) AS     CompName,

Subfield(Name,' ',2) AS     Node,

Replace(Subfield(Name,' ',3),'.txt',Null()) AS     Serial

Edit: forgot you gave the fieldname, just adjusted that

hope that helps

Joe

[Anonymous]

You can try this on your load:

SubField(Name,' ', 1) as CompName,

SubField(Name,' ', 2) as Node,

Replace(SubField(Name,' ', 3),'.txt','') as Serial

Regards,

Gabriel

[its_anandrjs]

Hi,

You can try this ways also by use Subfield and Where functions

LOAD * Where Serial <> 'txt';
LOAD Name,
SubField(Name,' ',1) as CompName,
SubField(Name,' ',-2) as Node,
SubField( SubField(Name,' ',-1),'.') as Serial;

LOAD * Inline [
Name
660026-6   660026-6 0KGSA42446.txt
OWNER6-PC   765726DT 1YQNZV1.txt
RCR-CTYPGV1   RL685229A CTYPGV1.txt
];

This is example you can use your existing source file in place of inline table

For your table you can try with

LOAD Name,
SubField(Name,' ',1) as CompName,
SubField(Name,' ',-2) as Node,
SubField( SubField(Name,' ',-1),'.') as Serial;
From <Source> Where SubField( SubField(Name,' ',-1),'.') <> 'txt';

Regards

Anand