Import txt file to database

[Waldek]

Hi,

I have looking example how to import ascii file via jdbc-sqlite to database which contain data in 2 columns

--- example file ----

1   34.5
2   125.0
3   950.0
....
-------------------
I have find example with jdbc-mysql which use LOAD DATA INFILE

            query = "LOAD DATA INFILE '"+filename+"' INTO TABLE example (col1,col2);";

but how to do with jdbc-sqlite?

Regards
Waldek

[SAADA]

Hi,

I'd to deal with the same issue a couple of years ago.
This fact is (was??) that the ASCII file ingestion is not a part of SQL but a inner feature of SQLlite.
I made a workaround by hacking the SQLite source code.
The 'LOAD DATA' SQLite function actually reads the input file line by line and uses a prepared statement executed in batch mode in batch mode to ingest them.
I wrote a similar (^C^V) algorithm in Java. The pseudo SQL statement 'LOAD DATA ' is trapped by a wrapper switching on that Java code instead of using the SQLiteJDBC code.
That works fine and fast, but that requires a wrapper.
Below is the core of the code doing this task.

Hoping the could help, best regards
LM
<code>
        /**
         * @param connection
         * @param tableName
         * @param tableFile
         * @throws Exception
         */
        public void storeTable(Connection connection, String tableName, int ncols, String tableFile) throws Exception {
                int nb_col=0;
                if( ncols == -1 ) {
                        DatabaseMetaData meta = connection.getMetaData();
                        ResultSet rsColumns = meta.getColumns(null, null, tableName.toLowerCase(), null);
                        /*
                         * Only TYPE_FORWARD supported: must read all columns to get the size
                         */
                        while( rsColumns.next() ) nb_col++;
                        rsColumns.close();
                }
                /*
                 * If the table is temporary, we must use the given column number (JDBC XERIAL weakness?)
                 */
                else {
                        nb_col = ncols;
                }
                String ps = "insert into " + tableName.toLowerCase() + "  values (";
                /*
                 * Build the prepared statement
                 */
                for( int i=0 ; i< nb_col ; i++ ) {
                        if( i > 0 )  ps += ",";
                        ps += "?";
                }
                ps += ")";
                PreparedStatement prep = connection.prepareStatement(ps);
                /*
                 * Maps file row in the prepared segment
                 */
                BufferedReader br = new BufferedReader(new FileReader(tableFile));
                String str = "";
                int line = 0;
                while( (str = br.readLine()) != null ) {
                        line++;
                        String fs[] = str.split("\\t");
                        if( fs.length != nb_col ) {
                                QueryException.throwNewException(SaadaException.FILE_FORMAT, "Error at line " + line + " number of values (" + fs.length + ") does not match the number of columns (" +  nb_col + ")");
                        }
                        for( int i=0 ; i< nb_col; i++ ) {
                                if( "null".equals(fs[i]) )
                                        prep.setObject(i+1, null);
                                else
                                        prep.setObject(i+1, fs[i]);
                        }
                        prep.addBatch();
                        if( (line%5000) == 0  )  {
                                if (Messenger.debug_mode)
                                        Messenger.printMsg(Messenger.DEBUG, "Store 5000 lines into the DB");
                                prep.executeBatch();
                        }
                }
                /*
                 * Load data
                 */
                if (Messenger.debug_mode)
                        Messenger.printMsg(Messenger.DEBUG, line + " lines stored");
                prep.executeBatch();
                prep.close();
                (new File(tableFile)).delete();
        }
<code>

[Waldek]

Hi,

Thank you very much for useful example. After many tests this methods import file via jdbc-sqlite is ok for small files. When I have import data
from file 10 Mb size this way, it was not useful, very long time import and for this reason I have switch my application to use H2 database which have
internal methods CSVREAD  which working fast.

Regards

Waldek

[Peter Borissow]

Have you played with SQLiteConfig.setSynchronous()? I have found this to dramatically improve write performance:

    org.sqlite.SQLiteConfig config = new org.sqlite.SQLiteConfig();
    config.setSynchronous(SynchronousMode.OFF);

Peter

---

From: Waldek <sp2...@gmail.com>
To: xer...@googlegroups.com
Sent: Saturday, March 2, 2013 11:55 AM
Subject: [xerial 661] Re: Import txt file to database

[Peter Borissow]

Also, when doing bulk inserts with SQLite, you can get a significant performance boost by using SQL Begin/End statements. Example:

CREATE TABLE "COMMON_NAMES"  (
          "NAME" VARCHAR(15) NOT NULL,
          "GENDER" CHAR(1)
     );

BEGIN;

INSERT INTO "COMMON_NAMES" ("NAME", "GENDER") VALUES('JAMES','M');
INSERT INTO "COMMON_NAMES" ("NAME", "GENDER") VALUES('JOHN','M');
INSERT INTO "COMMON_NAMES" ("NAME", "GENDER") VALUES('ROBERT','M');

...

END;