Carl, I tried that but it caused another error:
Here is more code: This code comes directly from Ben Nadel so all street cred goes to him! :) Here is the blog that I got most of the code from: http://www.bennadel.com/projects/poi-utility.htm I modified the code to handle more columns and of course used my own excel file. I got lazy and just renamed my excel file to the one that Ben used in his example. It should be simple to create an excel spredsheet and and then run this code and import it into mysql. This is where the difficulty begins. I need to clear the whole Database every time I need to do a re-import of the excel sheet. I think I can do this in two steps but it would be nice to do it all in one step. Thanks! Begin Code ----- <cfoutput> <!--- Create an instance of the POIUtility.cfc. ---> <cfset objPOI = CreateObject( "component", "POIUtility" ).Init() /> <!--- Read in the Exercises excel sheet. This has Push, Pull, and Leg exercises split up on to three different sheets. By default, the POI Utilty will read in all three sheets from the workbook. Since our excel sheet has a header row, we want to strip it out of our returned queries. ---> <cfset arrSheets = objPOI.ReadExcel( FilePath = ExpandPath( "./exercises.xls" ), HasHeaderRow = true ) /> <!--- The ReadExcel() has returned an array of sheet object. Let's loop over sheets and output the data. NOTE: This could be also done to insert into a DATABASE! ---> <cfloop index="intSheet" from="1" to="#ArrayLen( arrSheets )#" step="1"> <!--- Get a short hand to the current sheet. ---> <cfset objSheet = arrSheets[ intSheet ] /> <!--- Output the name of the sheet. This is taken from the Tabs at the bottom of the workbook. ---> <h3> #objSheet.Name# </h3> <!--- Output the data from the Excel sheet in a table. We know the structrure of the Excel, so we can use the auto-named columns. Also, since we flagged the workbook as using column headers, the first row of the excel was stripped out and put into an array of column names. ---> <table border="1"> <tr> <td> #objSheet.ColumnNames[ 1 ]# </td> <td> #objSheet.ColumnNames[ 2 ]# </td> <td> #objSheet.ColumnNames[ 3 ]# </td> <td> #objSheet.ColumnNames[ 4 ]# </td> <td> #objSheet.ColumnNames[ 5 ]# </td> <td> #objSheet.ColumnNames[ 6 ]# </td> <td> #objSheet.ColumnNames[ 7 ]# </td> <td> #objSheet.ColumnNames[ 8 ]# </td> <td> #objSheet.ColumnNames[ 9 ]# </td> <td> #objSheet.ColumnNames[ 10 ]# </td> <td> #objSheet.ColumnNames[ 11 ]# </td> </tr> <!--- Loop over the data query. ---> <cfloop query="objSheet.Query"> <!--- It is possible that the query read in read in blank rows of data. For our scenario, we know that we HAVE to have an exercise name. Therefore, if there is no exercise name returned (in Column1), then this row is not valid - skip over it. ---> <!--- <CFTRANSACTION> <cfquery name="qryTruncate" datasource="datasource"> TRUNCATE TABLE tablename </cfquery> </CFTRANSACTION> ---> <CFTRANSACTION> <cfquery name="qryInsert" datasource="datasource"> insert into tablename ( status, owner, site_name, street, city, st, zip, lat, longitude, agl, height ) VALUES ( '#objSheet.Query.column1#', '#objSheet.Query.column2#', '#objSheet.Query.column3#', '#objSheet.Query.column4#', '#objSheet.Query.column5#', '#objSheet.Query.column6#', '#objSheet.Query.column7#', '#objSheet.Query.column8#', '#objSheet.Query.column9#', '#objSheet.Query.column10#', '#objSheet.Query.column11#' ) </cfquery> </CFTRANSACTION> End Code ------ On Wed, Mar 2, 2011 at 2:31 PM, Carl Von Stetten <vonner.li...@vonner.net>wrote: > > I don't have any experience with MySQL, but I've heard that there's a > way to enable multiple statements in a single <cfquery>. Once thats > enabled, look at the MySQL docs for the syntax for putting multiple SQL > statements together. In SQL Server (which I use frequently), you would > do something like this: > > <cfquery> > delete from mytable > go > insert into mytable > ...... > go > </cfquery> > > or > > <cfquery> > delete from mytable; > insert into mytable......; **Note the trailing semicolon > </cfquery> > > You may be able to do something similar in MySQL. By putting both > statements into a single <cfquery>, you are allowing the DBMS to queue > up processing. Each statement shouldn't execute until the previous one > completes. > > As to the blank row left behind, where are you seeing this? In a > <cfquery> dump or are you looking at the table through a MySQL > management tool? > > Carl > > On 3/2/2011 11:20 AM, Mallory Woods wrote: > > Carl its MySQL > > > > On Wed, Mar 2, 2011 at 2:00 PM, Carl Von Stetten<vonner.li...@vonner.net > >wrote: > > > >> What DBMS are you using? SQL Server, MySQL, Oracle, or MS Access? > >> Carl > >> > >> On 3/2/2011 10:43 AM, Mallory Woods wrote: > >>> Carl, > >>> > >>> I will try that. I had the truncate in a cftrans.. let me try them > both > >> and > >>> see what I get. > >>> > >>> Nope.. still the same result. I have tried having both of them in one > >>> cftrans and also having them in their own cftrans block. > >>> > >>> The odd thing is.. When the truncate works, It removes all of the data > >> but > >>> leaves one blank record. Like blanks on all lines. > >>> > >>> Very odd. > >>> > >>> Thanks for the suggestion but that didn't do it. :( > >>> > >>> > >>> > >>> On Wed, Mar 2, 2011 at 1:30 PM, Carl Von Stetten< > vonner.li...@vonner.net > >>> wrote: > >>> > >>>> I wouldn't expect that the delete or truncate would still be running, > >>>> but if they are, wrapping all of the<cfquery> tags in > a<cftransaction> > >>>> might help, or wrapping the delete/truncate in a transaction, then > >>>> calling the insert in a separate<cftransaction>? > >>>> > >>>> HTH, > >>>> Carl > >>>> > >>>> On 3/2/2011 9:57 AM, Mallory Woods wrote: > >>>>> I am trying to clear all of the data from a table before importing > new > >>>> data. > >>>>> Example code: > >>>>> > >>>>> * > >>>>> <cfquery name="qryClear" datasource="datasource"> > >>>>> TRUNCATE TABLE tablename > >>>>> </cfquery> > >>>>> > >>>>> ---> > >>>>> <!--- > >>>>> <cfquery name="qryDelete" datasource="datasource"> > >>>>> delete from tablename > >>>>> </cfquery> > >>>>> ---> > >>>>> > >>>>> <cfquery name="qryInsert" datasource="datasource"> > >>>>> insert into tablename > >>>>> ...... > >>>>> </cfquery>* > >>>>> > >>>>> I have tried to truncate and delete the data. This appears to work > >> fine. > >>>>> However, the next set of lines which is supposed to import the data > >> runs > >>>> but > >>>>> no data is imported. > >>>>> Could the delete or truncate function still be running when the > import > >> is > >>>>> taking place? If so, what is the best suggested method to get both of > >>>> these > >>>>> functions to run after > >>>>> each other? > >>>>> > >>>>> Thanks in advance. > >>>>> > >>>>> Mallory Woods > >>>>> > >>>>> > >>>>> > >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342698 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
