Insert Excel Spreadsheet

[fs22]

I have been tasked with figuring out how to automatically insert an uploaded
Excel spreadsheet into a SQL database. Has anyone ever done this? If so, can
you point me in the right direction? I can upload the file and all pretty
easily. Where I am stumped is how to insert the excel data into the database.
Thoughts?

Thanks in advance.

~Clay

[Dan Bracuk]

The best way is to not use cold fusion, especially if the spreadsheet has a lot
of rows.

Originally posted by: fs22
I have been tasked with figuring out how to automatically insert an uploaded
Excel spreadsheet into a SQL database. Has anyone ever done this? If so, can
you point me in the right direction? I can upload the file and all pretty
easily. Where I am stumped is how to insert the excel data into the database.
Thoughts?

Thanks in advance.

~Clay

[fs22]

In this case, that is not really an option. I need to use ColdFusion.

Any other ideas?

[Dan Bracuk]

Then you had better read up on cffile and cfloop.

[mxstu]

fs22,

I agree that it may not be the ideal method, but it is possible with some
databases. The specifics depend on your database type. What database are you
using?

Different databases may have different tools available, but for example, with
MS Access you can query excel sheets directly with a CFQUERY + ODBC (see
attached). One advantage to that approach is that it doesn't require looping,
but keep in mind there are some limitations. There is also a free custom tag
at source forge (CFX_ExcelQuery) that reads the data from an excel sheet into a
CF query object. You could then loop through the query and insert each row
into your database table. The downside of that method is that it requires
looping, which as Dan mentioned, could be inefficient.



<!--- EXCEL 8.0 is excel 2000+ format --->
<cfquery name="importData" datasource="#yourAccessDSN#">
INSERT INTO yourAccessTable (firstName, lastName, address)
SELECT firstName, lastName, address
FROM [Sheet1$]
IN 'c:\someDirectory\yourImportFile.xls' 'EXCEL 8.0;'
</cfquery>

[fs22]

Well, I figured it out. I am attaching the code I came up with. Let me know if
you find any issues with it as I do not want to threaten the integrity of my
database.

<cfparam name="upload_error" default="0">
<!--- ################################################## ######################
--->
<!--- Check if form was submitted --->
<cfif IsDefined('FORM.Submit')>
<!--- Upload file --->
<cftry>
<cffile action="Upload" filefield="FORM.filetoupload"
destination="#APPLICATION.AbsAddr#\" nameconflict="overwrite"
attributes="Normal">
<cfcatch type="Any">
<cfset upload_error = 1>
</cfcatch>
</cftry>
<!--- End Upload file --->

