• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2454
  • Last Modified:

Reading more than 255 charaters into .asp from excel

I am trying to figure out a way for my .ASP page to not trucate the text in an excel file I am connecting to . my connection looks like this:

 vXlsFile = "/virtual/documents/website/web.xls"

		vXlsFile = Server.Mappath(vXlsFile)

		ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34)

		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open ExcelConnString

Open in new window


Is there something I can to to the connection string that will open the field up. the way I am displaying it is:
<td width=20% valign=top><%=rs.fields("TECHNICAL STRATEGY")%>&nbsp;</td>

Open in new window

0
jlcannon
Asked:
jlcannon
  • 16
  • 15
1 Solution
 
alorentzCommented:
Driver issue.  Switch to ODBC:
http://www.connectionstrings.com/excel
0
 
alorentzCommented:
0
 
jlcannonAuthor Commented:
ok If you use the example in the first link then my string looks like:
ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 8.0;"& chr(34) & _
		"HDR=" & chr(34) & "Yes;" & _
		"IMEX=" & chr(34) & "1;"

Open in new window

and that generates an error or:
ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/energysystc/Technology/se_instructions.asp, line 18
 and it is pointing to the line of HDR=Yes; IMEX=1;

so if I take out the HDR=Yes; then it give an error refering to the IMEX=1; line saying:
Microsoft OLE DB Service Components error '80040e73'

Format of the initialization string does not conform to the OLE DB specification.

/energysystc/Technology/se_instructions.asp, line 17
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
alorentzCommented:
0
 
BanthorCommented:
Have you considered migrating to CSV or XML?
0
 
jlcannonAuthor Commented:
I am wondering if it could be with the way I am opening the recordset because There is another field that shows up and I just did a character count on it and it had 526 characters counting the spaces but then it comes to the field thats being trucated and it just cuts off at 255. then if I add the HDR=yes; IMEX=1; it the goes out 255 characters then adds a line of junk that looks like this:
?????????????????????????????????Iš ???

my recordset open looks like:
set RS = server.createobject("adodb.recordset")
      RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly
0
 
alorentzCommented:
Odd.  Try this:

RS.open sql,Conn

or

RS.open sql,Conn, 1, 3
0
 
jlcannonAuthor Commented:
neither works, The data is set in 4 columns and in column 2 there is a lot of txt and it show perfectly. some of the cells in that column show 600 characters but then it comes to column 4 and thats where the 255 character limit hits. cannot figure it out.
0
 
alorentzCommented:
Doesn't make sense.  You'll have to post the rest of your code for accessing and displaying the data.
0
 
jlcannonAuthor Commented:
<%@ Language=VBScript %>
<!--#include file="../includes/adovbs.inc"-->
<%


		vXlsFile = "/virtual/documents/energysystc/G7M-0002-02.xls"

		vXlsFile = Server.Mappath(vXlsFile)
		
		

		ExcelConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
		"Data Source=" & chr(34) & vXlsFile & chr(34) & ";" & _
		"Extended Properties=" & chr(34) & "Excel 8.0; "& chr(34)

		'Open Database Connection
		Set Conn = Server.CreateObject("ADODB.Connection")
		Conn.Open ExcelConnString


	session("Category") = Request.QueryString("Category")
	session("EquipmentType") = Request.QueryString("EquipmentType")
	CatArray = Array("Batteries (E211)","Cables - Electrical Cables (E042)","Cables - Terminations and Splices (E047)","Cathodic Protection (E242)","Communication Systems (E205)","Control Panel - Electric (E172)","DC Bus System (Large) (E153)","Electric Heaters (E195)","Electrical Tools and Safety Equipment (E121)","Exciters - Rotating (E069)","Exciters - Static (E219)","Generators (E065)","Grounding - Equipment and Building Grounding (E011)","Grounding - Ground Resistor/Monitoring (E015)","Heat Tracing Circuit (E191)","Junction Boxes and Internals (E173)","Lighting and Power Panels (E081)","Lighting and Receptacles (E183)","Lightning and Surge Protection/Arresters (E013)","Low Voltage Disconnect Switches","Metering (E103)","Motor Control Centers (E073)","Motor Operated Valves (MOVs) (E068)","Motor Starters and Feeder Units (E072)","Motors - Electric (E061)","Pole Line and Insulators (E133)","Power Controllers (E215)","Power Factor Correction (E105)","Protective Relays (E101)","Reactors - Electrical Power (E095)","Rectifier - Large DC (E151)","Rectifier Control Panel (E154)","Rectifier Transformer/Rectiformer (E152)","Rectifier/Battery Charger (E218)","Substations, Switchrooms and Switchyards (E181)","Switchgear Systems - Circuit Breakers and Electrical Power Switches (E115)","Switchgear Systems - Electrical Bus Duct (E020)","Switchgear Systems - Electrical Switchgear (E112)","Transfer Switches (Excluding Switchgear) (E088)","Transformers - Instrument (E104)","Transformers (Dry) - Power (E092)","Transformers (Oil) - Load Tap Changer (E092)","Transformers (Oil) - Power (E092)","UPS Systems - Rotating (E063)","UPS Systems - Static (E212)","Variable Speed Drives (E213)")


