How to Convert CSV to TSV files by Batch

 
A CSV (comma separated values) file is basically a text file containing structured data, where a comma is used to separate or delimit values in each row or record. A TSV (tab separated values) file is similar to a CSV file, except that it uses a tab to separate values.
 
Both CSVed and TablePad are free applications for manipulating delimitor separated values files. These programs let you open a CSV file and save it as a TSV file, thereby effectively converting from CSV to TSV. But the programs can only convert one file at a time, and currently cannot batch convert all the files contained in a folder in one go.
 
Until this feature becomes available to the programs, here is an alternative way of batch converting files from CSV to TSV:
 
Go to Jojoyee.myfil.es and double click CSV2TSV.exe to download the executable (230 KB), rename the file get_file to CSV2TSV.exe.
 
Or use the VBScript code following the steps below.
 
  1. Copy the code (highlighted in blue) and paste it into Notepad.
  2. Save the file with the vbs extension (e.g. csv2tsv.vbs)
  3. Double-click on the vbs file to trigger the conversion process.
 
'This VBScript is for converting CSV files to TSV (tab delimited) files by batch.
'Copy and paste it in Notepad and save the file with vbs extension (e.g. CSV2TSV.vbs) 
'Double click CSV2TSV.vbs to do the conversion.
'The converted files will be saved as *.tsv while the original files remain.
 
Option Explicit
Dim objWsShell, objFSO, objShellAp, objFolder, objFile, objFileTSV, objResult
Dim strPath, strLine, strNewLine, strNewFileName
Dim TotalFilesConverted, FileNameLength
Set objWsShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("scripting.filesystemobject")
Set objShellAp = CreateObject("Shell.Application")
Set objFolder = objShellAp.BrowseForFolder(0, "BATCH CONVERSION FROM CSV TO TSV FILES" _
& vbLf & vbLf & "Select a folder containing CSV files for the conversion.", 0, 17)
If objFolder is Nothing Then
msgBox "No folder is selected for conversion of CSV files."
WScript.Quit
Else
strPath = objFolder.Self.Path
objWsShell.CurrentDirectory = strPath
End if
TotalFilesConverted = 0
For Each objFile In objFSO.getfolder(strPath).Files
If UCase(Right(objFile.Name, 4)) = ".CSV" Then
objResult = objWsShell.Popup("Converting " & objFile.Name & " ...",3,"")
FileNameLength = Len(objFile.Name)-4
strNewFileName = Left(objFile.Name,FileNameLength) & ".tsv"
Set objFile = objFSO.OpenTextFile(objFile, 1)
Set objFileTSV = objFSO.CreateTextFile(strNewFileName)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If instr(strLine,Chr(34)) =0 Then
strNewLine = Replace(strLine,",",vbTab)
Else
Call LineQuote(strNewLine)
End if
objFileTSV.WriteLine strNewLine
Loop
objFile.Close
TotalFilesConverted = TotalFilesConverted +1
objFileTSV.Close
End If
Next
If TotalFilesConverted =0 Then
MsgBox "No CSV files are found for conversion in the folder."
Else
MsgBox CStr(TotalFilesConverted) + " Files Converted from CSV to TSV."
End if
 
Sub LineQuote(strNewLine)
Dim LineLength, Linepos, blnQuote, Quotepos
LineLength = Len(strLine)
Linepos =1
strNewLine =""
blnQuote = False
Do While Linepos <= LineLength
Quotepos = instr(Mid(strLine,Linepos,LineLength-Linepos+1),Chr(34))
If Quotepos =1 Then
If Linepos < LineLength Then
If Mid(strLine,Linepos,2) = Chr(34) & Chr(34) and blnQuote Then
strNewLine = strNewLine & Chr(34)
Linepos = Linepos +2
Else 'one quote
If blnQuote Then
      blnQuote = False
Else
      blnQuote = True
End if
Linepos = Linepos +1
End if
Else 'last character
Linepos = Linepos +1
End if
Elseif Quotepos >1 Then
If blnQuote Then
strNewLine = strNewLine + Mid(strLine,Linepos,Quotepos-1)
Else 'not Quote
strNewLine = strNewLine + Replace(Mid(strLine,Linepos,Quotepos-1),",",vbTab)
End if
Linepos = Linepos +Quotepos -1
Else 'Quotepos =0
strNewLine = strNewLine + Replace(Mid(strLine,Linepos,LineLength-Linepos+1),",",vbTab)
Linepos = LineLength +1
End If
Loop
End Sub

 

