Skip to main content

Import from a database

Import product data from a database in Productsup.

You can request product data directly from a database. This lets you skip creating a feed file from the database for import.

Productsup provides a selection of data sources for several types of databases:

  • MySQL

  • PostgreSQL

  • Microsoft SQL Server

  • Other SQL databases based on Open Database Connectivity (ODBC)

Note

Productsup can add needed drivers and database imports on request.

Import from MySQL database

If you work with a vast infrastructure and keep data in MySQL database, use data source MySQL Import to get your data into Productsup. This data source connects directly to your database. You can activate the proxy feature only to allow traffic from the Productsup proxy servers.

To import from MySQL database:

  1. Go to Data Sources from your site's main menu and select ADD DATA SOURCE.

  2. Search for MySQL Import and select Add.

    MySQL Import

  3. Give your data source a custom name. This custom name replaces the name of the data source on the Data Sources Overview page. Then, select Continue.

  4. Add your hostname in Hostname to connect to your database.

  5. In Port, add the port numbers you assigned to the database.

  6. Add your credentials in the Username and Password fields to connect to the database.

  7. Enter the database name in Database.

  8. In Query, enter a query to select the relevant data. To import your product information from a database, you must create SQL queries to databases. See Write SQL database queries

  9. In Charset, select your database charset from the drop-down menu or leave the default value to let the platform choose the charset.

  10. Select Save.

    Set up MySQL

Import from PostgreSQL database

You can import data from PostgreSQL database using a SQL query. Productsup supports PostgreSQL UNICODE or PostgreSQL ANSI ODBC drivers.

Note

Don't execute queries from an online transactional database. Instead do it from a database warehouse database or backup. Ensure the user assigned for the Productsup integration only has read access to the necessary tables to complete the query.

To import from PostgreSQL database:

  1. Go to Data Sources from your site's main menu and select ADD DATA SOURCE.

  2. Search for PostgreSQL and select Add.

    PostgreSQL import

  3. Give your data source a custom name. This custom name replaces the name of the data source on the Data Sources Overview page. Then, select Continue.

  4. In Driver, choose the relevant driver. Select PostgreSQL UNICODE or Postgre ANSI from the drop-down menu.

  5. Add your hostname in Hostname to connect to your database.

  6. In Port, add the port numbers you assigned to the database.

  7. Enter the database name in Database.

  8. Add your credentials in the Username and Password fields to connect to the database.

  9. In Query, enter a query to select the relevant data. To import your product information from a database, you must create SQL queries to databases. See Write SQL database queries

  10. Select Save.

    Set up PostgreSQL

Import from Microsoft SQL Server database

You can import data from Microsoft SQL Server database using a SQL query. Productsup supports ODBC Driver 17 for SQL Server.

Note

Don't execute queries from an online transactional database. Instead do it from a database warehouse database or backup. Ensure the user assigned for the Productsup integration only has read access to the necessary tables to complete the query.

To import from MS SQL Server database:

  1. Go to Data Sources from your site's main menu and select ADD DATA SOURCE.

  2. Search for Microsoft SQL Server Import and select Add.

    Microsoft SQL Server Import

  3. Give your data source a custom name. This custom name replaces the name of the data source on the Data Sources Overview page. Then, select Continue.

  4. Add your hostname in Hostname to connect to your database.

  5. In Port, add the port numbers you assigned to the database.

  6. Enter the database name in Database Name.

  7. Add your credentials in the Username and Password fields to connect to the database.

  8. In Query, enter a query to select the relevant data. To import your product information from a database, you must create SQL queries to databases. See Write SQL database queries

  9. Select Save.

    Set up Microsoft SQL Server

Import from an ODBC database

You can import your data from any other SQL database based on Open Database Connectivity (ODBC). Productsup supports PostgreSQL UNICODE, PostgreSQL ANSI ODBC, and ODBC Driver 17 for SQL Server drivers.

Note

If you need support for other drivers, contact support@productsup.com.

Note

Don't execute queries from an online transactional database. Instead do it from a database warehouse database or backup. Ensure the user assigned for the Productsup integration only has read access to the necessary tables to complete the query.

  1. Go to Data Sources from your site's main menu and select ADD DATA SOURCE.

  2. Search for ODBC database Import and select Add.

    ODBC Import

  3. Give your data source a custom name. This custom name replaces the name of the data source on the Data Sources Overview page. Then, select Continue.

  4. Add your credentials in the Username and Password fields to connect to the database.

  5. Enter the database name in Connection String.

    • Ask you database administrator to provide this name.

    • Add the name in the format such as Driver={ODBC Driver 17 for SQL Server};Server=mssql.123testing.com;Database=tempdb;.

  6. In Query, enter a query to select the relevant data. To import your product information from a database, you must create SQL queries to databases. See Write SQL database queries

  7. Select Save.

    Set up ODBC database

Write SQL database queries

This section gives some helpful tips for writing SQL database queries in Productsup. SQL queries help you access product data from databases. You need to enter your queries into the Query field of the configured data source.

For example, you have two tables containing different data on some products:

Table 1. product_data

id

product_id

title

type

1

222

book

direct

2

333

table

dropship

3

444

lamp

dropship



Table 2. product_pricing

id

product_id

price

1

222

9.99

2

333

69.99

3

444

49.99



You can merge the tables and extract Productsup product data by writing a query:

  1. Select needed attributes using the select command followed by a list of product attribute names. You can also define the table you receive the data from using the from command. For example, to show that the product_id and title attributes come from the product_data table, write the following query:

    SELECT product_data.product_id,
       product_data.title
FROM product_data

  2. Add information from another table with the join command. Use the on command to specify the id column of each table. The query looks for the matches to the IDs between the tables. The data of the first table merges with the data of the second in case of a match. For example, the product_pricing merges with the product_data table. In this example, the on command specifies that the ID attribute for both tables is product_id.

    JOIN product_pricing 
ON product_data.product_id = product_pricing.product_id

  3. Specify conditions to fulfill for exporting data using the where command to define how the class type equals dropship in the product_data column.

    WHERE product_data.type = 'dropship'

Note

Each SQL query should include a select and from command. Use the join and where commands as necessary.

For this example, the final query may look as follows:

SELECT product_data.product_id,
       product_data.title,
       product_pricing.price
FROM product_data
JOIN product_pricing ON product_data.product_id = product_pricing.product_id
WHERE product_data.type = 'dropship'

The result table after merging is as follows:

product_id

title

price

333

table

69.99

444

lamp

49.99

Tip

See W3 Schools SQL Tutorial, to learn more about SQL.

Contact support@productsup.com if you have any questions.

In this section: