insert csv into database

[fyrehed]

Hello,

I am hoping that someone can point me to something completely obvious that I have overlooked (or something not obvious 🙂 .

I have many people upload csv files into one directory on a daily basis and I have a need to put them into a table and out put them into ONE csv then FTP to another location at the end of the day. I would like to use coldfusion for the whole process.

I have used a datasource for the CSV in prior locations, which will allow me to insert the csv into the table as they come in without looping through a bunch.The managers of the network will not allow a DS for the csv.

I have also used DTS for ONE file, with a timed event running. Is there a way for me to trigger DTS with a coldfusion page so I can run an insert when they hit the directory? Dont think I can batch a pile of files with DTS AND again, would like to encapsulate CF for the whole process.

Please, if anyone could point me in a direction that I havent gone OR this happens to trigger a thought, please let me know.

Thanks a ton!

Matt

[fyrehed · Accepted solution]

Hello,

I guess i was not clear enough for help.

I found this solution for others who are in need.

<cffile action="read"file="\\my\complete\Path\UploadFolder\data.csv" variable="datacsv">

<cfloop index="index" list="#datacsv#" delimiters="#chr(10)##chr(13)#">
<cfquery datasource="redding" timeout="180">
Insert INTO Table(ListNum, ListingAgencyID, ListingAgencyTechID, ListingAgencyName)
Values
('#listgetAt(index,1, ',')#', '#listgetAt(index,2, ',')#','#listgetAt(index,3, ',')#','#listgetAt(index,4, ',')#')
</cfquery>

</cfloop>

<strong><cfquery name="rscsvdemo" datasource="Datasource">
SELECT * FROM tablename
</cfquery>
<cfdump var="#rscsvdemo#">

Thanks.