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>

Related Entries:

• SQL Between Statement Not Working With New Year

| View count: 76

5 responses to “Simple Coldfusion/SQL Server Upload Script”

1. Michael De Jonghe Says:
   Oct 16, 2008 at 3:45 PM If you're running CF8, there are better ways to loop over a file line by line. CF8 supports the FILE attribute of CFLOOP. #line#
   If there are NULLS in your CSV you can find them buy converting the LIST to an ARRAY with the ListToArray() function. The cool thing about the ListToArray() is that it now offers a chance to capture blank spaces in a list. ListToArray(line,",",true) And also be sure to use CFQUERYPARAM tags when making calls to the Datasource. Try this... INSERT INTO tblCycleCountData (ctCampus,ctDate,ctAISLE,ctLOCATION,ctITEM,ctLPID,ctQTY,ctZONE,ctLOCTYPE,ctLPIDPRESENT,ctCOUNTTOTAL) VALUES ( , , etc, etc, etc... )
2. Steve Says:
   Oct 16, 2008 at 4:19 PM Thanks for your input Michael. I have only been using Coldfusion 8 for about a month, so I am pretty green. Next time I have to do something like this, I will definitely use your methodology.
3. Steve Bryant Says:
   Oct 16, 2008 at 5:58 PM If you know you will be using SQL Server, then you might also look into BULK INSERT: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
4. ike Says:
   Oct 16, 2008 at 9:38 PM Hey, some good stuff here that I wasn't aware of myself... I did want to point out very briefly that the script has some extra characters '#listgetAt('#index#',1, ',')#' could be '#listgetAt(index,1, ',')#' Personally I think the latter with fewer quotes and pounds is a bit more legible. Of course your mileage may vary. :)
5. Frederic Fortier Says:
   Oct 17, 2008 at 8:27 AM Also.. instead of having your loop setup like this ---SQL Statement You may want to have like this ---SQL Statement That will open on one connection to the SQL server and do everything in one query call

Leave a Reply