{"id":458,"date":"2019-02-27T12:11:33","date_gmt":"2019-02-27T11:11:33","guid":{"rendered":"https:\/\/bastienmalahieude.fr\/?p=458"},"modified":"2019-03-02T11:02:49","modified_gmt":"2019-03-02T10:02:49","slug":"exporter-tableau-php-csv-excel","status":"publish","type":"post","link":"https:\/\/bastienmalahieude.fr\/en\/export-array-php-csv-microsoft-excel\/","title":{"rendered":"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel"},"content":{"rendered":"<p>In this first article of the series, we will try to create a function to export a PHP array as a CSV file, readable in Excel.<\/p>\n<p>The purpose of the manipulation is to have in input data a\u00a0PHP\u00a0<a href=\"http:\/\/php.net\/manual\/fr\/language.types.array.php\" target=\"_blank\" rel=\"noopener noreferrer\">associative array<\/a>\u00a0and output a CSV file that will be downloaded to the user&#8217;s computer.\u00a0For that, we will create the\u00a0<em>export_data_to_csv<\/em>\u00a0() function.<\/p>\n<p>We will try to export the following PHP array<\/p>\n<pre class=\"dump\">array(2) {\n  [0]=>\n  array(4) {\n    [\"first name\"]=>\n    string(7) \"Bastien\"\n    [\"last name\"]=>\n    string(10) \"Malahieude\"\n    [\"phone\"]=>\n    string(17) \"06 XX XX XX XX XX\"\n    [\"email\"]=>\n    string(28) \"contact@bastienmalahieude.fr\"\n  }\n  [1]=>\n  array(4) {\n    [\"first name\"]=>\n    string(4) \"John\"\n    [\"last name\"]=>\n    string(3) \"Doe\"\n    [\"phone\"]=>\n    string(17) \"06 XX XX XX XX XX\"\n    [\"email\"]=>\n    string(11) \"john@doe.fr\"\n  }\n}\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Into a CSV file that will contain all these data :<\/p>\n    <table class=\"table table-striped table-responsive\">\n        <thead>\n        <tr>\n                            <th>first name<\/th>\n                            <th>last name<\/th>\n                            <th>phone<\/th>\n                            <th>email<\/th>\n                    <\/tr>\n        <\/thead>\n        <tbody>\n                    <tr>\n                                    <td>Bastien<\/td>\n                                    <td>Malahieude<\/td>\n                                    <td>06 XX XX XX XX XX<\/td>\n                                    <td>contact@bastienmalahieude.fr<\/td>\n                            <\/tr>\n                    <tr>\n                                    <td>John<\/td>\n                                    <td>Doe<\/td>\n                                    <td>06 XX XX XX XX XX<\/td>\n                                    <td>john@doe.fr<\/td>\n                            <\/tr>\n                <\/tbody>\n    <\/table>\n\n\n    \n<p>&nbsp;<\/p>\n<p>Our function will take 4 arguments to make it as flexible as possible<\/p>\n<ul>\n<li>$<strong>data<\/strong>: The data table<\/li>\n<li>$<strong>delimiter<\/strong>: The CSV delimiter you want to use. By default &#8221; ;\u00a0To be compatible with excel<\/li>\n<li>$<strong>enclosure<\/strong>: The character that delimits strings. By default the quotation mark<\/li>\n<li>$<strong>filename<\/strong>: The name of the file you want to export<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>The instantiation of our function will therefore be the following:<\/p>\n<pre>function export_data_to_csv($data,$filename='export',$delimiter = ';',$enclosure = '\"') {\r\n  \/\/@TODO Do something here\r\n }<\/pre>\n<h2>EXPORT DATA IN CSV FORMAT<\/h2>\n<p>The first step is then to force the browser to consider our data as a CSV file.\u00a0This is done by using the <a href=\"https:\/\/developer.mozilla.org\/fr\/docs\/Web\/HTTP\/Headers\">http headers<\/a>\u00a0sent to the client.<\/p>\n<pre>\/\/ Tell the browser that the data returned is a file named $filename.csv\r\nheader ( \"Content-disposition: attachment; filename = $ filename.csv\" );\r\n\/\/ Tell the browser that the data returned is a csv file.\r\nheader ( \"Content-Type: text \/ csv\" );<\/pre>\n<h2>WRITE TO THE FILE<\/h2>\n<p>Since we want to return a file, we must open a &#8220;file&#8221; in the php memory.<\/p>\n<p>For that, we use the function\u00a0<a href=\"http:\/\/fr.php.net\/manual\/fr\/function.fopen.php\" target=\"_blank\" rel=\"noopener noreferrer\"><em>fopen<\/em><\/a>\u00a0with as argument\u00a0<em>php: \/\/output<\/em><\/p>\n<pre>$fp = fopen(\"php:\/\/output\", 'w');<\/pre>\n<p>This tells PHP that you want to write to the memory that will be sent to the browser.<\/p>\n<p>Then, for compatibility problems, the data that we export must be encoded in UTF-8<\/p>\n<p>To do this, add the UTF-8 BOM to the file.\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/5601904\/encoding-a-string-as-utf-8-with-bom-in-php\" target=\"_blank\" rel=\"noopener noreferrer\">More information is available here<\/a><\/p>\n<pre>fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));<\/pre>\n<h3>ADD TABLE HEADERS<\/h3>\n<p>Then, we want to add the keys of our table as table header of our php file.<\/p>\n<p>With the help of the\u00a0<a href=\"http:\/\/php.net\/manual\/fr\/function.array-keys.php\">array_keys<\/a>\u00a0function\u00a0, we will be able to retrieve the keys of the array.<\/p>\n<p>Subsequently, the\u00a0<a href=\"http:\/\/php.net\/manual\/fr\/function.fputcsv.php\">fputcsv<\/a>\u00a0function\u00a0is used to add a line to the CSV file.<\/p>\n<pre>fputcsv($fp,array_keys($data[0]),$delimiter,$enclosure);<\/pre>\n<h3>ADD PHP TABLE DATA<\/h3>\n<p>The next step is to add all the data of the PHP array in the CSV file. For this, we will use a <em>foreach<\/em>\u00a0loop\u00a0which allows to add the elements line by line:<\/p>\n<pre>foreach ($data as $fields) {\r\n fputcsv($fp, $fields,$delimiter,$enclosure); \r\n}<\/pre>\n<p>Finally, we just have to close the file and stop the script<\/p>\n<pre>fclose($fp);\r\ndie();<\/pre>\n<p><strong>Note: Since you are modifying http headers, it is important that this script be executed before any HTML code.\u00a0<\/strong><a href=\"http:\/\/php.net\/manual\/en\/function.header.php\" target=\"_blank\" rel=\"noopener noreferrer\">More information on the subject is available in the PHP documentation\u00a0<\/a><strong>.<\/strong><\/p>\n<h2>FULL PHP FUNCTION<\/h2>\n<p>The final function we have just created is the following:<\/p>\n<pre>\/**\r\n *\r\n * Exports an associative array into a CSV file using PHP.\r\n *\r\n * @see https:\/\/stackoverflow.com\/questions\/21988581\/write-utf-8-characters-to-file-with-fputcsv-in-php\r\n *\r\n * @param array     $data       The table you want to export in CSV\r\n * @param string    $filename   The name of the file you want to export\r\n * @param string    $delimiter  The CSV delimiter you wish to use. The default \";\" is used for a compatibility with microsoft excel\r\n * @param string    $enclosure  The type of enclosure used in the CSV file, by default it will be a quote \"\r\n *\/\r\nfunction export_data_to_csv($data,$filename='export',$delimiter = ';',$enclosure = '\"')\r\n{\r\n    \/\/ Tells to the browser that a file is returned, with its name : $filename.csv\r\n    header(\"Content-disposition: attachment; filename=$filename.csv\");\r\n    \/\/ Tells to the browser that the content is a csv file\r\n    header(\"Content-Type: text\/csv\");\r\n\r\n    \/\/ I open PHP memory as a file\r\n    $fp = fopen(\"php:\/\/output\", 'w');\r\n\r\n    \/\/ Insert the UTF-8 BOM in the file\r\n    fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));\r\n\r\n    \/\/ I add the array keys as CSV headers\r\n    fputcsv($fp,array_keys($data[0]),$delimiter,$enclosure);\r\n\r\n    \/\/ Add all the data in the file\r\n    foreach ($data as $fields) {\r\n        fputcsv($fp, $fields,$delimiter,$enclosure);\r\n    }\r\n\r\n    \/\/ Close the file\r\n    fclose($fp);\r\n\r\n    \/\/ Stop the script\r\n    die();\r\n}<\/pre>\n<p>The code is available on github in the repository\u00a0<a href=\"https:\/\/github.com\/Xusifob\/lib\">xusifob\/lib<\/a><\/p>\n<p>Feel free to comment this article using the form underneath!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this first article of the series, we will try to create a function to export a PHP array as a CSV file, readable in Excel. The purpose of the manipulation is to have in input data a\u00a0PHP\u00a0associative array\u00a0and output a CSV file that will be downloaded to the user&#8217;s computer.\u00a0For that, we will create [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":463,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,22],"tags":[],"translation":{"provider":"WPGlobus","version":"2.12.2","language":"en","enabled_languages":["fr","en","es"],"languages":{"fr":{"title":true,"content":true,"excerpt":false},"en":{"title":true,"content":true,"excerpt":false},"es":{"title":false,"content":false,"excerpt":false}}},"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel - Bastien Malahieude\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Bastien Malahieude\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-27T11:11:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-03-02T10:02:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2019\/02\/php-html-code.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1160\" \/>\n\t<meta property=\"og:image:height\" content=\"778\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"bastien\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"bastien\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\"},\"author\":{\"name\":\"bastien\",\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7\"},\"headline\":\"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel\",\"datePublished\":\"2019-02-27T11:11:33+00:00\",\"dateModified\":\"2019-03-02T10:02:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\"},\"wordCount\":965,\"commentCount\":8,\"publisher\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7\"},\"articleSection\":[\"PHP\",\"WordPress\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\",\"url\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\",\"name\":\"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel\",\"isPartOf\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/#website\"},\"datePublished\":\"2019-02-27T11:11:33+00:00\",\"dateModified\":\"2019-03-02T10:02:49+00:00\",\"description\":\"Comment exporter facilement un tableau associatif PHP dans un fichier CSV compatible UTF-8 et Microsoft Excel.\",\"breadcrumb\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/bastienmalahieude.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PHP\",\"item\":\"https:\/\/bastienmalahieude.fr\/category\/php\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Exporter un tableau PHP en CSV, compatible UTF-8 et Microsoft Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bastienmalahieude.fr\/#website\",\"url\":\"https:\/\/bastienmalahieude.fr\/\",\"name\":\"Bastien Malahieude\",\"description\":\"Growth Hacker - Web Developer - Project Manager\",\"publisher\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bastienmalahieude.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7\",\"name\":\"bastien\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2017\/10\/bastien-3-carre-2.png\",\"contentUrl\":\"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2017\/10\/bastien-3-carre-2.png\",\"width\":\"404\",\"height\":\"404\",\"caption\":\"bastien\"},\"logo\":{\"@id\":\"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/","og_locale":"en_US","og_type":"article","og_title":"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel - Bastien Malahieude","og_url":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/","og_site_name":"Bastien Malahieude","article_published_time":"2019-02-27T11:11:33+00:00","article_modified_time":"2019-03-02T10:02:49+00:00","og_image":[{"width":1160,"height":778,"url":"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2019\/02\/php-html-code.jpg","type":"image\/jpeg"}],"author":"bastien","twitter_card":"summary_large_image","twitter_misc":{"Written by":"bastien","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#article","isPartOf":{"@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/"},"author":{"name":"bastien","@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7"},"headline":"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel","datePublished":"2019-02-27T11:11:33+00:00","dateModified":"2019-03-02T10:02:49+00:00","mainEntityOfPage":{"@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/"},"wordCount":965,"commentCount":8,"publisher":{"@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7"},"articleSection":["PHP","WordPress"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/","url":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/","name":"Export a PHP array to CSV, compatible with UTF-8 and Microsoft Excel","isPartOf":{"@id":"https:\/\/bastienmalahieude.fr\/#website"},"datePublished":"2019-02-27T11:11:33+00:00","dateModified":"2019-03-02T10:02:49+00:00","description":"Comment exporter facilement un tableau associatif PHP dans un fichier CSV compatible UTF-8 et Microsoft Excel.","breadcrumb":{"@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/bastienmalahieude.fr\/exporter-tableau-php-csv-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/bastienmalahieude.fr\/"},{"@type":"ListItem","position":2,"name":"PHP","item":"https:\/\/bastienmalahieude.fr\/category\/php\/"},{"@type":"ListItem","position":3,"name":"Exporter un tableau PHP en CSV, compatible UTF-8 et Microsoft Excel"}]},{"@type":"WebSite","@id":"https:\/\/bastienmalahieude.fr\/#website","url":"https:\/\/bastienmalahieude.fr\/","name":"Bastien Malahieude","description":"Growth Hacker - Web Developer - Project Manager","publisher":{"@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bastienmalahieude.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/59ad42a2add75f0f9af8e06f493841c7","name":"bastien","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/image\/","url":"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2017\/10\/bastien-3-carre-2.png","contentUrl":"https:\/\/bastienmalahieude.fr\/wp-content\/uploads\/2017\/10\/bastien-3-carre-2.png","width":"404","height":"404","caption":"bastien"},"logo":{"@id":"https:\/\/bastienmalahieude.fr\/#\/schema\/person\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/posts\/458"}],"collection":[{"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/comments?post=458"}],"version-history":[{"count":5,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/posts\/458\/revisions"}],"predecessor-version":[{"id":490,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/posts\/458\/revisions\/490"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/media\/463"}],"wp:attachment":[{"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/media?parent=458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/categories?post=458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bastienmalahieude.fr\/en\/wp-json\/wp\/v2\/tags?post=458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}