Gizmos Freeware Reviews  

Go Back   Gizmo's Freeware Forum > Freeware Forum > I Want a Freeware Program that ...

Reply
 
Thread Tools Display Modes
Old 06. May 2009, 10:38 PM   #1 (permalink)
Full Member
 
Join Date: May 2009
Posts: 49
Default CSV to Tab delimited text - batch converter

Hi,
I'd need a batch conveter that can turn CSV files into TAB delimited files. Is there anything like this free? Thanks!
stregacomandacolor is offline   Reply With Quote
Old 07. May 2009, 06:48 AM   #2 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default CSVed

Try CSVed, which allows you to export csv files by setting 'tab' or others as separators under 'save' tab.
http://csved.sjfrancke.nl/index.html
Jojo Yee is offline   Reply With Quote
Old 07. May 2009, 07:38 AM   #3 (permalink)
Full Member
 
Join Date: May 2009
Posts: 49
Default

Quote:
Originally Posted by Jojoyee View Post
Try CSVed, which allows you to export csv files by setting 'tab' or others as separators under 'save' tab.
http://csved.sjfrancke.nl/index.html
Many thanks. I installed it and it seems to be a very powerful program.
However I'd need something (even simplier) that can convert multiple files at the same time. I don't need to make changes to csv files, just convert them to tab delimited files, without opening each file one by one.

Thanks!
stregacomandacolor is offline   Reply With Quote
Old 07. May 2009, 11:58 AM   #4 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default

Yes, CSVed is a powerful program to edit or convert csv file, but the conversion has to be done one at a time for each file. If you have a lot of files to convert, you'll need to spend some time on conversion. So far I've not come across recommendable freeware which allows batch conversion for this. Maybe others can help post here if there's one.

In actual fact, technically the batch conversion from csv to tsv files should not be too difficult. If one has some basic programming experience, he probably can write a simple batch program to do it. Here's an example of vBulletin Script on how to convert csv to tsv files.
Jojo Yee is offline   Reply With Quote
Old 10. May 2009, 02:33 AM   #5 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default Batch convert from csv to tsv files using VB Script

Here's the vBulletin Script I did for converting csv to tsv (tab delimited) files by batch.

Copy the code below to Notepad and save it with a file extension ".vbs", e.g. CSV2TSV.vbs, place it in the same folders where you've all the .csv files, and double click CSV2TSV.vbs to do the conversion. Give it a try.

Code:
'Batch conversion from CSV files to TSV files in the same folder

Dim objFSO, objFile, objFileTSV
Dim strLine, strNewLine, strNewText
Dim FileNameLength, LineLength, NewFileName, Linepos, Quote, QuoteCount, TotalFilesConverted

Set objFSO = CreateObject("scripting.filesystemobject")
strCurPath = objFSO.GetAbsolutePathName(".")
TotalFilesConverted = 0

For Each objFile In objFSO.getfolder(strCurPath).Files
	If UCase(Right(objFile.Name, 4)) = ".CSV" Then
		FileNameLength = Len(objFile.Name)-4
		NewFileName = Left(objFile.Name,FileNameLength) & ".tsv"
		Set objFile = objFSO.OpenTextFile(objFile, 1)
		
		Do Until objFile.AtEndOfStream
			strLine = objFile.ReadLine
			LineLength = Len(strLine)
			Linepos =1
			strNewLine =""
			Quote = False
			QuoteCount = 0
			
			Do While Linepos <= LineLength
				If mid(strLine, Linepos, 1) = "," and Not Quote Then 
					strNewLine = strNewLine + vbTab
					Quote = False
				Elseif mid(strLine, Linepos, 1) = Chr(34) Then
					QuoteCount = QuoteCount +1
					If QuoteCount =2 and Linepos <> LineLength Then
						If mid(strLine, Linepos, 2) = Chr(34) & Chr(34) Then
							strNewLine = strNewLine + Chr(34)
							Linepos = Linepos +1
							Quote = True
							QuoteCount = 1
						Else
							Quote = False
							QuoteCount = 0
						End If
					Else 
						Quote = True
					End If
				Else
					strNewLine = strNewLine + Mid(strLine, Linepos, 1)
				End If
				Linepos = Linepos +1
			Loop
			strNewText = strNewText & strNewLine & vbCrLF
		Loop
		objFile.Close

		Set objFileTSV = objFSO.CreateTextFile(NewFileName)
		objFileTSV.WriteLine strNewText
		TotalFilesConverted = TotalFilesConverted +1
		strNewText = ""
		objFileTSV.Close

	End If
