Datamapper - Query CSV

OL Connect Professional DataMapper
1

I have a datamapper utilizing a PDF as the data. Each page of the PDF represents a line in a CSV file.

PDF page 1 = CSV line 1
PDF page 9 = CSV line 9

I would like to add an extraction in the datamapper that will pull the correct line from the CSV file, but I am not sure how to build that script out.

I did look at:

but this seems to want to look at specific headers and values and I want to pull in the row of the csv that is equal to record.index.

2

You’ll need to follow that article’s instructions on creating an ODBC connection. Once that’s done, add an Extraction step in your config with the following code:

var myCSVFile = "Basic.csv"

var myCSV = db.connect('jdbc:odbc:CSV');
var rows = myCSV.createStatement().executeQuery('SELECT * FROM "' + myCSVFile + '"');
var columnCount = rows.getMetaData().getColumnCount();

// Skip to the proper line in the CSV, based on current Record index
var page = 0;
while(page<record.index && rows.next()){
	page++;
};

// Grab all values generically
var oneRecord = '';
for (var i = 1; i <= columnCount; i++ ) {
	oneRecord += rows.getString(i);
	oneRecord += (i<columnCount) ? ',' : '';
}
oneRecord;

Change the name of the CSV file on the first line of the script.

Here’s what the code does:

  • It opens the file and generates a record set that includes all columns in the CSV.
  • It then skips over each CSV line until the line number patches the current PDF page number (which is controlled via record.index, since each PDF page is a record).
  • It iterates through all columns and grabs their values using their column number instead of the column name, making the code work generically for any CSV
  • It builds a comma-separated list of all those values, and that’s what the Extraction step stores in the data.
3

This works great!!! Thank you so much.

My only issue is that it is skipping the first line in the CSV?

4

That’s because your ODBC setting believes the first line of your CSV contains the column headers. So you have two options to fix this:

  1. Actually add the column headers as the first line in your CSV
  2. Change the ODBC definition: go back to the ODBC64 settings, click on Configure, then on Options and then on Define Format. Select your sample data file and then click Guess. This will instruct the ODBC drivers to use the specified names as column headers, causing the entire CSV to be read properly, including the first line:
    image
    image1151×849 38.7 KB
5

PERFECT! I didn’t realizes that was an option in the ODBC connection. Thank you again.