Problema lettura file di Excell

[bibuz]

Salve ragazzi ho la necessità di leggere un file di Excell ma ho un problema dovuto alla sua struttura.

Praticamente ho 6 colonne di questo tipo:
Comm.Pref Mail
Comm.Pref Email
Comm.Pref Share
Comm.Pref Phone
Comm.Pref Mobile Phone
Comm.Pref Sms

Il problema è che in fase di importazione il "puntino" da fastidio generando l'errore classico in cui mi dice che la colonna non viene trovata.

'titoli dei campi da estrarre
response.write "<table border='1'><tr>"
response.write "<td>Registration Date</td>"
response.write "<td>Company Name</td>"
response.write "<td>Gender Title</td>"
response.write "<td>First Name</td>"
response.write "<td>Last Name</td>"
response.write "<td>Birth date</td>"
response.write "<td>Email address</td>"
response.write "<td>Comm. Pref. Mail</td>"
response.write "<td>Comm. Pref Email</td>"
response.write "<td>Comm. Pref. Share</td>"
response.write "<td>Comm. Pref. Phone</td>"
response.write "<td>Comm. Pref. Mobile Phone</td>"
response.write "<td>Comm. Pref. Sms</td>"
response.write "<td>Cpc number</td>"
response.write "<td>Serial number</td>"
response.write "<td>Date purchased</td>"
response.write "<td>Purchase place type</td>"
response.write "<td>Dealer name</td>"
response.write "<td>Dealer city</td>"
response.write "<td>Outlet factory</td>"
response.write "<td>Product registration address street</td>"
response.write "<td>Product registration address house number</td>"
response.write "<td>Product registration address city</td>"
response.write "<td>Product registration address country</td>"
response.write "<td>Product registration address day phone</td>"
response.write "</tr>"


do while not rs.EOF
response.write "<tr>"
response.write "<td>"
response.write rs("Registration date")
response.write "</td>"
response.write "<td>"
response.write rs("Company name")
response.write "</td>"
response.write "<td>"
response.write rs("Gender title")
response.write "</td>"
response.write "<td>"
response.write rs("First name")
response.write "</td>"
response.write "<td>"
response.write rs("Last name")
response.write "</td>"
response.write "<td>"
response.write rs("Birth date")
response.write "</td>"
response.write "<td>"
response.write rs("Email address")
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref Mail") 
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref Email") 
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref Share") 
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref Phone") 
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref MobilePhone") 
response.write "</td>"
response.write "<td>"
response.write rs("Comm.Pref Sms") 
response.write "</td>"
response.write "<td>"
response.write rs("CPC Number")
response.write "</td>"
response.write "<td>"
response.write rs("Serialnumber")
response.write "</td>"
response.write "<td>"
response.write rs("Date purchased")
response.write "</td>"
response.write "<td>"
response.write rs("PurchasePlace type")
response.write "</td>"
response.write "<td>"
response.write rs("Dealer name")
response.write "</td>"
response.write "<td>"
response.write rs("Dealer city")
response.write "</td>"
response.write "<td>"
response.write rs("Outletfactory")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address street")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address house number")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address city")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address county")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address postal code")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address day phone")
response.write "</td>"
response.write "<td>"
response.write rs("ProductRegistration address mobile phone")
response.write "</td>"
response.write "</tr>"

Se levo i puntini funziona tutto correttamente.

C'è un modo per far leggere alle intestazioni i "puntini"?

Grazie a tutti in anticipo.

[optime]

prova con

response.write rs("[Comm.Pref Sms]")

o con il suo ordinale

[bibuz]

Ho provato ma nulla mi resrtituisce il seguente errore:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.


:master:

[optime]

hai usato anche l'ordinale?

[bibuz]

Scusa ma cosa intendi per ordinale???

[optime]

quello che significa in italiano 0,1,2,3,4,5....

che tradotto in informatichese

rs(0), rs(1) ecc

