Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Other Programming > VBScript
|
VBScript For questions and discussions related to VBScript.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VBScript section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 16th, 2005, 03:48 PM
Registered User
 
Join Date: Dec 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cannot retrieve string data in CSV and Excel files

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


Reply With Quote
  #2 (permalink)  
Old December 21st, 2005, 01:58 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old December 22nd, 2005, 08:56 PM
Registered User
 
Join Date: Dec 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old December 23rd, 2005, 08:25 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old December 23rd, 2005, 08:30 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old December 23rd, 2005, 08:31 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

=)

mmcdonal
Reply With Quote
  #7 (permalink)  
Old December 23rd, 2005, 03:24 PM
Registered User
 
Join Date: Dec 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old February 10th, 2006, 03:59 PM
Registered User
 
Join Date: Feb 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old February 13th, 2006, 07:08 AM
Authorized User
 
Join Date: Feb 2006
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old February 16th, 2006, 12:26 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
Reply With Quote





Similar Threads
Thread Thread Starter Forum Replies Last Post
Import CSV files to Database in C# prasanna.nadgir C# 2 September 4th, 2007 11:03 AM
how to import data from excel,csv into sql server therisingsun ASP.NET 1.0 and 1.1 Basics 2 October 1st, 2005 12:17 AM
How to retrieve specific string zorzos Excel VBA 1 August 25th, 2004 05:41 AM
Querying .csv files aspiring_db_fundu Excel VBA 4 February 25th, 2004 01:01 PM
Retrieve data from local Access files aslyon ADO.NET 2 October 31st, 2003 08:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.