CFtips.net

Simple Coldfusion/SQL Server Upload Script

Filed under ColdFusion , SQL Server

I found this script while browsing the other day.  I thought it might be useful for anyone needs to upload data into SQL Server from a csv file.  The only catch is that you can't have NULL values in your CSV file.

<cffile action="read" file="\\stg-wb-20\ims-STG-WB-20\testSteve\ss.csv" variable="csvfile">

<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="dsnDB1">
         INSERT INTO tblCycleCountData (ctCampus,ctDate,ctAISLE,ctLOCATION,ctITEM,ctLPID,ctQTY,_

ctZONE,ctLOCTYPE,ctLPIDPRESENT,ctCOUNTTOTAL)

         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#',
                   '#listgetAt('#index#',4, ',')#',
                   '#listgetAt('#index#',5, ',')#',
                   '#listgetAt('#index#',6, ',')#',
                   '#listgetAt('#index#',7, ',')#',
                   '#listgetAt('#index#',8, ',')#',
                   '#listgetAt('#index#',9, ',')#',
                   '#listgetAt('#index#',10, ',')#',
                   '#listgetAt('#index#',11)#')
   </cfquery>
</cfloop>

<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfoutput>Done</cfoutput>