CSV Import

You can import CSV files using the CSVManager project. This project is a MySQL database and some JAR files. Use this project to parse a CSV file, extract column headers, and use managed data to create a new target table and import the data. 

  1. Unzip the CSVManager.zip file into a new directory.
  2. Copy the JAR files from /lib directory to the following directory:
    • (For Tomcat) The %TOMCAT_HOME%/lib directory.
    • (For the single-user version based on Jetty) The %JETTY_HOME%/APICreator/lib/ext directory.
  3. Import the JavaScript library adminJS (the build/csvproject/src directory).
  4. Import the PROJECT CSVManager.json from the /build/csvproject/build/src directory.
  5. Deploy the MySQL script to a MySQL database named csvparser (/build/csvproject/src/csvparser.sql).
  6. In API Creator, go to Integrate, Data Sources, Connection tab, modify the Database URL field to use the new database parameters (for example, jdbc:mysql://<serername>:3306/csvparser), and save your changes.
  7. Click Reload Schema.
    On the Integrate, Schema, Tables tab, five tables display.
  8. Add a managed data server and a new data source. The following image shows the option to create a new database on the Integrate, Data Sources, Connection tab:

    For more information about how to set up a managed database, including adding a managed data server connection, see Managed Server Administration.
  9. Copy the value of the prefix field for the managed database. The following image shows this field:
  10. Go to the Integrate, Data Sources, Connection tab and verify that the Schema is editable field is selected.
  11. Launch Data Explorer.
  12. Select the table csvdefinition and insert a new 'csvdefinition.' This includes the name of the table, the delimiter (comma, pipe, or tab), and if the first row has a header value.
  13. Paste the name of the prefix from the managed database.
  14. Go to your definition and under the child tab 'csvheader', enter the file location or upload the CSV content, and then save your changes. If you use S3, you can modify the rule to loadFileFromS3(bucketName, keyName). The following image shows the Filename and Gen Table Flag fields:
  15. Select the Gen Table flag and save your changes.
    The processing starts by firing a rule on csvheader (use the debugger if you want to see how it works).
  16. Reload Data Explorer to see your new table and content.

How the CSVManager Project Works

  • csvdefinition. Holds the name of the target table, the URL from the managed server, and some CSV setup information.
  • csvheader. Handles the source file (either an S3 location, file system, or upload file content).
  • genTableFlag. After you set this to true, calls the rule that processes the import (creates the table and columns based on the column header and writes to the csvcolumnmap).
The following image shows the schema: