convert tsv to csv format
1
0
Entering edit mode
13 months ago

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 • 486 views
ADD COMMENT
0
Entering edit mode

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
ADD REPLY
0
Entering edit mode
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

ADD REPLY
0
Entering edit mode

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

show us the command lines

ADD REPLY
0
Entering edit mode
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
ADD REPLY
0
Entering edit mode

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?

ADD REPLY
0
Entering edit mode

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

ADD REPLY
0
Entering edit mode

check if file delimiter is tab or spaces

ADD REPLY
0
Entering edit mode

If above things are not working, one last thing you could do is, open the file in excel and save it as 'comma separated values (CSV)'. Please follow the tutorial here: https://kbroman.org/dataorg/pages/csv_files.html or here: https://support.microsoft.com/en-us/office/save-a-workbook-to-text-format-txt-or-csv-3e9a9d6c-70da-4255-aa28-fcacf1f081e6

ADD REPLY
2
Entering edit mode
13 months ago
Mensur Dlakic ★ 14k

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
ADD COMMENT
0
Entering edit mode

it didn't work at all

ADD REPLY
2
Entering edit mode

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.

ADD REPLY

Login before adding your answer.

Traffic: 1010 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6