%>

<html>

<head>
<title>Specific Equipment Instructions</title>
</head>
<script LANGUAGE="vbscript">
<!--
	sub Myformsubmit(index)
		select case index
			case 1
			myform.EquipmentType.value = ""
			MyForm.submit
			case 2
			MyForm.submit
		end select
	end sub
-->
</script>
<body onload="">


<table width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td >
</td>
  </tr>
  <tr>
    <td><div align="left"><table border="0" cellpadding="0" cellspacing="0" width="100%">
      <tr>
        <td width="81%">
        <!--webbot bot="Include" U-include="../includes/site_header.htm" TAG="BODY" --></td>
             </tr>
      <tr>
        <td width="81%" colspan="2"><div align="left"><table name="ContentTable" id="ContentTable" border="0" cellpadding="0" cellspacing="0" width="100%">
          <tr>
            <td class="Content" name="ContentCell" id="ContentCell" valign="top" width="100%">
           <form method="get" name="myform">

<table border="0" width="100%">
<tr>
<td width="20%"><b>Equipment Group</b></strong></td>
<td width="80%"><select name="Category" id="Category" style="Height: 22px; Width: 600px;" onChange="Myformsubmit(1)">
		<option></option>
<%
	for each item in catArray
		if item = session("Category") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
		else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
	next
%>
</select>
</td>
</tr>
<tr>
<td width="20%"><strong>Equipment Type</strong></td>
<td width="80%"><select name="EquipmentType" id="EquipmentType" style="Height: 22px; Width: 600px;" onChange="Myformsubmit(2)">
		<option></option>