[bibuz]

Sempre uguale solo che a differenza di prima l'errore me lo da già sul primo campo:

"Item cannot be found in the collection corresponding to the requested name or ordinal. "

Dico un'eresia se penso a un replace?

Il file avrà sempre le stesse colonne e si chiameranno sempre nello stesso modo.

[optime]

rs(0) NON può NON esistere!

posta il codice

[bibuz]

Ecco tutto lo script:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DBQ=" & Server.MapPath("test.xls") & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"
set rs = server.createobject("ADODB.recordset")

'questo sarà il nome del file
sql = "SELECT * FROM [Export$]"
set rs = server.createobject("ADODB.recordset")
rs.open sql, conn
if rs.EOF then
response.write("NESSUN DATO IN ARCHIVIO")
else
'titoli dei campi da estrarre
response.write "<table border='1'><tr>"
response.write "<td>Registration Date</td>"
response.write "<td>Company Name</td>"
.
.
response.write "</tr>"


do while not rs.EOF
response.write "<tr>"
response.write "<td>"
response.write rs("0")
response.write "</td>"
response.write "<td>"
response.write rs("1")
response.write "</td>"
response.write "<td>"
response.write rs("2")
response.write "</td>"
response.write "<td>"
response.write rs("3")
response.write "</td>"
response.write "<td>"
response.write rs("4")
response.write "</td>"
response.write "<td>"
response.write rs("5")
response.write "</td>"
response.write "<td>"
response.write rs("6")
response.write "</td>"
response.write "<td>"
response.write rs("7")
response.write "</td>"
response.write "<td>"
response.write rs("8")
response.write "</td>"
response.write "<td>"
response.write rs("9")
response.write "</td>"
response.write "<td>"
response.write rs("10")
response.write "</td>"
response.write "<td>"
response.write rs("11")
response.write "</td>"
response.write "<td>"
response.write rs("12")
response.write "</td>"
response.write "<td>"
response.write rs("13")
response.write "</td>"
response.write "<td>"
response.write rs("14")
response.write "</td>"
response.write "<td>"
response.write rs("15")
response.write "</td>"
response.write "<td>"
response.write rs("16")
response.write "</td>"
response.write "<td>"
response.write rs("17")
response.write "</td>"
response.write "<td>"
response.write rs("18")
response.write "</td>"
response.write "<td>"
response.write rs("19")
response.write "</td>"
response.write "<td>"
response.write rs("20")
response.write "</td>"
response.write "<td>"
response.write rs("21")
response.write "</td>"
response.write "<td>"
response.write rs("22")
response.write "</td>"
response.write "<td>"
response.write rs("23")
response.write "</td>"
response.write "<td>"
response.write rs("24")
response.write "</td>"
response.write "<td>"
response.write rs("25")
response.write "</td>"
response.write "<td>"
response.write rs("26")
response.write "</td>"
response.write "</tr>"

Dim varRegistrationDate, varCompanyName, varGenderTitle, varFirstName, varLastName, varBirthDate, varEmailAddress, varCommPrefMail, varCommPrefEmail, varCommPrefShare, varCommPrefPhone, varCommPrefMobilePhone, varCommPrefSms, varCpcNumber, varSerialNumber, varDatePurchased, varPurchasePlaceType, varDealerName, varDealerCity, varOutletFactory, varProductRegistrationAddressStreet, varProductRegistrationAddressHouseNumber, varProductRegistrationAddressCity, varProductRegistrationAddressCountry, varProductRegistrationAddressDayPhone, varProductRegistrationAddressPostalCode, varProductRegistrationAddressMobilePhone

varRegistrationDate = rs("0")