Next

MsgBox CStr(TotalFilesConverted) + " Files Converted from CSV to TSV."
The converted files will be saved with the extension .tsv in the same folder, while the original .csv files will remain. At the end of the conversion, total number of files converted will be shown in a message box.

Last edited by Jojoyee; 10. May 2009 at 11:12 AM.
Jojo Yee is offline   Reply With Quote
Old 10. May 2009, 02:01 PM   #6 (permalink)
Full Member
 
Join Date: May 2009
Posts: 49
Default

Quote:
Originally Posted by Jojoyee View Post
Here's the vBulletin Script I did for converting csv to tsv (tab delimited) files by batch.

Copy the code below to Notepad and save it with a file extension ".vbs", e.g. CSV2TSV.vbs, place it in the same folders where you've all the .csv files, and double click CSV2TSV.vbs to do the conversion. Give it a try.
Many thanks for this. I tried it with different CSV files. It seems to work with smaller files. But the typical CSV files I'm working with are usually very big, up to 100 MB. I'm now trying this script with a 80 MB file, but I cannot see the end of the conversion... and I would have to convert about 10 files like this!
I'm afraid it would take too long :-(
stregacomandacolor is offline   Reply With Quote
Old 11. May 2009, 10:34 AM   #7 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default Converting CSV to TSV by batch (Revised VB Script)

