Android - How to export the database in a csv file?

Question

I have an application that collects and stores information in a database, and the user can query this data within the application itself. However, I would like to insert a button to export the database into a file .csv.

Has anyone ever done or seen and tested an example where it actually works? I found several attempts on Stackoverflow gringo, as well as on some outside sites, but none that worked.

I’m using Android Studio.

Answer 1

Below the solution I implemented.

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;
    }

Answer 2

Take a look at this link: https://www.sqlite.org/cli.html

CSV Export

To export an Sqlite table (or part of a table) as CSV, Simply set the "mode" to "csv" and then run a query to Extract the desired Rows of the table.

> sqlite> . header on sqlite> . csv mode 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 helped! Good luck!