Export Sqlite files to CSV

Question

I need to give the option for the user to export a desired database table to a file in the SD card, so it becomes personal and it can import to another device later.

I have an application where I work with several tables in the database, this application is to use together with the equipment where you can import things, there is a great chance to rent the equipment together with a tablet.

My point is that the guy imports his way of working on the equipment, he has along with him his work table and he matters on the tablet to use together.

My problem is in exporting only the desired table.

I don’t find documentation on explaining it, just a few samples of code that don’t work, maybe by context.

I used these examples:

http://www.techrepublic.com/blog/software-engineer/export-sqlite-data-from-your-android-device/

http://www.tudosobretecnologia.com.br/2013/08/exportacao-de-dados-sqlite-do-seu.html

in the end with much cost I did it:

package com.example.app;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;
import java.util.ArrayList;
import java.util.List;

import android.app.AlertDialog;
import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;

import android.view.View;
import android.widget.AdapterView;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;

public class exportararquivo extends ListActivity{


    SQLiteDatabase Banco = null;
Cursor cursor;
List<Lista> tabelas = new ArrayList<Lista>();
String tabbanco="Tabela1";
TextView gerenciar;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.gerenciamento);
    gerenciar=(TextView)findViewById(R.id.textViewgerenciar);
    gerenciar.setText("   Escolha a tabela que deseja trabalhar.");
    abrebanco();
    buscardados();
    List<Lista> lista = gerarlista();

    final Listaadapter listasadapter = new Listaadapter(this,  lista);
    setListAdapter(listasadapter); 


    getListView().setOnItemClickListener(new OnItemClickListener() {



    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position,
            long id) {
        Lista tabela = listasadapter.getItem(position);
        exportDB(tabela.getNome());


        Intent intent = new Intent(exportararquivo.this, gerenciar.class);
        //intent.putExtra("tabbanco", tabela.getNome());
        exportararquivo.this.finish();
        startActivity(intent);


    }
    });

}

public List<Lista> gerarlista() {
    tabelas.add(criarLista("Tabela1"));
    cursor.moveToFirst();
    int x=cursor.getCount();
    while(x>1){
    nextdado(); 
    tabelas.add(criarLista(retornadado())); 
    x--;
    };
    return tabelas;
}
public boolean nextdado(){
    try{
        cursor.moveToNext();
        return true;

    }
    catch(Exception erro){
        return false;

    }
}
private Lista criarLista(String nome) {
    Lista tabelas = new Lista(nome);
    return tabelas;
}
public boolean buscardados(){
    try{
        cursor = Banco.query("tabela",
                new String [] {"tabelas",}
        , null, null, null, null, null);

        if (cursor.getCount() != 0){
            cursor.moveToFirst();

        }else{
            String sql = "INSERT INTO tabela (tabelas) " +
                      "values (Tabela1) ";
                Banco.execSQL(sql);
        }

        return true;
    }
    catch(Exception erro){
        Exibirmensagem("BANCO", "erro ao buscar no banco: "+ erro.getMessage(), "ok");
        return false;
    }
}
public String retornadado(){
    String dado = cursor.getString(cursor.getColumnIndex("tabelas"));
    return dado;
}
public void abrebanco(){
    try{
        Banco = openOrCreateDatabase("banco", MODE_WORLD_WRITEABLE, null);
        String sql ="CREATE TABLE IF NOT EXISTS tabela (ID INTEGER PRIMARY KEY" +
                ", tabelas TEXT)";
        Banco.execSQL(sql);

    }
    catch(Exception erro){
        Exibirmensagem("BANCO", "erro ao criar banco: =/"+ erro.getMessage(), "ok");
    }
}

public void Exibirmensagem (String titulo, 
        String texto, String button)
{
    AlertDialog.Builder mensagem = 
            new AlertDialog.Builder(exportararquivo.this);
    mensagem.setTitle(titulo);
    mensagem.setMessage(texto);
    mensagem.setNeutralButton(button,null);
    mensagem.show();
}  







    private void exportDB(String tabela){
        File sd = Environment.getExternalStorageDirectory();
            File data = Environment.getDataDirectory();
           FileChannel source=null;
           FileChannel destination=null;
           String currentDBPath = "/data/"+ "Star_Lighting_Division" +"/databases/"+tabela;
           String backupDBPath = tabela;
           File currentDB = new File(data, currentDBPath);
           File backupDB = new File(sd, backupDBPath);
           try {
                source = new FileInputStream(currentDB).getChannel();
                destination = new FileOutputStream(backupDB).getChannel();
                destination.transferFrom(source, 0, source.size());
                source.close();
                destination.close();
                Toast.makeText(this, "DB Exported!", Toast.LENGTH_LONG).show();
            } catch(IOException e) {
                e.printStackTrace();
            }
    }
}

The problem is that it doesn’t work and I can’t export anything...

was trying to use the google example http://developer.android.com/training/basics/data-storage/files.html#WriteExternalStorage

but I still know how to write a file, I can’t even create it

Answer 1

In this case we have to keep in mind that the file .csv has a simplified field division.

To work assembling a file like this, just separate the fields with a point and comma - ;.

Any file reader csv interprets by default the ; as field separator, in this case you just read the table and write it in a text file and each written field of each record you add a ;, after finishing the line (record) you are writing and moving the cursor, you add a line break in the file.

This also requires you to add the permission to write in external memory on Android.manifest of your App.

You will also have to use the class FileOutputStream and File;

    public void escrever(){
    File caminho = new File(Environment.getExternalStorageDirectory().getAbsolutePath()+"/arquivos");
    if (!caminho.exists())
        caminho.mkdir();

    File file = new File(caminho.getPath()+"/arquivo.csv");

    try {
        FileOutputStream out = new FileOutputStream(file);
        //aqui você pode adicionar o que desejar salvar usando estruturas de código e tudo mais
        String aux = "o que eu quero salvar;dividido por ponto e virgula\n"+
                    "e com quebra de texto";

        out.write(aux.getBytes(), 0, aux.getBytes().length);
        //esse método write deve ficar dentro da estrutura de repetição 

        //já essa parte de flush e close tem que ficar fora e 
        //deve ser executada apenas quando já tiver terminado de gerar todo o arquivo 
        out.flush();
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }       
}