Export SQLite files to CSV

1

I need to give the user the option to export a desired database table to a file on the SD card so that it can become personal and can be imported into another device later.

I have an application where I work with multiple tables in the database, this application is to use along with the equipment where you can import things, there is a great chance of renting the equipment along with a tablet.

My balcony is for the guy to import his way of working on the equipment, he has his work table along with him and he imports the tablet to use with it.

My problem is to export only the desired table.

I do not find documentation explaining it, just some code samples that do not work, perhaps from the context.

I used these examples:

link

link

In the end it was very costly:

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 this does not work and I can not export anything ...

I was trying to use the google example link

But I do not know how to write a file, I can not even create it.

    
asked by anonymous 28.10.2014 / 17:57

1 answer

3

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

To work by putting together such a file, you just have to separate the fields with a semicolon - ; .

Any file reader csv interprets ; as the field separator by default, in that case you just read the table and write it to a text file and every 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.

For this you also need to add the permission to write to external memory in the 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();
    }       
}
    
13.10.2015 / 15:34