1

I have a text file with 9267 lines. I want to find "A" with 5 spaces after it and replace it with "A" with 5 spaces before and after it on all lines, but only between columns 67 and 88. Text outside of those columns needs to be excluded. I'm hoping sed and/or awk can be of help here, though I don't know how to use them very well.


Details: I'm converting a 128 page PDF into a spreadsheet. I used PDFedit to save the PDF as a text file. I'm planning to use LibreOffice to Insert a "Sheet from File". That's proving to be difficult because while the columns in the PDF line up well, the spreadsheet-type columns aren't in quite the same text-file-type column throughout the text file. Every 50 lines or so is analogous to a different PDF page, and that's where a spreadsheet-type column might move a few text-file-type columns to the left or right. In other words, the spreadsheet-type column for "Shelf" might start on text-file-type column 70 in one part of the file, but start on text-file-type column 76 in another part. That makes it so the spreadsheet-type columns overlap when I try to do a "Fixed Width" Insert into LibreOffice. I want to make the columns more spread out by adding spaces via find and replace so I can tell LibreOffice where to define the columns. If I find and replace on the whole file, I end up putting spaces where I don't want them.


Here's an fairly accurate example:

Item Code             Active Description                                Store Room      Row      Shelf     Bin  On Hand   
 38NUTZSL                Y      3/8"-16 HEX ZINC NUT                     B            03           C         2         0  
 WELD-AL                 Y      WELD, ALUM. ROD, STICK OR SPOOL          A            01             B       2         68.25  
 08300                   Y      WASHER, 7/8 CASE HARDENED 29/32 ID,1 3/4 OD  A            01        C            5         0
  • 2
    What is a delimiter between columns? Some simplified example with ~5 columns and a few lines would be helpful to understand the problem. – jimmij Jul 24 '15 at 17:15
  • @jimmij: I added an example, thanks for the idea. And, to answer your question, the only delimiter is a varying amount of spaces. – aswine Jul 24 '15 at 17:33
  • I'm afraid that if the values in the columns contains spaces and additionally columns are not aligned then the problem is not trivial anymore... – jimmij Jul 24 '15 at 17:37
  • @jimmij: That's why I'm asking for help. :-) I don't need to make the columns line up, I just need to separate them enough to tell LibreOffice what it should think a column is. – aswine Jul 24 '15 at 17:39
3

The better way to change the column separator and use columncommand:

sed 's/   */;/g' file | column -ts';'

which produce:

Item Code  Active  Description                                  Store Room  Row  Shelf  Bin  On Hand
 38NUTZSL  Y       3/8"-16 HEX ZINC NUT                         B           03   C      2    0
 WELD-AL   Y       WELD, ALUM. ROD, STICK OR SPOOL              A           01   B      2    68.25
 08300     Y       WASHER, 7/8 CASE HARDENED 29/32 ID,1 3/4 OD  A           01   C      5    0
  • Thanks, @Costas. That makes things look much nicer. I'll need to do some manual fixing in addition, but that's a great start. Could you update your answer explaining how the command works? – aswine Jul 24 '15 at 18:09
  • @mikeserv: could you expand on that some more? Are you suggesting that I don't use sed, and only use tr, or pipe sed into tr? – aswine Jul 24 '15 at 20:09
  • @mikeserv if there are not spaces in "Description"-column the tr could be OK but … – Costas Jul 25 '15 at 10:59
  • 1
    @mikeserv But in the case there are some spaces in 3rd column – Costas Jul 25 '15 at 15:21
  • @Costas - very good point. – mikeserv Jul 25 '15 at 15:25

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.