Converting all csv files to tsv files

[Srezzi]

Hi
I need a vb script to convert all csv files in a folder to tsv files. I want it such that when I execute the script from a folder, all the csv files in that folder get converted to tsv files and also their file name gets appended to the header of the file. Any idea on how thats supposed to be done?

 

[Geates]

Loop through the csv replacing delimiter commas with tabs. Although, it really depends on how the data is structured in the CSV. If the file has a header or commas within data streams will make the conversion harder.

However, by using ADO, one can read the CSV into a recordset which can easily be used to compile a TSV.

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[Srezzi]

Actually I am completely new to vbscripting. Could you help me out with the script?

 

[PHV]

What have you tried so far and where in your script are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Geates]

Sure. I will not discuss ADO as it is out of the scope for someone brand new to VBS. Although, I'm sure someone else will make the suggestion. Actaully, just recently did FengShui1998 ask a very similar question. You might want to read that thread - it may help clarify things (

http://www.tek-tips.com/viewthread.cfm?qid=1640933&page=1).

Also, being so new to vbs, I recommend visiting this reference site. In my opinion, it is clean and easy to follow - very helpful for quick references (

http://raqxtr.sstar.com/caspdoc/html/vbscript_language_reference.htm).

I created a simple CSV in Excel, this is what is looks like.

srezzi.csv
"HeaderA","HeaderB","HeaderC"
"A","B","C"
"A,2","B2","C2"
"A3","B,3","C3"

1. Notice that ALL text is encapulated with quotes.
2. Notice that the values are separated by commas.

Your original request was to convert a csv into a tsv. We can do this with one instruction; namely, replace(opString, searchString, replaceString). Combine it with the loop above to complete the script.

Logic:
1. Open CSV
2. Open new TSV
3. Loop through CSV line by line
i. Change commas to tabs*
ii. Write new TSV line to TSV file.
4. Close open files
5. Cleanup

'0. Declare File System Object
set objFSO = CreateObject("Scripting.FileSystemObject")

'1. Open CSV srezzi.csv for reading (1).  Create the file if it doesn't exist (true) and do so using that ASCII format (0)
set objCSV = objFSO.OpenTextFile("C:\srezzi.csv", 1, true, 0)

'2. Open new TSV srezzi.tsv for writing (2).  Create the file if it doesn't exist (true) and do so using that ASCII format (0)
set objTSV = objFSO.OpenTextFile("C:\srezzi.tsv", 2, true, 0)

'3. Loop through CSV line by line
do while NOT (objCSV.AtEndOfStream)
   strLine = objCSV.ReadLine
   
   'i. Change commas to tabs
   strLine = replace(strLine, ",", vbTab)

   'ii. Write new TSV line to TSV file.
   objTSV.WriteLine strLine
loop

'4. Close open files
objCSV.Close
objTSV.Close

'5. Cleanup
set objFSO = nothing
set objCSV = nothing
set objTSV = nothing

Output:
"HeaderA" "HeaderB" "HeaderC"
"A" "B" "C"
"A 2" "B2" "C2"
"A3" "B 3" "C3"

Well wait a minute! This is a pretty ugly looking TSV, not to mention that the script didn't do what we wanted. Take a look at the "replace" line of code.

   'i. Change commas to tabs
   strLine = replace(strLine, "[red],[/red]", vbTab)

We're searching for commas to replace with tabs. But what about the commas inside the text fields? Those can't be replaced! In this case, we need to change what we're looking for. Instead of making our delimiter a comma, let's make it a quote comma quote.

   'i. Change commas to tabs
   strLine = replace(strLine, "[red]"",""[/red]", vbTab)

Output:
"HeaderA" "HeaderB" "HeaderC"
"A" "B" "C"
"A2" "B2" "C2"
"A3" "B3" "C3"

You may ask, "Why are there so many quotes in search string?" In VBS, a quote is an escape character. An escape character, according to Wikipedia, is a character which invokes an alternative interpretation on subsequent characters in a character sequence. We use an escape character to change the way the script is intrepeted. In this case, we don't want the next quote to symbalize the end of the text string, so we need to escape it.

"Hello ""World""" = Hello "World"
[red]"[/red]Hello [blue]"[/blue][green]"[/green]World[blue]"[/blue][green]"[/green] [red]"[/red]
[red]Open quote[/red], [blue]escape character[/blue], [green]escaped character[/green], [red]closing quote[/red]

I hope this makes sense. Feel free to ask questions.

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[PHV]

The whole '3. loop may be replaced with a single line:

objTSV.Write Replace(objCSV.ReadAll, """,""", vbTab)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886