How to convert a tab separated file to CSV format?

Question

I have a text file in this format :

{

attribute1 attribute2 attribute3.... attributeN

value"A" value"B" value"C".... value"Z"

/* next line of values*/

}

Each word is separated by a tab.

How do I convert to CSV format? I tried using Excel but it's giving compatibility issues.

Witchery

Note that CSV is not a very defined format. Some use ";" as the separator, some use the ",". The date formatting is also very variable, and the option to delimit the strings wih " or not. You should clarify those requirements with your client (person or process). – Infrequent

Answer 1

Import the data with excel (Data > Load from text file), using tab as a column separator. Then save the file as csv.

It cannot have compatibility issues, it's a basic task and i did it quite often in the past.

Torsi

Answer 2

If you can use a scripting language, you might give Python a shot:

import csv

# read tab-delimited file
with open('yourfile.tsv','r') as fin:
    cr = csv.reader(fin, delimiter='\t')
    filecontents = [line for line in cr]

# write comma-delimited file (comma is the default delimiter)
with open('yourfile.csv','w') as fou:
    cw = csv.writer(fou, quotechar='', quoting=csv.QUOTE_NONE)
    cw.writerows(filecontents)

Example interpreter session:

>>> import csv
>>> with open('yourfile.tsv','r') as fin:
...     cr = csv.reader(fin, delimiter='\t')
...     filecontents = [line for line in cr]
...
>>> with open('yourfile.csv','w') as fou:
...     cw = csv.writer(fou, quotechar='', quoting=csv.QUOTE_NONE)
...     cw.writerows(filecontents)
...
>>> with open('yourfile.csv','r') as see_how_it_turned_out:
...     for line in see_how_it_turned_out: 
...         line
... 
'attribute1,attribute2,attribute3,attributeN\r\n'
'value"A",value"B",value"C",value"Z"\r\n'

---

Notes:

• The default field delimiter is ,.

• The default line terminator for csv.writer is \r\n, but should you have a need to do so you may specify an alternative as a keyword argument AKA kwarg.

Alternative line-terminator example:

with open('yourfile.csv','w') as fou:
    cw = csv.writer(fou,quotechar='',quoting=csv.QUOTE_NONE,lineterminator='\n')
    ...

Solmization

First it give issue that it reading in byte mode, when i changed to 'r' and 'w' then Error: need to escape, but no escapechar set. I am using python 3.6 – Masque

Answer 3

Here's some Excel-VBA code that will do this conversion. Paste this in Excel's visual basic editor (Alt-F11) and run it (after adjusting your filenames, of course).

Sub TabToCsv()

    Const ForReading = 1, ForWriting = 2
    Dim fso, MyTabFile, MyCsvFile, FileName
    Dim strFileContent as String
    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Open the file for input.
    Set MyTabFile = fso.OpenTextFile("c:\testfile.dat", ForReading)

    ' Read the entire file and close.
    strFileContent = MyTabFile.ReadAll
    MyTabFile.Close

    ' Replace tabs with commas.
    strFileContent = Replace(expression:=strFileContent, _
                             Find:=vbTab, Replace:=",") 
    ' Can use Chr(9) instead of vbTab.

    ' Open a new file for output, write everything, and close.
    Set MyCsvFile = fso.OpenTextFile("c:\testfile.csv", ForWriting, True)
    MyCsvFile.Write strFileContent
    MyCsvFile.Close

End Sub

Icj

@jfc: it should also drop the empty lines and those with { and }. – Infrequent

Why? Not necessarily. Depends what OP wants. My code outputs a file in "CSV format" as requested, but "CSV" really means only one thing: values are separated by commas (usually) and line breaks. I can't prejudge whether or not the OP needs the { and } and blank lines in the recipient application. If OP gives an unambiguous example of the desired output, then we'll have a chance to answer properly. – Verso

Answer 4

Just do File > Save as... and choose .csv file format

Mitchell

Answer 5

1. Open MS Excel
2. Click on Data tab
3. Click on From text
4. Choose your tsv file
5. Choose delimited
6. Click on Next
7. Click on Check on tab and comma
8. Click on Finish.

Denudate

To choose your tsv file, make sure ("All files types") is selected in the file explorer on windows. I did that mistake and overlooked the file type my file finder was searching for and kept thinking to myself that I can not select a tsv file as it does not show up and then I realized the file type selection on my finder was set to "Text Files" – Dynamism