We help IT Professionals succeed at work.

import csv to access via cf

Medium Priority
283 Views
Last Modified: 2013-12-20
How do I import a csv text file to access using coldfusion? I need to put certain criteria on the import.
Comment
Watch Question

Top Expert 2008
Commented:
hi best to use list structures for this job:

<cfset delims=","> <!-- or tab or whatever else you have delimited the csv with. -->
<cfset numcols="3"> <!-- or however many columns in your datasource -->

<!-- read the file -->
<cffile action=read file="#file_path#" filefield="file">

<!-- loop through line by line -->
<cfloop list="#filefield#" index="line" delimiters="#chr(10)##chr(13)#">
  <!-- loop through the columns -->
  <cfloop from="1" to="#numcols#" index="num">
    <!-- any pre-processing here if you want to... -->
    <!-- insert the data -->
    <cfquery datasource="#dsn#">
      insert into table_name(col1, col2, col3, ...)
      values(
        '#listgetAt('#line#', 1, '#delims#')#"',
        '#listgetAt('#line#', 2, '#delims#')#"',
        '#listgetAt('#line#', 3, '#delims#')#"',
        ...
        )
    </cfquery>
  </cfloop>
</cfloop>

this should be example only - i did not check the syntax of the above code, but should get you in the right direction.

regards.

Author

Commented:
Thank you kindly :)

Author

Commented:
Is this advisable for large files of 1-10 MBs of rows of text with 500-15000 records? I am exporting accounting data with thousands of records to a csv file, then auto ftping it up to the cf server. I wanted to them import this csv files into the access 2000 database automatically, so I thought I could do it using the cf scheduler, and code something like what you explained. However I read somewhere that cffile action=read reads it into memory, and can crash the system with big files.

Author

Commented:
For me the best route was to link the access database to the csv file, then do an insert query from the linked csv table to the access database query. that was i could also use criteria.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.