Extract data from databases Leave feedback

GroupDocs.Parser provides the functionality to extract data from databases via JDBC. This feature allows you to parse database tables and extract their content as text, similar to how you would extract data from document files.

The Parser class provides constructors specifically for database extraction:

// Constructors with java.sql.Connection object
Parser(java.sql.Connection connection);
Parser(java.sql.Connection connection, ParserSettings parserSettings);

// Constructor with JDBC connection string
Parser(String filePath, LoadOptions loadOptions);

• getToc returns Iterable<TocItem> where each TocItem represents a database table: getText returns the table name and getPageIndex returns the table index
• getText(int pageIndex) returns TextReader containing the table data for the specified table index; getText() returns the data of all tables
• isText and isToc methods of Features class indicate if text and table extraction are supported

GroupDocs.Parser works with any database that has a JDBC driver. Add the driver to the classpath of your application. Common drivers include:

• SQLite - org.xerial:sqlite-jdbc
• SQL Server - com.microsoft.sqlserver:mssql-jdbc
• MySQL - com.mysql:mysql-connector-j
• PostgreSQL - org.postgresql:postgresql
• Oracle - com.oracle.database.jdbc:ojdbc11

• Database extraction is primarily for text-based table content
• Complex relationships between tables are not automatically resolved
• The list of tables is obtained from DatabaseMetaData.getTables with no type filter, so it contains every object the JDBC driver reports (depending on the driver, views and system tables as well)

To create an instance of Parser class to extract data from a database with java.sql.Connection object the following constructors are used:

// Basic constructor
Parser(java.sql.Connection connection);

// With parser settings (for logging, etc.)
Parser(java.sql.Connection connection, ParserSettings parserSettings);

The second constructor allows you to use ParserSettings object to control the process; for example, by adding logging functionality.

Note

The connection is owned by your code: GroupDocs.Parser doesn’t close it when the Parser object is closed. Close the connection yourself, for example with try-with-resources.

Here are the steps to extract data from a database:

• Prepare java.sql.Connection object;
• Instantiate Parser object with connection object;
• Call isText method to check if text extraction is supported;
• Call isToc method to check if table of contents extraction is supported;
• Call getToc method and obtain collection of tables;
• Iterate through the collection and get a text from tables.

The following example shows how to extract data from a SQLite database:

// Create java.sql.Connection object
try (java.sql.Connection connection = java.sql.DriverManager.getConnection(String.format("jdbc:sqlite:%s", Constants.SampleDatabase))) {
    // Create an instance of Parser class to extract tables from the database
    try (Parser parser = new Parser(connection)) {
        // Check if text extraction is supported
        if (!parser.getFeatures().isText()) {
            System.out.println("Text extraction isn't supported.");
            return;
        }
        // Check if toc extraction is supported
        if (!parser.getFeatures().isToc()) {
            System.out.println("Toc extraction isn't supported.");
            return;
        }
        // Get a list of tables
        Iterable<TocItem> toc = parser.getToc();
        // Iterate over tables
        for (TocItem i : toc) {
            // Print the table name
            System.out.println(i.getText());
            // Extract a table content as a text
            try (TextReader reader = parser.getText(i.getPageIndex())) {
                System.out.println(reader.readToEnd());
            }
        }
    }
}

String connectionString = "jdbc:sqlserver://localhost:1433;databaseName=MyDatabase;integratedSecurity=true;";

try (java.sql.Connection connection = java.sql.DriverManager.getConnection(connectionString)) {
    try (Parser parser = new Parser(connection)) {
        if (parser.getFeatures().isText() && parser.getFeatures().isToc()) {
            for (TocItem table : parser.getToc()) {
                System.out.println("Extracting from table: " + table.getText());
                try (TextReader reader = parser.getText(table.getPageIndex())) {
                    if (reader != null) {
                        System.out.println(reader.readToEnd());
                    }
                }
            }
        }
    }
}

To create an instance of Parser class to extract data from a database with a JDBC connection string the following constructor is used:

Parser(String filePath, LoadOptions loadOptions);

• filePath: The JDBC connection string (not a file path); it is passed to java.sql.DriverManager.getConnection
• loadOptions: Must be initialized with FileFormat.Database

In this case GroupDocs.Parser opens the connections itself and closes them: the connection used to list the tables is closed right after that, and the connection used to read the table data is closed when the TextReader object is closed.

The list of tables is represented as table of contents. Each table can be extracted using the getText(int) method where the integer parameter is the table index from the table of contents.

Here are the steps to extract data from Sqlite database:

• Prepare connection string;
• Instantiate Parser object with connection string;
• Call isText method to check if text extraction is supported;
• Call isToc method to check if table of contents extraction is supported;
• Call getToc method and obtain collection of tables;
• Iterate through the collection and get a text from tables.

The following example shows how to extract data from Sqlite database:

String connectionString = String.format("jdbc:sqlite:%s", Constants.SampleDatabase);
// Create an instance of Parser class to extract tables from the database
// As filePath connection parameters are passed; LoadOptions is set to Database file format
try (Parser parser = new Parser(connectionString, new LoadOptions(FileFormat.Database))) {
    // Check if text extraction is supported
    if (!parser.getFeatures().isText()) {
        System.out.println("Text extraction isn't supported.");
        return;
    }
    // Check if toc extraction is supported
    if (!parser.getFeatures().isToc()) {
        System.out.println("Toc extraction isn't supported.");
        return;
    }
    // Get a list of tables
    Iterable<TocItem> toc = parser.getToc();
    // Iterate over tables
    for (TocItem i : toc) {
        // Print the table name
        System.out.println(i.getText());
        // Extract a table content as a text
        try (TextReader reader = parser.getText(i.getPageIndex())) {
            System.out.println(reader.readToEnd());
        }
    }
}

You may easily run the code above and see the feature in action in our GitHub examples:

• GroupDocs.Parser for .NET examples
• GroupDocs.Parser for Java examples

Along with full featured Java library we provide simple, but powerful free Apps.

You are welcome to extract data from PDF, DOC, DOCX, PPT, PPTX, XLS, XLSX, Emails and more with our free online Free Online Document Parser App.