<cfif NOT upload_error>
<!--- Getting uploaded file extension --->
<cfset fname_ArrayOld = ListToArray(cffile.CLIENTFILE, ".")>
<cfset fname_Ext = fname_ArrayOld[#ArrayLen(fname_ArrayOld)#]>
<!--- End getting uploaded file extension --->

<!--- Delete inappropriate files --->
<cfif ListContainsNoCase("csv", fname_Ext) EQ 0>
<cfset upload_error = 1>
</cfif>
<!--- End check for upload error --->

<!--- Upload Data --->
<cfif NOT upload_error>
<!---Lock file and read the file contents in order to parse through and insert
rows into database.--->
<cflock name="reading" type="ReadOnly" timeout="30">
<cffile action="Read" file="#APPLICATION.AbsAddr#\#cffile.SERVERFILE#"
variable="TheFileContents" charset="utf-8">
</cflock>
<!---If no upload errors exist, then begin looping through the file contents
to insert new records.--->
<cfif NOT upload_error>
<!---Begin loop through csv file.--->
<cfloop index="row" list="#TheFileContents#" delimiters="#chr(13)##chr(10)#">
<!---Begin try to catch errors so rollback can be performed.--->
<cftry>
<!---Pull data from existing database to check and see if customer already
exists in the database.--->
<cfquery name="chkDup" datasource="#datasource#">
SELECT * FROM customer
WHERE Store = '#ListFirst(row, "")#'
</cfquery>
<!---Catch any errors and stop processing.--->
<cfcatch type="any">
<cfset upload_error = 1>
<cfbreak>
</cfcatch>
</cftry>
<!---If record does not exist in database, begin transactiono of insert record
into database.--->
<cfif chkDup.RecordCount IS 0>
<cftransaction action="begin">
<!---Begin try to catch errors so rollback can be performed.--->
<cftry>
<!---Insert customer record--->
<cfquery name="InsertNew" datasource="#datasource#">
INSERT INTO customer
(store)
VALUES
('#ListGetAt(row,1)#')
</cfquery>
<!---Insert User record--->
<cfquery name="InsertUser" datasource="#datasource#">
INSERT INTO users
(store,uid,pwd)
VALUES
('#ListGetAt(row,1)#','Administrator','#ListGetAt( row,1)#')
</cfquery>
<!---Catch any errors and stop processing.--->
<cfcatch type="any">
<cfset upload_error = 1>
<!---Perform rollback due to errors received.--->
<cftransaction action="rollback" />
<cfbreak>
</cfcatch>
</cftry>
<!---With no errors, commit transaction.--->
<cftransaction action="commit" />
</cftransaction>
</cfif>
</cfloop>
</cfif>

<cfif upload_error>
<!--- Delete file --->
<cflock name="deleting" type="exclusive" timeout="30">
<cffile action="Delete" file="#APPLICATION.AbsAddr#\#cffile.SERVERFILE#">
</cflock>
<!--- End delete file --->
<cfset upload_error = 1>
</cfif>
<!--- End delete inappropriate files --->
</cfif>
</cfif>
</cfif>
<!--- End if form was submitted --->
<!--- ################################################## ######################
--->

<form action="upload.cfm" method="POST" enctype="multipart/form-data">
<input type="file" name="filetoupload">
<input type="hidden" name="submit" value="1">
<input type="Submit" value="Upload">
</form>

[mxstu]

So you're importing a CSV and not an actual xls file? What database are you using?

[fs22]

Yes, a csv file. I found it easier than having to deal with the extranneous code in an Excel file. I am using a SQL database.

[mxstu]

fs22,

Yes, using text files tends to be easier in most cases. Specifically what database type: mySQL, Access, oracle, sql server?

[fs22]

SQL & Access

[mxstu]

fs22,

Well, SQL is a language and Access is your database type. Maybe you mean
mySQL or sql server.

You certainly can use cffile + cfloop to import data from a CSV file.
However, since it involves looping it may not be as efficient as some other
methods. Also, ou might want to take a look at this line. If you're going to
specify a delimiter, shouldn't it be comma not an empty string?

WHERE Store = '#ListFirst(row, "")#' .

With Access, you can typically read a CSV file directly with CFQUERY (i.e. no
need for cfloop). This technique can be used in combination with at a
"schema.ini" file for greater control over the data types. Although, the
drivers should be able to read the CSV file without it. The schema.ini file is
essentially a text file that contains settings which describe the format of
your text file (ie. the column names, data types, etc). This allows you to
CFQUERY a text file almost like a regular access table.

[url]http://www.devx.com/tips/Tip/12566[/url]

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcje[/url]
tschema_ini_file.asp

Here is a simple example of reading the data from a text file named
"myAddresses.csv", which contains three columns: FirstName,LastName,Address.
The file contents are inserted into a regular Access table named
"myDestinationTable". Obviously it needs validation, but should demonstrate
the idea.

<cfquery name="insertData" datasource="myAccessDSN">
INSERT INTO myDestinationTable(FirstName,LastName,Address)
SELECT FirstName,LastName,Address
FROM [TEXT;DATABASE=c:\myCSVDirectory;].myAddresses.csv;
</cfquery>

[fs22]

Thanks for the response. I know that SQL is a language. What I meant is that we use both database types...Access and SQL server.

[mxstu]

fs22,

Okay, I wasn't sure :-) With sql server you could obviously use dts or bcp
and call it from CF. You can also use essentially the same techique described
in my last post, but using openrowset, opendatasource, etc. Another
alternative is to add a linked server (for a text file directory). The latter
two methods are pretty simple to set up and use.

[url]http://www.databasejournal.com/features/mssql/article.php/3331881[/url]

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_s[/url]
erver_4uuq.asp