Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Splitting the data in a column into several columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting the data in a column into several columns
# 1  
Old 05-10-2012
Splitting the data in a column into several columns
Hi,
I have the following input file
Code:
32895901-d17f-414c-ac93-3e7e0f5ec240	AND	@GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]
73b129e1-1fa9-4c0d-b95b-4682e5389612	AUS	@GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]
40f82e88-d1ff-4ce2-9b8e-d827ddb39447	BEL	@GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]
36e9c3f1-042a-43a4-a80e-4a3bc2513d01	BGR	@GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]

I want to split column 3 into two columns:
Lets say I want BaseName, HouseNumber and PostalCode in column 4 and
ISOCountryCode,LanguageCode in column 5; if there are more fields then i want to ignore them

Here's how I want my output to look like
Code:
32895901-d17f-414c-ac93-3e7e0f5ec240    AND     @GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]    CATALUNYA;1;AD200    AND;GER    
73b129e1-1fa9-4c0d-b95b-4682e5389612    AUS     @GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]    MARKET STREET;68;NSW 2000    AUS;GER
40f82e88-d1ff-4ce2-9b8e-d827ddb39447    BEL     @GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]    CHAYSSEE DE CHARLEROI;38;1060    BEL
36e9c3f1-042a-43a4-a80e-4a3bc2513d01    BGR     @GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]    BULGARIA BOULEVARD;1;1421    BGR;GER

# 2  
Old 05-10-2012
If these fields are always there you can just split $3 around a bit to grab those values, and use a static print statement like below:

Code:
#!/usr/bin/awk -f

BEGIN { FS="\t"; OFS=";" }
{
        s=$3

        # remove outside and including brackets
        gsub(/^.*\[| *]$/,"",s)
        n=split(s, a, /; */)
        for (i=1;i<n;i++) {
                split(a[i], b, /:/)
                c[b[1]]=b[2]
        }

        print $0 FS c["BaseName"], c["HouseNumber"], c["PostalCode"] FS c["ISOCountryCode"], c["LanguageCode"]
}

Code:
[mute@geek ~/temp/ramky79]$ ./script input
32895901-d17f-414c-ac93-3e7e0f5ec240    AND     @GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ] CATALUNYA;1;AD200       AND;GER
73b129e1-1fa9-4c0d-b95b-4682e5389612    AUS     @GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ] MARKET STREET;68;NSW 2000        AUS;GER
40f82e88-d1ff-4ce2-9b8e-d827ddb39447    BEL     @GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]     CHAUSSEE DE CHARLEROI;38;1060   BEL;GER
36e9c3f1-042a-43a4-a80e-4a3bc2513d01    BGR     @GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]  BULGARIA BOULEVARD;1;1421        BGR;GER

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Splitting the numeric vs alpha values in a column to distinct columns

How could i take an input file and split the numeric values from the alpha values (123 vs abc) to distinc columns, and if the source is blank to keep it blank (null) in both of the new columns: So if the source file had a column like: Value: |1 | |2.3| | | |No| I would... (7 Replies)
Discussion started by: driftlogic
7 Replies

2. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

3. Shell Programming and Scripting

Splitting columns

Hi Friends, My input file has more than 20 columns UniProtKB A0A183 LCE6A GO:0031424 GO_REF:0000037 IEA UniProtKB-KW:KW-0417 P Late cornified envelope protein 6A LCE6A_HUMAN|C1orf44|LCE6A protein taxon:9606 20120303 UniProtKB ... (9 Replies)
Discussion started by: jacobs.smith
9 Replies

4. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range - extended

Dear all, there is a nice solution for a text merge where the second file has only variables with a numeric range ( sorry, cannot post URL + thread is closed ). The real world is however more complicated than in the earlier example. file1 A 1 A 2 A 3 B 1 B 2 B 3 B 4 C 1 C 2 C 3 C... (4 Replies)
Discussion started by: underscore
4 Replies

5. Ubuntu

How to compare two columns and fetch the common data with additional column

Dear All, I am new to this forum and please ignore my little knowledge :p I have two types of data (a subset is given below) data version 1: 439798 2 1 451209 1 2 508696 2 1 555760 2 1 582757 1 2 582889 1 2 691827... (2 Replies)
Discussion started by: evoll
2 Replies

6. Shell Programming and Scripting

Splitting data from one row as multiple columns

Hi I have a file containing some data as follows: 11-17-2010:13:26 64 4 516414 1392258 11-17-2010:13:26 128 4 586868 695603 11-17-2010:13:26 256 4 474937 1642294 11-17-2010:13:32 64 4 378715 1357066 11-17-2010:13:32 128 4 597981 1684006 ... (17 Replies)
Discussion started by: annazpereira
17 Replies

7. UNIX for Dummies Questions & Answers

How to match 2 columns where one column has data as a range

Hi, I have a query about joining files using data ranges. Example files below - I want to join file1 to file2 with matches where file1 column 1 is equal to file2 column1, and file1 column 2 is within the range of file2 columns 3 and 4. I would like rows which don't match to be printed too. ... (4 Replies)
Discussion started by: auburn
4 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

Separating data from one column into two columns

Hello, I have a file that contains 64,235 columns and over 1000 rows and looks similar to this: ID dad mom 1 2 3 4 5.... 64232 1234 5678 6789 AA BB CC DD EE....ZZ 1342 5786 6897 BB CC DD EE FF....AA 1423 5867 6978 CC DD EE FF GG....BB I need to leave the first three columns in... (4 Replies)
Discussion started by: doobedoo
4 Replies

10. Shell Programming and Scripting

Convert two column data into 8 columns

Apologies if this has been covered - I did search but couldn't find what I was looking for. I have a simple X-Y input file. I want to convert it from two columns into 8 columns - 4 pairs of X-Y data. So my input file looks like X1 Y1 X2 Y2 X3 Y3 X4 Y4 X5 Y5 etc And I want it to look... (8 Replies)
Discussion started by: NickC
8 Replies
Login or Register to Ask a Question