Examples with Csv org.h2.tools.Csv used on opensource projects

Example 6 with Csv

use of org.h2.tools.Csv in project h2database by h2database.

the class TestCsv method testWriteResultSetDataType.

private void testWriteResultSetDataType() throws Exception {
    // Oracle: ResultSet.getString on a date or time column returns a
    // strange result (2009-6-30.16.17. 21. 996802000 according to a
    // customer)
    StringWriter writer = new StringWriter();
    Connection conn = getConnection("csv");
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("select timestamp '-100-01-01 12:00:00.0' ts, null n");
    Csv csv = new Csv();
    csv.setFieldDelimiter((char) 0);
    csv.setLineSeparator(";");
    csv.write(writer, rs);
    conn.close();
    // getTimestamp().getString() needs to be used (not for H2, but for
    // Oracle)
    assertEquals("TS,N;0101-01-01 12:00:00.0,;", writer.toString());
}

Example 7 with Csv

use of org.h2.tools.Csv in project h2database by h2database.

the class TestCsv method testPseudoBom.

private void testPseudoBom() throws Exception {
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    // UTF-8 "BOM" / marker
    out.write(StringUtils.convertHexToBytes("ef" + "bb" + "bf"));
    out.write("\"ID\", \"NAME\"\n1, Hello".getBytes(StandardCharsets.UTF_8));
    byte[] buff = out.toByteArray();
    Reader r = new InputStreamReader(new ByteArrayInputStream(buff), StandardCharsets.UTF_8);
    ResultSet rs = new Csv().read(r, null);
    assertEquals("ID", rs.getMetaData().getColumnLabel(1));
    assertEquals("NAME", rs.getMetaData().getColumnLabel(2));
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
}

Example 8 with Csv

use of org.h2.tools.Csv in project h2database by h2database.

the class TestCsv method testNull.

/**
 * Test custom NULL string.
 */
private void testNull() throws Exception {
    deleteDb("csv");
    String fileName = getBaseDir() + "/testNull.csv";
    FileUtils.delete(fileName);
    OutputStream out = FileUtils.newOutputStream(fileName, false);
    String csvContent = "\"A\",\"B\",\"C\",\"D\"\n\\N,\"\",\"\\N\",";
    byte[] b = csvContent.getBytes(StandardCharsets.UTF_8);
    out.write(b, 0, b.length);
    out.close();
    Csv csv = new Csv();
    csv.setNullString("\\N");
    ResultSet rs = csv.read(fileName, null, "UTF8");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals(4, meta.getColumnCount());
    assertEquals("A", meta.getColumnLabel(1));
    assertEquals("B", meta.getColumnLabel(2));
    assertEquals("C", meta.getColumnLabel(3));
    assertEquals("D", meta.getColumnLabel(4));
    assertTrue(rs.next());
    assertEquals(null, rs.getString(1));
    assertEquals("", rs.getString(2));
    // null is never quoted
    assertEquals("\\N", rs.getString(3));
    // an empty string is always parsed as null
    assertEquals(null, rs.getString(4));
    assertFalse(rs.next());
    Connection conn = getConnection("csv");
    Statement stat = conn.createStatement();
    stat.execute("call csvwrite('" + fileName + "', 'select NULL as a, '''' as b, ''\\N'' as c, NULL as d', " + "'UTF8', ',', '\"', NULL, '\\N', '\n')");
    InputStreamReader reader = new InputStreamReader(FileUtils.newInputStream(fileName));
    // on read, an empty string is treated like null,
    // but on write a null is always written with the nullString
    String data = IOUtils.readStringAndClose(reader, -1);
    assertEquals(csvContent + "\\N", data.trim());
    conn.close();
    FileUtils.delete(fileName);
}

Example 9 with Csv

use of org.h2.tools.Csv in project h2database by h2database.

the class TestCsv method testRead.

private void testRead() throws Exception {
    String fileName = getBaseDir() + "/test.csv";
    FileUtils.delete(fileName);
    OutputStream out = FileUtils.newOutputStream(fileName, false);
    byte[] b = ("a,b,c,d\n201,-2,0,18\n, \"abc\"\"\" ," + ",\"\"\n 1 ,2 , 3, 4 \n5, 6, 7, 8").getBytes();
    out.write(b, 0, b.length);
    out.close();
    ResultSet rs = new Csv().read(fileName, null, "UTF8");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals(4, meta.getColumnCount());
    assertEquals("A", meta.getColumnLabel(1));
    assertEquals("B", meta.getColumnLabel(2));
    assertEquals("C", meta.getColumnLabel(3));
    assertEquals("D", meta.getColumnLabel(4));
    assertTrue(rs.next());
    assertEquals("201", rs.getString(1));
    assertEquals("-2", rs.getString(2));
    assertEquals("0", rs.getString(3));
    assertEquals("18", rs.getString(4));
    assertTrue(rs.next());
    assertEquals(null, rs.getString(1));
    assertEquals("abc\"", rs.getString(2));
    assertEquals(null, rs.getString(3));
    assertEquals("", rs.getString(4));
    assertTrue(rs.next());
    assertEquals("1", rs.getString(1));
    assertEquals("2", rs.getString(2));
    assertEquals("3", rs.getString(3));
    assertEquals("4", rs.getString(4));
    assertTrue(rs.next());
    assertEquals("5", rs.getString(1));
    assertEquals("6", rs.getString(2));
    assertEquals("7", rs.getString(3));
    assertEquals("8", rs.getString(4));
    assertFalse(rs.next());
    // a,b,c,d
    // 201,-2,0,18
    // 201,2,0,18
    // 201,2,0,18
    // 201,2,0,18
    // 201,2,0,18
    // 201,2,0,18
    FileUtils.delete(fileName);
}

Example 10 with Csv

use of org.h2.tools.Csv in project h2database by h2database.

the class TestCsv method testWriteRead.

private void testWriteRead() throws SQLException {
    deleteDb("csv");
    Connection conn = getConnection("csv");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID IDENTITY, NAME VARCHAR)");
    // int len = 100000;
    int len = 100;
    for (int i = 0; i < len; i++) {
        stat.execute("INSERT INTO TEST(NAME) VALUES('Ruebezahl')");
    }
    long time;
    time = System.nanoTime();
    new Csv().write(conn, getBaseDir() + "/testRW.csv", "SELECT X ID, 'Ruebezahl' NAME FROM SYSTEM_RANGE(1, " + len + ")", "UTF8");
    trace("write: " + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - time));
    ResultSet rs;
    time = System.nanoTime();
    for (int i = 0; i < 30; i++) {
        rs = new Csv().read(getBaseDir() + "/testRW.csv", null, "UTF8");
        while (rs.next()) {
        // ignore
        }
    }
    trace("read: " + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - time));
    rs = new Csv().read(getBaseDir() + "/testRW.csv", null, "UTF8");
    // stat.execute("CREATE ALIAS CSVREAD FOR \"org.h2.tools.Csv.read\"");
    ResultSetMetaData meta = rs.getMetaData();
    assertEquals(2, meta.getColumnCount());
    for (int i = 0; i < len; i++) {
        rs.next();
        assertEquals("" + (i + 1), rs.getString("ID"));
        assertEquals("Ruebezahl", rs.getString("NAME"));
    }
    assertFalse(rs.next());
    rs.close();
    conn.close();
    FileUtils.delete(getBaseDir() + "/testRW.csv");
}