I found most of this in scripts and tips, I added the first 11 lines. The added lines allow a user to enter a path and file name then sets the path to the variable "directory" and sets the file name to the variable "filename". I'm confused about how to input the directory and filename variables into the vbscript. There is a line in the vbscript that defines the directory: "DefaultDir=C:\;" I tried changing this to "DefaultDir=%directory%;". There is also a line in the vbscript that defines the file name: "select * from test.csv". I tried changing this to: "select * from %filename%". When I made these alterations the script gave errors indicating it was missing the file information. Is there any way to pass these two variables to this vbscript?
I have a method of breaking up a .csv file into usable components that I wrote about 4 years ago but its very difficult to adapt to each new usage. I found this script and found it much easier to use. But for my current situation I don't want to hardcode the path and filename. Perhaps someone has a reliable easy to replicate method of breaking up any CSV file into its various cells that they would be willing to share? Or perhaps there is one already on the forum and I failed to find it.
Thanks for any help,
Dick
Reposted with HTML disabled. Although, I would note that this code does not work though it is now (hopefully) not corrupt.
Code: Select all
Input>file1,CSV File to process
Separate>file1,\,dir
Let>filename=dir_%dir_count%
sub>dir_count,1
Let>directory=
Let>k=0
Repeat>k
add>k,1
let>value=dir_%k%
Concat>directory,%value%\
Until>k=dir_count
VBSTART
'An example file. Save as c: est.csv or change file name and DefaultDir in function
'Remeber to remove comments at start of line if pasting into editor for example file
'header1,header2,header3,header4
'r1c1,r1c2,r1c3
'r2,c1",r2c2,r2c3
'r3c1,"r""3,c""2",r3c3
'r4c1,r4c2,"r,4"",""c3"
'Global Array for holding all data
Dim FieldArray
' ImportCSV - imports data from a CSV file and populates a mulitdimensional array
Sub ImportCSV
Dim fieldnum
Dim recnum
set rs = createobject("ador.recordset")
strConnect = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & "DefaultDir=C:\;"
Const adOpenStatic = 3
rs.open "select * from test.csv", strConnect, adOpenStatic
ReDim FieldArray(rs.recordcount+1,rs.fields.count)
'set record and field counters. I'm started at field 1 rather than 0.
recnum = 0
fieldnum = 1
'add headers in row zero (optional, but handily means actual data starts in row 1)
'if you omit this section data will start in row 0 unless you change recnum declaration above
for each f in rs.fields
if f.name <> "" then
FieldArray(recnum,fieldnum) = f.name
end if
fieldnum = fieldnum + 1
next
recnum = recnum + 1
'end header section
'now add the data starting in row 1
do until rs.eof
fieldnum = 1
for each f in rs.fields
if f.value <> "" then
FieldArray(recnum,fieldnum) = f.value
end if
fieldnum = fieldnum + 1
next
recnum = recnum + 1
rs.movenext
loop
End Sub
VBEND
'Get Data into Array
VBRun>ImportCSV
'First header name
VBEval>FieldArray(0,1),field
MessageModal>field
'Get the first field of the first record:
VBEval>FieldArray(1,1),field
MessageModal>field
'Get the second field of the third record:
VBEval>FieldArray(3,2),field
MessageModal>field
