CSV to Tab delimited text - batch converter

[stregacomandacolor]

Originally Posted by Jojoyee

A 65MB csv file can now be converted in less than 1 minute depending on the processor.

GREAT!!! Around 3 seconds for a 15 MB csv file. A million thanks!

[Jojoyee]

Some minor touch-up on the codes, and add a feature to browse for a folder containing the csv files. In this way, the Script can be kept in any other folder. Hope this is even better.

'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

[stregacomandacolor]

Originally Posted by Jojoyee

Hope this is even better.

It is!! Thanks!!!

[andrews]

Now that is what I call customer service. I think you should write a small how to article for the main site, include this code and cross link it to some of the appropriate review catagories. I'm sure there are others looking for something like this and they are less likely to stumble upon it in the forums.

Excellent work!
Andy

[Jojoyee]

Originally Posted by andrews

... I think you should write a small how to article for the main site, include this code... I'm sure there are others looking for something like this and they are less likely to stumble upon it in the forums.

Thanks Andy for the kind suggestion.
Done here: http://www.techsupportalert.com/cont...iles-batch.htm

[FrankO]

Outstanding Jojoyee! Very good work!

[Jojoyee]

For ease of use, I've converted the VBScript to the executable. The download link is available from the article How to Convert CSV to TSV files by Batch. Give it a try.

[lovelove]

@Jojoyee:
many thanks for sharing your program with the rest of the world. So far it's the only one I have found which can do batch conversions.

2 suggestions which would make a great program outstanding:

(1) below the DIR selection tree: adding a text box where the DIR path can be pasted (instead of painfully navigating there manually) or - even better - allow a command line parameter like this:
csv2tsv.exe -p "DIRpath"

(2) adding support for multiline fields (which are enclosed and delimited by double quotations marks)


cheers