ColdFusion: Upload a pipe delimited file and insert into SQL table
There have be many times that I have had a project where the client has a list of records that will be used for some type of web application. Could be email blasts, or just to update a database with new records. I could also be picking up a pipe delimited file from a ftp location, that needs to be inserted into a database. The following code will provide the steps for the upload and insertion of the pipe file into a Microsoft SQL table.
<cfscript>
// function for uploading the file from a standard web form that the user submits
</cfscript
<CFFILE ACTION="upload" FILEFIELD="FileContents" DESTINATION="d:\file location\" NAMECONFLICT="overwrite" >
<cfscript>
// step for reading the pipe delimited file and begin the process
</cfscript>
<cfhttp
url="http://www.domain.com/filelocation/#serverFile#" name="newfile"
firstrowasheaders="true"
delimiter="|"
textQualifier="" />
<cfscript>
// loop through the file and insert each record
</cfscript>
<cfloop query="newfile">
<cfquery name="importfile" datasource="datasource">
INSERT INTO EMLOYEE
( emplid,
fname,
lname,
business_unit,
deptid,
jobcode,
jobtitle,
email,
street1,
street2,
city,
state,
zip,
phone,
fax
)
VALUES
(
'#trim(newfile.EMPLID)#',
'#trim(newfile.FIRST_NAME)#',
'#trim(newfile.LAST_NAME)#',
'#trim(newfile.BUSINESS_UNIT)#',
'#trim(newfile.DEPTID)#',
'#trim(newfile.JOBCODE)#',
'#trim(newfile.JOBTITLE)#',
'#trim(newfile.EMAIL_ADDR)#',
'#trim(newfile.STREET1)#',
'#trim(newfile.STREET2)#',
'#trim(newfile.CITY)#',
'#trim(newfile.STATE)#',
'#trim(newfile.ZIP)#',
'#trim(newfile.PHONE)#',
'#trim(newfile.fax)#'
)
</cfquery>
</cfloop>
Some Other ColdFusion Articles
- Adobe Coldfusion Tip For Finding Duplicate Records In SQL Database
- Coldfusion: allowing max file size when uploading
- ColdFusion: Monitor Hard Drive Space And Email Alert
- Coldfusion SQL Tips: Creating Tables, Backup, Fields
- Coldfusion Programming: Accessing a shared network drive
- Coldfusion: Encrypting and Decrypting Data
- Adobe Coldfusion Help: Query To Spreadsheet
- ColdFusion: Upload a pipe delimited file and insert into SQL table
- Coldfusion programming: How to submit muliple records at the same time that have the same field name
- How To Upload Multiple Files With ColdFusion
- ColdFusion & Javascript: How to pause a web page process