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>

More by this Author

  • How to Build Wooden Roof Trusses
    23

    You may refer to a truss as the rafter, but it's basically the skeleton of the roof, carrying the weight of the frame and supporting the walls of the building. Trusses are very important to preventing the walls from...

  • Tips For How to Stain Concrete Floors
    1

    You don't always have to tile, carpet, or hardwood your floors. The new thing is to just stain the concrete that's already there. It's a lot of work, but if done properly, your home will look gorgeous. If you're...

  • How to Get Rid of Mice In and Around The House?
    5

    Until this year, the only time that I've had mice or rats in the house were when my daughter had pet mice and rats, and one of those times we did have one loose in the house. (Science fair project got loose.) Anyway,...


Click to Rate This Article
working