VBScript Array

[Face Book]

Hi All,

I need to read a csv file using vbscript See Sample below of CSV:

SAMPLE of CSV File:
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2


when the line is read, it needs to look at the last column and in this example on line 1 is 3 this means this particular line needs to be written 3 times to a new file. etc etc

The output csv need to look like this:
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2


Hope this helps

Thanks in advance

Faizal

[Mayayana]

You have two options. Where s is the line read in:

a = Split(s, ",")
s2 = a(Ubound(a)) ' this will be "3"

x = InStrRev(s, ",")
s2 = Right(s, (Len(s) - x)) ' s2 will be "3"

Don't forget error trapping, just in case
there's a blank line or a line with a non-numeric
string at the end.

The code above assumes you know how to
read in the CSV file and write back the new file.
If not, look up Textstream and FileSystemObject.

[Face Book]

Post by Mayayana
a = Split(s, ",")
s2 = a(Ubound(a)) ' this will be "3"
x = InStrRev(s, ",")
s2 = Right(s, (Len(s) - x)) ' s2 will be "3"
Don't forget error trapping, just in case
there's a blank line or a line with a non-numeric
string at the end.
The code above assumes you know how to
read in the CSV file and write back the new file.
If not, look up Textstream and FileSystemObject.

I am a complete newbie.....
Each line always has a number at the end of it. and there are no blank lines.
full code would help as I am a complete newbie.
& Thanks for th quick reply

[GS]

Post by Face Book
and there are no blank lines.

Mayayana was referring to the typical empty line at the bottom of the
file. This will be the line below the last line of data. Unless the
file was *explicitly* written to exclude that last line then it exists
when you open the file in Notepad and do Ctrl+End!

[Face Book]

Post by GS

Post by Face Book
and there are no blank lines.

Mayayana was referring to the typical empty line at the bottom of the
file. This will be the line below the last line of data. Unless the
file was *explicitly* written to exclude that last line then it exists
when you open the file in Notepad and do Ctrl+End!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry, Yes when I put the csv into notepad there is an extra blank line.

[GS]

Post by Face Book

Post by GS

Post by Face Book
and there are no blank lines.

Mayayana was referring to the typical empty line at the bottom of
the file. This will be the line below the last line of data. Unless
the file was *explicitly* written to exclude that last line then it
exists when you open the file in Notepad and do Ctrl+End!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry, Yes when I put the csv into notepad there is an extra blank line.

Ok, this is what Mayayana was referring to regarding error handling!
One caveat to *not* explicitly excluding this line is that rewrites
often increase the number of trailing blank line if existing ones are
not remove before writing back to the file. In cases where data is
stored in delimited text files, I prefer to have those files structured
same as a database table...

Rule#1: 1st line contains delimited fieldnames only;
Rule#2: Subsequent lines contain records only;
Rule#3: Never include a trailing blank line.

..so that using an array to manipulate the data results as follows:

Array(0) contains fieldnames
UBound(Array) is the number of records

I use the following wrapper procedure to ensure this is how my data
files are structured:


Sub WriteTextFile(TextOut, Filename, _
Optional AppendMode = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFile()

[R.Wieser]

Faizal,

Not a bad explanation, well done. But what have you (already) tried
yourself ? Or, to be more specific: what do you have a *problem* with ?

In other words: First see how far you can go yourself, and only than ask for
help. And when you do that, please be *specific* to what you need help
with. Your current post looks like you want help with everything ...

Step #1 - See if you can read each line of the source CSV file and write
them to a target file (creating an exact copy)

Step #2 - See if you can isolate the last column (you can do that with the
"Split()" function, but you could also use the "InStrRev()" function
(looking for the last ",") )

Step #3 - use the result of step #2 to create a "for" loop to repeat writing
the line.

Hope that helps.

Regards,
Rudy Wieser

Post by Face Book
Hi All,
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
when the line is read, it needs to look at the last column and in this

example on line 1 is 3 this means this particular line needs to be written 3
times to a new file. etc etc

Post by Face Book
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
Hope this helps
Thanks in advance
Faizal

[Face Book]

Post by R.Wieser
Faizal,
Not a bad explanation, well done. But what have you (already) tried
yourself ? Or, to be more specific: what do you have a *problem* with ?
In other words: First see how far you can go yourself, and only than ask for
help. And when you do that, please be *specific* to what you need help
with. Your current post looks like you want help with everything ...
Step #1 - See if you can read each line of the source CSV file and write
them to a target file (creating an exact copy)
Step #2 - See if you can isolate the last column (you can do that with the
"Split()" function, but you could also use the "InStrRev()" function
(looking for the last ",") )
Step #3 - use the result of step #2 to create a "for" loop to repeat writing
the line.
Hope that helps.
Regards,
Rudy Wieser

Post by Face Book
Hi All,
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
when the line is read, it needs to look at the last column and in this

example on line 1 is 3 this means this particular line needs to be written 3
times to a new file. etc etc

