VBS Script to convert csv to txt

i have uploaded to this message a sample csv file. all data is in first column and is comma separated.

here is what i do manually, I open the file as a comma delimited file (click file, open, choose file, highlight all data, click on Data tab in Excel, and click onText to Columns button, choose comma delimited) so that the data is separated into there own column and then i need to save the data as a tab separated text file.

Is it possible to do this faster conversion with a vb script?
conv.csv
LVL 1
zequestionerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
Could be as simple as this, depending on those quotations...

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile("c:\yourfolder\conv.csv")
Dim objOut : Set objOut = objFSO.CreateTextFile("C:\yourfolder\new_conv.csv")

arrData = objFile.ReadAll
objOut.Write Replace(arrData,",",vbTab)
objFile.Close
objOut.Close

Open in new window

0
sirbountyCommented:
Modifying the replace line to read this will remove the quotes, if that's what you're after:
Replace(Replace(arrData, ",",vbTab), chr(34), "")

Open in new window

0
zequestionerAuthor Commented:
no i do not want the quotes and when i open it excel automatically removes the quotes.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

zequestionerAuthor Commented:
When I run this, it produces a text file with NULL across the page. Also, the openTxt file is actually a .csv. I want to open the csv and then save to regular txt.

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile("c:\yourfolder\conv.csv")
Dim objOut : Set objOut = objFSO.CreateTextFile("C:\yourfolder\new_conv.csv")
arrData = objFile.ReadAll
objOut.Write Replace(Replace(arrData, ",",vbTab), chr(34), "")
objFile.Close
objOut.Close
0
zequestionerAuthor Commented:
here is my code: (but for some reason it does not open the file as comma delimited)

This code also saves as .xlsx but i want to save to txt

can you tell what is wrong?

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
  WScript.Echo "Input file does not exist."
  WScript.Quit
End If

' Build the full paths to the input and output files
strInPath = objFSO.GetAbsolutePathName(strInFile)
strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
objExcel.Workbooks.OpenText strInPath,,,xlDelimited,,,,,,,True,"|"
Set objWorkbook = objExcel.ActiveWorkBook
                                           
' Save as an Excel file, close Excel                                            
objWorkbook.SaveAs strOutPath, 51
objWorkbook.Close False
objExcel.Quit
0
zequestionerAuthor Commented:
I changed this line:
objExcel.Workbooks.OpenText strInPath,,,xlDelimited,,,,,,,True,"|"

to this:

objExcel.Workbooks.OpenText strInPath,,,xlDelimited,,,,,,,True,","

but it still does not open as comma delimited.

also, i can change this: objFSO.GetBaseName(strInPath) & ".xlsx" to anything such as txt or csv, et. to save it how i need.
0
sirbountyCommented:
Yeah, you're loading up Excel to accomplish this, which add a load on process time...if all you're needing changed is the name to a .txt, then this should do it:
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objFile : Set objFile = objFSO.OpenTextFile("c:\yourfolder\conv.csv")
Dim objOut : Set objOut = objFSO.CreateTextFile("C:\yourfolder\conv.txt")
arrData = objFile.ReadAll
objOut.Write Replace(Replace(arrData, ",",vbTab), chr(34), "")
objFile.Close
objOut.Close

Open in new window

0
zequestionerAuthor Commented:
For some reason, your code works with the sample csv file but it does not work with my 'real' customer csv file.

it is all the same data and same format, my real file simply has many more rows. (12k+ rows) plus it has longer names and longer email addresses.

It is the same format:

username    email    screen-name
my.username,"my.username@yahoo.com","my.screenname"

both of my files (sample and real file) work if I do process given to you with excel. (open file manually)

can you make it work on larger number of file without excel or do i need excel still?
0
sirbountyCommented:
You should not need excel, no.
It could be the character set used - when you open in notepad and click save as, what is the encoding marked as?
0
zequestionerAuthor Commented:
Oh, I see now.. the text in my real file is Unicode and the sample i uploaded is ANSI.
0
zequestionerAuthor Commented:
Do I need to post separate question for that?
0
zequestionerAuthor Commented:
excel must have automatic change it from unicode to ansi when i made the sample file since i did not change that. I apologize for that.
0
sirbountyCommented:
Can you not just save it with ANSI encoding?
0
sirbountyCommented:
Try this version - should test for encoding before proceeding...

SourceFile = "c:\yourfolder\conv.csv"
TargetFile = "c:\yourfolder\conv.txt"
OpenAsUnicode = False

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
'Detect Unicode Files
Dim Stream :Set Stream = objFSO.OpenTextFile(SourceFile, 1, False)
intChar1 = Asc(Stream.Read(1))
intChar2 = Asc(Stream.Read(1))
Stream.Close
If intChar1 = 255 And intChar2 = 254 Then 
    OpenAsUnicode = True
End If

'Get script content
Set Stream = objFSO.OpenTextFile(SourceFile, 1, 0, OpenAsUnicode)
arrData = Stream.ReadAll()
Stream.Close

'Create output file
Dim objOut : Set objOut = objFSO.CreateTextFile(TargetFile)
objOut.Write Replace(Replace(arrData,",",vbTab),chr(34), "")
objOut.close

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zequestionerAuthor Commented:
Thank you sir bounty this code will work perfectly for me. can you please help with other question i have? nobody seem to help and maybe i don't explain good. i can explain better if you need. thank you again for help me with this i thank you greatly.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28493779.html#a40250545
0
zequestionerAuthor Commented:
also is it possible i can have two vars change so they can be arguments on command line?

instead of currently static for example:
SourceFile = "c:\yourfolder\conv.csv"
TargetFile = "c:\yourfolder\conv.txt"

instead, so i can do this from cmd.exe:
cscript  yourscript.vbs  SrouceFile.csv  Targetfile.txt

I try to do with this but I get subscript out of range error:

'Const ForReading = 1
'Const ForWriting = 2
'SourceFile = Wscript.Arguments(1)
'TargetFile = Wscript.Arguments(2)
0
sirbountyCommented:
I try to do with this but I get subscript out of range error:

The reason is, the wscript arguments are read cardinally, not ordinally...

Try SourceFile=wscript.arguments(0) instead (1) for the 2nd element...

Glad I could help - thanks for the grade! :^)
0
zequestionerAuthor Commented:
Thank you very much again! I will update other question now. I think the problem is related to ANSI and UniCode again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.