vbscript - type mismatch error, please help

Hi, little experience with scripts. Trying to create a program that will take a csv file and convert it to a fixed txt file with user specified field lengths.
I'm not sure if the script will even work once I get beyond my error but I've been stuck on a "type mismatch error" on line 46 for a few days now and I've exhausted my google searches. Your help is appreciated. if I change line 46 to:  "spacetoadd = 20-strlen" I do not get the error, but since I need to have the variable spacetoadd equal the desired field size less the length of the current field, I can't use a constant value like 20.
here is my code:  (any other suggestions or tips will also be appreciated)


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

cnt = 1

'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)
strPos = InputBox("what are the field Lenghts 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

Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile(outputFile,2,true)
set objTextFile = fs.OpenTextFile(inputFile)
Wscript.Echo "INput file:" & inputFile
Wscript.Echo "Output file:" & outputFile
'set objTextFile = fs.OpenTextFile("testconv100.csv")
Do while NOT objTextFile.AtEndOfStream
      'ReDim Preserve arrStr(intSize)
      fieldSizes = (Split(strPos, ","))
      For h = LBound(fieldSizes) to UBound(fieldSizes)
    'Wscript.Echo fieldSizes(h)
    asize = fieldSizes
    Next

      'Wscript.Echo "lenght of field1 = " & str0len
 
  arrStr = split(objTextFile.ReadLine,",")
  For i = LBound(arrStr) to UBound(arrStr)
  'Wscript.Echo arrStr(i)
  strlen = len(arrStr(i))
  'Wscript.Echo "in the read line for loop, strlen = " & strLen & "," & spacetoadd & "," & asize
  spacetoadd = asize-strlen
  'Window.returnvalue = spacetoadd
  field = field & arrStr(i)
  field = field & space(spacetoadd)
  Wscript.Echo "field =" & field
  'outputString = (field0 & field1 & field2 & field3 & field4 & field5)
  outputString = field
  Next
   field = ""
   objFileToWrite.WriteLine(outputString)
  cnt = cnt + 1
Loop

objFileToWrite.Close
objTextFile.Close
set objTextFile = Nothing
set fs = Nothing
Wscript.Echo "Total Record Count:" & cnt
andy pollinait mgrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Nadav SolomonCommented:
check for empty line (might even be at the end) in your input file, or line without "," sign.
andy pollinait mgrAuthor Commented:
input file I'm testing with is just 4 lines - a header line followed by 3 lines of information
FName,LName,Addr,City,ST,Zip
Alana,Arbekey,45 8th Ave Apt 13c,New York,NY,10001
Alex,Guarnaschello,43 W 24th 12b,New York,NY,10001
Alexander,Awachiw,45 8th Ave Apt 1f,New York,NY,10001
Nadav SolomonCommented:
you ask:
what are the field Lenghts seperate with comma?
put it in strPos
then in line 32 you do the following;
fieldSizes = (Split(strPos, ","))
why?
then line 35:
asize = fieldSizes
then your problem in line 45:
spacetoadd = asize-strlen
asize is not a number here any more its an array of god knows what based on line 32,35.

by the way, if you work for some reason with vbscript use vbsedit will make your life easyer.

hope I helped.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

andy pollinait mgrAuthor Commented:
I'm doing the split on line 32 so I can pass the field sizes entered on line 16 to the script. How do I pass the desired field lengths which was stored in strPos so I can pad with spaces to the script.
I understand about asize not being a number anymore - that's my problem - how do I get the value from strPos to be a number in asize?
Nadav SolomonCommented:
what are the field Lenghts seperate with comma?
put it in strPos
then in line 32 you do the following;
fieldSizes = (Split(strPos, ","))
if you say field lengths is let's say 10 then strPos is 10 why split it with ","? what will that give you?
Nadav SolomonCommented:
I'm not sure if that's what you wanted but check this out: (it's not that beautiful but it is what it is)

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

cnt = 1

'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)
strPos = InputBox("what are the field Lenghts 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

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
      'ReDim Preserve arrStr(intSize)
'      fieldSizes = (Split(strPos, ","))
'      For h = LBound(fieldSizes) to UBound(fieldSizes)
    'Wscript.Echo fieldSizes(h)
'    asize = fieldSizes
'    Next

      'Wscript.Echo "lenght of field1 = " & str0len
  
  arrStr = split(objTextFile.ReadLine,",")
  field = ""
  For i = LBound(arrStr) to UBound(arrStr)
	  'Wscript.Echo arrStr(i)
	  strlen = len(arrStr(i))
	  'Wscript.Echo "in the read line for loop, strlen = " & strLen & "," & spacetoadd & "," & asize
	  spacetoadd = strPos-strlen
	  'Window.returnvalue = spacetoadd
	  field = field & arrStr(i)
	  field = field & space(spacetoadd)
	  Wscript.Echo "field =" & field
  Next
   objFileToWrite.WriteLine(field)
  cnt = cnt + 1
Loop

objFileToWrite.Close
objTextFile.Close
set objTextFile = Nothing
set fs = Nothing
Wscript.Echo "Total Record Count:" & cnt

Open in new window

andy pollinait mgrAuthor Commented:
in line 15 I ask how many fields are in the csv file, then in in line 16 I ask for the field lengths separated by a comma. So if the response to line 15 is "6", and the response to line 16 is "20,20,30,20,2,5" I then know the field lengths to all 6 fields.
if the value of field 1 is "Alana" and I want to convert this value to a fixed field of 20 positions, I need to add 14 spaces to the end of the field.
line 46 attempts to compute to get the value of 14, but I get the type mismatch error instead. How to I pass 20 - the first value in strPos to the equation?
Nadav SolomonCommented:
ah what i wrote waits for only one value in line 16
Nadav SolomonCommented:
This should work with several values:
dim fs,objTextFile
set fs=CreateObject("Scripting.FileSystemObject")

cnt = 1

'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)
strPos = InputBox("what are the field Lenghts 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

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
      'ReDim Preserve arrStr(intSize)
    fieldSizes = (Split(strPos, ","))
    arrStr = split(objTextFile.ReadLine,",")
    field = ""
  
  For i = LBound(arrStr) to UBound(arrStr)
	  'Wscript.Echo arrStr(i)
	  strlen = len(arrStr(i))
	  'Wscript.Echo "in the read line for loop, strlen = " & strLen & "," & spacetoadd & "," & asize
	  spacetoadd = fieldSizes(i)-strlen
	  'Window.returnvalue = spacetoadd
	  field = field & arrStr(i)
	  field = field & space(spacetoadd)
	  Wscript.Echo "field =" & field
  Next
   objFileToWrite.WriteLine(field)
  cnt = cnt + 1
Loop

objFileToWrite.Close
objTextFile.Close
set objTextFile = Nothing
set fs = Nothing
Wscript.Echo "Total Record Count:" & cnt

Open in new window

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
andy pollinait mgrAuthor Commented:
Awesome!!! it works and solves my issue. Appreciate your help, I'll review your code now and figure out what I was doing incorrectly. Thanks again.
Nadav SolomonCommented:
Glad I could help, thanks for the feedback.
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.