Sep 21, 2015; 18:32
Jolle Carlestam
Creating a csv file using really large data sets
In the lasso chat room there’s a discussion right now about how to create and download a csv file based on data from a really large data set. In this case tables with more than 200000 rows.
The original approach using inlines tended to crash the server due to the result being so freakishly large. The resulting file some 28 MB.
With some iterations between the chatters a way to use DS and web_response -> sendChunk was constructed.
Web_response -> sendChunk BTW is what Lasso uses to send stuff to the browser.
For the benefit of listers not presently in the chat room here’s the code example as it looks right now. It can serve as a starting point for others in the same situation.
Advantages:
It does not rely on creating a temporary file on the server.
There’s probably no limit on how many records it can export.
It is probably reasonably fast
Code:
[
define export_csv(
ds::ds,
table::string,
filename::string,
breakpoint::integer // this would be set to a really large number, like 20000
) => {
local(
presentcount = 1,
_table = #table -> encodesql,
rowcount = #ds -> sql("SELECT COUNT(*) AS found FROM " + #_table, 1) -> firstrow -> find('found'),
exportrows = #ds -> sql("SELECT * FROM " + #_table + " LIMIT 1," + #breakpoint, #breakpoint) -> rows,
colnames = #exportrows -> first -> columns,
output = bytes
)
web_response -> setHeaders(array(
'MIME-Version' ='1.0',
'Content-Type' = 'application/text',
'Content-Disposition' = 'attachment; filename="' + #filename + '"'
// 'Content-Length' = ''
))
#output -> append('"' + #colnames -> join('","') + '"\n')
while(#presentcount <= #rowcount) => {
with row in #exportrows do {
#output -> append('"' +
(with col in #colnames select(
#row -> find(#col)
)) -> join( '","') + '"\n'
)
}
web_response -> rawcontent = #output
web_response -> sendChunk
#output = bytes
#presentcount += #breakpoint
#exportrows = ds(::mysql) -> sql("SELECT * FROM help_keyword LIMIT " + #presentcount + "," + #breakpoint + "", #breakpoint) -> rows
}
}
export_csv(
ds(::mysql),
'help_keyword',
'demo.csv',
10
)
]
#############################################################
This message is sent to you because you are subscribed to
the mailing list Lasso Lasso@lists.lassosoft.com
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <Lasso-unsubscribe@lists.lassosoft.com>
Send administrative queries to <Lasso-request@lists.lassosoft.com>
Sep 21, 2015; 19:26
Jolle Carlestam
Re: Creating a csv file using really large data sets
Sep 22, 2015; 07:44
Jolle Carlestam
Re: Creating a csv file using really large data sets
Sep 22, 2015; 09:58
Jolle Carlestam
Re: Creating a csv file using really large data sets