Separating Query in multiple CSV Files Using ColdFusion?

Question

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?

possible duplicate of Coldfusion, help creating a download all button that will zip all the CSV files that are created from a query — James A Mohler
– James A Mohler, Commented Jan 2, 2015 at 16:24
That is actually my other thread, I'm doing it a different way and the way I was breaking it into the 2000 doesn't work this way :/ — SaSquadge
– SaSquadge, Commented Jan 2, 2015 at 16:25
It sounds like you want to write all the files, then use <cfzip> and then do the Content-Disposition part on the resulting zip file — James A Mohler
– James A Mohler, Commented Jan 2, 2015 at 16:27
I wouldn't suppose an xlsx / <cfspreadsheet> is an option. xlsx files are naturally zipped. — James A Mohler
– James A Mohler, Commented Jan 2, 2015 at 16:48
I would like to use the xlsx but the user that requested this wanted the files to be csv — SaSquadge
– SaSquadge, Commented Jan 2, 2015 at 16:50

Dan Bracuk · Accepted Answer · 2015-01-02 16:45:50Z

3

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

edited Jan 2, 2015 at 16:45

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

answered Jan 2, 2015 at 16:39

James A Mohler

11.1k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SaSquadge Over a year ago

Yes this is what I am looking for, the only thing with this is it stops will do the 2000 then just stop instead of going onto 2001-4000

James A Mohler Over a year ago

Are you saying you have a version of ColdFusion where <cfoutput> does not have the startrow attribute? Or that startrow does not work?

SaSquadge Over a year ago

It does work, but when I tried this it would just do 2000 results and be done. Instead of continuing to the next 2000 results.

James A Mohler Over a year ago

Do a cfoutput on just the section variable and confirm that it is iterating as expected

Regular Jo · Accepted Answer · 2015-01-02 16:55:00Z

2

<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.

answered Jan 2, 2015 at 16:55

Regular Jo

5,5483 gold badges28 silver badges54 bronze badges

5 Comments

James A Mohler Over a year ago

I like the use of <cfsavecontent> for the csv string generation

SaSquadge Over a year ago

Edit: Think I got it to work using this way will verify in a second.

SaSquadge Over a year ago

Seems to be breaking it apart correctly, only question now is how do I add the new lines back in? Taking the trim off doesn't seem to work.

Regular Jo Over a year ago

Do you have the </cfsavecontent> (closing tag) on a seperate line after the #Firstname#, as I have it in my demo? If not, do so.

SaSquadge Over a year ago

Great that did the trick, now to get the zip to working and I will be set thank you!

Collectives™ on Stack Overflow

Separating Query in multiple CSV Files Using ColdFusion?

2 Answers 2

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related