How to import data into a table in a database using MYSQL Command Line and solve all error problems encountered.

In the course of my recent database design project, I encountered challenges in the process of loading data into pre-existing tables, challenges that were effectively resolved. For a thorough understanding of my previous database design endeavors, please consult the related documentation available via this link.

Returning to our current discussion, it is crucial to ensure the fulfillment of the following prerequisites before commencing the data loading process into our database:

1. Database Creation
2. Table Creation with the correct data types

This guide will be structured into three steps:

Step 1:

Upon the successful completion of the aforementioned prerequisites, kindly proceed to execute the following command in your MySQL Workbench.

It will provide you with the authorized file directory for uploading files into your database, as a security measure in MySQL databases. The file directory presented here reflects the result I obtained, and it may be applicable in your case as well.

Find the specified file directory and position the file you intend to upload into that location. Here is a snapshot of mine within the designated directory.

STEP 2:

The subsequent action involves enabling the local-infile option. Here is a step-by-step breakdown to accomplish this:

1. Press the Windows Key.
2. Search for MYSQL Command Line Client.
3. Run the application as an Administrator.
4. Upon launch, enter your database connection password.
5. Enter the following command

SHOW GLOBAL VARIABLES LIKE 'local_infile';

You should receive an output similar to the following:

If the status of your local_infile is ON, similar to mine, type "exit" to exit the application and proceed to the next step. However, if it is not, execute the following code to change it to ON:

SET GLOBAL local_infile=ON

Verify the status using the following code:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Once confirmed, type “exit” to close the application.

STEP 3:

Now, for the final preparatory step before uploading, navigate to your MYSQL Server folder in your file directory. You should find the last file provided in my snapshot.

Open the file using Notepad and insert the term local_infile=1 in the designated locations as highlighted in my snapshot.

Additionally, scroll down to the Server Section and incorporate it in the specified position highlighted in the screenshot.

Upon completion, please save and exit. In case you encounter challenges saving the file due to insufficient administrative rights, follow these steps:

1. Right-click on the file.
2. Select “Properties.”
3. Navigate to the “Security” tab.
4. Check the ‘Full Control’ option for your desktop users, as illustrated below.

1. Subsequently, click on ‘Apply.’
2. Then select ‘OK’ and attempt to save it once more. The error should no longer be encountered.

Final Step:

Now, we can proceed to upload our data into the database. Please follow the steps outlined below:

1. Open your MySQL Command Line Client.
2. Enter your password.
3. Highlight your database using the following query:

USE database_name;

1. Then, enter this query:

LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\EDITED_HR_Analytics.csv'
INTO TABLE Employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Ensure to modify the file directory to your specific path and the table name to the table where you intend to load the data. Upon successful execution, your screen should resemble mine.

If you still encountered any further issues after followings my steps and your problem wasn’t solved. Feel free to reach out to me via LinkedIn or Twitter and please also leave a comment if this helped you greatly.

Thanks for reading and happy solving complex data problems