Irakli Tchigladze
on 2022-05-30
In this guide, we’ll show you how to configure a spreadsheet importer to collect user-uploaded data in a PostgreSQL database.
Sign up for an APISpreadsheets account to create a shareable spreadsheet importer. You’ll need a Business plan subscription to store user-uploaded data in a PostgreSQL database.
Reach out on sales@apispreadsheets.com to try out this feature for free. Our team will arrange for you a free 7-day trial period of a Business plan subscription.
Log in to your account to access APISpreadsheets dashboard.
Then follow these steps:
Go to https://www.apispreadsheets.com/import by clicking ‘Import’ in the main menu.
On this page, create a basic spreadsheet importer by clicking the ‘Create a Customized Importer’ button.
Importers will have a link to the landing page. Share it with your customers, so they can go to the URL and upload spreadsheets.
To connect your PostgreSQL database with APISpreadsheets, you must provide necessary credentials, such as the name, host/IP address, username and password of the database.
You may need to explicitly allow APISpreadsheets IP address (34.194.225.150) to connect with your database. Learn more about setting up a database connection in this guide.
Once you have all your database information, go to the spreadsheet importer settings and open the ‘Database’ tab of the ‘Destinations’ section.
Turn on the PostgreSQL toggle, and click ‘Add New Database’ button
Clicking this button will open a prompt.
First, select the type of database.
Enter database credentials in respective fields. You may need to read this guide to better understand database credentials and where to get them.
If your database is accessible without Username and Password, you can leave these fields empty.
Short description of your database can help you remember its purpose. This is especially important if you’re going to connect many databases.
Note: Enter hostname without http prefix, or slashes at the beginning or the end.
Once you enter the credentials, click the button to finish connecting the database.
If the connection is successful, APISpreadsheets will take you to the database settings page.
Open the ‘Destinations’ section and ‘Database’ tab of spreadsheet importer settings, you’ll see that you can select the newly added Postgres database as a destination.
Select the checkbox next to the database name. This will open a list of tables in that database. Toggle the switch for the table where you want to insert data.
Clicking on each table will show you information about its schema - fields, types of values, and so on.
In this case, the customers table is going to store data from spreadsheets, so we toggle it. It has four columns: - pk, f_name, l_name, sign_up_dt.
Pay attention to field names, because you’ll need to reference them later, when connecting spreadsheet columns with table fields.
Once you’ve selected the database and the table, don’t forget to click the ‘Save Destinations’ button, located on the right side of the page.
Next, you need to define the relationship between spreadsheet columns and fields in the database.
In other words, which spreadsheet columns hold values that should be inserted into a specific field of the database.
To keep things simple, let’s say an excel spreadsheet contains these columns: First Name and Last Name.
We need to specify that values in the ‘First Name’ column should be inserted into the f_name field of the customers table.
Values from the ‘Last Name’ column should be stored in the l_name field of the table.
To do this, we need to open the Columns section of spreadsheet importer settings.
The ‘Display Name’ field should contain the descriptive label to help your customers map their spreadsheet data with one of the fields in the database.
The ‘Internal Name’ field should contain the field name from the database table.
In this case, the internal name field should contain f_name, because we want to store 'First Name' values in that field.
APISpreadsheets gives you the option to have identical values in both fields. f_name is not a descriptive label, so it's better to set the value of the ‘Display Name’ field to help customers map spreadsheet columns with database fields.
Once you make the changes, don’t forget to save them.
Next time a customer uploads a spreadsheet, they’ll have to go through two additional steps.
For starters, users will have to specify which row contains column names. In most cases, it is the first row.
Then they will have to select which spreadsheet columns contain data that should be inserted into specific fields in the table.
Customers need to specify that the ‘First Name’ column contains values for the ‘f_name’ field in the database. Follow the same principle for ‘Last Name’ and ‘l_name’ fields.
Confirm your selection by clicking the ‘Save Column Mapping’ button.
Going forward, data from uploaded files will be inserted into the connected database.
