help with VBScript - cannot get desired result

Hi experts, I'm trying to code a vbscript that will open a csv file and write the contents to a fixed format txt file. The Txt file will always contain 562 byte records in 17 possible fields.  The user is prompted for the fields that will be output and then the conversion will take place.
The csv file can sometimes contain records that have an extra comma in the record that throws the record off, as in the example below in line 2  (notice extra comma in record between "Kenney" and "Esq"

FName,LName,Addr,City,ST,Zip
Mr. Alana,Arbe-Blakey,34 9th Ave Apt 3c,New York,NY,10001
Alfreida,"Kenney, Esq",26 Broadway,New York,NY,10004
Mr. Alexander,Iwachiw,345 8th Ave Apt 17f,New York,NY,10001

So I must first remove that extra comma and then split the record according to the fields provided in csv file.
I'm getting strange results in my output file whereby my code is skipping in writing all the records in the csv file. Can someone please help me find what I'm doing wrong?
Thanks!

dim fs,objTextFile
set fs=CreateObject("Scripting.FileSystemObject")

On Error Resume Next
'Err.Raise 6   ' Raise an overflow error.
'MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description & "on line # " & cnt
'Err.Clear   ' Clear the error.

cnt = 0

'Asks the user to type the message
strFilename = InputBox("What is the csv filename that you are converting:","Messenger Service", strFilename)
inputFile = strFilename & ".csv"
outputFile = strFilename & ".txt"
If strFilename = "" Then
   Wscript.Echo "Operation canceled by the user"
   Wscript.Quit
End If

'strNumfields = InputBox("How many fields are in file?","Messenger Service", strNumfields)
Wscript.Echo "Line  1: Title         Start:   1   End: 20   Len: 20" & vbCrLf & _
             "Line  2: Fname     Start:  21   End: 60   Len: 40" & vbCrLf & _
             "Line  3: Mid Init    Start:  61   End: 70   Len: 10" & vbCrLf & _
             "Line  4: Lname     Start:  71   End:110   Len: 40" & vbCrLf & _
             "Line  5: Suffx       Start: 111   End:120   Len: 10" & vbCrLf & _
             "Line  6: FullName  Start: 121   End:160   Len: 40" & vbCrLf & _
             "Line  7: Addr1      Start: 161   End:200   Len: 40" & vbCrLf & _
             "Line  8: Addr2      Start: 201   End:240   Len: 40" & vbCrLf & _
             "Line  9: Addr3      Start: 241   End:280   Len: 40" & vbCrLf & _
             "Line 10: City        Start: 281   End:320   Len: 40" & vbCrLf & _
             "Line 11: ST           Start: 321   End:322   Len:  2" & vbCrLf & _
             "Line 12: Zip          Start: 323   End:332   Len: 10" & vbCrLf & _
             "Line 13: phone1   Start: 333   End:352   Len: 20" & vbCrLf & _
             "Line 14: phone2   Start: 353   End:372   Len: 20" & vbCrLf & _
             "Line 15: Patron    Start: 373   End:392   Len: 20" & vbCrLf & _
             "Line 16: Code       Start: 393   End:412   Len: 20" & vbCrLf & _
             "Line 17: other       Start: 413   End:562   Len:150"

strPos = InputBox("what are the fields to convert, seperate with comma?","Messenger Service", strPos)
dim arrStr, strMessage, spacetoadd, fieldSizes, strlen, asize
intSize = 0
spacetoadd = 0
fieldSizes = 0
fieldSizes = CLng(fieldSizes)
strlen = 0
asize = 0
fsize1 = 20
fsize2 = 40
fsize3 = 10
fsize4 = 40
fsize5 = 10
fsize6 = 40
fsize7 = 40
fsize8 = 40
fsize9 = 40
fsize10 = 40
fsize11 = 2
fsize12 = 10
fsize13 = 20
fsize14 = 20
fsize15 = 20
fsize16 = 20
fsize17 = 150
field1 = space(20)
field2 = space(40)
field3 = space(10)
field4 = space(40)
field5 = space(10)
field6 = space(40)
field7 = space(40)
field8 = space(40)
field9 = space(40)
field10 = space(40)
field11 = space(2)
field12 = space(10)
field13 = space(20)
field14 = space(20)
field15 = space(20)
field16 = space(20)
field17 = space(150)

Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile(outputFile,2,true)
set objTextFile = fs.OpenTextFile(inputFile,1)
'Wscript.Echo "INput file:" & inputFile
Wscript.Echo "Output file:" & outputFile
'set objTextFile = fs.OpenTextFile("testconv100.csv")

Do while NOT objTextFile.AtEndOfStream
    StrLine = objTextFile.Readline
    Wscript.Echo "Strline before replace = " & StrLine
    StrLine = Replace (StrLine, ", ", " ",1)
    Wscript.Echo "Strline after replace = " & strLine
    arrStr = split(objTextFile.ReadLine,",")
    fField = Split(strPos, ",")
    field = ""
   
  For i = LBound(arrStr) to UBound(arrStr)
    'Wscript.Echo "checkString= " & objTextFile.Readline
    field = ""
      If fField(i) = 1 then
          strlen = len(arrStr(i))
          spacetoadd = fsize1-strlen
          field1 = field & arrStr(i) & space(spacetoadd)
          'field1 = replace(field1, ",", "")
          field1 = replace(field1, chr(34), "")
          'MsgBox "field is Title " & field1
    Elseif fField(i) = 2 then
          strlen = len(arrStr(i))
          spacetoadd = fsize2-strlen
          field2 = field & arrStr(i) & space(spacetoadd)
          'field2 = replace(field2, ",", "")
          field2 = replace(field2, chr(34), "")
          'field2 = field & strNewText & space(spacetoadd)
          'MsgBox "field is fname " & field2
    Elseif fField(i) = 3 then
          strlen = len(arrStr(i))
          spacetoadd = fsize3-strlen
          field3 = field & arrStr(i) & space(spacetoadd)
          'field3 = replace(field3, ",", "")
          field3 = replace(field3, chr(34), "")
          'MsgBox "field is Mid Init " & field3
    Elseif fField(i) = 4 then
          strlen = len(arrStr(i))
          spacetoadd = fsize4-strlen
          field4 = field & arrStr(i) & space(spacetoadd)
          field4 = replace(field4, chr(34), "")
          MsgBox "field is Lname " & field4
    Elseif fField(i) = 5 then
          strlen = len(arrStr(i))
          spacetoadd = fsize5-strlen
          field5 = field & arrStr(i) & space(spacetoadd)
          field5 = replace(field5, chr(34), "")
          'MsgBox "field is suffix " & field5
    Elseif fField(i) = 6 then
          strlen = len(arrStr(i))
          spacetoadd = fsize6-strlen
          field6 = field & arrStr(i) & space(spacetoadd)
          field6 = replace(field6, chr(34), "")
          'MsgBox "field is Fullname " & field6
    Elseif fField(i) = 7 then
     strlen = len(arrStr(i))
          spacetoadd = fsize7-strlen
          field7 = field & arrStr(i) & space(spacetoadd)
          field7 = replace(field7, chr(34), "")
          'MsgBox "field is Addr1 " & field7
    Elseif fField(i) = 8 then
     strlen = len(arrStr(i))
          spacetoadd = fsize8-strlen
          field8 = field & arrStr(i) & space(spacetoadd)
          'MsgBox "field is Addr2 " & field8
    Elseif fField(i) = 9 then
     strlen = len(arrStr(i))
          spacetoadd = fsize9-strlen
          field9 = field & arrStr(i) & space(spacetoadd)
          'MsgBox "field is Addr3 " & field9
    Elseif fField(i) = 10 then
     strlen = len(arrStr(i))
          spacetoadd = fsize10-strlen
          field10 = field & arrStr(i) & space(spacetoadd)
          'MsgBox "field is City " & field10
    Elseif fField(i) = 11 then
     strlen = len(arrStr(i))
          spacetoadd = fsize11-strlen
          field11 = field & arrStr(i) & space(spacetoadd)
          'MsgBox "field is ST " & field11
    Elseif fField(i) = 12 then
     strlen = len(arrStr(i))
          spacetoadd = fsize12-strlen
          field12 = field & arrStr(i) & space(spacetoadd)
          'MsgBox "field is Zip " & field12
    Else
          MsgBox "field is something else " & fField(i)
    End If
   
        field = field & arrStr(i)
        'field = field & space(spacetoadd)
        'Wscript.Echo "field =" & field
  Next
  'objFileToWrite.WriteLine(field)
  outputString = UCase((field0 & field1 & field2 & field3 & field4 & field5 & field6 & field7 & field8 & field9 & field10 & field11 & field12 & field13 & field14 & field15 & field16 & field17))
  'objFileToWrite.WriteLine(field0) & 
  Wscript.Echo "Output line to write :" & outputString
  objFileToWrite.WriteLine(outputString)
  cnt = cnt + 1
Loop

If Err.Number <> 0 Then
   MsgBox "There were lines that did not fit the columns - take a look at output"
End IF

objFileToWrite.Close
objTextFile.Close
set objTextFile = Nothing
set fs = Nothing
Wscript.Echo "Total Record Count:" & cnt
csvtomainframe.vbs
test3.csv
apollinait mgrAsked:
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.

Patrick MatthewsCommented:
You sample source data look like these:

FName,LName,Addr,City,ST,Zip
Mr. Alana,Arbe-Blakey,34 9th Ave Apt 3c,New York,NY,10001
Alfreida,"Kenney, Esq",26 Broadway,New York,NY,10004
Mr. Alexander,Iwachiw,345 8th Ave Apt 17f,New York,NY,10001

And yet:
Your code indicates that you should have 17 fields, and not just the 6 above
Your code indicates the first field should be a title, and yet your 1st and 3rd rows of data seem to bundling a title in with your first name
Your code also indicates that you have up to three fields for the street address, and yet your sample data has one street address field and then skips right to the city

This would be much, much clearer if your sample data agreed with your code :)
0
apollinait mgrAuthor Commented:
That's the purpose of my script, to take in different csv files and map the fields to the fixed format with 17 fields.
line 39 accomplishes this. it asks which fields are in the csv file, thus with the test csv file - I would enter 2,4,7,10,11,12.
Thus you are telling the script which fields are in the csv file so it knows where to place them within the 17 fields of the txt file
0
Patrick MatthewsCommented:
OK, I see that now.

This uses Regular Expressions to handle fields which include commas.  For more info on RegExp you can see my article here: http://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

This should be more than enough to get you started.

Dim fso, tsIn, tsOut
Dim RegX
Dim FieldSizes(16)
Dim FieldValues(16)
Dim TheLine
Dim Mats
Dim LineSplit
Dim Counter
Dim CurrentValue
Dim FieldsPresent
Dim strPos
Dim FieldLength

Const FldPath = "C:\Users\pmatthews\Documents\Personal\Experts Exchange\Question Files\Q_28785839\"

FieldSizes(0) = 20
FieldSizes(1) = 40
FieldSizes(2) = 10
FieldSizes(3) = 40
FieldSizes(4) = 10
FieldSizes(5) = 40
FieldSizes(6) = 40
FieldSizes(7) = 40
FieldSizes(8) = 40
FieldSizes(9) = 40
FieldSizes(10) = 2
FieldSizes(11) = 10
FieldSizes(12) = 20
FieldSizes(13) = 20
FieldSizes(14) = 20
FieldSizes(15) = 20
FieldSizes(16) = 150

Wscript.Echo "Line  1: Title         Start:   1   End: 20   Len: 20" & vbCrLf & _
             "Line  2: Fname     Start:  21   End: 60   Len: 40" & vbCrLf & _
             "Line  3: Mid Init    Start:  61   End: 70   Len: 10" & vbCrLf & _
             "Line  4: Lname     Start:  71   End:110   Len: 40" & vbCrLf & _
             "Line  5: Suffx       Start: 111   End:120   Len: 10" & vbCrLf & _
             "Line  6: FullName  Start: 121   End:160   Len: 40" & vbCrLf & _
             "Line  7: Addr1      Start: 161   End:200   Len: 40" & vbCrLf & _
             "Line  8: Addr2      Start: 201   End:240   Len: 40" & vbCrLf & _
             "Line  9: Addr3      Start: 241   End:280   Len: 40" & vbCrLf & _
             "Line 10: City        Start: 281   End:320   Len: 40" & vbCrLf & _
             "Line 11: ST           Start: 321   End:322   Len:  2" & vbCrLf & _
             "Line 12: Zip          Start: 323   End:332   Len: 10" & vbCrLf & _
             "Line 13: phone1   Start: 333   End:352   Len: 20" & vbCrLf & _
             "Line 14: phone2   Start: 353   End:372   Len: 20" & vbCrLf & _
             "Line 15: Patron    Start: 373   End:392   Len: 20" & vbCrLf & _
             "Line 16: Code       Start: 393   End:412   Len: 20" & vbCrLf & _
             "Line 17: other       Start: 413   End:562   Len:150"

