{"id":28911,"date":"2024-04-19T23:28:05","date_gmt":"2024-04-19T22:28:05","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/guia-completa-para-importar-archivos-csv-en-mysql-con-load-data-infile\/"},"modified":"2024-06-03T17:43:14","modified_gmt":"2024-06-03T16:43:14","slug":"guia-completa-para-importar-archivos-csv-en-mysql-con-load-data-infile","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/","title":{"rendered":"Complete Guide to Import CSV Files into MySQL with LOAD DATA INFILE"},"content":{"rendered":"<p>Importing data from a CSV file to a MySQL database is a common task for many developers and data analysts. MySQL facilitates this operation through the SQL statement <code>LOAD DATA INFILE<\/code>, which is not only efficient but also simple to use. In this article, I will show you how you can implement this functionality in a practical and effective way.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#%C2%BFQue_es_un_archivo_CSV\" >What is a CSV file?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Preparacion_del_entorno\" >Environment Preparation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Creacion_de_la_tabla_en_MySQL\" >Creating the table in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Formato_correcto_del_archivo_CSV\" >Correct CSV file format<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Usando_LOAD_DATA_INFILE\" >Using LOAD DATA INFILE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Consideraciones_de_seguridad\" >Security considerations<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Resolucion_de_problemas_y_errores_comunes\" >Troubleshooting and common errors<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/complete-guide-to-import-csv-files-in-mysql-with-load-data-infile\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_un_archivo_CSV\"><\/span>What is a CSV file?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A CSV (Comma Separated Values) file is a type of document in a simple open format to represent data in table form. Each line of text corresponds to a row in the table, and columns are separated by commas or semicolons.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Preparacion_del_entorno\"><\/span>Environment Preparation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before you start importing your data, make sure you have access to a MySQL server and know the access details such as username, password, and database name. Also, you need to have the CSV file that you want to import and it must be accessible by the MySQL server.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Creacion_de_la_tabla_en_MySQL\"><\/span>Creating the table in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>First, you need to make sure that the table you want to import data into exists in your database. If you don&#039;t have a table yet, you can create one using a statement <code>CREATE TABLE<\/code>. Here I show you a simple example:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT, salary DECIMAL(10, 2) );<\/code><\/pre>\n<p>In this example, we have created a table called <code>employees<\/code> with four columns: <code>id<\/code>, <code>name<\/code>, <code>age<\/code> y <code>salary<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Formato_correcto_del_archivo_CSV\"><\/span>Correct CSV file format<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before proceeding with the import, it is crucial that the CSV file is formatted correctly. Make sure that the delimiter (commonly a comma or semicolon) and character encoding (such as UTF-8) are compatible with your specifications in MySQL.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Usando_LOAD_DATA_INFILE\"><\/span>Using LOAD DATA INFILE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Sentence <code>LOAD DATA INFILE<\/code> It is very powerful and allows you to quickly import large amounts of data from a CSV file directly to a MySQL table. Here is a basic example of how to use it:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">LOAD DATA INFILE &#039;\/path\/to\/your\/file.csv&#039; INTO TABLE employees FIELDS TERMINATED BY &#039;,&#039; ENCLOSED BY &#039;&quot;&#039; LINES TERMINATED BY &#039;n&#039; IGNORE 1 ROWS;<\/code><\/pre>\n<p>Explanation of the components of the sentence:<\/p>\n<ul>\n<li><code>LOAD DATA INFILE &#039;\/path\/to\/your\/file.csv&#039;<\/code>: Specifies the path of the CSV file to import.<\/li>\n<li><code>INTO TABLE employees<\/code>: Defines which table the data will be imported into.<\/li>\n<li><code>FIELDS TERMINATED BY &#039;,&#039;<\/code>: Indicates that the columns in the CSV file are separated by commas.<\/li>\n<li><code>ENCLOSED BY &#039;&quot;&#039;<\/code>: Specific for files where data is enclosed in double quotes.<\/li>\n<li><code>LINES TERMINATED BY &#039;n&#039;<\/code>: Denotes that each line is terminated by a line break.<\/li>\n<li><code>IGNORE 1 ROWS<\/code>: Used if the first row of the CSV contains column names and we want to skip it.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Consideraciones_de_seguridad\"><\/span>Security considerations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>It is important to mention that the use of <code>LOAD DATA INFILE<\/code> can present security risks if not handled with caution, especially when importing files from untrusted sources. Be sure to validate and clean the data before importing it into your database.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Resolucion_de_problemas_y_errores_comunes\"><\/span>Troubleshooting and common errors<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>During import, you may encounter various errors such as file permission issues, character incompatibility, or database integrity violations. Here are some tips:<\/p>\n<ul>\n<li>Check that the CSV file has read permissions for the MySQL server.<\/li>\n<li>Make sure the file format and encoding are compatible with your MySQL configuration.<\/li>\n<li>Check the table&#039;s restrictions and data types to ensure that the CSV data matches them.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Import data from a CSV file to a MySQL table using <code>LOAD DATA INFILE<\/code> It is an efficient and fast technique for manipulating large volumes of data. With the basics we have covered in this tutorial, you can now implement this functionality safely and effectively.<\/p>\n<p>If you have additional questions or need help with specific issues related to importing CSV files into MySQL, feel free to <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact me<\/a>. I will be happy to help you get the most out of your databases. Visit <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev<\/a> for more resources and guides on development and databases.<\/p>","protected":false},"excerpt":{"rendered":"<p>Importing data from a CSV file into a MySQL database is a common task for many developers and data analysts. MySQL makes this operation easy by using the SQL statement LOAD DATA INFILE, which is not only efficient but also simple to use. In this article, I will show you how you can implement this functionality [\u2026]<\/p>","protected":false},"author":1,"featured_media":28912,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185],"tags":[602,205,500,90,2013,2189,639,358,625,2011,2012,352,60,2201,791],"class_list":["post-28911","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","tag-archivos","tag-blog","tag-completa","tag-con","tag-csv","tag-curso","tag-data","tag-guia","tag-importar","tag-infile","tag-load","tag-mysql","tag-para","tag-user-defined","tag-variables"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28911","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=28911"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28911\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/28912"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=28911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=28911"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=28911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}