<%
if session("Category") <> "" then

	select case session("Category")

		case "Grounding - Equipment and Building Grounding (E011)"
			typeArray = Array("E011A Equipment and Building Grounding","E011B Instrument and Computer","E011C Power Cable Shield","E011D Instrument & Cable Shields","E011E Static Electric Systems","E011F Equipment Potential Bonding")
		case "Batteries (E211)"
			typeArray = Array("E211A Lead Plante Type","E211B Lead Antimony Plates","E211C Lead Selenium Plates","E211D Lead Calcium Plates","E211E NI-CAD Type","E211F Valve Regulated (sealed)","E211G Valve Regulated (MOD 5)")
		case "Cables - Electrical Cables (E042)"
			typeArray = Array("E042A Cables: 0 - 1000V","E042B Cables: > 1000V","E042C Instrument Cables","E042D Fiber Optic Cables","E042E Open Wire Systems")
		case "Grounding - Ground Resistor/Monitoring (E015)"
			typeArray = Array("E015A Power System Resistors (excluding generator resistors)","E015B Generator Neutral Grounding","E015C Ground Resistor Monitor")
		case "Lightning and Surge Protection/Arresters (E013)"
			typeArray = Array("E013A Station Class - < 66kV Nominal line-line voltage","E013B Station Class - > 66kV Nominal line-line voltage","E013C Intermediate Class Surge Arresters","E013D Distribution Class Surge Arresters","E013E Generator and Motor Surge Arresters and Capacitors")
		case "Switchgear Systems - Electrical Bus Duct (E020)"
			typeArray = Array("E020A Bus Duct: 0 - 1kV","E020B Bus Duct: >1kV - 7.2kV","E020C Bus Duct: 7.2kV - 36kV","E020D Bus Duct: >36kV")
		case "Cables - Terminations and Splices (E047)"
			typeArray = Array("E047A Terminations: >1000V","E047B Splices: > 1000V")
		case "Motors - Electric (E061)"
			typeArray = Array("E061A IEC Motors: < 1kV Induct w/antifrict bearings IN CRITICAL SERVICE <315kW","E061A IEC Motors: < 1kV Induct w/antifrict bearings IN NON-CRITICAL SERVICE <315kW","E061B ANSI Motors: < 1kV Induct w/antifrict bearings IN CRITICAL SERVICE<700HP","E061B ANSI Motors: < 1kV Induct w/antifrict bearings IN NON-CRITICAL SERVICE <700HP","E061C IEC Motors: < 1kV Induction w/antifrict bearings >316kW","E061D ANSI Motors: < 1kV Induction w/antifrict bearings >701HP","E061E Motors: < 1kV Induction (sleeve bearings)","E061F Motors: 1kV - 6.9kV Induction (antifriction bearings)","E061G Motors: 1kV - 6.9kV Induction (sleeve bearings)","E061H Motors: > 6.9kV Induction","E061J Motors: < 1kV Synchronous","E061K Motors: 1kV - 6.9kV Synchronous (brushless type)","E061L Motors: 1kV - 6.9kV Synchronous (brush type)","E061M Motors: > 6.9kV Synchronous (brushless type)","E061N Motors: > 6.9kV Synchronous (brush type)","E061P Motors: DC 0 - 50 KW","E061Q Motors: DC > 51 KW")
		case "UPS Systems - Rotating (E063)"
			typeArray = Array("E063A Parallel Type","E063B Serial Type")
		case "Generators (E065)"
			typeArray = Array("E065A Standby/Emergency","E065B Air Cooled Generators - Minor Inspection","E065B Air Cooled Generators - Major Overhaul","E065C H2 Cooled Generators - Minor Inspection","E065C H2 Cooled Generators - Major Inspection")
		case "Motor Operated Valves (MOVs) (E068)"
			typeArray = Array("E068A Motor Operated Valves (MOVs)")
		case "Exciters - Rotating (E069)"
			typeArray = Array("E069A Rotating Exciters")
		case "Motor Starters and Feeder Units (E072)"
			typeArray = Array("E072A < 1000V General","E072B < 1000V IEC","E072C < 1000V ANSI","E072D > 1000V IEC","E072E > 1000V ANSI")
		case "Motor Control Centers (E073)"
			typeArray = Array("E073A General","E073B IEC MCC < 1000 V","E073C ANSI < 1000 V","E073D IEC > 1000V","E073E ANSI > 1000V","E073F MCC 600V Class NA","E073G MCC 5kV NUM CLS E2")
		case "Lighting and Power Panels (E081)"
			typeArray = Array("E081A Light and Power Panel","E081B Sub Dist Panel Multi-Cub","E081C Sub Dist Panel Multi-Box")
		case "Transfer Switches (Excluding Switchgear) (E088)"
			typeArray = Array("E088A Transfer Switch & System")
		case "Transformers (Oil) - Load Tap Changer (E092)"
			typeArray = Array("E092M Load Tap Changer")
		case "Transformers (Dry) - Power (E092)"
			typeArray = Array("E092K Dry Type: 0 - 300kVA","E092L Dry Type: > 301KVA")
		case "Transformers (Oil) - Power (E092)"
			typeArray = Array("E092A Hydraulically Filled: < 3.5MVA","E092B Conservator Design: 0 - 3.5MVA","E092C Conservator Design: 3.6 - 16MVA","E092D Conservator Design: 16 - 30MVA, <100kV Primary","E092D Conservator Design: 16 - 30MVA, >100kV Primary","E092E Conservator Design: > 30MVA, <100kV Primary","E092E Conservator Design: > 30MVA, >100kV Primary","E092F Oil/N2: < 0.5MVA","E092G Oil/N2: 0.5 - 3.5MVA","E092H Oil/N2: 3.5 - 20MVA, <100kV Primary","E092H Oil/N2: 3.5 - 20MVA, >100kV Primary","E092J Oil/N2: > 20MVA, <100kV Primary","E092J Oil/N2: > 20MVA, >100kV Primary","E092K Dry Type: 0 - 300kVA","E092L Dry Type: > 301KVA","E092M Load Tap Changer")
		case "Reactors - Electrical Power (E095)"
			typeArray = Array("E095A Liquid Iron Core","E095B Air Insulated Iron Core","E095C Air/Air Core")
		case "Protective Relays (E101)"
			typeArray = Array("E101A Electro-mechanical: 1- Element","E101B Electro-mechanical: 3+ Element","E101D Solid State: 1-Element","E101E Solid State: 3+ Element","E101G Microprocessor (non-motor)","E101H Microprocessor Motor Protection Relay","E101J Microprocessor Universal")
		case "Metering (E103)"
			typeArray = Array("E103A Ampere Transducer/Loop","E103B Volt Transducer/Loop","E103C Watt Transducer/Loop","E103D KWH Transducer/Loop","E103E KVAR Transducer/Loop","E103F Hours Transducer/Loop","E103G AMP-Hours Transducer/Loop","E103H Power Factor Transducer/Loop","E103J Revenue Metering Packages/Loops")
		case "Transformers - Instrument (E104)"
			typeArray = Array("E104A Current Transformers","E104B Voltage Transformers")
		case "Power Factor Correction (E105)"
			typeArray = Array("E105A Capacitor Type","E105B Rotating Type")
		case "Switchgear Systems - Electrical Switchgear (E112)"
			typeArray = Array("E112A < 1 KV Metal-encolsed","E112B > 1 KV Metalclad","E112C Station Class","E112D > 36 KV GIS","E112E Oil Insulated Switchgear")
		case "Switchgear Systems - Circuit Breakers and Electrical Power Switches (E115)"
			typeArray = Array("E115A < 1kV Air Magnetic","E115B 1kV - 6.9kV Air Magnetic","E115C >6.9kV Air Magnetic","E115D < 15kV Oil","E115E > 15kV Oil","E115F < 36kV SF6 or Vacuum","E115G > 36kV SF6","E115H: Load Switch/Isolator","E115J Non-Load Break Isolator","E115K >36kV Isolator Non-Loadbreak","E115L Oil Fused Disconnect","E115M Porcelain Fused Disconnect")
		case "Electrical Tools and Safety Equipment (E121)"
			typeArray = Array("Electrical Tools and Safety Equipment (E121)","E121B Extension Cords","E121C Portable GFCI","E121D Fixed Shop Equipment","E121E Safety Equipment","E121F HV Equipment","E121G Measure Instrument")
		case "Pole Line and Insulators (E133)"
			typeArray = Array("E133A Pole line","E133B Insulator","E133C Wood Poles")
		case "Rectifier - Large DC (E151)"
			typeArray = Array("(E151A, E151B) Rectifier Cubicle -Annual Off-Line Inspection","(E151A, E151B) Rectifier Cubicle - 7 Year Major Inspection","(E151A, E151B) Rectifier Cubicle -10 Year Major Inspection","(E151A, E151B) Rectifier Cubicle - 15 Year Off-Line Inspection","(E151D) Chopper Cubicles - Annual Off-Line Inspection","(E151C, E152C) Deion Cooling - Annual Off-Line Inspection","(E1512) Deion Cooling - 7 Year Major Inspection","(E1512) Deion Cooling - 10 Year Major Inspection")
		case "Rectifier Transformer/Rectiformer (E152)"
			typeArray = Array("(E152A, E152B) Regulating Transformer - Annual Off-Line Inspection","(E152A, E152B) Regulating Transformer - 7 Year Major Inspection","(E152A, E152B) Rectifier Transformer - Annual Off-Line Inspection","(E152A, E152B) Rectifier Transformer -7 Year Major Inspection")
		case "DC Bus System (Large) (E153)"
			typeArray = Array("(E153A) DC Bus System - 7 Year Major Inspection","(E153B, E153C) DC Isolating Switches - Annual Off-Line Inspection","(E153B, E153C) DC Isolating Switches - 7 Year Major Inspection")
		case "Rectifier Control Panel (E154)"
			typeArray = Array("(E154A) Rectifier Control Panels - Annual Off-Line Inspection","(E154A) Rectifier Control Panels - 7 Year Major Inspection","(E154A) Rectifier Control Panels - 10 Year Major Inspection")
		case "Control Panel - Electric (E172)"
			typeArray = Array("E172A Control Panel Electric")
		case "Junction Boxes and Internals (E173)"
			typeArray = Array("E173A Junction Boxes and Internals")
		case "Substations, Switchrooms and Switchyards (E181)"
			typeArray = Array("E181A Substation Building","E181B Switchroom","E181C Switchyard")
		case "Lighting and Receptacles (E183)"
			typeArray = Array("E183A Light Area/Bldg.","E183B Emergency Lighting","E183C Lighting Pole","E183D Process Lighting","E183E Receptacles")
		case "Low Voltage Disconnect Switches"
			typeArray = Array("E184A Disconnect Switches")
		case "Heat Tracing Circuit (E191)"
			typeArray = Array("E191A Heat Tracing","E191B Impedance Heating","E191C Induction Heating","E191D Skin Effect Heating")
		case "Electric Heaters (E195)"
			typeArray = Array("E195A Immersion","E195B Tubular","E195C Strip","E195D Ring or Disc","E195E Cartidge","E195F Band or Nozzle","E195G Finned","E195H Furnace")
		case "Communication Systems (E205)"
			typeArray = Array("E205A Intercoms","E205B Annunciator","E205C Warning Horns/Sirens","E205D SFT Shower Alarms","E205E Video Systems","E205F Telephone Communications","E205G Data Communication","E205H Scada Systems","E205J Fire Detector System")
		case "UPS Systems - Static (E212)"
			typeArray = Array("E212A Parallel Type","E212B Serial Type")
		case "Variable Speed Drives (E213)"
			typeArray = Array("E213A Frequency Drives","E213B DC Drives","E213C Electronic Reduced Voltage Starters","E213D Common DC Bus")
		case "Power Controllers (E215)"
			typeArray = Array("E215A Power Supplies","E215B 0 -125V DC System","E215C > 125V DC System")
		case "Rectifier/Battery Charger (E218)"
			typeArray = Array("E218A Rectifier","E218B Battery Charger","E218C Battery Monitor: Package Battery System Monitor")
		case "Exciters - Static (E219)"
			typeArray = Array("E219A Static Exciter")
		case "Cathodic Protection (E242)"
			typeArray = Array("E242A Direct Current","E242B Sacrificial")
	end select

	for each item in typeArray
		if item = session("EquipmentType") then
			response.write "<option selected>" & item & "</option>" & vbcrlf
		else
			response.write "<option >" & item & "</option>" & vbcrlf
		end if
	next
