CODEWITHSUNDEEP
csvcoldfusion
SaSquadge
SaSquadge

Reputation: 219

Separating Query in multiple CSV Files Using ColdFusion?

What I'm trying to do is

A User puts in a Date They press a Download Button, this runs a query generating the results for the date they entered. While doing this it is writing these files to my server as .CSV. I will end up zipping them and the file will start downloading as a zip.

I have the code working to write the query to our server but my problem is it puts it all into one big file, where I would like to have the results separated in 2000 results per file. Is this possible? If so how do I go about this?

Current code I have for the query and writing data to server. Some things adjusted.

<cfquery name="users" datasource="#request.db#">
    SELECT DISTINCT firstname
    FROM Users
    WHERE Users.Date=#Date#
</cfquery>

<cfset filePath="D:/Users/Users.csv">
<cfset content = "firstname">

<cffile
    action="write"
    file="#filePath#"
    output="#content#">

<cfoutput query="users">
    <cfset content = "">
    <cfset content = "#firstname#">
    <cffile
        action="append"
        file="#filePath#"
        output="#content#">
</cfoutput>

<cfheader name="Content-Disposition" value="attachment; filename=#getFileFromPath (filePath)#">
<cfcontent file="#filePath#" type="application/octet-stream" deletefile="yes">

This code will run the query, writing the file to server and automatically download. But only downloads and writes to one file. How do I separate it into multiple small result files?

Upvotes: 2

Views: 366

Answers (2)

Regular Jo
Regular Jo

Reputation: 5510

<cfset maxrows = 2000>
<cfset onrow = 1>
<cfset filen = 1>
<cfset filelist = "">
<cfloop condition="#onrow# lte #users.recordcount#">
  <cfsavecontent variable="MakeContents"><cfoutput query="users" startrow="#onrow#" maxrows="#maxrows#">#firstname#
</cfoutput></cfsavecontent>
  <cfset MakeContents = trim(MakeContents)><!--- trimming gets rid of an extraneous return at the end of makecontents --->
  <cfset filepath = "d:\users\users_#filen#.csv">
  <cfset onrow = onrow + maxrows>
  <cfset filelist = listAppend(filelist,"users_#filen#.csv")>
  <cffile...> <!---write MakeContents to a file--->
  <cfset filen = filen + 1>
</cfloop>

<cfzip...>
  <cfloop list="#filen#" index="fi">
    <cfzipparam source="d:\users\#fi#">
  </cfloop>
</cfzip>

This puts everything for the result set into a variable, writes the variable to a file and stores the file in a list.

After all the file creation is done, cfzip gets all the files by looping over the list.

You can either add a unique identifier to the filename and keep them, or you can delete them afterwards.

Upvotes: 2

James A Mohler
James A Mohler

Reputation: 11120

Are you looking to do something like this?

<cfloop from="1" to="#users.recordcount#" step="2000" index="section">


  <cfoutput query="users" startrow="#section#" maxrows="2000">

    <cfset content = "#firstname#">
    <cffile
        action="append"
        file="#filePath##section#.csv"
        output="#content#">
  </cfoutput>

</cfloop>

...

Then zip resulting files

Upvotes: 3

Related Questions