Quote:
Originally Posted by stregacomandacolor View Post
I tried it with different CSV files. It seems to work with smaller files. But the typical CSV files I'm working with are usually very big...I'm afraid it would take too long :-(
Wow the file sizes you're dealing with are huge! You've got it right, it takes too long to process the big files using the previous code.

Now I've revised the code (see below) with these improvements:

1. Use much lesser memory.
2. Process much faster. (It takes about 3 minutes or so to convert a 65MB vbs file, quite reasonable as CSVed takes about the same time as tested by me, could be faster if run on a faster PC.)
3. Add a popup window (set to auto-close within 3 secs) to show which file it is converting.

Code:
'Batch conversion from CSV files to TSV files in the same folder

Dim objFSO, objFile, objFileTSV
Dim strLine, strNewLine
Dim FileNameLength, LineLength, NewFileName, Linepos, Quote, QuoteCount, TotalFilesConverted

set WshShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("scripting.filesystemobject")
strCurPath = objFSO.GetAbsolutePathName(".")
TotalFilesConverted = 0

For Each objFile In objFSO.getfolder(strCurPath).Files

	If UCase(Right(objFile.Name, 4)) = ".CSV" Then
		Result = WshShell.Popup("Converting " & objFile.Name & "...", 3,"" , 64)
		FileNameLength = Len(objFile.Name)-4
		NewFileName = Left(objFile.Name,FileNameLength) & ".tsv"
		Set objFile = objFSO.OpenTextFile(objFile, 1)
		Set objFileTSV = objFSO.CreateTextFile(NewFileName)
		
		Do Until objFile.AtEndOfStream
			strLine = objFile.ReadLine
			LineLength = Len(strLine)
			Linepos =1
			strNewLine =""
			Quote = False
			QuoteCount = 0
			
			Do While Linepos <= LineLength
				If mid(strLine, Linepos, 1) = "," and Not Quote Then 
					strNewLine = strNewLine + vbTab
					Quote = False
				Elseif mid(strLine, Linepos, 1) = Chr(34) Then
					QuoteCount = QuoteCount +1
					If QuoteCount =2 and Linepos <> LineLength Then
						If mid(strLine, Linepos, 2) = Chr(34) & Chr(34) Then
							strNewLine = strNewLine + Chr(34)
							Linepos = Linepos +1
							Quote = True
							QuoteCount = 1
						Else
							Quote = False
							QuoteCount = 0
						End If
					Else 
						Quote = True
					End If
				Else
					strNewLine = strNewLine + Mid(strLine, Linepos, 1)
				End If
				Linepos = Linepos +1
			Loop
			objFileTSV.WriteLine strNewLine
			strNewLine = ""
		Loop
		objFile.Close
		TotalFilesConverted = TotalFilesConverted +1
		objFileTSV.Close

	End If
Next

MsgBox CStr(TotalFilesConverted) + " Files Converted from CSV to TSV."
Hope the above helps.

Last edited by Jojoyee; 11. May 2009 at 10:37 AM.
Jojo Yee is offline   Reply With Quote
Old 11. May 2009, 01:05 PM   #8 (permalink)
Full Member
 
Join Date: May 2009
Posts: 49
Default

Quote:
Originally Posted by Jojoyee View Post
Now I've revised the code (see below) with these improvements:

1. Use much lesser memory.
2. Process much faster. (It takes about 3 minutes or so to convert a 65MB vbs file, quite reasonable as CSVed takes about the same time as tested by me, could be faster if run on a faster PC.)
3. Add a popup window (set to auto-close within 3 secs) to show which file it is converting.
Many thanks! Converting times are definitely better now and also the popup window is very useful! I'll use it for sure!
I also have an exe file that was compiled from Perl by a former collegue that can convert csv into a different format to be imported into translation memories. This exe is much faster, I guess that Perl is faster than VBScript, but unfortunately I cannot write code :-(

Many thanks again!!
stregacomandacolor is offline   Reply With Quote
Old 13. May 2009, 08:49 AM   #9 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default

You're welcome. Not sure if Perl can be faster than VBScript, but how the program is coded can affect the processing speed quite a lot.

The last code can actually be improved further. Later I'll post here a better one which shall run faster. You might want to check it out.
Jojo Yee is offline   Reply With Quote
Old 13. May 2009, 04:39 PM   #10 (permalink)
Maestro di Search
 
Jojo Yee's Avatar
 
Join Date: Jul 2008
Posts: 7,845
Default Convert CSV to TSV files by Batch

Here it is. This is the improved VBScript to do the conversion. With a change in searching methods, it should now run at least 3 times faster than the last code. A 65MB csv file can now be converted in less than 1 minute depending on the processor.

Code:
'This VBScript is for converting CSV files to TSV (tab delimited) files by batch.
'Copy and paste it to a Notepad and save with a vbs extension (e.g. CSV2TSV.vbs) under the same folder of csv files.
'Double click CSV2TSV.vbs to do the conversion.
'The converted files will be saved as *.tsv while the original files remain.

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
For Each objFile In objFSO.getfolder(strCurPath).Files
	If UCase(Right(objFile.Name, 4)) = ".CSV" Then
		Result = WshShell.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
MsgBox CStr(TotalFilesConverted) + " Files Converted from CSV to TSV."

Sub LineQuote(strNewLine)
Dim LineLength, Linepos, Quote, QuoteCount, Quotepos
LineLength = Len(strLine)
Linepos =1
strNewLine =""
Quote = False
QuoteCount = 0
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 Quote Then
				strNewLine = strNewLine & Chr(34)
				Linepos = Linepos +2
			Else 'one quote
				QuoteCount = QuoteCount +1
				If QuoteCount =2 Then
					Quote = False
					QuoteCount =0
				Else
					Quote = True
				End if
				Linepos = Linepos +1
			End if
		Else 'last character
			Linepos = Linepos +1
		End if
	Elseif Quotepos >1 Then
		If Quote 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
	Elseif Quotepos =0 Then
		strNewLine = strNewLine + Replace(Mid(strLine,Linepos,LineLength-Linepos+1),",",vbTab)
		Linepos = LineLength +1
	End If
Loop
End Sub
Jojo Yee is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off



All times are GMT +1. The time now is 06:58 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.