end if
%>
</select>
</td></tr>

</table>
<p>
<a href="http://hydroenergy.intranet.dow.com/virtual/documents/Energysystc/CopyG7M-0002-02.xls">CBE-PPM SEI Excel Version</a></p>
<%
if session("EquipmentType") <> "" then
	sql = "SELECT * FROM tblData WHERE tblData.[Equipment Category]='" & Session("Category") & "' AND tblData.[Equipment Type]='" & Session("EquipmentType") & "';"
	'Response.Write sql
	'response.end
	set RS = server.createobject("adodb.recordset")
	RS.open sql,Conn,adOpenForwardOnly,adLockReadOnly
	'RS.open sql,Conn, 1, 3



%>
<table border="1" width="100%">
<tr class="cellcolorlighter">
<td width=20% align="center" valign="top" class="menuheadcell">Equipment</td>
<td width=25% align="center" valign="top" class="menuheadcell">Specific Equipment Instructions (SEI)</td>
<td width=15% align="center" valign="top" class="menuheadcell">Hyperlink to Additional SEI Information</td>
<td width=20% align="center" valign="top" class="menuheadcell">Recommended Replacement Strategy</td>
<td width=20% align="center" valign="top" class="menuheadcell">MANDATORY - END OF USE DATE AND/OR OPERATIONAL RESTRICTIONS</td>
</tr>
<%
do while not rs.eof
'\\Hsnt05\Gbl_team\Discipline teams\Electrical\TRN folders\CBE-PPM Web Information\
%>
<tr>
<td width=20% valign=top ><%=rs.fields("SPECIFIC EQUIPMENT")%>&nbsp;</td>
<td width=25% valign=top><%=rs.fields("SPECIFIC EQUIPMENT INSTRUCTIONS")%>&nbsp;</td>
<td width=15% valign=top><a target="_blank"  href="\\txnt38\puwtclibrary\Level1\CBE-PPM-SEI Information\<%=rs.fields("Hyperlink to SEI Informtion")%>"><%=rs.fields("Hyperlink to SEI Informtion")%></a>&nbsp;</td>
<td width=20% valign=top><%=rs.fields("TECHNICAL RESOURCE NETWORK RECOMMENDED REPLACEMENT STRATEGY")%>&nbsp;</td>
<td width=20% valign=top><a target="_blank"  href="\\txnt38\puwtclibrary\Level1\CBE-PPM-SEI Information\<%=rs.fields("MANDATORY - END OF USE DATE AND/OR OPERATIONAL RESTRICTIONS")%>"><%=rs.fields("MANDATORY - END OF USE DATE AND/OR OPERATIONAL RESTRICTIONS")%></a>&nbsp;</td>
</tr>
<%
	rs.movenext
