Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
A CSV file is a plain text file that contains a list of data and allows us to save it in a tabular manner. It is mostly used to transfer data across applications. Comma-separated values are another name for it. It is commonly used with the comma character to separate data, but other characters, such as semicolons, can also be used.
When we need to export complex data from one program to a CSV file and then import the file data into another application, this file comes in handy. The CSV file can be opened in a spreadsheet tool such as Microsoft Excel or Google Spreadsheets. The CSV file can be imported into a database or table using MySQL's functionality.
Now, let’s understand the CSV file in a better way.
About CSV File
The structure of a CSV file is quite straightforward. Consider the following scenario: You have a few contacts in a contact manager that you want to export as a CSV file. You'd get a text file that looked like this:
Name, Email, Phone Number, Address
Divyansh Jain, djain@gmail.com, 222648188, G-3, 5 South Mada St, Nungambakkam Chennai, Tamil Nadu.
Aditya Singh, aditya838@gmail.com,02224444585, F-7/4 &5, Vijay Chowk, Laxmi Nagar Delhi
In the above statements you can see that in the first line the name of the columns are mentioned and in the following lines the data in the columns are present which are separated by commas.
Use Of Command Line To Import CSV File Into MySQL
The following method will allow us to import a CSV file into the MySQL database using the command-line client. It's best for database developers because they're experts at accessing and utilizing databases, and they'll be comfortable with the MySQL command-line interface. Using this strategy, we can also load big CSV files into the table. The data can be imported into the MySQL table in the following way:
1. Accessing Command Line Client of MySQL: Once MySQL is installed on the system, we can use the following command to access its console. It will ask you for a password, and if you enter it correctly, you will be granted access to the database.
mysql -u username -p
2. Enter Password: Now enter your MySQL password and then your MySQL Command Line client will open on the window.
3. Create a table in the Database: To store the information, we must first create a database table that corresponds to the information in the CSV file. This step requires a little more attention because the table's column order must match the order in the CSV. This must be done because we indicate the type of data that should be put in the columns when we create a table. Importing the CSV using the MySQL command line will fail if the table is created incorrectly.
use user;
create table product_details(
id INTEGER,
product_name VARCHAR(20),
product_life INTEGER,
PRIMARY KEY (id)
);
4. Importing the CSV file into a table: After the table has been built, the contents of the CSV files can be imported into it. We can either specify the file location or save the file in the MySQL server's default directory.
LOAD DATA INFILE ‘Path of the CSV file’
INTO TABLE product_details
FIELDS TERMINATED BY ‘,’
IGNORE 1 ROWS;
The data will be successfully imported into the table once these commands get executed.
Importing CSV Into MySQL Using Workbench
This approach will show you how to use MySQL Workbench to import a CSV file. MySQL Workbench is a graphical user interface that MySQL provides for managing databases, writing SQL queries, and other tasks. It comes with a wizard that lets us export or import data in CSV or JSON format. If you're using MySQL Workbench or a regular text file to import data from an excel sheet into a table, be sure the data is in.csv format. The process is rather basic, as we only need to open the wizard and follow the wizard's instructions.
1. Open the import wizard in the workbench of MySQL: Draw your attention to the navigator panel on the left-hand side of the MySQL workbench to access the Import wizard. A schema area of the navigator panel lists all of the databases and tables. When you right-click on any of the tables, you'll find two options for selecting a wizard: one for exporting data and the other for importing data. To access the import wizard, select the latter option.
2. Now select the source of the data: When you first launch the import wizard, it will prompt you for the location of the CSV file. You can either state it clearly or utilize the explore option to guide you through the process.
3. Now the table is to be selected or created: It will give you two options when you pick the file: import the data into an existing table or create a new one. Select one of the tables where you wish to import the data if you're using an existing table. When importing a CSV file into a new table, specify the table's name.
4. Fill in all the required fields: Additional details such as the line separator, field separator, encoding, and so on will be available through the wizard. Make sure the encoding field in the wizard is set to the same as the encoding of the CSV file. It will also mention the list of columns recognized by the wizard from the CSV. You can choose the field type for each column. The importing procedure will begin when you click Next, and the CSV file will be imported into MySQL.
Now you have to click on next and you will reach to finish.
Well done Pal✌️, Your CSV file has been imported.
Frequently Asked Questions
What are CSV files? A CSV (comma-separated values) file is a text file with a specified format for storing data in a table-structured format.
How long does it take to import a CSV file into a MySQL database? CSV/text files are imported much faster than MySQL Workbench. It would take me 40 minutes to connect to a distant MySQL server using MySQL admin. The upload takes a few minutes on the MySQL server.
To import a CSV file, what approach do you use in MySQL workbench? Select Tools from the shortcut menu and then Import Data. The wizard for data import appears. Select the .csv data format in the Source file tab and browse for the file name.
In the CSV file, what is a row delimiter? A CSV file stores data in rows, with a separator, sometimes known as a delimiter, separating the values in each row. The delimiter could be anything, despite the fact that the file is designated as Comma Separated Values. A comma (,), a semicolon (;), a tab (t), a space (), and a pipe (|) are the most popular delimiters.
What is the origin of a CSV file? Because a CSV file is a text file, any text editor can be used to generate and modify it. A CSV file is most commonly created by exporting a spreadsheet or database from the program that developed it (File > Export).
Conclusion
In the above session we have learned about the methods to import CSV files into our MySQL database using command line client and also with workbench. Using this it will become much easier for you to import a CSV file into MySQL and you will not have any problem with handling large amounts of data.
Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.