Post by Face Book
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 SYMPHONY,441048,12558/18346/MCAFEE,3
ANGLIA,89305,1,4'6 FLOOR STANDING,441048 12558/18346/MCFEE,1
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
ANGLIA,89305,2,4'6 SENSATION,441048,12579/18449/SPARKES,2
Hope this helps
Thanks in advance
Faizal

Hi, I can read the CSV file and output it to a diffrent filename. I am having problems when it comes to the last column and then duplicating the rows and outputing to a new file.

[GS]

Post by Face Book
Hi, I can read the CSV file and output it to a diffrent filename. I
am having problems when it comes to the last column and then
duplicating the rows and outputing to a new file.

Load each line into a dynamic array;
This requires using ReDim statement and specifying the number of
elements to add.
'Join' the array with vbNewLine;
Specify the path;

WriteTextFile Join(Array, vbNewLine), FilePath

Make sure the path includes a trailing path separator!

[GS]

A simplified example that doesn't require using an array...

Sub AddLineToFile()
Dim sFile, sData, sTmp, sOut, n, k
sFile = "C:\Users\Garry\Documents\VBA_Stuff\vbs_AddLinesToFile.txt"
sData = Split(ReadTextFile(sFile), vbCrLf)
For n = LBound(sData) To UBound(sData)
sTmp = Split(sData(n), ",")
For k = 1 To sTmp(UBound(sTmp))
sOut = sOut & "|" & sData(n)
Next 'k
Next 'n
WriteTextFile Replace(Mid(sOut, 2), "|", vbCrLf), sFile
End Sub


''''''''''''''''''
'Support functions
''''''''''''''''''
Function ReadTextFile(Filename)
' Reads large amounts of data from a text file in one single step.
Dim iNum
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFile = Space$(LOF(iNum))
ReadTextFile = Input(LOF(iNum), iNum)

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFile()

Sub WriteTextFile(TextOut, Filename, _
Optional AppendMode = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFile()

[Mayayana]

I think you may have wandered off somewhere
along the way there. I can't make any sense
out of the routine you wrote, but you also
provided VB code for the read and write methods.
They don't work in VBS.

[GS]

Post by Mayayana
I think you may have wandered off somewhere
along the way there. I can't make any sense
out of the routine you wrote, but you also
provided VB code for the read and write methods.
They don't work in VBS.

Yes, I wrote this with vba and so I realize that! I'm trusting someone
(like you) might convert those to vbs. I don't have anything equivalent
for vbs because I rarely use it, but I suspect something FSO should
work.

[GS]

Post by Mayayana
I think you may have wandered off somewhere
along the way there. I can't make any sense
out of the routine you wrote, but you also
provided VB code for the read and write methods.
They don't work in VBS.

Oops! I see I failed to mention in my post about the support functions
being vba. My bad!

It would nice to have vbs versions of those...

[GS]

Ok, I made this vbScript version of the previous vba code I posted. The
invalid vbs lines are commented out so the code is usable in VB/VBA...

Const lOpenR = 1 '//for reading
Const lOpenW = 2 '//for writing
Const lOpenA = 8 '//for appending

'Sub AddLineToFile()
Dim sFile, sData, sTmp, sOut, n, k
sFile = "C:\Users\Garry\Documents\VBA_Stuff\vbs_AddLinesToFile.txt"
sData = Split(FSO_ReadTextFile(sFile), vbCrLf)
For n = LBound(sData) To UBound(sData)
sTmp = Split(sData(n), ",")
For k = 1 To sTmp(UBound(sTmp))
sOut = sOut & "|" & sData(n)
Next 'k
Next 'n
'FSO_WriteTextFile Replace(Mid(sOut, 2), "|", vbCrLf), sFile, lOpenW
'//overwrite
FSO_WriteTextFile Replace(Mid(sOut, 2), "|", vbCrLf), sFile, lOpenA
'//append
'End Sub

Function FSO_ReadTextFile(FileIn)
' Reads an entire file in one step

Dim oFSO, oFile

'On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.OpenTextFile(FileIn, lOpenForR)
FSO_ReadTextFile = oFile.ReadAll

'ErrHandler:
oFile.Close
Set oFSO = Nothing
Set oFile = Nothing
End Function

Sub FSO_WriteTextFile(TextOut, FileOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile, TxtOut

'On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenForA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenForA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenForW)
oFile.Write (TextOut)
End If

'ErrHandler:
oFile.Close
Set oFSO = Nothing
Set oFile = Nothing
End Sub

[GS]

I revised the write routine to create a new file if not appending...

Sub FSO_WriteTextFile(TextOut, FileOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile, TxtOut

'On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenForA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenForA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file contents
Set oFile = oFSO.CreateTextFile(FileOut, lOpenForW)
oFile.Write (TextOut)
End If

'ErrHandler:
oFile.Close
Set oFSO = Nothing
Set oFile = Nothing
End Sub