Comma Separated Values (CSV) files are a popular plain text format for exporting and importing data because of their simplicity and wide compatibility. However, they lack strict standardization or a built-in mechanism for data validation, which can lead to errors and inconsistencies.
They can't store complex data types and relationships between data or enforce data integrity rules. That’s why product and development teams prefer relational databases such as MySQL to handle large relational datasets while maintaining data integrity.
Importing CSV files into MySQL is common for developers and businesses handling large datasets. However, this process is largely developer-centric. Non-technical users often find it challenging to handle data imports—especially when import requirements vary consistently with sourced data.
A solution is to build custom data import systems, regardless of how long it takes to implement. Iterating on a custom solution based on varying data requirements can also be cumbersome and require constant developer support.
This guide breaks down the steps to import a CSV file into a MySQL database, followed by a simplified, user-friendly approach using Ingestro Importer. Let’s dive in.
Importing data from a CSV file into a MySQL database is a common practice for several reasons:
There are different ways of importing CSV files into MySQL, depending on the situation, feasibility, and desired user experience. Here are three common approaches:
The wizard will also provide options to fill in additional details like the line separator, field separator, encoding, etc. Here, you need to ensure that the value of the encoding field in the wizard matches the encoding of the CSV file.
A common challenge with these methods is the inability to provide non-technical users with an optimized process for managing complex data import scenarios—and that’s not the only hurdle.
Some key challenges of importing CSV files into a MySQL database include:
These complexities make manual CSV imports time-consuming and error-prone—particularly for large datasets.
To handle data imports as accurately as possible, you will have to cover all these scenarios and edge cases. Building an optimized user interface for non-technical users to handle imports multiplies the error probability.
What would a more efficient, scalable, and precise CSV import solution look like?
Ingestro Data Importer SDK enables development teams to implement user-friendly interfaces for non-technical users to map, clean, and validate file-based data quickly and efficiently—regardless of the input format. This guide walks you through using the Ingestro importer to migrate CSV data to MySQL.
Before getting into this tutorial, please make sure you have a basic understanding of the following tools and concepts:
Let’s get started by setting up a sample MySQL database.
If you’ve already set up a MySQL database, skip this step and create a backend server that interacts with the database.
Download and install the MySQL database on your system. For this guide, you can use the community edition, enterprise edition, or the cloud service for MySQL. Follow the instructions in the official documentation to install MySQL on your Windows, Mac, or Linux OS.
If you are using Ubuntu, you can install MySQL by typing this command in a terminal session; you can type the following in the command line even in Windows WSL:
To check if MySQL is installed correctly, you can check its version using the following command:
You can check if MySQL is running or not using the systemctl command with the status option:
To start running the MySQL service, type the following command in the terminal:
To check which port is used by MySQL on Linux, you can use:
The MySQL root user has all the privileges over all databases. You will use the mysql_secure_installation script, where you can not only define the password of the root user, but you can also add other configurations.In Ubuntu, run the following command to use the mysql_secure_installation script:
The installation will ask you to set the root user password and other configurations; you can choose the default options.
Now, create a new user for your database:
You will be asked for the MySQL root user password when you run the command. Then, you will be able to use MySQL commands.
To create a new user run the following statement:
Replace ‘user’ with the username of your choice. ‘localhost’ is the host where you will create the user. In ‘password’ enter the password for this new user.
The newly created user has no privileges on any database, and you can't access the MySQL shell either. Therefore, the root user has to grant certain privileges to this new user.
There are several privileges, and their assignment will depend on the server administrator. Here, you can grant CREATE privilege to create new tables or databases.
If you want to grant all permissions on all tables in a single call for the test database, the best option is ALL PRIVILEGES:
Now, you are ready to use your database. You can also use MySQL workbench to work with the database and tables.
Type the following command to list all existing databases:
Before you can start working with the database, create and use the database:
Now, you can create the database table. The basic MySQL syntax to create a table is the following:
Alternatively, if you want to import an SQL file, you can do so in the terminal:
To follow through the tutorial, you can download a sample database and import it into a database. Here’s a sample database you can work with. To load the data into the MySQL server, you can run:
Remember to specify your data path. The example above used ours.
Use the SHOW DATABASES command to list all databases in the current server:
The output will be a classicmodels database.
You can create a backend server using Node.js, Express, or any other language. Here, we will use Flask, a lightweight Python web framework, to create a backend server that handles database operations.
Start with installing the necessary Python packages in your project:
Create a file called app.py and set up your Flask server to connect to MySQL:
Test the connection to ensure it works. You can do so by querying the database for certain data. For example, you can ask to get the customers table as shown below. Add the following commands to the above code. This will return all users from the customer table.
You can run this backend server that listens to port 5000 for post requests by running this Python command (against the file):
Sign up on Ingestro's user platform and get your license key.
Set up a target data model for the target table of your MySQL Database. Click on New Model.
Create a Target Data Model (TDM) by choosing a name.
Use Simple mode or Ingestro AI to generate a TDM from a file.
Below is a sample CSV data you can use with the above database. You can manually introduce additional issues on this CSV file, such as encoding issues, adding more columns removing certain columns, or creating phone number validation issues to test Ingestro importer data cleaning and validation capability. The data already has some issues, which will be highlighted by Ingestro once you import it.
| Counterparty | IBAN Nr | Type of payment | Purpose | Amount | Currency | Transaction date | Verified date | First name | Last name | Merchant | Merchant category code | Currency exchange rate | Town | Country |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| XYZ Corporation | NL91ABNA0417164300 | Wire Transfer | Invoice Payment | 1500 | EUR | 1/15/2025 | 1/16/2025 | John | Doe | Amazon | 5411 | 1.1 | Amsterdam | Netherlands |
| ABC Ltd. | DE89370400440532013000 | Direct Debit | Subscription Fee | 299.99 | EUR | 2/1/2025 | 2/2/2025 | Jane | Smith | Netflix | 4899 | 1.12 | Berlin | Germany |
| ACME Inc. | GB33BUKB20201555555555 | Credit Card | Office Supplies | 675.75 | GBP | 2/5/2025 | 2/6/2025 | David | Johnson | Staples | 5311 | 1.3 | London | UK |
| DEF Enterprises | FR7630006000011234567890189 | PayPal | Online Purchase | 89.99 | EUR | 1/20/2025 | 1/21/2025 | Alice | Brown | eBay | 5311 | 1.08 | Paris | France |
| GHI Holdings | ES9121000418450200051332 | Cash Deposit | Savings Transfer | 5000 | EUR | 2/10/2025 | 2/11/2025 | Michael | White | Santander | 6021 | 1.11 | Madrid | Spain |
Save the above data into a CSV file and drop it into the TDM dropdown. This will automatically identify the header and map it to the target data model. You can define the Target data model in the UI or in the code. Additionally, you can apply required, unique, and regex validations, as well as specify the column type (e.g., float, int, date, currency, etc.). Many of these data types already have certain validation logic, reducing our data cleaning effort.
Now that you have a TDM defined, you can set up your frontend. You can choose from React, Angular, Vue.js, JavaScript, and Next.js. In the Ingestro dashboard, navigate to the Setup SDK tab and follow the setup instructions listed. Here, we will use React.js.
Copy the license key and select the TDM you created.
Ingestro provides a pre-configured IngestroImporter component in Step 4 of the setup instructions. We will use that shortly.
Now click on Test in Sandbox to get a starter code. Replace the NuvoImporter with the one from Step 4 in the dashboard. We will update the columns prop shortly with the right values.
You can run the page in the sandbox or download the dev box to run it locally.
Since React is a frontend library, it will interact with the MySQL database on the Python backend server. Now, you need to send the data from the frontend to your backend server. This article will use Axios to make HTTP requests from the React App.
Update the onResults prop calls the handleResults function which sends the data to your Flask backend using Axios. Install it in your project with:
Update the onResults prop calls the handleResults function which sends the data to your Flask backend using Axios. Install it in your project with:
Update the app to handle the importer. Also, remember to update the Ingestro license key and the backend URL to match yours.
The column section consists of the Target Data Model, which is the schema for the target table provided on the setup page. You can additionally apply required, unique, and regex validations. Furthermore, you can add your own data transformation formulas to adjust the input data to match your expected
output structure, this is particularly useful for data cleaning, styling, and white labeling. Here’s what you will use:
Your system is now ready to be tested. Assuming you used npm as a package manager, you can start a development server for your react app using this command:
Click on Import data to open the Ingestro importer.
Select the CSV file previously created by navigating to the local CSV file location. The Ingestro importer will automatically parse the imported CSV data in its user-friendly interface. Its algorithm powered by Contextual AI will automatically select the header and match the columns according to the provided TDM.
Ingestro
Importer can also scan your data for common validation issues. Review the entries and fix any errors in the row. Then click Complete Import.
Your data will be imported into the database.
In this guide, you’ve discovered a more efficient way to import CSV files into MySQL using Ingestro Data Importer SDK. Ingestro takes care of the data import workflow from start to finish, simplifying imports and reducing the risk of errors.
Powered by AI and an intuitive setup, Ingestro handles data mapping, validation, enrichment, and cleaning, so you can focus on what matters most. If you're ready to see how Ingestro can help you streamline data imports, book a call with our team, and we will happily support you every step of the way.
.png)
.png)
