1

Below follows test.txt contents, with generally 22 columns:

BusinessDate,SourceSystemId,IceIndexId,IceIndexName,ComponentId,ComponentReferenceType,ComponentType,ComponentName,ComponentIssuerCIS,ComponentIssuerName,ComponentWeighting,IceCurveID,IceCurveName,RiskyCurveCIS,OriginalWeighting,DerivedWeighting,indexType,cafName,indexStartDate,indexCurrency,componentCurrency
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,20173QAG6,CUSIP,BOND,GCCFC 2007-GG9 A-M,FCMT7US,COML MORT TST 2007-GG9,0.04,19063270,risky_20173QAG6_FCMT7US.usd,FCMT7US,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,61753JAF6,CUSIP,BOND,MSC 2007-IQ13 AM,Z01IYUS,MORGAN STNLY CAP I TST 2007-IQ13,0.04,19059680,risky_61753JAF6_Z01IYUS.usd,Z01IYUS,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,12345400,risky_itraxx_europe32_14.eur,XSNOREFOB258,ISIN,BOND,NOREFOB_BANCO_SANTANDER_SA,BBDERES,BANCO SANTANDER SA, MADRID HO,0.008,20286090,risky_bank_bsch_14.eur,BBDERES,0.008,0.008,indexCds,index_risky_itraxx_europe32_14.eur,2019-09-18,EUR,EUR

I want to find the line with more than 22 columns which are issue record and replace it with comma.

bcz BANCO SANTANDER SA, MADRID HO is one keyword which should not be comma-separated, and it should be like without comma, as BANCO SANTANDER SA MADRID HO.

  • What's the rule that would pick out that text as the one that needs fixing? Or do you just want to report which lines/entries have more than 22 commas? – Jeff Schaller Sep 23 at 13:21
  • 5
    What writes this CSV file? It should be patched to properly quote the fields with embedded commas. If that's fixed, the file will be a properly formatted CSV file and any CSV parser would be able to read it. – Kusalananda Sep 23 at 13:55
  • Assuming that it's always the tenth field (ComponentIssuerName) containing the unwanted comma, sed '/\(,.*\)\{22\}/s/,/ /10' should do the trick. I would recommend writing the output to a file and do a diff between input and output to see whether it did what it was supposed to do. – Philippos Sep 23 at 13:56
  • first 3 lines have 21 columns and 4th line have 22 columns... Seems u want to have all rows should be 21 columns.. which means need to concatenate the column 10 and 11 dynamically in 4th line ... – msp9011 Sep 23 at 14:01
  • Yes, means need to concatenate the column 10 and 11 dynamically in 4th line.. Which are more than 22 column – Rabindra Panigrahi Sep 23 at 19:36
3

It's easy enough to detect which input lines have more than 21 fields. e.g.

awk -F, 'NF>21' input.txt

Fixing it is not so easy. You will have to manually edit those lines (e.g. with vi or nano or your preferred editor) because there is no easy way for a script to know WHICH fields are bogus (or, conversely, which fields contain commas that they shouldn't).

It's a job requiring actual intelligence, not some simple (or even complicated) search-and-replace heuristics. It also requires actual knowledge of your data set.

This is why real CSV files have string fields inside quotes, and why real CSV parsers understand quoted string fields.

If you can get whatever produced your comma-separated file to produce real CSV with quoted string fields, then processing that would be much easier. That is the real solution to this problem - fix the problem at the source.


Other answers have noted that at least part of what you need to do is to merge any field beginning with a space with the previous field.

If you're going to do that, you should also convert the input to properly formatted CSV. For example:

$ perl -e '
use Text::CSV qw(csv);
$csv = Text::CSV->new();

while($row = $csv->getline(ARGV)) {

  # merge fields beginning with whitespace with the previous field.
  for ($i=1; $i <= @$row; $i++) {
    if ($row->[$i] =~ m/^\s/) {
      $row->[$i-1] .= "," . $row->[$i];
      $row->[$i] = undef;
    };
  };

  # delete any undef-ed fields    
  @$row = grep{defined $_} @$row;

  $csv->say(STDOUT, $row);
};
' input.txt

Note:

  • the $csv->getline() command gets a complete row of CSV data whether it's all on a single line or spread over multiple lines due to, e.g., multi-line quoted strings.

    This isn't particularly relevant to your current input file (because it's not CSV, it just happens to look a bit like a CSV file), but is very useful when working with real CSV files.

