Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Other Programming > VBScript
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 16th, 2005, 03:48 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
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
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Location: , , .
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
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Location: , , .
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
Location: , , .
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
Location: , , .
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
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:09 AM.


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