What this code has considered for converting a csv file:

Fields are separated by commas in csv files. Each record is one line terminated by a linefeed or a carriage return. Fields with embeded commas are enclosed with double-quote characters. Fields with embeded double-quote characters are enclosed within double-quote characters, and each of the embedded double-quote characters is represented by a pair of double-quote characters. Fields with leading or trailing spaces are enclosed within double-quote characters. Fields with embeded line breaks have not been considered.

 

Share this
5
Average: 5 (9 votes)
Your rating: None

Comments

by ChemDude (not verified) on 26. June 2012 - 14:54  (95404)

You Sir, are Fantastic :D

by jmrdib on 2. November 2010 - 22:04  (60682)

Thank you very, very much for posting this solution you built Jojoyee. It didn't fully meet my needs... but I modified it to be utilized from a command prompt (dos) w/ multiple parameters for flexibility. I can't quite post it in this reply (due to apparent comment length restrictions)... so I'm going to post it over the next couple comments. Please add it to your site (http://jojoyee.myfil.es/) ... so it may be utilized by others. thx.

by jmrdib on 2. November 2010 - 22:09  (60683)

********Content of VB script***************************

'This VBScript is for converting CSV files to a dilimiter of your choice, files by batch.
'Copy and paste it to a Notepad and save with a vbs extension (e.g. csv2Dilim.vbs).
'Save location can be where ever you wish to execute it from (you can add location to Path sting in autoexec if you wish to access it as a command globally).

'Double click csv2Dilim.vbs for command usage details.
'The converted files will be saved as *.(your specified extention) while the original files remain.

'Usage Syntax: csv2Dilim {Location of files | Extention of Files to Convert | Dilimiter to Use}
' Command Line Usage Example: csv2Dilim "c:\temp location" csv ~

'**************
'References and Credit Due to Jojoyee for the original base VB script (csv2tsv.vbs) which was modified by JSpecht (jm.rd.i.b+csv2Dilim@gmail.com) in order to build this VB script (csv2Dilim.vbs). References below:
'http://jojoyee.myfil.es/
'http://www.techsupportalert.com/content/how-convert-csv-tsv-files-batch.htm
'http://www.techsupportalert.com/freeware-forum/portable-apps/813-little-luxuries-please-share-yours.html
'http://www.techsupportalert.com/freeware-forum/i-want-freeware-program-that/737-csv-to-tab-delimited-text-batch-converter-2.html
'***************

Dim objFSO, objFile, objFileTSV
Dim strLine, strNewLine, strNewFileName
Dim TotalFilesConverted, FileNameLength
set WshShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("scripting.filesystemobject")
strCurPath = objFSO.GetAbsolutePathName(".")
TotalFilesConverted = 0
Set args = WScript.Arguments
If (args.Count < 4) Then
Wscript.Echo "Missing the Required Parameters." &vbCrLf _
&vbCrLf _
& "Usage Syntax: csv2Dilim Location of files | Ext of Files to Convert | Ext of New Files | Dilimiter to Use " &vbCrLf _
&vbCrLf _
& "Example #1: csv2Dilim c:\temp csv txt ~ " &vbCrLf _
& "Example #2: csv2Dilim ""c:\temp location"" txt txt ""|~|"""
Wscript.Quit
End If
arg1 = args.Item(0)
arg2 = args.Item(1)
arg3 = args.Item(2)
arg4 = args.Item(3)

If arg2 = arg3 then
strAddSuffix = "_Mod." & arg3
else strAddSuffix = "." & arg3
End If

by Jojo Yee on 3. November 2010 - 3:46  (60694)

Thank Jmrdib for your suggestion and it's good that you found the vb script useful. The script provided in this article is quite basic, but users who understand the scripts are welcome to modify or add more features to suit their needs.

by Anonymous on 7. July 2009 - 13:18  (24771)

Thanks for support
i would like to know how to convert tsv file to csv?

by Jojo Yee on 8. July 2009 - 2:40  (24780)

Try opening a tsv file with CSVed or TablePad, set comma as separator and save as a new file.

Hope this helps.

by Anonymous on 28. May 2009 - 7:12  (22480)

WOW! Good timing, needed this today :)
Thanks a lot!

Gizmos Needs You

Gizmo's Freeware is Recruiting

 We are looking for people with skills or interest in the following areas:
 -  Mobile Platform App Reviews for Android and iOS
 -  Windows, Mac and Linux software reviews       Interested? Click here