I have an application that collects and stores information in a database, and the user can query that data within the application itself.
However, I would like to insert a button to export the database to a .csv file.
Has anyone done or already seen and tested an example where it really works? I found several attempts at StaCkOverFlow gringo, as well as some outside sites, but none that worked.
I'm using Android Studio.
Take a look at this link: link
CSV Export
To export an SQLite table (or part of a table) to CSV, simply set the "mode" to "csv" and then run a query to extract the desired rows of the table.
> sqlite > .header on sqlite > .mode csv sqlite > .once c: /work/dataout.csv sqlite > SELECT * FROM tab1; sqlite > .system c: /work/dataout.csv
In the example above, the ".header on" line causes column labels to be printed as the first row of output. This means that the first row of the resulting CSV file will contain column labels. If column labels are not desired, set ".header off" instead. (The ".header off" setting is the default and can be omitted if the headers have not been previously turned on.)
The line ".once FILENAME" causes all query output to go into the named file instead of being printed on the console. In the example above, that line causes the CSV content to be written into a file named "C: /work/dataout.csv".
The final line of the example (the ".system c: /work/dataout.csv") has the same effect as double-clicking on the c: /work/dataout.csv file in Windows. This will typically bring up a spreadsheet program to display the CSV file. That command only works as shown on Windows. The equivalent line on a Mac would be ".system open /work/dataout.csv". On Linux and other unix systems you will need to enter something like ".system libreoffice /work/dataout.csv", substituting your preferred CSV viewing program for "libreoffice".
I hope I have helped! Good luck!
Below is the solution I deployed.
1- Include opencsv.jar in the project
2 -
private boolean criaCsv(String select, long parametro, String nomeArquivoCsv) throws Exception
{
try{
File arquivoCsv = new File( caminho + nomeArquivoCsv );
arquivoCsv.createNewFile();
CSVWriter writer = new CSVWriter(new FileWriter(arquivoCsv), CSVWriter.DEFAULT_SEPARATOR);
Boolean includeHeaders = false;
PreparedStatement ps = getConnection().prepareStatement(select, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(FETCH_BD);
ps.setLong(1, parametro);
ResultSet resultSet = (ResultSet) ps.executeQuery();
writer.writeAll(resultSet, includeHeaders);
writer.flush();
writer.close();
return true;
}
catch(Exception e){
log.error("",e);
return false;
}
}
public Connection getConnection()
{
try
{
if( conn == null || conn.isClosed()){
conn = dataSource.getConnection();
conn.setAutoCommit(false);
}
} catch (SQLException e)
{
log.error("", e);
}
return conn;
}