strPos = InputBox("what are the fields to convert, separate with comma?","Messenger Service", strPos)
FieldsPresent = Split(strPos, ",")

'humans will mark the first field as 1 but vbscript uses zero. loop through array and correct it
For Counter = 0 To UBound(FieldsPresent)
    FieldsPresent(Counter) = FieldsPresent(Counter) - 1
Next
    
Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile(FldPath & "test3.csv")
Set tsOut = fso.CreateTextFile(FldPath & "Output.txt", True)
Set RegX = CreateObject("VBScript.RegExp")
RegX.Pattern = """[^""]+"""
RegX.Global = False

Do Until tsIn.AtEndOfStream
    TheLine=tsIn.ReadLine
    Do While RegX.Test(TheLine)
        Set Mats = RegX.Execute(TheLine)
        TheLine = RegX.Replace(TheLine, Split(Replace(Mats(0), ",", "@@@"), """")(1))
    Loop
    FieldValues(0) = Space(FieldSizes(0))
    FieldValues(1) = Space(FieldSizes(1))
    FieldValues(2) = Space(FieldSizes(2))
    FieldValues(3) = Space(FieldSizes(3))
    FieldValues(4) = Space(FieldSizes(4))
    FieldValues(5) = Space(FieldSizes(5))
    FieldValues(6) = Space(FieldSizes(6))
    FieldValues(7) = Space(FieldSizes(7))
    FieldValues(8) = Space(FieldSizes(8))
    FieldValues(9) = Space(FieldSizes(9))
    FieldValues(10) = Space(FieldSizes(10))
    FieldValues(11) = Space(FieldSizes(11))
    FieldValues(12) = Space(FieldSizes(12))
    FieldValues(13) = Space(FieldSizes(13))
    FieldValues(14) = Space(FieldSizes(14))
    FieldValues(15) = Space(FieldSizes(15))
    FieldValues(16) = Space(FieldSizes(16))
    LineSplit=Split(TheLine,",")
    For Counter = 0 To UBound(LineSplit)
        If Counter <= 16 Then
            CurrentValue = Replace(LineSplit(Counter), "@@@", ",")
            FieldLength = FieldSizes(FieldsPresent(Counter))
            If Len(CurrentValue) <= Fieldlength Then
                FieldValues(FieldsPresent(Counter)) = CurrentValue & Space(FieldLength - Len(CurrentValue))
            Else
                FieldValues(FieldsPresent(Counter)) = Left(CurrentValue, FieldLength)
                'insert error handling for current value too big for field
            End If        
        Else
            'insert error handling for too many columns in input
        End If
    Next
    tsOut.WriteLine Join(FieldValues, "")
Loop

tsIn.Close
tsOut.Close
Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing
Set RegX = Nothing

WScript.Echo "Done"

Open in new window

1

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
apollinait mgrAuthor Commented:
Hey Patrick, that does it!!! awesome, thanks
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.