Parsing an Excel Sheet for a particular format and then send out an email

Do NOT go the DSN route if you want to have any sanity left.  Find yourself a third-party tool that parses Excel files into query data.  There are several.  If you're running CF6.1 or higher then I'd recommend CFX_Excel from MasRizal.com.  No, I don't work for them, but have used it on several projects and am extremely satisfied with the results.

http://www.masrizal.com/index.cfm?fuseaction=idea.download_detail&ProductID=cfx_excel

I won't be able to use paid ones,I tried using the trial version of <cfx_Excel2Query  but it is somehow not registering  on my server.

Also can u tell me how will i call the columnames here,as i have to query one particular column name and if the criteria is met I will send an email.


SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=#pathtoyourxlssheet#;HDR=YES', 'SELECT * FROM [Sheet1$]')


Thanks
hd

SELECT catid, catname, etc
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=#pathtoyourxlssheet#;HDR=YES', 'SELECT * FROM [Sheet1$]')

 ya know that cfx_excel2query is a java cfx - so after creating it manually (I had to create it manually on one server cause it wouldn't register) you need to set the java class path in jvm.config or install the cfx into an existing class path

Hi ,

I tried putting the above query in cfquery then it gave me an error to put in some more attributes.So I then created a dsn to the excel sheet(first in odbc and then in cfadmin) but still its not connecting to the excel sheet and querying it.
this is the code that i put in.The esdexcel is pointing to list.xls too.

<cfquery name="hello" datasource="esdexcel">
      SELECT computername
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Webs\list.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
      </cfquery>
            <cfoutput>  
            #hello.computername#
            
            </cfoutput>

Thanks
HD

your datasource needs to be a sql datasource - not an excel datasource - just use a datasource you already have setup.
just do select * to start so you can see whats up.

post the error you get.

Earlier I was getting the Microsoft Error that you mentioned earlier so I followed the procedure but now it gives me a different error


Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.  
 
The error occurred in E:\Webs\excelscan_action.cfm: line 63
 
61 : </cfoutput>
62 :
63 :       <cfquery name="hello" datasource="exesd">
64 :       SELECT *
65 : FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Webs\excel\list.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
 

--------------------------------------------------------------------------------
 
SQL    SELECT computername FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Webs\excel\;HDR=YES', 'SELECT * FROM [Sheet1$]')  
DATASOURCE   altirisesd
VENDORERRORCODE   7399
SQLSTATE   HY000
 

are you sure the file E:\Webs\excel\list.xls is correct?
are you sure the datasource is correct : at one point you have exesd and the error says altirisesd
which one is it?
does this server have the 'Microsoft.Jet.OLEDB' driver installed?
is this shared or your own server you control?

The correct datasource is altirisesd.I do not own the server but I do have admin rights on it.The file is also correct.

But I am not sure about the datasource part.Because this datasource is not pointing to the excel sheet , the datasource is for my sql server and as you mentioned earlier I used this one rather than the one for the excel dsn.
So I dont get it as to how will it work with this datasource.Also the cfoutput seems to be a amiss as how will the code detect that the first row are fieldnames when we have not specified them.

Thanks
HD

yes thats correct - you use your usual sql server datasource - as long as it is registered and is a valid datasource.
now does your sheet have a Sheet1 ?
if not - whatever the sheet you want to read would go where the Sheet1$ goes - you need the $ after the sheet name.

<cfquery datasource="altirisesd" name="XLSData">
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Webs\excel\list.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
</cfquery>
<cfdump var="#XLSData#">

If this doesn't work -
is MSDTC enabled on your server?
does sql server have service pack at least 3?
is this sql server 2000?
try this:
select *
FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls);
 DBQ=E:\Webs\excel\list.xls', 'SELECT * FROM [Sheet1$]')

Same kind of error.Also everything that you mentioned , my server is compatible to it.

Error Executing Database Query.  

[Macromedia][SQLServer JDBC Driver][SQLServer]OLE DB provider 'MSDASQL' reported an error.


Thanks
HD

go with custom tag ...

Hi dgrafx,

Thankyou for all your help.I did use the custom tag and its finally working on my server but as it is a trial version,I have been asked to use this code,and it will return only 10 rows.
<cfx_excel2query
      file="E:\Webs\excel\list.xls""
      firstRowIsHeader=1
      nodates
      r_qResults="results">

presently I am gett ing this error.Also I want to query the entire sheet and if I find one hit according to my query then I would need to send out an email.But where should I query in this tag,as this will give me the ten rows only,so does that mean if my criteria is not met in the first ten rows then this tag is not useful for me.

Variable R_QRESULTS is undefined.  
 
 
The error occurred in E:\Webs\admin\excelscan_action.cfm: line 67
 
65 :       r_qResults="results">
66 :       
67 :       <cfoutput>  #r_qResults#    </cfoutput>
68 :             
69 :   <hr width="670" align="left">
 
Thanks
HD

the 10 rows is the limitation of the trial.
the full ver only costs $30 bucks

I finally got the 10 rows ,and the above error that I mentioned is gone by using cfdump but now the question remains,that how do I query the entire sheet.I am not interested in showing 10 rows,I just want one hit according to my sql query to send out an email...Any suggestions?.
Thanks
HD

Thanks dgrafx.I guess I need to find out now whether my company will pay for this product.
Thanks
HD

where is your "name" param?
maybe the trial is different or different version but on the one server where I needed to use it I do:
<cfx_Excel2Query
name="XLSData"
file="#filelocation##cffile.serverfile#"
headerRow="1"
firstRowIsHeader="1">

Then - what this is is a query. You treat it like you would any query.
<cfset cList="">
<cfloop query="XLSData">
     <cfif computername is "the_ones_you_are_looking_for">
          <cfset cList=ListAppend(cList,computername)>
     </cfif>
</cfloop>
<cfif listlen(cList)>
<cfmail from=et cetera>
#replace(cList,","," and ","all")# are in this excel sheet
</cfmail>
</cfif>

I tried it,but it says that name and headerrow are unknown attributes.So I guess they do have blocked it for the trial version.And if I use  r_qResults="results"> it checks only in the first 10 rows :(

Thanks
HD