loop
rs.close
set rs = nothing
%>
</table>

Open in new window

0
 
alorentzCommented:
And what line is the problem?
0
 
alorentzCommented:
And, if you look at the .xls file, is the truncated field a different data type (or format) than the ones that do display fully?
0
 
jlcannonAuthor Commented:
The problem occurs when it posts the data in this cell:
<td width=20% valign=top><%=rs.fields("TECHNICAL RESOURCE NETWORK RECOMMENDED REPLACEMENT STRATEGY")%>&nbsp;</td>
 this one seems to be the one cut off at 255 characters.
0
 
jlcannonAuthor Commented:
in the xls file, it is set to general formatting just as the other field with >255 characters has set.
0
 
alorentzCommented:
Are you able to change the registry on the computer?  That should solve the problem.  See here:
http://support.microsoft.com/kb/281517
0
 
jlcannonAuthor Commented:
I agree about changing the setting on the computer may solve the issue but what about all the other users that browse to that page?
0
 
jlcannonAuthor Commented:
If i set the value in the registry to 0 it still is not working. If I open the excel this is whats in the cell:

See G7C-0907-00 for the >60kV Transformer Lifecycle calculation methodology for transformers with >60kV primary windings.  For units that show = 10 years life remaining: decrease oil sample intervals to 2/3 of those per CBE for younger units if the unit does not have an on-line DGA system. For units that show = 5 years life remaining per the tool: perform internal inspection at the next available shutdown, obtain a paper sample if possible, and adjust remaining life estimate based on the inspection and paper sample results.
and this is what shows up on the web page:

