Irakli Tchigladze
on 2022-04-28
In this article, we will demonstrate how to set up a spreadsheet importer for your users and customers, and connect it to your MySQL databases.
Once finished, data from user uploaded spreadsheets will be instantly inserted in your MySQL database.
In order to insert the data from user uploaded files in your database, create an APISpreadsheets account. You will need a Business plan to use this feature. However, you can email us at sales@apispreadsheets.com to request a 7 day free trial!
Once you log into the dashboard, follow three easy steps:
Go to the ‘Import’ page. Then simply click the ‘Create a Customized Importer’ button.
This will set up a spreadsheet importer with basic functionality and a shareable link. Users can go to this URL to upload spreadsheets.
Connect your database to API Spreadsheets by providing the necessary details of the database such as the name, username, host/IP address, etc.
You may need to explicitly allow APISpreadsheets IP address (34.194.225.150) to connect with your database. You can 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 MySQL toggle, and click ‘Add New Database’ button
This will open a window, asking you to enter your database information and credentials.
If you’re connecting a MySQL database, make sure to select this type.
Database Name, Hostname and Port fields are required. Refer to this guide to better understand these values and where to get them.
If the database requires authentication, you must enter Username and Password.
Enter a quick description of the database in the last field.
*Enter hostname value (the URL or IP Address of your database) without HTTP or ending slash.
Once you fill out all the fields, click the ‘Connect’ button.
If the database connection is successful, you will be redirected to the database settings.
If you go back to spreadsheet importer settings, now you’ll be able to select the recently connected database to store spreadsheet data.
Select the database to see the list of tables.
Turn on the toggle next to one of the tables to store spreadsheet data.
Click on the table name to see detailed information about its fields. As you can see, the customers table has two columns: id and email.
In the spreadsheet importer settings we need to align spreadsheet columns with fields in the database.
For this guide, let’s imagine we have a spreadsheet with two columns: Name and User Email.
In this case, we need to tell APISpreadsheets to take values from the ‘User Email’ column and store them in the email field of the customers table in the database.
Open the Columns section to map spreadsheet columns with fields in the table.
In the ‘Display Name’ field, enter a descriptive label for the column. This can help users understand the type, format, and purpose of anticipated input data.
In the ‘Internal Name’ field, enter the exact name of the field in the database table.
Once you’re done mapping the column, make sure to save it.
Once the column mapping is set up, the file upload process will be a little different.
First, users have to select the row that contains column names. This is usually the first row.
Then users will have to select the source of the data that should be inserted into the database.
In this example, the uploaded excel spreadsheet contains two columns. We map the ‘User Email’ column with the respective field in the database.
Once finished, click the green ‘Save Column Mapping’ button.
From now on, data from uploaded spreadsheets will be inserted in the connected MySQL database table.
