How to create a h2 database from some fields of a CSV file

You can add the column definitions in the CREATE TABLE as documented and combine this with a WHERE clause. Please note using CREATE TABLE AS SELECT is a bit faster than separate CREATE TABLE and INSERT INTO statements (not sure if speed is very important for you):

CREATE TABLE WORLDCITIES(  COUNTRY VARCHAR(3),   ACCENTCITY VARCHAR(40),   POPULATION FLOAT,   LATITUDE FLOAT,   LONGITUDE FLOAT)AS SELECT   COUNTRY,   ACCENTCITY,   POPULATION,   LATITUDE,   LONGITUDEFROM CSVREAD('snippet.csv') WHERE POPULATION IS NOT NULL;

finally, i proceeded like this as you adviced.I only put the parts of the code which i think is more related to the question :)

`

private final String createTableString = ""    + "CREATE TABLE IF NOT EXISTS " + _tableName    + " ("    + "id INT UNSIGNED NOT NULL AUTO_INCREMENT, "    + "country VARCHAR(3) NOT NULL, "    + "city VARCHAR(40) NOT NULL, "    + "region VARCHAR (5) NOT NULL, "    + "population FLOAT NOT NULL, "    + "latitude FLOAT NOT NULL, "    + "longitude FLOAT NOT NULL, "    + "PRIMARY KEY(id)"    + " );";private final String insertString = ""    + "INSERT INTO " + _tableName    + " (country, city, region, population, latitude, longitude) "    + "VALUES (?,?,?,?,?,?)"    + ";";public void go() throws IOException, SQLException {    loadDriver();    Connection conn = null;    Properties connectionProps = new Properties();    connectionProps.put("user", "");    connectionProps.put("password", "");    String connectionURL = _protocol + _subprotocol + _dbName + _dbSettings;    ResultSet rs = null;    try {        conn = DriverManager.getConnection(connectionURL, connectionProps);        logger.info("Connected to {} database.", _dbName);        conn.setAutoCommit(false);        Savepoint savept1 = conn.setSavepoint();        Statement stmt = conn.createStatement();        try {            stmt.execute(createTableString);            logger.info("The table '{}' created successfully", _tableName);        } catch (SQLException sqle) {            logger.error("Error while creating the table '{}'", _tableName);            printSQLException(sqle);        }        PreparedStatement pstmt = conn.prepareStatement(insertString);        _allStatements.add(pstmt);        /* rs:                           pstmt:         * 1 -> COUNTRY                           * 2 -> CITY                     1 -> COUNTRY         * 3 -> ACCENTCITY               2 -> CITY         * 4 -> REGION                   3 -> REGION         * 5 -> POPULATION               4 -> POPULATION         * 6 -> LATITUDE                 5 -> LATITUDE         * 7 -> LONGITUDE                6 -> LONGITUDE         */        rs = Csv.getInstance().read(_csvFileName, null, _csvCharset);        int rowCount = 0;        while (rs.next()) {            if (rs.getFloat(5) != 0) { // If population is not null.                pstmt.setString(1, rs.getString(1)); // country                pstmt.setString(2, rs.getString(3)); // city (accentcity in reality)                pstmt.setString(3, rs.getString(4)); // region                pstmt.setFloat(4, rs.getFloat(5));   // population                pstmt.setFloat(5, rs.getFloat(6));   // latitude                pstmt.setFloat(6, rs.getFloat(7));   // longitude                pstmt.addBatch();                rowCount ++;            }        }        int[] rowsUpdated = pstmt.executeBatch();        for (int i=0; i<rowsUpdated.length; i++) {            if (rowsUpdated[i] == -2) {                logger.error("Execution {}: unknown number of rows inserted.", i);                logger.error("Rolling back ...");                conn.rollback(savept1);            } else {                logger.trace("Successful: execution {}, {} rows updated !", i, rowsUpdated[i]);            }        }        conn.commit();    }    finally { // release all open resources to avoid unnecessary memory usage.

....`

Thanks!

Use the read() method of the H2 class Csv, and iterate through the ResultSet, inserting the desired rows as you find them.