Home | Pulling Non-Printable Characters from a Database into GridField – CODESOFT Instructions
Print or Email this article

Pulling Non-Printable Characters from a Database into GridField – CODESOFT Instructions

Summary

This Instruction Article explains how to use formula in conjunction with GridField in CODESOFT with non-printable GS1 codes.

Preparation

You should have some familiarity with database integration and GridField in CODESOFT and an understanding of GS1 standards.

Instructions

GridField Concept

The function of GridField is to take a large set of organized data from a particular data source (database or table lookup) and populate it on a label template. If the data for the GridField is pull directly from a database, the procedure is fairly straight forward and this article does not cover it.

Pulling Data from a Database

There will be times where you need to manipulate the data from the database before using it in GridField. An example is, if the data from the database contain unprintable characters, those characters will be substituted with a hex readable format ‘_xNNNN_’ where NNNN is the hex equivalence of the character. In order to properly use the data, the hex readable data needed to be converted back to the non-printable characters. (Note: the non-printable characters are very common in 2D barcodes).

GridField Setup

One thing to keep in mind. When CODESOFT retrieves data from a  database or table lookup, it comes in a form of one giant data stream. This is why you have to designate Field separator and Record separator strings in the database or table lookup properties. CODESOFT uses these two strings to parse the data stream into multiple fields and records.  We tend to think that CODESOFT reads the data one record at a time. That is incorrect. The interface that CODESOFT uses for Table lookup pulls all the data into one long string and then parse it into multiple records.

Example

You want to read data from an Excel sheet to be used in a DataMatrix (2D) barcode. The data in Excel contains Record Separator [RS], Group Separator [GS] and End of Transmission [EOT] values used by the GS1 standards.

  • [RS]   = ASCII 30  HEX = 1E
  • [GS]   = ASCII 29  HEX = 1D
  • [EOT] = ASCII 4   HEX = 04

In Excel, you may have the field defined as

=”[)>”&CHAR(30)& “06” & CHAR(29)& “Q”& CHAR(29)& “4L”&CHAR(4) 

  • CHAR(30) represents the [RS] record separator value
  • CHAR(29) represents the [GS] group separator value
  • CHAR(4) represents the [EOT] end of transmission value

In the cell, it shows [(>06Q4L. Since Char(30), Char(29) and Char(4) are not printable, you do not see them.  However, when CODESOFT pulls this data into the label, the data comes in as

[[)>_x001E_06_x001D_Q_x001D_4L_x0004_

The non-printable values are unfortunately converted to Hex digit representations. If this data is fed directly into a DataMatrix, the barcode will show all the hex representations…not the [RS], [GS], and [EOT] values.

To fix this, we need to convert the Hex digit representations back to a format what will allow CODESOFT to insert them correctly as not printable ASCII. We can use the ReplaceString function to substitute _x001E_ with char(30), _x001D_ with char(29) and _x0004_ with char(04). 

Method #1 – Nested Function

FixString – ReplaceString(ReplaceString(ReplaceString (INPUT1,”_x001E_”,char(30)),”_x001D_”,char(29)),_”x0004_”,char(4))

Note easy to read, is it? In the GridField, you will use FixString as the data

Grid Properties with Replace

Method #2 – Multiple Function

Replace30 – ReplaceString(INPUT1, “_x001E”,char(30))

Replace29 – ReplaceString(Replace30, “_x001D”,char(29))

Replace4 – ReplaceString(Replace29, “_x001D”,char(4))

Much easier to read and follow

In the GridField, you will use Replace4 as data

Author

Dave Klement

Was this article helpful?

Related Articles