Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Modifying this VB Script output for Excel CSV (Easy I think) 1

Status
Not open for further replies.
rjsws

rjsws

Technical User
Jun 20, 2010
2
US
I am writing data to a CSV file and I'm a little lost with this script. I borrowed this script from an old form from work and would like to modify two things. Please take a look at my issues and help come up with a solution.

If I fill out the form 3 times this is currently what I get written into a CSV:
Rob,6/19/2010,4:59:22 PM,1,2,3,4,5,6,7,8,9,
Rob,6/19/2010,4:59:24 PM,1,2,3,4,5,6,7,8,9,
Rob,6/19/2010,4:59:26 PM,1,2,3,4,5,6,7,8,9,

Rob = The user account
6/19/2010 4:59:22 PM = Current date and time
The numbers are just fillers in my form for the different textareas and dropdowns


Issue 1: I would like the Date/Time to be seperated.
The output should look something like:

Rob,6/19/2010,4:59:22 PM,1,2,3,4,5,6,7,8,9,


Issue 2: I would like to add a "Header" to my CSV file
If I filled out the form 3 times I would expect the output to look something like this:

Oracle,Date,Time,Box1,Box2,Box3,Box4,Box5,Box6,Box7,Box8,Box9,
Rob,6/19/2010,4:59:22 PM,1,2,3,4,5,6,7,8,9,
Rob,6/19/2010,4:59:24 PM,1,2,3,4,5,6,7,8,9,
Rob,6/19/2010,4:59:26 PM,1,2,3,4,5,6,7,8,9,


Here's the script I'm using...



<meta name="vs_defaultClientScript" content=VBScript>
<HTA:APPLICATION
ID= "Tier 2 Tracker"
VERSION= "0.1a"
APPLICATIONNAME= "Tier 2 Tracking Form"
BORDER= "THIN"
CAPTION= "yes"
SHOWINTASKBAR= "yes"
MAXIMIZEBUTTON= "no"
INNERBORDER= "NO"
SINGLEINSTANCE= "yes"
SYSMENU= "yes"
SELECTION= "NO"
SCROLL= "no"
STATUS= "YES"
>

<script id="clientEventHandlersVBS" language="VBScript">

Sub Find_User
set e = CreateObject("WScript.Network")
SPID.InnerHTML = e.UserName
MyDateTime.InnerHTML = Now
End Sub

Sub Write_CSV
Incomplete = 0
WriteRow = ""
WriteRow = SPID.innerText & ","
WriteTemp = MyDateTime.innerText
WriteRow = Writerow & WriteTemp & ","
on error resume next
MyNumber = 1
do until MyNumber = 800
Pull_Down_Name = "r" & MyNumber
WriteRow = Writerow & replace(trim(Document.SurveyForm.Elements(Pull_Down_Name).Value),",",";") & ","
MyNumber = MyNumber +1
if err.number = 0 then
if len(Document.SurveyForm.Elements(Pull_Down_Name).Value) < 1 then Incomplete = Incomplete + 1
end if
err.clear
loop
on error goto 0
If Incomplete > 0then
Msgbox "Please fill in all text boxes and select a response for every question and click SUBMIT again."
'msgbox Incomplete
else
WriteRow = replace(WriteRow,chr(13)," ")
WriteRow = replace(WriteRow,chr(10)," ")
WriteRow = trim(WriteRow)
MyFullFileName = "C:\Users\robertjsadler\Documents\Stream\CoC\Data\CloudyOrClear.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(MyFullFileName,8,true,0)
objFile.WriteLine WriteRow
objFile.Close
SurveyForm.reset
end if
end sub

</script>


Please help.

Thank you,
Rob
 
Sort by date Sort by votes
For Issue 1: Compute date and time separately
For Issue 2: Before opening the file, proove if the file already exists. If the file doesn't exist then write the header line first
Code: 
[COLOR=#0000ff]'# Date and Time [/color]
my_date_time [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]Now[/color]
wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"my_date_time = "[/color] [COLOR=#804040][b]&[/b][/color] my_date_time

my_date [COLOR=#804040][b]=[/b][/color] [COLOR=#2e8b57][b]Date[/b][/color]
wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"my_date = "[/color][COLOR=#804040][b]&[/b][/color] my_date

my_time [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]Time[/b][/color]
wscript[COLOR=#804040][b].[/b][/color]echo [COLOR=#ff00ff]"my_time = "[/color] [COLOR=#804040][b]&[/b][/color] my_time


[COLOR=#0000ff]'# appending data to a file with header[/color]
my_filename [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"output_file.txt"[/color]
[COLOR=#804040][b]set[/b][/color] objFSO [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Scripting.FileSystemObject"[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]'before opening the file, proove if the file already exists[/color]
my_path [COLOR=#804040][b]=[/b][/color] objFSO[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]GetAbsolutePathName[/color][COLOR=#804040][b]([/b][/color]my_filename[COLOR=#804040][b])[/b][/color]
file_existed_before [COLOR=#804040][b]=[/b][/color] objFSO[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]FileExists[/color][COLOR=#804040][b]([/b][/color]my_path[COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]'open the file[/color]
[COLOR=#804040][b]set[/b][/color] objFile [COLOR=#804040][b]=[/b][/color] objFSO[COLOR=#804040][b].[/b][/color]OpenTextFile[COLOR=#804040][b]([/b][/color]my_filename[COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]8[/color][COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]true[/color][COLOR=#804040][b],[/b][/color][COLOR=#ff00ff]0[/color][COLOR=#804040][b])[/b][/color]

[COLOR=#0000ff]'if file is opened first time then write the header[/color]
[COLOR=#804040][b]if[/b][/color] [COLOR=#804040][b]not[/b][/color] file_existed_before [COLOR=#804040][b]then[/b][/color]
  my_header [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"This is a header, which shoud be written only once.."[/color]
  objFile[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]WriteLine[/color] my_header
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]

[COLOR=#0000ff]'append the data line[/color]
my_line [COLOR=#804040][b]=[/b][/color] my_date_time
objFile[COLOR=#804040][b].[/b][/color][COLOR=#a020f0]WriteLine[/color] my_line
objFile[COLOR=#804040][b].[/b][/color][COLOR=#804040][b]Close[/b][/color]
Output written to output_file.txt after running the ebove script 3 times
Code: 
This is a header, which shoud be written only once..
21. 6. 2010 11:00:32
21. 6. 2010 11:00:33
21. 6. 2010 11:00:34
 
Upvote 0 Downvote
I wish I could tell you this code has helped me!!! Thank you soooooooooooo much Mikrom!!!

-Rob
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

wchull
  • Locked
  • Question
How do I resolve this permissions error in my script?
Replies
0
Views
228
wchull
wchull
bdwilcox
  • Locked
  • Question
Script seeing input variable value as blank but sees hard-coded value 4
Replies
6
Views
679
bdwilcox
bdwilcox
Brianfree
  • Locked
  • Question
Excel Rows to Column 1
Replies
2
Views
298
Brianfree
Brianfree
jtm2020hyo
  • Locked
  • Question
What is wrong with this code?
Replies
3
Views
252
strongm
strongm
Katto
  • Locked
  • Question
Excel VBA functions in VBScript
Replies
4
Views
309
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top