Example: Setup System DSN Connection – O2001SSW to Excel 2007/Excel 2010

 

Step 1. Setting up the Excel spreadsheet

Create Excel workbook with column names in first row: Barcode,ScannerID,BarcodeType,ScannerTStamp,LogTime,BarcodeCount

Save workbook in folder: C:\opnlog\log.xlsx

image

 

Step 2. Setting up the spreadsheet as an ODBC Data Source

Spreadsheet can become accessible to O2001SSW, by making it an ODBC data object. Under Windows XP, go to the Control Panel, select Administrative Tools, and click on Data Sources (ODBC). 

If you are using other Windows platforms, you can usually find this item in the Control Panel or visit this link: http://technet.microsoft.com/en-us/library/ms188691.aspx 

 

IMPORTANT NOTE: if you are on a 64-bit machine, __do not__ use the ODBC Window that is accessible from the control panel. Instead, go to START > RUN > C:\Windows\SysWOW64\odbcad32.exe

 

Click System DSN tab.

image

Click the Add button to add the Microsoft Excel spreadsheet C:\opnlog\log.xlsx as an ODBC Data Source. 

In the Create New Data Source dialog box, click the driver:

Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb). Click Next.

Type OPN2EXCEL in Data Source Name.

Next, select the "Select Workbook" button, and select the Excel spreadsheet created earlier.  Also, make sure that the checkbox for "Read Only" is NOT checked in the Select Workbook menu.

image

 

Note: If you do not see Excel 12.0 Driver in list, you may download it from here:

http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

 

Step 3. O2001SSW ODBC setup

Close the ODBC Data Source Administrator and return to O2001SSW and click on ‘configure log to database’ button.

Enter following value in Connection Descriptor field:

Dsn=OPN2EXCEL;dbq=log.xlsx;defaultdir=C:\opnlog\;driverid=790;maxbuffersize=2048;pagetimeout=5

Enter SQL insert template in the Insert SQL field:

INSERT INTO [Sheet1$] (Barcode,ScannerID,BarcodeType,ScannerTStamp,LogTime,BarcodeCount) VALUES (?, ?, ?, ?, ?, ?)

Note: question marks are placeholders for format – field selection variables which will be replaced by actual values read from OPN2001 scanner when SQL insert statement is executed.

Enter exact number of format variables as number of question marks in Format Field Selection:

{Barcode}, {ScannerID}, {BarcodeType}, {ScannerRTC}, {SysTime}, {BarcodeCount}

imageImage: O2001SSW odbc to setup – save barcodes from OPN2001 memory to Excel sheet.

Click ‘Test Now’ button to verify setup.

Available values for format:

{Barcode}

Barcode stored in OPN-2001 memory

{ScannerID}

Unique scanner ID (set by manufacturer)

{BarcodeType}

Barcode type (EAN13, UPC, EAN128 …)

{ScannerRTC}

Scan time - Scanner real time clock (If enabled)

{SysTime}

System – computer time

{BarcodeCount}

Total number of barcodes in scanner memory

{SessionCounter}

Total number of barcodes read from scanner since program was started

Check http://www.connectionstrings.com/ for more connection string examples.