This is a test version of Biostars. For the public version, visit https://www.biostars.org.
convert tsv to csv format

I have a tsv file which is as follows:

drugname prod_ai
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
ATORVASTATIN CALCIUM. ATORVASTATIN CALCIUM
ZOCOR SIMVASTATIN
SIMVASTATIN. SIMVASTATIN
DILANTIN PHENYTOIN
DILANTIN PHENYTOIN
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
ATORVASTATIN CALCIUM. ATORVASTATIN CALCIUM
CRESTOR ROSUVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM
LIPITOR ATORVASTATIN CALCIUM

I want to get unique words as well as I want to convert this tsv file into csv. I have used sed, tr and awk but didn't work. Please help me in this.

shell bash
• 2,433 views • link
updated by Mensur Dlakic Mensur Dlakic • written by smrutimayipanda • smrutimayipanda

I have used sed, tr and awk but didn't work

how did you use that?

What is the output you expect? this?

drugname,prod_ai
LIPITOR ATORVASTATIN,CALCIUM
ATORVASTATIN CALCIUM.,ATORVASTATIN CALCIUM
ZOCOR,SIMVASTATIN
SIMVASTATIN.,SIMVASTATIN
DILANTIN,PHENYTOIN
CRESTOR,ROSUVASTATIN,CALCIUM
• 0 views • link
written by JC JC 
drugname,prod_ai
LIPITOR, ATORVASTATIN CALCIUM
ATORVASTATIN CALCIUM, ATORVASTATIN CALCIUM
ZOCOR,SIMVASTATIN
SIMVASTATIN.,SIMVASTATIN
DILANTIN,PHENYTOIN
CRESTOR,ROSUVASTATIN,CALCIUM

yes i am expecting this result

• 0 views • link
updated by cpad0112 cpad0112 • written by smrutimayipanda • smrutimayipanda

I have used sed, tr and awk but didn't work.

show us the command lines

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum 
sed -e 's/^/"/; s/$/"/; s/\t/","/g;' all_drugs.tsv > data.csv
awk 'BEGIN { FS="\t"; OFS="," } {$1=$1; print}' all_drugs.tsv > data.csv
tr '\t' ',' < all_drugs.tsv > data.csv
• 0 views • link
updated by cpad0112 cpad0112 • written by smrutimayipanda • smrutimayipanda

your code is trying to convert drugname prod_ai to "drugname","prod_ai" as I understand in sed code. In awk code, tab is is getting converted to comma, but no inverted commas as sed code is doing. Tr code is trying convert tab to comma.

What's the output from each one?

• 0 views • link
written by cpad0112 cpad0112

the output is nothing. Its same as input file. now can you please tell me how to do it?

• 0 views • link
written by smrutimayipanda • smrutimayipanda

check if file delimiter is tab or spaces

• 0 views • link
written by cpad0112 cpad0112

1 answer

Perl:

perl -pi -e 's/\t/\,/g' file_name

sed:

sed -i 's/\t/\,/g' file_name

Note that both commands will change the file in-place. If that's hot what you want, delete the -i part from the command and redirect into a different file:

perl -p -e 's/\t/\,/g' file_name > new_file
• 0 views • link
written by Mensur Dlakic Mensur Dlakic

If it didn't work, then you don't have a tab-delimited file. Because the commands I listed above are not of the kind that sometimes work and other times they don't.

By the way, you are not helping yourself by writing one-sentence responses. Unless you tell us what changed as a result of command - if anything - we will not be able to help. If nothing changed in your file after you did my commands, it is a guarantee that you don't have tabs in your file.

• 0 views • link
written by Mensur Dlakic Mensur Dlakic

Log in to answer this question.

More posts like this