Unable to read data from excel file

ASP Free Forums Sponsor:

December 28th, 2006, 07:50 AM

digitaldxb

Contributing User

Join Date: Oct 2006

Posts: 155

Time spent in forums: 2 Days 18 h 1 m 48 sec
Reputation Power: 5

Send a message via Google Talk to digitaldxb

Unable to read data from excel file

Hi,

I am using an vb script to connect to excel file and reading a data from it , some of the fileds which are without having dot for example ( Name Address etc ) at hearder row i am able to read but the fileds are like ( 1111111_AB.Test ) unable to read with vb script if i remove "_" and "dot" in between of this field then i am able read please suggest , the code can be found below :

Code:

Set objConn = CreateObject("ADODB.Connection")
strConnection =  "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;HDR=Yes;DBQ=" & PathName
objConn.Open  strConnection

'objConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
'					  "DriverId=790;" & _
'					  "Dbq=" & PathName &";" & _
'					  "DefaultDir=B:\temp" 
			
			'dim dd,ee
			Set objRS=CreateObject("ADODB.recordset")
			' Write the SQL Query
			objRS.open "SELECT * FROM [Sheet1$A1:CQ303]", objConn
			
			do until objRS.EOF

			dim mname, maddress, mchr, m_type, m_score, m_the, mar, m_allq, m_log, m_le, m_cns, m_allcalls, sub_id, de_date, de_time,Pos
					sub_id = subid
					de_date =  SDT
					de_time = STime

MsgLog("Database Connected " & SDT & " | " & STime ) 
MsgLog(objRS("AGT")) 

mname = objRS("Name")
maddress = objRS("Address")
mchr = objRS("Chr")
m_type = objRS("AGT")
 Quote:
     m_score = objRS("11111111_A.SC")
m_the = objRS("11111111_A.The")
mar = objRS("11111111_A.R")
m_allq = objRS("11111111_AB.Allq")
m_log = objRS("11111111_A.Log")
m_le = objRS("11111111_A.Le")
m_cns = objRS("11111111_A.CNS")
ma_allcalls = objRS("11111111_A.AllCalls")   
 
not able to reade all the field in excel file having header row with the value dot and underscore in it please help to find out any solutions for the same.
Position = objRS("Position")

__________________
SELF REALIZATION IS THE FIRST ENCOUNTER WITH REALITY.- Sahajayoga.org

December 28th, 2006, 12:15 PM

Shadow Wizard

Moderator From Beyond

ASP Free God 46th Plane (27500 - 27999 posts)

Join Date: Sep 2004

Location: Israel

Posts: 27,635

Folding Points: 373781 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 3 Months 2 Weeks 2 h 55 m 37 sec
Reputation Power: 1902

--moved to the Windows Scripting forum.
what do you mean by "unable to read"? what error you get and
on what exact line of code?

__________________

Visit The Windows Scripting Guru

December 29th, 2006, 01:59 AM

digitaldxb

Contributing User

Join Date: Oct 2006

Posts: 155

Time spent in forums: 2 Days 18 h 1 m 48 sec
Reputation Power: 5

unable to read is shows an error is below :

Item cannot be found in the collection corresponding to the request name or ordinal ADODB.Recordset

but the filed is there if i remove the dot and _ it works.

Quote:

One more thing i found important is that even with "_" it works but not with "."

Please suggest

Last edited by digitaldxb : December 29th, 2006 at 02:02 AM.

December 29th, 2006, 06:32 AM

Shadow Wizard

Moderator From Beyond

Join Date: Sep 2004

Location: Israel

Posts: 27,635

Time spent in forums: 3 Months 2 Weeks 2 h 55 m 37 sec
Reputation Power: 1902

for the sake of debug, have such code:

Code:

If Not(objRS.EOF) Then
   For x=0 To objRS.Fields.Count-1
      MsgBox("field #" & (x+1) & " name: " & objRS.Fields(x).Name & ", value: " & objRS.Fields(x).Value)
   Next
End If

do you see correct field names and values?

January 2nd, 2007, 02:36 AM

digitaldxb

Contributing User

Join Date: Oct 2006

Posts: 155

Time spent in forums: 2 Days 18 h 1 m 48 sec
Reputation Power: 5

Quote:

Originally Posted by Shadow Wizard

for the sake of debug, have such code:

Code:

If Not(objRS.EOF) Then
   For x=0 To objRS.Fields.Count-1
      MsgBox("field #" & (x+1) & " name: " & objRS.Fields(x).Name & ", value: " & objRS.Fields(x).Value)
   Next
End If

do you see correct field names and values?

Thanks again for helping with good idea .... i used this script and found that its giving as # .... just replaced with # it worked thanks .....

any idea how to convert vbscript to ASP pages if any do let me know how to do it , I want to convert this whole script in asp and try to scan the csv or xls file via file uploading options so that user can upload files and it will scan and put the data in database.

January 2nd, 2007, 03:02 AM

Shadow Wizard

Moderator From Beyond

Join Date: Sep 2004

Location: Israel

Posts: 27,635

Time spent in forums: 3 Months 2 Weeks 2 h 55 m 37 sec
Reputation Power: 1902

no problem!

that code is already ASP, except minor change - change
any CreateObject to Server.CreateObject
however I suspect MsgLog function will already need
more changes, post its code if you need help converting it.

Comments on this post

Nilpo agrees!

January 7th, 2007, 09:18 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Folding Points: 206875 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

Shadow is right. ASP supports VBS natively. No need to convert anything.

Comments on this post

thaminda agrees: Testing reputation for this thread

__________________

Click the image if at any point you don't like my decision.

Scripting problems? Windows questions? Ask the Windows Guru!

Subscribe to Nilpo's Live Article Feed

January 8th, 2007, 12:46 AM

Shadow Wizard

Moderator From Beyond

Join Date: Sep 2004

Location: Israel

Posts: 27,635

Time spent in forums: 3 Months 2 Weeks 2 h 55 m 37 sec
Reputation Power: 1902

Quote:

Originally Posted by Nilpo

Shadow is right. ASP supports VBS natively. No need to convert anything.

there are few major differences:
ASP VBScript has the Server, Request, Response, Application and Session
while "Windows VBScript" (any better name?) has none of these but rather
it has WScript object with all the input/output support.
so it means that any code involving these must be changed when converting.

January 8th, 2007, 01:10 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

Quote:

Originally Posted by Shadow Wizard

"Windows VBScript" (any better name?)

Visual Basic has 3 primary releases. The full Visual Basic language (available in MS's Visual Studio) and 2 scripting variants: VBScript, and VBA (Visual Basic for Applications).

VBScript was designed as a web language to compete with such languages as JavaScript. It was designed to be a client-side web scripting language. It is the same language used by the WSH interpreter. The Server, Request, Response, Application and Session are objects only available within the browser environment. Likewise, the Wscript, TextStream, WshArguments, WshCollection, WshEnvironment, WshNetwork, WshShell, WshShortcut, WshSpecialFolders, and WshUrlShortcut objects are only available from within the WSH environment. But the language is the same.

VBA is a form of VBScript for use in macros. This is the variation that is packaged in Microsoft Word and Excel among others. While it's structure and syntax is very similar there are minor differences and a limited feature set.

Remember, all interpreted (scripting) languages are environmental. The use and performance is completely dictated by the environment in which it's run. Furthermore, VBS is object-oriented. Therefore, it relies solely on the objects available to it. Several objects are not supported by browsers for obvious security reasons just like several objects aren't supported by WSH for lack of any real purpose.

Comments on this post

Shadow Wizard agrees!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: