Ubuntu – Trying to create a CSV file from a text file in Ubuntu

command linecsv

I have a text file that contains passwords and their respective hashes that is formatted as such:

'plaintext password'
'SHA256 hash'
'SHA3-512 hash'

This format continues for several more passwords. Each password and hash has its own line within the file. Each of the hashes below the 'plaintext password' are its respective hashes. I want to create a CSV file that will end up looking like this:

'plaintext password','SHA256 hash','SHA3-512 hash'

Essentially, I want to combine those three fields into one line for each password, delimited by commas for each field. I have tried searching for similar situations to mine but with no avail. I have tried awk as well as tr -s, but I haven't been able to get exactly what I want. I would appreciate the help!

Best Answer

You can try

paste -d, - - - < file.txt > file.csv

The paste command generally merges lines of files side-by-side (with an optional delimiter supplied by -d). In this case we are telling it to read the standard input stream 3 times - - - instead.

Related Solutions

Ubuntu – Retain white spaces in fields when using awk

Using awk

from=1; to=3; delimiter="|"; awk -F"$delimiter" -v from="$from" -v to="$to" '!/^[[:blank:]]*$/ {printf "Index="; for(i=from; i<=to; i++) {printf $i; if(i<to) {printf "|"};} printf "\n"; printf "Key="; for(i=to+1; i<=NF; i++) {printf $i; if(i<NF) {printf "|"};} printf "\n"}' foo

More readable

from=1; to=3; delimiter="|";
awk -F"$delimiter" -v from="$from" -v to="$to" '!/^[[:blank:]]*$/ {
    printf "Index=";
    for(i=from; i<=to; i++) {
        printf $i;
        if(i<to) {
            printf "|"
        };
    }
    printf "\n";
    printf "Key=";
    for(i=to+1; i<=NF; i++) {
        printf $i;
        if(i<NF) {
            printf "|"
        };
    }
    printf "\n"
}' foo

Example

$ cat foo
a|b|c d e|1|2|3
a|b c|d|1|2|2 3

$ from=1; to=3; delimiter="|"; awk -F"$delimiter" -v from="$from" -v to="$to" '!/^[[:blank:]]*$/ {printf "Index="; for(i=from; i<=to; i++) {printf $i; if(i<to) {printf "|"};} printf "\n"; printf "Key="; for(i=to+1; i<=NF; i++) {printf $i; if(i<NF) {printf "|"};} printf "\n"}' foo
Index=a|b|c d e
Key=1|2|3
Index=a|b c|d
Key=1|2|2 3

Ubuntu – convert txt file to csv seperated with tabs

Ok, so you need to replace the first two and the last space in every line with a comma. You can't just replace every space, because the 3rd field may contain spaces itself. You can do this with regular expression replacement. Here's a sed script/command, that works:

sed -re 's/^(\S*) (\S*) (.*) (\S+)\s*$/\1,\2,\3,\4/' in.txt > out.csv

With the above example this returns:

Account,Units,Description,Delta
2281,19,Toshiba PX-1982GRSUB,0
9618,200,HP MX19942-228b,-25
19246,4,CompuCom HD300g Hard Drive,4

This is still quite fragile with handling empty fields and breaks entirely, if columns other than the 3rd contain spaces. It's very easy to introduce such malformed data if it is formatted manually as done by your boss. You should suggest to him to switch to a more robust table format (e. g. proper CSV & Co.) and editor (common spread sheet tools can manipulate CSV quite well and flexibly, e. g. LibreOffice/OpenOffice Calc, Microsoft Excel and Google Docs).

Best Answer

Related Solutions

Ubuntu – Retain white spaces in fields when using awk

Ubuntu – convert txt file to csv seperated with tabs

Related Question