Exporting csv file in form of zip

[ishansrivastava]

Hello,
I have to download the data from db to CSV. And i have to downlaod it in .zip form how can we attain that.Right now i am just creating a file and transferring data to that csv file using this datapipeline. But as i said i want to show that the zip file is getting downloaded in the background.

[DeleTaylor]

Hi, I'll leave the Zip part for you to search on Stackoverflow. An example of the CSV export part can be found in this demo: https://github.com/NorthConcepts/DataPipeline-Demos/tree/master/Export-CSV-and-Excel-from-Java-web-apps

[ishansrivastava]

Hi dele ,
Had one doubt, does JdbcWriter() auto commits in the db?

[DeleTaylor]

Normally, it's however you've configured the connection passed in to it. However, setCommitBatch() controls this for batch inserts.

See https://northconcepts.com/javadocs/com/northconcepts/datapipeline/jdbc/JdbcWriter.html#setCommitBatch-boolean-

[ishansrivastava]

Well i did that
JdbcWriter writer=new JdbcWriter(connection,table_name).setAutoCloseConnection(true).setCommitBatch(true);
((JdbcWriter) writer).setBatchSize(1000);

By the way how can we use this setBatchSize() when we are writing from db to .csv file

[DeleTaylor]

setBatchSize() doesn't apply when writing a CSV file or reading from a database. What would you expect it to do in your case?

[ishansrivastava]

So,actually i am writing data from db to csv. So as you suggested last time(csv to db) that if we incorporate setBatchSize() then the loading becomes faster.So in case of db to csv what should i use?

[DeleTaylor]

1. How much data are you loading from DB to CSV?
2. How much time is it taking?
3. How much time do you need it to be instead?

[ishansrivastava]

To download 129 MB data its taking around 1.5 minutes.Requirement is like 15 -20 seconds max

[DeleTaylor]

The downoad time depends on many things: network speed, database indexes, pipeline logic, etc. Understanding your situation would require opening a commercial support ticket to review your code and environment.

You can try the AsyncReader to read and write in separate threads, but that requires a Small Busines license.

https://northconcepts.com/docs/examples/use-multi-threading-in-a-single-job/

[ishansrivastava]

Hi,
So i observed something so there are some columns which can be null and can have values also so those columns has to be part of the record, but it seems we can't send null values, as it is throwing null pointer exception. So is there a way that i can send null values also(from csv to db)

[ishansrivastava]

Any advice sir? Mainly the question can we send null values by this API

[DeleTaylor]

Yes, we can send null values to the database without doing anything special. This is a core feature of the framework.

[ishansrivastava]

Hello sir ,
I am uploading data from csv to db and doing 4 jdbc lookups on each record for validation purpose.And its taking 25-30 min to upload 10000 data. I am commiting batches(1000).Just want to know that in this scenario is this the best speed which i can attain or something else can be done to make it really fast

[ishansrivastava]

Hello Dele,
I want to know that how the jdbclookup is happening in this API will the amount of lookup time will increase if the data in the table on which the lookup is performed increases? Want a clear picture of it.

[DeleTaylor]

You can speed up database lookup a few ways.

1. Ensure your database lookup query is using an index.

2. Try caching individual lookups. https://northconcepts.com/docs/examples/cache-lookup-values/

3. If your lookup table is small, you can try pre-cache it by changing the lookup to something like:

   Lookup lookup = new DataReaderLookup(
   new JdbcReader(connection, "SELECT * FROM hospital"),
   new FieldList("hospital_name", "address1", "city", "state", "zip_code"),
   new FieldList("hospital_id"));

BTW, 25 minutes for 10k records seems very slow, even with uncached lookups. Try running a profiler to see where you're losing time.