ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

ColdFusion: Upload a pipe delimited file and insert into SQL table

Updated on October 14, 2012

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>

Click to Rate This Article