varCompanyName = rs("1")
if Len(Trim(varCompanyName)) > 0 then
 varCompanyName=Replace(varCompanyName,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCompanyName = rs("1")
end if 

varGenderTitle = rs("2")
if Len(Trim(varGenderTitle)) > 0 then
 varGenderTitle=Replace(varGenderTitle,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varGenderTitle = rs("2")
end if 

varFirstName = rs("3")
if Len(Trim(varFirstName)) > 0 then
 varFirstName=Replace(varFirstName,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varFirstName = rs("3")
end if 

varLastName = rs("4")
if Len(Trim(varLastName)) > 0 then
 varLastName=Replace(varLastName,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varLastName = rs("4")
end if 

varBirthDate = rs("5")
varEmailAddress = rs("6")
varCommPrefMail = rs("7")
varCommPrefEmail = rs("8")

varCommPrefShare = rs("9")
if Len(Trim(varCommPrefShare)) > 0 then
 varCommPrefShare=Replace(varCommPrefShare,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCommPrefShare = rs("9")
end if 

varCommPrefPhone = rs("10")
if Len(Trim(varCommPrefPhone)) > 0 then
 varCommPrefPhone=Replace(varCommPrefPhone,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCommPrefPhone = rs("10")
end if 

varCommPrefMobilePhone = rs("11")
if Len(Trim(varCommPrefMobilePhone)) > 0 then
 varCommPrefMobilePhone=Replace(varCommPrefMobilePhone,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCommPrefMobilePhone = rs("11")
end if 

varCommPrefSms = rs("12")
if Len(Trim(varCommPrefSms)) > 0 then
 varCommPrefSms=Replace(varCommPrefSms,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCommPrefSms = rs("12")
end if 

varCpcNumber = rs("13")
if Len(Trim(varCpcNumber)) > 0 then
 varCpcNumber=Replace(varCpcNumber,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varCpcNumber = rs("13")
end if 

varSerialNumber = rs("14")
if Len(Trim(varSerialNumber)) > 0 then
 varSerialNumber=Replace(varSerialNumber,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varSerialNumber = rs("14")
end if 

varDatePurchased = rs("15")

varPurchasePlaceType = rs("16")
if Len(Trim(varPurchasePlaceType)) > 0 then
 varPurchasePlaceType=Replace(varPurchasePlaceType,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varPurchasePlaceType = rs("16")
end if 

varDealerName = rs("17")
if Len(Trim(varDealerName)) > 0 then
 varDealerName=Replace(varDealerName,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varDealerName = rs("17")
end if 

varDealerCity = rs("18")
if Len(Trim(varDealerCity)) > 0 then
 varDealerCity=Replace(varDealerCity,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varDealerCity = rs("18")
end if 

varOutletFactory = rs("19")
if Len(Trim(varOutletFactory)) > 0 then
 varOutletFactory=Replace(varOutletFactory,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varOutletFactory = rs("19")
end if 

varProductRegistrationAddressStreet=rs("20")
if Len(Trim(varProductRegistrationAddressStreet)) > 0 then
 varProductRegistrationAddressStreet=Replace(varProductRegistrationAddressStreet,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressStreet = rs("20")
end if 

varProductRegistrationAddressHouseNumber = rs("21")
if Len(Trim(varGenderTitle)) > 0 then
 varProductRegistrationAddressHouseNumber=Replace(varProductRegistrationAddressHouseNumber,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressHouseNumber = rs("21")
end if 

varProductRegistrationAddressCity = rs("22")
if Len(Trim(varGenderTitle)) > 0 then
 varProductRegistrationAddressCity=Replace(varProductRegistrationAddressCity,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressCity = rs("22")
end if 

varProductRegistrationAddressCountry = rs("23")
if Len(Trim(varProductRegistrationAddressCountry)) > 0 then
 varProductRegistrationAddressCountry=Replace(varProductRegistrationAddressCountry,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressCountry = rs("23")
end if 

varProductRegistrationAddressDayPhone = rs("24")
if Len(Trim(varProductRegistrationAddressDayPhone)) > 0 then
 varProductRegistrationAddressDayPhone=Replace(varProductRegistrationAddressDayPhone,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressDayPhone = rs("24")
end if 

varProductRegistrationAddressPostalCode = rs("25")
if Len(Trim(varProductRegistrationAddressPostalCode)) > 0 then
 varProductRegistrationAddressPostalCode=Replace(varProductRegistrationAddressPostalCode,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressPostalCode = rs("25")
end if 

varProductRegistrationAddressMobilePhone = rs("26")
if Len(Trim(varProductRegistrationAddressMobilePhone)) > 0 then
 varProductRegistrationAddressMobilePhone=Replace(varProductRegistrationAddressMobilePhone,"'","''")
  'Response.Write("La variabile contiene un valore")
else
  'Response.Write(" La variabile NON contiene un valore")
  varProductRegistrationAddressMobilePhone = rs("26")
end if 


Set ConnDown=Server.CreateObject("ADODB.Connection")
ConnDown.Open "Driver={SQL Server}; Server=localhost; Database=xxxxxx; Uid=xxxx; Pwd=xxxx;"
SqlInsert = "SELECT emailAddress FROM portfolio WHERE emailAddress = '" & varEmailAddress & "'"
Set rsInsert = ConnDown.Execute(SqlInsert)
If rsInsert.EOF Then
strSQL = "INSERT INTO portfolio (registrationDate, companyName, genderTitle, firstName, lastName, birthDate, emailAddress, commPrefMail, commPrefEmail, commPrefShare, commPrefPhone, commPrefMobilePhone, commPrefSms, cpcNumber, serialNumber, datePurchased, purchasePlaceType, dealerName, dealerCity, outletFactory, productRegistrationAddressStreet, productRegistrationAddressHouseNumber, productRegistrationAddressCity, ProductRegistrationAddressCountry, productRegistrationAddressDayPhone, productRegistrationAddressPostalCode, productRegistrationAddressMobilePhone) VALUES (CONVERT(DATETIME,'"&varRegistrationDate&"',103), '"&varCompanyName&"', '"&varGenderTitle&"', '"&varFirstName&"', '"&varLastName&"', CONVERT(DATETIME,'"&varBirthDate&"',103), '"&varEmailAddress&"', '"&varCommPrefMail&"', '"&varCommPrefEmail&"', '"&varCommPrefShare&"', '"&varCommPrefPhone&"', '"&varCommPrefMobilePhone&"', '"&varCommPrefSms&"', '"&varCpcNumber&"', '"&varSerialNumber&"', CONVERT(DATETIME,'"&varDatePurchased&"',103), '"&varPurchasePlaceType&"', '"&varDealerName&"', '"&varDealerCity&"', '"&varOutletFactory&"', '"&varProductRegistrationAddressStreet&"', '"&varProductRegistrationAddressHouseNumber&"', '"&varProductRegistrationAddressCity&"', '"&varProductRegistrationAddressCountry&"', '"&varProductRegistrationAddressDayPhone&"', '"&varProductRegistrationAddressPostalCode&"', '"&varProductRegistrationAddressMobilePhone&"')" 

Set RECdown = CreateObject("ADODB.Recordset")
set RECdown = connDown.execute(strSQL) 
 Else
	Response.Write(varEmailAddress)
	Response.Write(" è già presente nel database.
")
  End If
rsInsert.Close
Set rsInsert = Nothing
'strSQL = "INSERT INTO cpc (cpc, descrizione, modello, famiglia) VALUES ('"&varcpc&"', '"&vardescrizione&"', '"&varmodello&"', '"&varfamiglia&"')" 

'Set RECdown = CreateObject("ADODB.Recordset")
'set RECdown = connDown.execute(strSQL) 

rs.movenext
loop
response.write "</table>"
end if
rs.close
set rs = nothing
response.flush
response.end()

%>

[optime]

per te rs(0) e rs("0") sono la stessa cosa?

0 zero è un numero (ordinale!)
"0" è un nome, una label, un'etichetta