Icon

m_​001_​write_​csv_​loop_​chunks

Download data from a database in chunks and append them to an CSV file

Download data from a database in chunks and append them to an CSV file

The example uses ROWIDs from SQLite. You could see if you either have a ROWIDs present or you could create one yourself. You can choose the size of the chunk/batch yourself (I use integers you might have to see if with very large numbers you might need big integers or something. Even a workaround with using strings might be possible).

This formulas can be used to split up every no of items into equal chunks and one wich contains the size of the rest.

Download data from a database in chunks and append them to an CSV filehttps://forum.knime.com/t/export-huge-ms-sql-table-to-csv-without-fetching-all-records-together/15105/2?u=mlauber71 Integer result;if ($${IcurrentIteration}$$.equals($n_batches_full$)) {result = $${In_batches_overspill}$$;} else {result = $${Iv_batch_size}$$;}return result;-----------------If the current iteration is the same as the no of full batches then thechunk size is set to the rest This formulas can be used to split up every no of items into equal chunks and one wich contains the size of therest. n_batchesn_batches_fulln_batches_overspilln_batches_loopcurrent_batch_sizev_start_row1 + ($${Iv_batch_size}$$ * $${IcurrentIteration}$$)v_end_row1 + ($${Iv_batch_size}$$* $${IcurrentIteration}$$) + $current_batch_size$ -1delete_this_file=> the resulting CSV fileknime://knime.workflow/../data/result.csvdb.sqlitedata1data1SELECT MAX(rowid) as max_rowid FROM data1select by rowidv_batch_size=> the size of thebatches/chunks to beappended to the CSV file500 per batchmake surethe CSV filehas been deleteddelete_this_full_path../data/result.csvappend data to CSV fileif exists../data/result.csv Data Generator Math Formula Math Formula Math Formula Math Formula Java Snippet(simple) Math Formula Math Formula Table Creator String to URI SQLite Connector DB Writer DB Query Reader DB Query Reader IntegerConfiguration DeleteFiles/Folders String to Path Table Rowto Variable Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables URL to File Path Counting Loop Start CSV Writer Table Rowto Variable Variable Loop End CSV Reader Download data from a database in chunks and append them to an CSV filehttps://forum.knime.com/t/export-huge-ms-sql-table-to-csv-without-fetching-all-records-together/15105/2?u=mlauber71 Integer result;if ($${IcurrentIteration}$$.equals($n_batches_full$)) {result = $${In_batches_overspill}$$;} else {result = $${Iv_batch_size}$$;}return result;-----------------If the current iteration is the same as the no of full batches then thechunk size is set to the rest This formulas can be used to split up every no of items into equal chunks and one wich contains the size of therest. n_batchesn_batches_fulln_batches_overspilln_batches_loopcurrent_batch_sizev_start_row1 + ($${Iv_batch_size}$$ * $${IcurrentIteration}$$)v_end_row1 + ($${Iv_batch_size}$$* $${IcurrentIteration}$$) + $current_batch_size$ -1delete_this_file=> the resulting CSV fileknime://knime.workflow/../data/result.csvdb.sqlitedata1data1SELECT MAX(rowid) as max_rowid FROM data1select by rowidv_batch_size=> the size of thebatches/chunks to beappended to the CSV file500 per batchmake surethe CSV filehas been deleteddelete_this_full_path../data/result.csvappend data to CSV fileif exists../data/result.csvData Generator Math Formula Math Formula Math Formula Math Formula Java Snippet(simple) Math Formula Math Formula Table Creator String to URI SQLite Connector DB Writer DB Query Reader DB Query Reader IntegerConfiguration DeleteFiles/Folders String to Path Table Rowto Variable Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables URL to File Path Counting Loop Start CSV Writer Table Rowto Variable Variable Loop End CSV Reader

Nodes

Extensions

Links