How to convert space&tab delimited file to CSV?

Shell Programming and Scripting
1

Hello,

I have a text file with space and tab (mixed) delimited file and need to convert into CSV.

# cat test.txt
/dev/rmt/tsmmt32   HP        Ultrium 6-SCSI      J3LZ    50:03:08:c0:02:72:c0:b5  F00272C0B5               0/0/6/1/1.145.17.255.0.0.0   /dev/rmt/c102t0d0BEST
/dev/rmt/tsmmt37   HP        Ultrium 6-SCSI      J3LZ    50:03:08:c0:02:72:c0:97  F00272C097               0/0/6/1/1.145.17.255.4.0.0   /dev/rmt/c103t0d0BEST
/dev/rmt/tsmmt48   IBM       ULT3580-TD3         5AT0                             5458623002               0/0/6/1/1.145.0.255.8.0.0    /dev/rmt/c72t0d0BESTn
/dev/rmt/tsmmt53   IBM       ULT3580-TD3         5AT0                             5458623006               0/0/6/1/1.145.0.255.8.0.1    /dev/rmt/c72t0d1BESTn

I tried the below

sed 's/\t/ /g' test.txt |sed 's/  */ /g' |sed 's/ /,/g' >test.csv

It works but unfortunately, few fields have blank values and with above technique, values are getting shifted to left. Please advise.

Also, how to find out if a file has spaces or tabs, do I need to use an editor which shows control characters?

Thanks in advance!

2

Perhaps you could use the expand command and then replace 2 or more spaces with comma:

# expand text.txt | sed 's/   */,/g' > test.csv

or replace tab and 2 or more spaces with comma:

# sed -e 's/\t/,/g' -e 's/   */,/g' infile
3

Thanks Chubler_XL for the reply but unfortunately both solutions generate same output as mine.

4

Can you post your input and the (undesired) output you are getting.

5 
$ cat test.txt
/dev/rmt/tsmmt32   HP        Ultrium 6-SCSI      J3LZ    50:03:08:c0:02:72:c0:b5  F00272C0B5               0/0/6/1/1.145.17.255.0.0.0   /dev/rmt/c102t0d0BEST
/dev/rmt/tsmmt37   HP        Ultrium 6-SCSI      J3LZ    50:03:08:c0:02:72:c0:97  F00272C097               0/0/6/1/1.145.17.255.4.0.0   /dev/rmt/c103t0d0BEST
/dev/rmt/tsmmt48   IBM       ULT3580-TD3         5AT0                             5458623002               0/0/6/1/1.145.0.255.8.0.0    /dev/rmt/c72t0d0BESTn
/dev/rmt/tsmmt53   IBM       ULT3580-TD3         5AT0                             5458623006               0/0/6/1/1.145.0.255.8.0.1    /dev/rmt/c72t0d1BESTn


$ expand test.txt | sed 's/   */,/g'
/dev/rmt/tsmmt32,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:b5,F00272C0B5,0/0/6/1/1.145.17.255.0.0.0,/dev/rmt/c102t0d0BEST
/dev/rmt/tsmmt37,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:97,F00272C097,0/0/6/1/1.145.17.255.4.0.0,/dev/rmt/c103t0d0BEST
/dev/rmt/tsmmt48,IBM,ULT3580-TD3,5AT0,5458623002,0/0/6/1/1.145.0.255.8.0.0,/dev/rmt/c72t0d0BESTn
/dev/rmt/tsmmt53,IBM,ULT3580-TD3,5AT0,5458623006,0/0/6/1/1.145.0.255.8.0.1,/dev/rmt/c72t0d1BESTn


$ sed -e 's/\t/,/g' -e 's/   */,/g' test.txt
/dev/rmt/tsmmt32,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:b5,F00272C0B5,0/0/6/1/1.145.17.255.0.0.0,/dev/rmt/c102t0d0BEST
/dev/rmt/tsmmt37,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:97,F00272C097,0/0/6/1/1.145.17.255.4.0.0,/dev/rmt/c103t0d0BEST
/dev/rmt/tsmmt48,IBM,ULT3580-TD3,5AT0,5458623002,0/0/6/1/1.145.0.255.8.0.0,/dev/rmt/c72t0d0BESTn
/dev/rmt/tsmmt53,IBM,ULT3580-TD3,5AT0,5458623006,0/0/6/1/1.145.0.255.8.0.1,/dev/rmt/c72t0d1BESTn


$

Desired output

/dev/rmt/tsmmt32,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:b5,F00272C0B5,0/0/6/1/1.145.17.255.0.0.0,/dev/rmt/c102t0d0BEST
/dev/rmt/tsmmt37,HP,Ultrium 6-SCSI,J3LZ,50:03:08:c0:02:72:c0:97,F00272C097,0/0/6/1/1.145.17.255.4.0.0,/dev/rmt/c103t0d0BEST
/dev/rmt/tsmmt48,IBM,ULT3580-TD3,5AT0,,5458623002,0/0/6/1/1.145.0.255.8.0.0,/dev/rmt/c72t0d0BESTn
/dev/rmt/tsmmt53,IBM,ULT3580-TD3,5AT0,,5458623006,0/0/6/1/1.145.0.255.8.0.1,/dev/rmt/c72t0d1BESTn
6

You may have to deal with missing data individually, here I check the 58th character to see if it is a space and replace all spaces from here with a comma. You will need to repeat this technique for any column that may contain blank values.

sed -Ee 's/^(.{57})  */\1,/' -e 's/\t/,/g' -e 's/   */,/g' infile
7

Hi, maybe the best approach is to start with how the data was generated in the first place?
How are you generating the data thats going into this file? If its been giving to you by some one else, it may be a good idea to ask them this question.

Maybe there is a a way to control the input. Those blank spaces can be populated with "null" or "blank".