How To Filter Data From Csv File In Java

CSV file often used for download or upload data from/to database. If table'due south records count is big or very large then downloaded data to CSV file volition have large in size.
Opening that large CSV file using spreadsheet application requires a lot of memory and CPU resources. Depending on user computer specifications, some swallow much time to open up.

It is much faster using Notepad++ to open up large CSV file but if we want to filter data and save it to another CSV then using Notepad++ will not be efficient especially for some complex filtering criteria.

==> The workaround of this situation is to import that large CSV to SQLite. After importing to SQLite, nosotros tin can filter it using SQL Query and save it back to CSV file.

According to documentation, SQLite is an embedded SQL database engine. SQLite reads and writes directly to ordinary deejay files. A consummate SQL database with multiple tables, indices, triggers, and views, is contained in a unmarried disk file.
Think of SQLite not as a replacement for Oracle but as a replacement for fopen(). Its functioning is normally quite good even in low-memory environments.

This blog mail gives example on bones connexion to SQLite, Sql Query, importing CSV, salve query to CSV. Also example on using SQLite in Coffee so you tin build your ain application.

Basic SQLite Usage

Download SQLite binary executable file from https://www.sqlite.org/download.html
Extract Zip file to a folder. It is recommended to excerpt on easy founded folder
Open command prompt and go to SQLite folder. In order to connect & create a database 'db1', type:
sqlite3 db1
This syntax volition connect you to database db1 if information technology is already exist or create db1 if information technology does non exist. The db1 database is but a file with same name and reside in SQLite directory.
Remember you must on SQLite directory to run sqlite3 command. If y'all dont want to be e'er in SQLite directory then you lot need to register the SQLite directory on 'PATH' of Environment Variable.
If you lot already accept SQLite Path on Environment Variable so you lot should specify the database path to connect i.east sqlite3 C:/SQLite/db1. Otherwise a new db1 database will be created in same directory of wherever you practice sqlite3 command.
Afterwards database 'db1' created, please create a tabular array 'testtable' for beginner demonstration i.eastward
```
create tabular array testtable (one varchar(50), two int);              
```
In a higher place code volition create 'testtable' with just ii columns, ane and two.

Insert records to 'testtable' example

insert into testtable (one, two) values ('hello', ane); insert into testtable (one, two) values ('world!', 2);

Showing table records & utilise filter case

select * from testtable; select * from testtable where two=two;

Show records without cavalcade header

Updating & deleting example

update testtable set two=ii+1; delete from testtable where ii=ii;

Using dot-command example

.header on select * from testtable;

Show records along with column header

Importing CSV to SQLite example
```
.mode csv .import C:/work/largeCSV.csv csvtbl1;              
```
Import largeCSV CSV file to csvtbl1 SQLite tabular array.

Saving sql query result to CSV

.mode csv .header on .one time C:/piece of work/anotherCSV.csv select * from testtable;

More than documentation on SQLite 3 please refer to https://www.sqlite.org/docs.html

Running SQLite command Inside Text File Example

SQLite can run commands inside Text File. We write SQLite commands in text file and then pipe the file to database.
Create a txtImport.txt file with contains:

.mode csv .import C:/piece of work/largeCSV.csv csvtbl1;

Pipage this file to db1 from windows command prompt within SQLite directory

sqlite3 db1 < C:/work/txtImport.txt

Above commands will import largeCSV.csv file to table csvtbl1

You can effort it past your self using a real large CSV file to import. It is much faster then opening using spreadsheet awarding.
Subsequently imported then we can filter data using SQL query. Off course by using query is very flexible.

In lodge to salve Query result to Csv, delight Create a txtExport.txt file with contains:

.mode csv .header on .in one case C:/work/filteredCSV.csv select * from csvtbl1 where col1 similar '%xxx%' and col2=10;

Pipe this file to db1 from windows command prompt inside SQLite directory

sqlite3 db1 < C:/work/txtExport.txt

Above commands will save Query outcome to filteredCSV.csv file

Implementing SQLite Using Java