Sample Output:

BusinessDate,SourceSystemId,IceIndexId,IceIndexName,ComponentId,ComponentReferenceType,ComponentType,ComponentName,ComponentIssuerCIS,ComponentIssuerName,ComponentWeighting,IceCurveID,IceCurveName,RiskyCurveCIS,OriginalWeighting,DerivedWeighting,indexType,cafName,indexStartDate,indexCurrency,componentCurrency
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,20173QAG6,CUSIP,BOND,"GCCFC 2007-GG9 A-M",FCMT7US,"COML MORT TST 2007-GG9",0.04,19063270,risky_20173QAG6_FCMT7US.usd,FCMT7US,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,61753JAF6,CUSIP,BOND,"MSC 2007-IQ13 AM",Z01IYUS,"MORGAN STNLY CAP I TST 2007-IQ13",0.04,19059680,risky_61753JAF6_Z01IYUS.usd,Z01IYUS,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,12345400,risky_itraxx_europe32_14.eur,XSNOREFOB258,ISIN,BOND,NOREFOB_BANCO_SANTANDER_SA,BBDERES,"BANCO SANTANDER SA, MADRID HO",0.008,20286090,risky_bank_bsch_14.eur,BBDERES,0.008,0.008,indexCds,index_risky_itraxx_europe32_14.eur,2019-09-18,EUR,EUR

This has merged the two problematic fields on the 4th line as "BANCO SANTANDER SA, MADRID HO"

Note that ALL fields with embedded spaces (and any other potentially problematic characters) are also double-quoted. i.e. the output is now properly formatted CSV.

This only fixes the one problem that we know about. There may be others that we (i.e. you) don't know about yet. e.g. there may be extra commas that aren't immediately followed by whitespace.

This script (like the other answers here that implement a variation of this algorithm) will break any input rows where a field is supposed to begin with whitespace character(s). There aren't any in your sample input, but it's not safe to assume that from a sample size of one header and three data rows.

The real solution is still, as stated before, to fix the problem at the source by making it output properly formatted CSV instead of this broken garbage.


If you can't make the program that produces this output produce properly quoted CSV, another alternative is to get it to use a column separator that isn't in the data. A pipe character | or a semi-colon ; or a a TAB character (0x09, Ctrl-I, \t) are often good choices for separators.

1

Looks like what you really want is to join any field that starts with a space onto the end of the previous field:

$ awk -F, '{print NF}' file
21
21
21
22

$ cat tst.awk
BEGIN { FS=OFS="," }
{
    rec = $1
    for (i=2; i<=NF; i++)  {
        rec = rec ( $i ~ /^[[:space:]]/ ? "" : OFS) $i
    }
    print rec
}

$ awk -f tst.awk file
BusinessDate,SourceSystemId,IceIndexId,IceIndexName,ComponentId,ComponentReferenceType,ComponentType,ComponentName,ComponentIssuerCIS,ComponentIssuerName,ComponentWeighting,IceCurveID,IceCurveName,RiskyCurveCIS,OriginalWeighting,DerivedWeighting,indexType,cafName,indexStartDate,indexCurrency,componentCurrency
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,20173QAG6,CUSIP,BOND,GCCFC 2007-GG9 A-M,FCMT7US,COML MORT TST 2007-GG9,0.04,19063270,risky_20173QAG6_FCMT7US.usd,FCMT7US,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,61753JAF6,CUSIP,BOND,MSC 2007-IQ13 AM,Z01IYUS,MORGAN STNLY CAP I TST 2007-IQ13,0.04,19059680,risky_61753JAF6_Z01IYUS.usd,Z01IYUS,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,12345400,risky_itraxx_europe32_14.eur,XSNOREFOB258,ISIN,BOND,NOREFOB_BANCO_SANTANDER_SA,BBDERES,BANCO SANTANDER SA MADRID HO,0.008,20286090,risky_bank_bsch_14.eur,BBDERES,0.008,0.008,indexCds,index_risky_itraxx_europe32_14.eur,2019-09-18,EUR,EUR

