0

I'm joining two pipe delimited files, but after using my join command:

join -a 1 -i -t"|" -o 1.3 1.1 2.2 1.4 1.5 2.3 2.4 2.5 2.6 2.7 2.8 2.9  <(sort -d -t"|" -z  alt.csv) <(sort -d -t"|" -z  ../original/alt.csv) > ../out/alt.csv

The output file has a carriage return where the join has occurred e.g:

IRN|EADUnitID|EADPhysicalTechnical|AdmPublishWebNoPassword|AdmPublishWebPassword
|EADUnitTitle|EADBiographyOrHistory|EADScopeAndContent|EADArrangement|EADAcquisitionInformationRef|EADRelatedMaterial|BibBibliographyRef_tab
51899|ga.1.1|GLS Add. GA 1/1|Yes|Yes
|Photographic negatives ||&lt;p&gt;The albums comprise of negatives of Gypsies and Gypsy life in Germany and eastern Europe. The albums have been indexed and the negatives numbered by Althaus in series I-IV; VII-VIII, though numbering is not continuous. The majority of the negatives have duplicates in slide or photograph format (GA 1/2 and GA 3) and reference has been made to these. The captions are those taken from the index except for unindexed negatives, whereupon the caption has been taken from a duplicate photograph or slide. Where there is no duplicate, the caption simply describes what can be seen in the negative. The list also includes 22 negatives that are indexed in the albums but are missing. There is a closed section from GA 1/1/53 - GA 1/1/68 due to the sensitive nature of the negatives. &lt;&#x0002F;p&gt;||||
51900|ga.1.1.1|GLS Add. GA 1/1/1|Yes|Yes
|Ehepaar Weltzel. ||||||
51901|ga.1.1.2|GLS Add. GA 1/1/2|Yes|Yes
|Ehepaar Weltzel. ||||||
51902|ga.1.1.3|GLS Add. GA 1/1/3|Yes|Yes
|Roßlau, Dessauerstr Kegli. Julius Braun, Bitterfield, 1939 Koitsch. ||||||

But in order to be processed properly, the carriage return needs to occur after the last column:

IRN|EADUnitID|EADPhysicalTechnical|AdmPublishWebNoPassword|AdmPublishWebPassword|EADUnitTitle|EADBiographyOrHistory|EADScopeAndContent|EADArrangement|EADAcquisitionInformationRef|EADRelatedMaterial|BibBibliographyRef_tab
51899|ga.1.1|GLS Add. GA 1/1|Yes|Yes|Photographic negatives ||&lt;p&gt;The albums comprise of negatives of  life in Germany and eastern Europe. The albums have been indexed and the negatives numbered by Althaus in series I-IV; VII-VIII, though numbering is not continuous. The majority of the negatives have duplicates in slide or photograph format (GA 1/2 and GA 3) and reference has been made to these. The captions are those taken from the index except for unindexed negatives, whereupon the caption has been taken from a duplicate photograph or slide. Where there is no duplicate, the caption simply describes what can be seen in the negative. The list also includes 22 negatives that are indexed in the albums but are missing. There is a closed section from GA 1/1/53 - GA 1/1/68 due to the sensitive nature of the negatives. &lt;&#x0002F;p&gt;||||
51900|ga.1.1.1|GLS Add. GA 1/1/1|Yes|Yes|Ehepaar Weltzel. ||||||
51901|ga.1.1.2|GLS Add. GA 1/1/2|Yes|Yes|Ehepaar Weltzel. ||||||
51902|ga.1.1.3|GLS Add. GA 1/1/3|Yes|Yes|Roßlau, Dessauerstr Kegli. Julius Braun, Bitterfield, 1939 Koitsch. ||||||

Is there a way using sed or awk to get my desired result? Would I first need to add another pipe to the end of the last column and do a replace based on the number of occurrences?

  • Can you tell us more about your input .csv files? are they strictly null terminated (\0), or is the line ending some combination of nulls and other characters such as \n\0 or \r\0? – steeldriver Oct 4 '18 at 14:37
  • @steeldriver they're a mixture of \n and \r – joesch Oct 4 '18 at 14:48
  • 1
    In that case I don't understand why you are using the -z option of sort. Have you tried simply converting them to Unix endings, either with dos2unix or by changing the inputs to <(sed 's/\r$//' alt.csv | sort -d -t"|") & similar? – steeldriver Oct 4 '18 at 14:54
  • @steeldriver, to be honest, I know next to nothing about Unix and have been landed with approx 100k records to process so I'm just feeling around trying to get to grips with it. I'll definitely look into dos2unix and I've only just seen there's a unix2dos which I'll also need. – joesch Oct 5 '18 at 10:56
1

I've sort of found a solution but it's not particularly elegant. I did decide to add an additional pipe to the second file for joining as this allowed me to do some additional processing to get the right format.

Right now the steps I need to take are:

    # add pipe to the end of the line for ORIGINAL files only
    sed -i 's/$/|/' ../original/alt.csv

    --- Do join and output joined file to ../out/alt.csv ---

    # match on last pipe and add a carriage return
    sed -i 's/\(.*\)\|/\0\r/' ../out/alt.csv

    # remove carriage return where join occurred (the use of pipe is simply to locate carriage return) and replace with pipe
    sed -i 's/\r|/|/' ../out/alt.csv

    # remove all blank lines 
    sed -i '/^\s*$/d' ../out/alt.csv

    # remove pipe at the end of the line of output file and add a carriage return
    sed -i 's/[^\r\n].$/\r/' ../out/alt.csv 

If there's an easy way to accomplish this I'd be glad to hear it.

  • If you're pre-processing the input files, why don't you just remove the CRs? – glenn jackman Oct 4 '18 at 14:40

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.