See G7C-0907-00 for the >60kV Transformer Lifecycle calculation methodology for transformers with >60kV primary windings. For units that show = 10 years life remaining: decrease oil sample intervals to 2/3 of those per CBE for younger units if the unit dal', 'Verbrugge Terminal'  
0
 
alorentzCommented:
"I agree about changing the setting on the computer may solve the issue but what about all the other users that browse to that page? "

This is not related to use computers, only the server.  The change is made on the server, not local machines.

"and this is what shows up on the web page:" > that doesn't make sense.  The output is different that what is in the fields.  "unit dal', 'Verbrugge Terminal'" isn't even in the cell.  

Are there any hidden columns in the excel?  Or, are you reading the wrong cell?  Where does 'unit dal', 'Verbrugge Terminal'' exist in the excel file?
0
 
jlcannonAuthor Commented:
I have searched the excel file for that line and it finds nothing in the whole workbook, much less in the neamed range.
0
 
alorentzCommented:
So, 'Verbrugge Terminal' is nowhere in the doc you are looking at?  

Well, that's not possible, so it seems your looking at the wrong doc.  Meaning, the code is accessing a different doc than the one you are looking at manually.  Perhaps you are looking on the wrong mapped drive or file system.

So, "/virtual/documents/energysystc/G7M-0002-02.xls" is mapped differently on the server (to a different file) than the file you are looking at.

