One of the most common formats to record and store data is CSV(Comma Separated Values) and Excel files. As the data in these CSV files grows it becomes important to store and maintain this data in a scalable Database Management System like MySQL. This allows organizations to effectively manage and analyze their data from a central repository. To load data from file to table in MySQL, you can either opt for writing SQL statements or use the GUI(Graphical User Interface) of MySQL Workbench, phpMyAdmin, or other data integration tools. Show
In this article, you will learn how to effectively load data from file to table in MySQL using 3 different methods. Table of Contents
What is MySQL?Image SourceMySQL is a popular Open-Source Relational Database Management System. MySQL data is stored in tables consisting of rows and columns. Introduced in 1995, MySQL is completely developed, distributed, and maintained by Oracle Corporation. Looking over the technical side, MySQL is written in the C and C ++ languages. MySQL is compatible with multiple platforms such as Microsoft Windows, Oracle Solaris, AIX, Symbian, Linux, and macOS. You can use SQL commands to retrieve, change or add data from your MySQL tables. MySQL is also a vital component of the Modern LAMP stack, which consists of a Linux-based Operating System, Apache Web Server, MySQL Database, and PHP for processing. SQL can also be used to define the table schema or even configure the relationship between the tables in the database. Client-Side Graphical User Interfaces such as MySQL WorkBench, SequelPro, or DBVisualizer can be used to type the SQL commands and the server will respond with the requested information. MySQL also offers a paid Enterprise version which includes premium support services and several extensions that can be installed as Server Plugins. Key Features of MySQLMySQL is widely used by organizations like Facebook, Flickr, Twitter, Wikipedia, and YouTube. This is due to the following set of MySQL’s eye-catching features:
Understanding the Statements to Load Data from file to table in MySQLOnce you have designed your MySQL Table, you can start to Load Data from file to table in MySQL using the following statements:
LOAD DATA StatementBy using the LOAD DATA statement, you can read rows from a text file into a table very quickly. You can read this file from the server host or the client host, based on whether the LOCAL modifier is provided. The LOCAL modifier also influences operations such as data interpretation and error handling. Just like the SELECT … INTO OUTFILE statement is used to write data from a table to a file, the LOAD DATA statement reads the file back into the table. You can also use the mysqlimport utility that sends a LOAD DATA statement to the server to load data from file to table in MySQL. To understand the application of the Load Data statement, consider a table containing the following Product details:
To populate your empty MySQL Table, you can first create a text file containing a row for each of your products, then load data from file to table in MySQL with a single statement. For that, you have to create a text file product.txt containing one record per line, with all the values separated by tabs, and written in the order in which the columns were provided in your initial CREATE TABLE statement. For cases where the values are missing such as Category for Spoons or the Data of Expiry, you can use NULL values. You can use N (backslash, capital-N) to demonstrate these values. For instance, the record for your product Spoons would appear as shown below(the whitespaces represent a single tab):
If you are an Apple user with MacOS, you would likely want to use LINES TERMINATED BY ‘r’. MySQL also provides complete flexibility by allowing you to mention the column value separator and end of line marker explicitly in the LOAD DATA statement. However, the default values i.e. tab and linefeed are sufficient for the statement to read the file product.txt easily. To understand the LOAD DATA statement in detail, you can go through the following aspects:
Index HandlingYou can ignore foreign key constraints during the data loading process by issuing the SET Foreign_Key_Checks = 0 statement before running the LOAD DATA command. Whenever you run the LOAD DATA statement for a completely empty MyISAM table, then all non-unique indexes are created in a separate batch. This usually makes the LOAD DATA command much faster when there are many indexes. For some scenarios, you can also create an index even faster by disabling the index using the ALTER TABLE … DISABLE KEYS statement before starting the data loading process into the table and rebuilding the index with ALTER TABLE … ENABLE KEYS command after loading the file. Duplicate-Key and Error HandlingTo effectively handle & control cases where the new rows you want to input have the same unique key values(PRIMARY KEY or UNIQUE index values) as the existing table rows, you can use the following modifiers:
For cases when none of REPLACE, IGNORE, or LOCAL is specified, an error will occur if duplicate key values are detected and the rest of the text file is ignored. Apart from duplicate keys, IGNORE and LOCAL also affect error handling as described below:
Input File LocationThe location of the LOAD DATA input file can be identified by the following LOCAL modifier rules:
If you are using the LOCAL modifier, the client program reads the file and transfers its contents to the server. Now, the server will make a copy of the file in the directory that contains the temporary files. Note that the LOAD DATA LOCAL statement can fail if there is not enough space for a copy of this directory. The non-local rule means that the server reads the file named ./product.txt associated with the data directory and the file named product.txt from the database directory of the default database. For instance, if you run the following LOAD DATA statement when FirrstDB is the default database, the server will read the test.txt file from the firstdb database directory, even if the statement explicitly loads the file into a table in the seconddb database.
Security RequirementsFor Non-Local load operations, the server reads text files present on the server host and must meet the following security requirements:
While you are performing the LOCAL load operation, the client program reads the text file present on the client host. The contents of the file are sent by the client over the connection to the server, so letting the server access the file directly may be a faster method than using LOCAL. However, with LOCAL you don’t need the FILE permissions. The file can be placed in any directory accessible by the client program. Local vs Non-Local OperationWhen you start applying the LOCAL modifier for the LOAD DATA operations, you will notice the following changes compared to non-LOCAL operations:
It is to be noted that the LOCAL works only if both the server and the client are configured to allow it. For instance, if you start mysqld with the local_infile system variable disabled, LOCAL will generate an error. INSERT StatementApart from the LOAD DATA statement to load data from file to table in MySQL, you can use the INSERT statement to insert new rows into an existing table. You can use the INSERT statement to load data from file to table in MySQL in the following forms:
The INSERT statement is the simplest method to insert new single rows one at a time by providing values for each column, in the order in which the columns were listed in the CREATE TABLE statement. For instance, for adding a new product to your product table you can use the following command:
As shown above, you can directly use NULL instead of the N used in LOAD DATA statements. However, when you have multiple entries to load data from file to table in MySQL, you have to execute several INSERT statements as compared to a single LOAD DATA command. Generally, when you want to load data from a text file, it is suggested to use the LOAD DATA command as it is 20 times faster than the INSERT command. It is recommended to use the INSERT statements with multiple VALUES lists to insert several rows at a time when you need to insert several rows from the same client. While using the INSERT command you can also configure the bulk_insert_buffer_size variable for faster data insertion, especially when you want to add data to a non-empty table. MySQL is a popular Open-Source Relational Database Management system that allows you to effectively store and manage data using simple SQL commands or via its simple user interface MySQL Workbench. To load data from file to table in MySQL, check out the following three methods: Method 1: Using Hevo Data to Load Data from File to Table in MySQL Hevo Data, an Automated Data Pipeline, provides you a hassle-free solution to easily load data from 100+ data sources like FTP/SFTP & Google Sheets to your desired destination such as MySQL, Data Warehouses, or BI tools in a completely hassle-free & automated manner. Using Hevo you can easily upload files in formats such as CSV, JSON, and XML to your MySQL Database within minutes with an easy-to-use no-code interface. Hevo is fully managed and completely automates the process of not only loading data from CSV files but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Get Started with Hevo for Free Method 2: Using Command-Line and MySQL Workbench to Load Data from File to Table in MySQL This method would be time-consuming and somewhat tedious to implement. Users will have to write custom SQL statements or use the MySQL Workbench user interface to load data from file to table in MySQL. This method is suitable for users with a technical background. Method 3: Using phpMyAdmin to Load Data from File to Table in MySQL This method would require you to install the phpMyAdmin database administration tool to load data from file to table in MySQL. This tool is a good option if you require to perform data transfer once in a while with little to no data transformation. Understanding Data Loading in MySQL MethodsTo seamlessly load data from file to table in MySQL, you can go through the following methods:
Method 1: Using Hevo Data to Load Data from File to Table in MySQLImage SourceHevo Data is a No-code Data Pipeline solution that can help you move data from 100+ data sources like FTP/SFTP & Google Sheets to your desired destination such as MySQL, Data Warehouses, or BI tools in a completely hassle-free & automated manner. Using Hevo you can easily upload files in formats such as CSV, JSON, and XML to your MySQL Database. Hevo also supports MySQL as a Source for loading data to a destination of your choice. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss. Hevo Data takes care of all your data preprocessing needs and lets you focus on key business activities and draw a more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. Take a look at some of the salient features of Hevo:
Sign up here for a 14-Day Free Trial! To effortlessly load data from file to table in MySQL using Hevo, follow the simple steps given below:
Method 2: Using Command-Line and MySQL Workbench to Load Data from File to Table in MySQLUsing simple SQL commands you can load data from file to table in MySQL. To achieve this follow the steps given below via the Command-Line and MySQL Workbench:
Step 1: Create TableTo load data from file to table in MySQL, you have to first create a table in your database if there is no existing table. Consider creating a table named items containing item_id, item_name, cost, & manufacturing_date. Using the CREATE TABLE statement, you can easily design your new table in your MySQL Database:
Step 2: Importing Data into your TableAssume you have an items.csv file containing the following item details with the first line being the column headings:
Using the following LOAD DATA INFILE commands, you can load your c:tmpitems.csv file into
The above statement used to load data from file to table in MySQL has the following commands:
Step 3: Transforming Data while ImportingWhen you want to load data from file to table in MySQL, you will often notice that the format of the incoming data doesn’t accurately match the target table. For instance, the manufacturing date format in the items.csv file is “yyyy/mm/dd” and the target table format is “mm/dd/yyyy”. Using the SET clause in the LOAD DATA INFILE statement, you can transform the CSV file date format to the one present in the destination table in MySQL. You can use the SET clause & the str_to_date() function while performing the operation to load data from file to table in MySQL.
Step 4: Importing File from Client to a remote MySQL Database ServerUsing the LOCAL modifier with the LOAD DATA INFILE statement, you can import file from the client (local computer) to a remote MySQL database server. Here, the client program reads the file present on the client and sends it to the desired remote MySQL Database Server. The file is generally imported to the database server operating system’s temporary folder. For instance, C:windowstemp on Windows or /tmp on Linux. Note that this folder is not configurable or determined by MySQL.
While using LOCAL to load data from file to table in MySQL, you don’t need to have the FILE privilege to import the file. However, it has some security issues that you should be aware of to avoid potential security risks. Also, if you are trying to load a big CSV file using the LOAD DATA LOCAL INFILE statement, then it will be a slower process as it takes relatively more time to transfer the file to the database server. Step 5: Importing CSV Files using MySQL WorkbenchAn alternative approach to load data from file to table in MySQL is via MySQL Workbench. To get started with MySQL Workbench to load data from file to table in MySQL, follow these simple steps:
Method 3: Using phpMyAdmin to Load Data from File to Table in MySQLAnother method to load data from file to table in MySQL is using an open-source tool like phpMyAdmin. It is commonly used to administer MySQL & MariaDB databases and can also be employed to load data from file to table in MySQL. By default, it allows data loading for a maximum size of 2 MB CSV files. However, it also allows you to configure the settings and increase the limit for importing large CSV files. To easily load data from file to table in MySQL using phpMyAdmin, follow these simple steps:
After you have successfully completed the process to load data from file to table in MySQL, phpMyAdmin will display a confirmation message. Image SourceConclusionIn this article, you have learned how to effectively load data from file to table in MySQL using 3 different methods. You can use the Command line in MySQL to load from file to table in MySQL via SQL statements such as LOAD DATA and INSERT. For a more user-friendly interface, you can use the MySQL Workbench GUI to import your CSV files. You can also employ an open-source database administration tool like phpMyAdmin to load data from file to table in MySQL. All these manual methods are a good choice if you are rarely required to load data from file to table in MySQL with little to no data transformation. However, for frequently performing complex transformations & loading large-sized CSV files in real-time you can opt for a No-Code Automated Data Integration platform like Hevo Data! Visit our Website to Explore Hevo Hevo Data, a No-code Data Pipeline can seamlessly load data from a vast sea of 100+ sources such as FTP/SFTP & Google Sheets to a desired destination such as MySQL, Data Warehouses, or BI Tools. Hevo also supports MySQL as a Source to load data to your Data Warehouse or a destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!
If you are using MySQL as your Database Management System and searching for a no-fuss alternative to manual data integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources and BI tools(Including 40+ Free Sources like Google Sheets), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy. Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs. Tell us about your experience of completing the process to load data from file to table in MySQL! Share your thoughts with us in the comments section below. How do I import a CSV file into MySQL?In the Format list, select CSV. Changing format-specific options. If the csv file is delimited by a character other than a comma or if there are other specifications to the csv files, we can change it in this portion. Click Go to start importing the csv file and the data will be successfully imported into MySQL.
How do I import a CSV file into database?Choose the data source (CSV) that you wish to convert. Choose input options to ensure the SQL queries are properly created.. Choose output options to format your data.. Copy the SQL queries, and run them in your database.. Create a New Workspace.. Import your CSV file.. How do I import data into MySQL?Importing a database from a file
To import a file, open Workbench and click on + next to the MySQL connections option. Fill in the fields with the connection information. Once connected to the database go to Data Import/Restore. Choose the option Import from Self-Contained File and select the file.
How do I import a CSV file into an existing table in SQL server?Here's how to import CSV to MSSQL table using SSMS.. From the Object Explorer, Expand the Databases Folder. ... . Select the Target Database. ... . Select a Flat File Source. ... . Specify the CSV File. ... . Configure the Columns. ... . Choose the Destination (SQL Server) ... . Specify the Database Table and Check Column Mappings.. |