$ awk -f tst.awk file | awk -F, '{print NF}'
21
21
21
21
0

if your goal is to intercept fields that start with a space, using sed and Miller (https://github.com/johnkerl/miller) is

sed -r 's/, /|/g' input.csv | mlr --csv put -S 'for (k in $*) {$[k] = gsub($[k], "[|]", ", ");}'

You will have

BusinessDate,SourceSystemId,IceIndexId,IceIndexName,ComponentId,ComponentReferenceType,ComponentType,ComponentName,ComponentIssuerCIS,ComponentIssuerName,ComponentWeighting,IceCurveID,IceCurveName,RiskyCurveCIS,OriginalWeighting,DerivedWeighting,indexType,cafName,indexStartDate,indexCurrency,componentCurrency
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,20173QAG6,CUSIP,BOND,GCCFC 2007-GG9 A-M,FCMT7US,COML MORT TST 2007-GG9,0.04,19063270,risky_20173QAG6_FCMT7US.usd,FCMT7US,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,61753JAF6,CUSIP,BOND,MSC 2007-IQ13 AM,Z01IYUS,MORGAN STNLY CAP I TST 2007-IQ13,0.04,19059680,risky_61753JAF6_Z01IYUS.usd,Z01IYUS,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,12345400,risky_itraxx_europe32_14.eur,XSNOREFOB258,ISIN,BOND,NOREFOB_BANCO_SANTANDER_SA,BBDERES,"BANCO SANTANDER SA, MADRID HO",0.008,20286090,risky_bank_bsch_14.eur,BBDERES,0.008,0.008,indexCds,index_risky_itraxx_europe32_14.eur,2019-09-18,EUR,EUR
  • What would that do if any line of input contained |? Btw you don't need/want -r in that sed command as that's making it specific to older versions of GNU sed for no reason. – Ed Morton Sep 23 at 20:57
0

Try this,

awk -F ',' '{a=$1;for (i=2;i<=10;i++) a=a","$i ; 
             for (i=11;i<=(NF-11);i++) a=a$i; 
             for (i=(NF-10);i<=NF;i++) a=a","$i; 
             print a}'  input.csv  


BusinessDate,SourceSystemId,IceIndexId,IceIndexName,ComponentId,ComponentReferenceType,ComponentType,ComponentName,ComponentIssuerCIS,ComponentIssuerName,ComponentWeighting,IceCurveID,IceCurveName,RiskyCurveCIS,OriginalWeighting,DerivedWeighting,indexType,cafName,indexStartDate,indexCurrency,componentCurrency
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,20173QAG6,CUSIP,BOND,GCCFC 2007-GG9 A-M,FCMT7US,COML MORT TST 2007-GG9,0.04,19063270,risky_20173QAG6_FCMT7US.usd,FCMT7US,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,11260370,risky_CMBX3_AM_19HGEMAC7.usd,61753JAF6,CUSIP,BOND,MSC 2007-IQ13 AM,Z01IYUS,MORGAN STNLY CAP I TST 2007-IQ13,0.04,19059680,risky_61753JAF6_Z01IYUS.usd,Z01IYUS,0.04,0.04,indexCds,index_risky_CMBX3_AM_19HGEMAC7.usd,2010-02-09,USD,USD
2019-09-19,ICEEUR,12345400,risky_itraxx_europe32_14.eur,XSNOREFOB258,ISIN,BOND,NOREFOB_BANCO_SANTANDER_SA,BBDERES,BANCO SANTANDER SA MADRID HO,0.008,20286090,risky_bank_bsch_14.eur,BBDERES,0.008,0.008,indexCds,index_risky_itraxx_europe32_14.eur,2019-09-18,EUR,EUR

The above code will concatenate from column 11 to column NF-11...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.