Hello everyone, I'm having the some problems parsing CSV and Excel files. I can retrieve a numerical data like 5 digit zip code but cant retrieve a string data which is 9 digit zip code with "-" between them in a same Zip code field. Any one has an idea about this problem?

thanks

You might try turning that field into a string since even though it has numbers in it, it is a string (zip codes are not calculated.)

The problem with vbscript is that it is typeless until you start pulling data, and then it makes a best guess - unless you type it.

To correct this, pull the data in that field using "CStr()". This will turn that field into a string value, and the "-" won't be read as a minus sign.

HTH

mmcdonal

Actually I have an asp page that reads any CSV file, retrieves the data and displays on the page and then inserts into the SQL database. I cant even pull the data having 9 digit zip. It returns null for 9 digit zip codes with "-" between them. The problem arises when I try to read 9 digit zip code field having both 5 and 9 digit zip codes. Do you have any clue how to resolve this problem?

thanks
ajimi

That could be two problems.

The SQL database field for a zip code should be a text or varcahr or char field. It should not be any kind of number field. There should also not be any validation for this field that might cause it to refuse data.

The script should be "typed" at the time the data is read, not when it is processed.

If when you retrieve the data you specify:

myVariable = CStr(csvField)

Then the script will type the variable as a string. If you don't type the variable, then the script may type it and you might get:

myVariable = csvField

If the csvField = "90210" then it is read as the number 90210.
If the csvField = "00210" then it may be read as the number 210.
If the csvField = "90210-4230" then it may be read as 85980.
If the csvField = "00210-4230" then it may be read as -4020.

If these values are typed, then they would be read as "90210", "00210" and "90210-4230" respectively.

If the values are expected to meet certain parameters in the database, and the database will take the record and discard invalid field data, then it may come in as the whole record minus the invalid fields.

It sounds like your script is rejecting the invalid fields before it gets to the SQL server, and the zip field is taking nulls.

HTH



mmcdonal

Sorry, when I say typed, I mean that the script sets teh variable type as either a number of a string. With zip fields it will want a number if it is not typed. It just guesses it to be a number field, which means it will carry out any arithmetic instructions in the variable, so 00210-4230 = -4020.

If you force the type to string at read time, then your script doesn't have to guess the variable type and will take text and numbers, and not apply any arithmetic.

If your SQL server is looking for numbers and sees an arithmetic expression coming in, it may have problems. If it sees a text variable from your script trying to go into a number field, it will reject it. If it sees a string going into a string field of the proper length, it will accept it.

HTH


mmcdonal

"as either a number OR a string." I am fat fingering all over the place this morning.

=)

mmcdonal

I tried using "myVariable = CStr(csvField)". It still does not work. It assigns the previous record's 5 digit zip to the next record having 9 digit zip code. It does not accept any 9 digit zip instead assigns previous records 5 digit zip to that record.

ajimi,
  Did you ever find a resolution for this? I'm having the same problem.

hi guys same probem i m getting when i retrieved a column value(having mobile number of 10 digits ) from excel sheet using oledb data control then it takes it as null even i have made that column as text format.
but when i entered any character(,(,), like these characters ) in that column then it takes the value.
anyone tell me the solution of this
byee
with regards
jitender

Can anyone post their code, and the SQL table design?

mmcdonal