The Stamps.com PC software gives you the ability to connect to an external data source, such as a Microsoft Access database, SQL database, or Excel file through ODBC (Open Database Connectivity). The ODBC connection can be used to both import data into the Stamps.com software and to export data from Stamps.com to an external data source.
This article will connect the Stamps.com PC software to a SQL database as an example. However, you can follow the steps in this article to connect to many different data sources such as MySQL, Access, Excel, and so on. This article covers:
-
Creating an ODBC profile
-
Selecting your ODBC data source
-
Adding an ODBC data source in the Windows ODBC Manager when needed
-
Mapping the fields in your data source to the Stamps.com PC software
-
Import Field Mapping
-
Post Back Data Source Settings
-
-
Be sure that you have the details specific to your data source available. These details may include:
-
The name of the data source
-
The username and password used to connect to the data source
-
-
This article assumes that you have a working knowledge of ODBC and database management. If you are unfamiliar with ODBC, reach out to your IT manager for assistance. Our support team is also here to assist you.
-
If you intend to use custom queries to import and postback data, follow the steps in this article to set up your ODBC data source first. Then, see our ODBC: Custom Queries help article.
ODBC with Stamps.com 64-bit PC Software
We recommend running the latest 64-bit version of the Stamps.com PC software when connecting to an ODBC data source. If you are currently running the 32-bit version and wish to update to the 64-bit version and are connecting to a Microsoft Access database or Excel file as your data source you may need to install the correct ODBC drivers. Visit the Microsoft Access Database Engine 2016 Redistributable help article for more information.
-
Go to ORDERS. Then, select Manage Data Sources and select Add Source.
-
Select ODBC from the list. Then, click Next.
-
Enter a name for the connection into the Profile Name field. Then, click Next.
The name you enter identifies this order source in the Stamps.com software.
The Set ODBC Data Source screen will open.
-
Select from the following two options. Then, click Next.
-
Select an existing ODBC data source: Select this option when your data source driver has already been added to the Microsoft ODBC Data Source Administrator.
-
This article assumes you will be selecting an existing ODBC data source. Continue with the next step.
-
-
Create a new data source: Select this option when you need to add your ODBC driver to the Microsoft ODBC Data Source Administrator.
-
Select this option to open the ODBC Data Source Administrator where you can add your data source. See our Add a New ODBC Data Source help article for assistance with this process. Then, return here and continue with the next step.
-
-
-
Select your data source from the ODBC Data Source drop-down menu. Then, enter the Username and Password used to connect to the data source. Click Next.
You are now connected to your data source. The Import Field Mapping screen will open.
The Import Field Mappings screen allows you to map the data from your source file to the available fields in the PC software. Data fields to be mapped are broken down into the following types:
-
Order: Order-specific data such as Order ID, Order Date, etc.
-
Items: Item data specific to the items on the orders such as SKU, Item Name, Quantity, Unit Price, etc.
-
Shipping Options: Data specific to the shipment such as Ship Method, Weight, Dimensions, etc.
-
Recipient: Data specific to the recipient of the order such as Name, Address, Phone number, etc.
-
Return Address: Data specific to the return address for the shipment.
Mapping the return address fields will overwrite the Return Address entered in the Profile Settings for this profile with the return address information from your data source. This is useful when you have more than one return address.
Required Fields
Any field indicated with <required> requires mapping. There are many available fields to import, but only a few are required. The required fields are:
-
Order > Order ID
-
The Order ID can be numeric or alphanumeric
-
-
Order > Order Date
-
The date-time format must have values for year, month, day, hour, minute, and second - though they don't have to be in that specific order. Examples: YYYY/MM/DD HH:MM:SS, MM/DD/YYYY HH:MM:SS, etc.
-
-
Order > Status
-
At least one status in your source file must be mapped to an available status in the PC software. See the Specific Field Mapping and Unit Selection section for more details.
-
To map your fields:
Reference our Import Field Mapping Table for detailed information for each field.
-
Select the table in your source file that contains your order information from the Data Source drop-down menu.
-
Map the fields in your data source to the available fields in the PC software by selecting the field in your data source from the available drop-down menus.
We recommend mapping the fields on the Order tab first, then continue mapping the Items, Shipping Options, Recipient, and Return Address tabs.
Auto Match
The Auto Match feature attempts to map each field in your source file to the corresponding PC software field. The matching is based on the field name. Additionally, you can map these fields manually if you prefer or if Auto Match doesn't map the fields properly.
-
Some fields, such as the Status field on the Order tab, require the field values in your source file to be mapped to the available field values in the PC software. Other fields, such as the Unit Weight field on the Items tab, need to have a value selected.
Follow the steps in the Specific Field Mapping and Unit Selection section below for detailed steps.
-
The first time you import orders from your data source, the Stamps.com software will retrieve orders up to two days old. Then, on subsequent imports, orders up to seven days old will be retrieved.
Click the Next button to configure Post Back Options once you have completed all of your field mappings.
Map the values in your source file to the PC software:
For this example, we will map the values for the Status field on the Order tab.
-
Click the Status button on the Order tab.
-
Enter the status value from your source file into the New Code field.
-
Select the PC software value (Printed, Not Printed, or Cancelled) that your status maps to from the Maps To drop-down menu.
-
Click the Add button to create the status code mapping.
-
Repeat steps 1 - 4 for any additional status code maps you wish to create. Then, click Save.
-
Select the field in your source file that contains the status codes from the Status drop-down.
Select a value for a field mapping:
For this example, we will select a value for the Unit Weight field on the Items tab.
The Post Back Data Source screen allows you to select and configure how you wish for shipment information, such as the tracking number, to be posted. There are three options:
-
Same data source: Post shipping information back to your data source
-
Another data source: Post back to a different data source
-
Do not post back data
To post back to the same source:
-
Select Same data source from the Post Back Data Source options. Then, click Next.
-
Map the PC software fields you wish to post back by selecting the fields in your data source from the available drop-down menus. Then, click Next.
The Order ID field is required. Reference our Post Back Field Mapping Table for detailed information on each field.
-
Click Done on the Profile Options screen.
The available Profile Options are outside the scope of this article. Learn more about the available options on the Profile Options screen.
The PC software is now configured to import orders and to post shipment information back to the same source.
As an example, this section will show you how to configure the Stamps.com software to post shipment information back to a CSV file rather than to your original SQL database. This can be useful when you need the shipping details exported to a format that can be used to run reports against or to import into another system.
You also have the option to post shipment information back to an Excel file, to Microsoft Access, or to a different SQL database. Select the correct option in step 3, and the wizard will guide you through the rest of the process.
-
Select Another data source from the Post Back Data Source options. Then, click Next.
-
Select the option to Create a new data source. Then, click Next.
-
Select Text/CSV File and click Next.
-
Select the folder that your source file is in by clicking the Browse button. Go to the folder in the Windows navigator, select the folder, and click OK. Then, click the Next button.
The path to the folder you selected will be displayed in the Location: field.
-
Click the Test Connection button to make sure that the PC software can connect to the folder you selected.
We recommend not modifying the Connection String. However, advanced users can update the string if needed.
A success message will be displayed. Click OK. Then, click Next.
-
Select your source file from the Data Table: drop-down menu.
-
Map the PC software fields you wish to post back by selecting the fields in your data source from the available drop-down menus. Then, click Next.
The Order ID field is required. Reference our Post Back Field Mapping Table for detailed information on each field.
-
Click Done on the Profile Options screen.
The available Profile Options are outside the scope of this article. Learn more about the available options on the Profile Options screen.
The PC software is now configured to import orders from your source file and to post shipment information back to a different file.
-
Select Do not post back data from the Post Back Data Source options. Then, click Next.
-
Click Done on the Profile Options screen.
The available Profile Options are outside the scope of this article. Learn more about the available options on the Profile Options screen.
The PC software is now configured to import orders from your source file.