I know you may not think that is the problem, but based on what you are saying, that is the only possibility.  Recordsets DO NOT change data like that.
0
 
jlcannonAuthor Commented:
the way the file is mapped is it takes the current server that is serving the user which there is a bank of 3 redundant servers that share the load and which ver server the user is connected to it goes to the virtual folders on that server. they are identical mirrors. change one and the changes are replicated across. I have verified on each server that this is the case and none of the 3 copies have that language in them.
0
 
jlcannonAuthor Commented:
and as further troubleshooting I have tried reloading the web page with the xls file open and i get the error that the file is currently open so I know i am reading the file I have searched for that verbage and it does not exist. Unless it just id getting garbage from the page file buffer on my machine.
0
 
alorentzCommented:
You could clear your local cache, but that shouldn't be related.  Are you running the script locally on your machine, or is the script running on the server?  Again, if running on the server, it can only pull information from the associated file.  

You could response write the the doc location, just to make sure you're dealing with the right file.
0
 
jlcannonAuthor Commented:
Confirmed I am dealing with the correct file. still blows my mind where its getting the extra data characters from.
0
 
alorentzCommented:
Clearly something is not as it seems, and you will kick yourself when you find it.  It's not possible for a recordset to change words like that.  Verbrugge Terminal HAS TO BE somewhere.
0
 
alorentzCommented:
Also, if the file is not a production file, then I would replace it altogether with a test doc, and see how that works to display all the data in the test doc.  That will at least show if the correct doc is being accessed.

And, again, make sure there are no hidden columns in the Excel file.  You should be able to see all columns A, B, C, D - etc.
0
 
jlcannonAuthor Commented:
yes I do see all columns with none hidden a-J and the data in those cells has also been verified. I recreated the excel file and in the excel file column G is populated with this text.


Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.

on the web page this is whats displayed


Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show before the weird text starts showing up.Ok this is a test to see just how many charaters are show b?????????????????????????????????Iš ???
0
 
alorentzCommented:
Any of this help?
http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB

If the code is always executed on the same server, you could just change the registry on that server, and it should work.
0
 
jlcannonAuthor Commented:
well the code is actually executed on any one of 3 mirrored load balancing servers and they are not within my control so I was hoping for a code fix for the issue. if its not possible i may have to explore other options, maybe breaking the code into smaller bits across cells.
0
 
alorentzCommented:
All solutions tend to point to registry changes.  

You could try to save the file as .csv in code (with File System Object), and then read from .csv instead of xls, but never tried that.
0
 
jlcannonAuthor Commented:
ok thanks. looks like there is an answer just not one I can impliment. Appriciate all the help.
0
 
jlcannonAuthor Commented:
excellent solution just not one I can employ as I do not have console access to the server.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now