These Java examples can exist used in order to make application that can display content of large CSV file. Every bit previously explained that SQLite is better than fopen() and fputs(). Using SQLite to process large CSV file is faster and more efficient.
Notwithstanding your application need to have a threshold of how many rows in CSV that catagorized big. For example if the CSV rows but below one thousand then information technology is not large so you lot can use normal File operation.

Here is Java example for working with SQLite

static void RetrieveData() {     	effort { 	Class.forName("org.sqlite.JDBC"); 	Connexion connection = nada; 	try { 		connexion = DriverManager.getConnection("jdbc:sqlite:D:/utility/sqlite-beat out-win32/db1"); 		Statement argument = null; 		statement = connection.createStatement(); 		statement.setQueryTimeout(30);  		ResultSet rs = argument.executeQuery("select * from csvtbl1 limit 0,ii"); 		while(rs.side by side()) 		{ 		  // read the consequence set 		  System.out.println("i = " + rs.getString("one")); 		  System.out.println("ii = " + rs.getInt("two")); 		} 		rs.close(); 		connection.shut(); 	} grab (SQLException ex) { 		Logger.getLogger(Sqlite2.class.getName()).log(Level.Severe, nil, ex); 	}  	} grab (ClassNotFoundException ex) {             Logger.getLogger(Sqlite1.class.getName()).log(Level.SEVERE, cipher, ex);     } 	 }

In that location is a connectedness string dbc:sqlite:D:/utility/sqlite-shell-win32/db1 and you should modify its path to your own SQLite directory, otherwise new database 'db1' will exist created in java compilation directory.
Inserting, Update and Delete operations are trivial. Y'all tin can use something similar argument.executeUpdate(UpdateQuery);.

Importing and Exporting From/To CSV
Please refer to Import Csv file indicate. This Java case using those SQLite dot-commands. Firstly, creating txtImport.txt programmatically and run sqlite3 automatically using Process.

static void createTxtFile(Cord txtPath, ArrayList contents) { 	File f = new File(txtPath); 	 	endeavour { 		FileWriter fw = new FileWriter(f, true); 		PrintWriter prisoner of war = new PrintWriter(fw); 		for (Object line : contents) { 			pow.println(line); 		} 		pw.close(); 		fw.close(); 	} catch (IOException ex) { 		Logger.getLogger(Sqlite2.grade.getName()).log(Level.SEVERE, nix, ex); 	} }  static void ImportCsv() { 	ArrayList al = new ArrayList(); 	al.add together(".manner csv"); 	al.add(".import C:/largeCSV.csv csvtbl1"); 	createTxtFile("D:/txtImport.txt",al);        	String command = "cmd.exe /c sqlite3.exe D:/utility/sqlite-beat out-win32/db1 < D:/txtImport.txt"; 	//System.out.println(command); 	try { 		 Procedure p = Runtime.getRuntime().exec(command); 	} take hold of (IOException ex) { 		Logger.getLogger(Sqlite2.class.getName()).log(Level.Severe, null, ex); 	} }

Please look at part cmd.exe /c sqlite3.exe D:/utility/sqlite-shell-win32/db1 < D:/txtImport.txt.
This part pipes SQLite commands inside the text file to database 'db1'. Also I already annals sqlite3 path in Surroundings Variable so I can type in sqlite3 in anywhere from command prompt but the database path itself must be specified.

Below is code for save filtered query to CSV using Java. The filter is simply to retrieve 1000 rows.

static void ExportToCsv() { 	ArrayList al = new ArrayList(); 	al.add(".header on"); 	al.add(".mode csv"); 	al.add(".once C:/filteredcsv.csv");         	al.add("select * from csvtbl1 limit 0,1000;"); 	createTxtFile("D:/txtExport.txt",al); 	String command = "cmd.exe /c sqlite3.exe D:/utility/sqlite-crush-win32/db1 < D:/txtExport.txt"; 	//Organization.out.println(command); 	endeavor { 		 Process p = Runtime.getRuntime().exec(command); 	} grab (IOException ex) { 		Logger.getLogger(Sqlite2.class.getName()).log(Level.Severe, null, ex); 	} }

Retrieve to put semicolon mark later on every SQL Query syntaxt.

This large CSV functioning with help of SQLite is fast & efficient.

Cheers,
Agung Gugiaji