How to export sqlite to excel

Question

I'm having a database called :"Money" and a Table and I want to export it to excel (.csv).But i don't know how to export it ,please help me :(

Here is "comsumer" 's stucture :

public static final String DATABASE_KhoanChi = "CREATE TABLE "
            + KhoanChiTable + "(" + colkcID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + mathloaichi
            + " TEXT, " + colsotienkc + " Integer, " + colngaychi
            + " Date NOT NULL,FOREIGN KEY (" + mathloaichi + ") REFERENCES "
            + TheloaichiTable + " (" + coltlcID + "));";

Use CSV as the export format. Read each row of your table, jopin the columns using a semicolon (`;`) and append the line to a text file. EASY. — Phantômaxx, Jul 18 '16 at 12:03
The problem with the current answer is, that CSV fails badly with l10n und i18n messing up dates and decimal places in nearly every locale except en_US. — max, Sep 08 '19 at 18:41

score 3 · Answer 1 · answered Jul 18 '16 at 12:06

3

You can use Sqlite Browser for the same.

Download from the link http://sqlitebrowser.org/

Now open the sqlite file in the Sqlite browser.

Now you can export database to excel as shown in below image

answered Jul 18 '16 at 12:06

Rahul Vats

277
3
17

But i want to use code to export it .Thanks for your answer – Lá Dối Trá Jul 18 '16 at 12:07
The you can use this link I think it will help you: http://stackoverflow.com/questions/21448001/how-to-implement-export-sqlite-to-excel-csv-file-in-android – Rahul Vats Jul 18 '16 at 12:09
is it support utf-8 ? – m.sajjad.s Mar 02 '21 at 12:05

score 0 · Answer 2 · edited Jun 13 '17 at 08:56

This has been discussed too many times on SO. A simple search would have given you the full solution.

As discussed here, you just need to iterate through the values using a cursor and write comma separated values to a file. If you have more than one row, just iterate through them, build new dataString and perform out.write(dataString).

String columnString =   "\"Col1\",\"Col2\",\"Col3\",\"Col4\"";
String dataString   =   "\"" + var1 +"\",\"" + var2 + "\",\"" + var3 + "\",\"" + var4+ "\"";
String combinedString       =   columnString + "\n" + dataString;

File file   = null;
File root   = Environment.getExternalStorageDirectory();
if (root.canWrite()) {
    File dir    =   new File (root.getAbsolutePath() + "/PersonData");
    dir.mkdirs();
    file   =   new File(dir, "Data.csv");
    FileOutputStream out   =   null;
    try {
        out = new FileOutputStream(file);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    try {
        out.write(combinedString.getBytes());
    } catch (IOException e) {
        e.printStackTrace();
    }
    try {
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Also if you are writing to external storage, don't forget the permission in the manifest.

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"></uses-permission>

score 0 · Answer 3 · answered Jul 25 '20 at 18:33

You can use the following python program to do so:

import os, fnmatch
import sqlite3
import pandas as pd

#creates a directory without throwing an error
def create_dir(dir):
  if not os.path.exists(dir):
    os.makedirs(dir)
    print("Created Directory : ", dir)
  else:
    print("Directory already existed : ", dir)
  return dir

#finds files in a directory corresponding to a regex query
def find(pattern, path):
    result = []
    for root, dirs, files in os.walk(path):
        for name in files:
            if fnmatch.fnmatch(name, pattern):
                result.append(os.path.join(root, name))
    return result



#convert sqlite databases(.db,.sqlite) to pandas dataframe(excel with each table as a different sheet or individual csv sheets)
def save_db(dbpath=None,excel_path=None,csv_path=None,extension="*.sqlite",csvs=True,excels=True):
    if (excels==False and csvs==False):
      print("Atleast one of the parameters need to be true: csvs or excels")
      return -1

    #little code to find files by extension
    if dbpath==None:
      files=find(extension,os.getcwd())
      if len(files)>1:
        print("Multiple files found! Selecting the first one found!")
        print("To locate your file, set dbpath=<yourpath>")
      dbpath = find(extension,os.getcwd())[0] if dbpath==None else dbpath
      print("Reading database file from location :",dbpath)

    #path handling

    external_folder,base_name=os.path.split(os.path.abspath(dbpath))
    file_name=os.path.splitext(base_name)[0] #firstname without .
    exten=os.path.splitext(base_name)[-1]   #.file_extension

    internal_folder="Saved_Dataframes_"+file_name
    main_path=os.path.join(external_folder,internal_folder)
    create_dir(main_path)


    excel_path=os.path.join(main_path,"Excel_Multiple_Sheets.xlsx") if excel_path==None else excel_path
    csv_path=main_path if csv_path==None else csv_path

    db = sqlite3.connect(dbpath)
    cursor = db.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    print(len(tables),"Tables found :")

    if excels==True:
      #for writing to excel(xlsx) we will be needing this!
      try:
        import XlsxWriter
      except ModuleNotFoundError:
        !pip install XlsxWriter

    if (excels==True and csvs==True):
      writer = pd.ExcelWriter(excel_path, engine='xlsxwriter')
      i=0
      for table_name in tables:
          table_name = table_name[0]
          table = pd.read_sql_query("SELECT * from %s" % table_name, db)
          i+=1
          print("Parsing Excel Sheet ",i," : ",table_name)
          table.to_excel(writer, sheet_name=table_name, index=False)
          print("Parsing CSV File ",i," : ",table_name)
          table.to_csv(os.path.join(csv_path,table_name + '.csv'), index_label='index')

      writer.save()


    elif excels==True:
      writer = pd.ExcelWriter(excel_path, engine='xlsxwriter')
      i=0
      for table_name in tables:
          table_name = table_name[0]
          table = pd.read_sql_query("SELECT * from %s" % table_name, db)
          i+=1
          print("Parsing Excel Sheet ",i," : ",table_name)
          table.to_excel(writer, sheet_name=table_name, index=False)

      writer.save()

    elif csvs==True:
      i=0
      for table_name in tables:
          table_name = table_name[0]
          table = pd.read_sql_query("SELECT * from %s" % table_name, db)
          i+=1
          print("Parsing CSV File ",i," : ",table_name)
          table.to_csv(os.path.join(csv_path,table_name + '.csv'), index_label='index')
    cursor.close()
    db.close()
    return 0
save_db();

How to export sqlite to excel

3 Answers3

Linked