?
Solved

VBS Script to convert csv to txt

Posted on 2014-08-08
18
Medium Priority
?
2,890 Views
Last Modified: 2014-08-11
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
0
Comment
Question by:zequestioner
  • 11
  • 7
18 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 40249573
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 40249579
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40249605
no i do not want the quotes and when i open it excel automatically removes the quotes.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:zequestioner
ID: 40249616
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40249634
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40249714
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 40249848
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40250082
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 40250476
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40251185
Oh, I see now.. the text in my real file is Unicode and the sample i uploaded is ANSI.
0
 
LVL 1

Author Comment

by:zequestioner
ID: 40251186
Do I need to post separate question for that?
0
 
LVL 1

Author Comment

by:zequestioner
ID: 40251193
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 40251195
Can you not just save it with ANSI encoding?
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 2000 total points
ID: 40251199
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40251717
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40251758
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 40252026
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
 
LVL 1

Author Comment

by:zequestioner
ID: 40253533
Thank you very much again! I will update other question now. I think the problem is related to ANSI and UniCode again.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question