Import and export CSV files using PHP

1 In project development, many times you need to import an external CSV file into the database or export the data to a CSV file. So how do you achieve this? This article will use PHP in conjunction with mysql to implement CSV format data import and export functions. 3 We first prepare the mysql data table. Assume that there is a table student in the project that records student information, and has id, name, sex, age to record the student's name, gender, age and other information.
 4 CREATE TABLE `student` (
 5 `id` int (11) NOT NULL auto_increment,
 6 `name` varchar (50) NOT NULL,
 7 `sex` varchar (10) NOT NULL,
 8 `age` smallint (3) NOT NULL default‘ 0 ’,
 9 PRIMARY KEY (`id`)
10) ENGINE = MyISAM DEFAULT CHARSET = utf8;
11 We also need an html interactive page with the import form and export buttons.
12 <form id = "addform" action = "do.php? Action = import" method = "post" enctype = "multipart / form-data">
13 <p> Please select a CSV file to import: <br/> <input type = "file" name = "file"> <input type = "submit"
14 class = "btn" value = "import CSV">
15 <input type = "button" class = "btn" value = "export CSV" onclick = "window.location.href =‘ do.php?
16 action = export ‘"> </ p>
17 </ form>
18 After selecting the local csv file, click Import to submit to do.php? Action = import for processing, and click the Export button to request the address do.php? Action = export for data export processing.
19 1. Import CSV
20 do.php needs to handle the import and export process according to the parameters from get. The PHP structure is as follows:
21 include_once ("connect.php"); // Connect to the database
twenty two  
23 $ action = $ _GET [‘action‘];
24 if ($ action == ‘import‘) {// Import CSV
25 // Import processing
26} elseif ($ action == ‘export’) {// Export CSV
27 // Export processing
28}
29 Import CSV processing flow: check the validity of the csv file (ignored in this article)-> open the fields in the csv file for reading and analysis-> cycle to get the value of each field-> add to the data table in batch-> complete
30 if ($ action == ‘import‘) {// Import CSV
31 $ filename = $ _FILES [‘file‘] [‘tmp_name’];
32 if (empty ($ filename)) {
33 echo ‘Please select a CSV file to import! ‘;
34 exit;
35}
36 $ handle = fopen ($ filename, ‘r’);
37 $ result = input_csv ($ handle); // parse csv
38 $ len_result = count ($ result);
39 if ($ len_result == 0) {
40 echo ‘No data! ‘;
41 exit;
42}
43 for ($ i = 1; $ i <$ len_result; $ i ++) {// Recycle each field value
44 $ name = iconv (‘gb2312’, ‘utf-8’, $ result [$ i] [0]); // Chinese transcoding
45 $ sex = iconv (‘gb2312’, ‘utf-8’, $ result [$ i] [1]);
46 $ age = $ result [$ i] [2];
47 $ data_values. = "(‘ $ Name ’,‘ $ sex ’,‘ $ age ’),";
48}
49 $ data_values = substr ($ data_values, 0, -1); // Remove the last comma
50 fclose ($ handle); // close the pointer
51 $ query = mysql_query ("insert into student (name, sex, age) values $ data_values");
52 if ($ query) {
53 echo ‘Import success! ‘;
54} else {
55 echo ‘Import failed! ‘;
56}
57}
58 Note that PHP's fgetcsv function can easily handle csv. Using this function can read a line from the file pointer and parse the CSV field. The following function parses the csv file fields and returns them as an array.
59 function input_csv ($ handle) {
60 $ out = array ();
61 $ n = 0;
62 while ($ data = fgetcsv ($ handle, 10000)) {
63 $ num = count ($ data);
64 for ($ i = 0; $ i <$ num; $ i ++) {
65 $ out [$ n] [$ i] = $ data [$ i];
66}
67 $ n ++;
68}
69 return $ out;
70}
71 In addition, when importing into the database, we use batch inserts instead of inserts one by one, so we need to do a little processing when constructing SQL statements, see code.
72 2.Export CSV
73 We know that the csv file is a plain text file composed of comma delimiters, you can open it with excel, the effect is the same as the xls table.
74 Export CSV processing flow: read the student information table-> loop record to build comma-separated field information-> set header information-> export file (download) to local
75 ...
76} elseif ($ action == ‘export’) {// Export CSV
77 $ result = mysql_query ("select * from student order by id asc");
78 $ str = "name, gender, age \ n";
79 $ str = iconv (‘utf-8’, ‘gb2312’, $ str);
80 while ($ row = mysql_fetch_array ($ result)) {
81 $ name = iconv (‘utf-8’, ’gb2312’, $ row [‘name’]); // Chinese transcoding
82 $ sex = iconv (‘utf-8’, ’gb2312’, $ row [‘sex‘]);
83 $ str. = $ Name. ",". $ Sex. ",". $ Row [‘age‘]. "\ N"; // Separate with commas
84}
85 $ filename = date (‘Ymd‘). ‘.Csv’; // Set the file name
86 export_csv ($ filename, $ str); // Export
87}
88 To export the data to the local immediately, you need to modify the header information. The code is as follows:
89 function export_csv ($ filename, $ data) {
90 header ("Content-type: text / csv");
91 header ("Content-Disposition: attachment; filename =". $ Filename);
92 header (‘Cache-Control: must-revalidate, post-check = 0, pre-check = 0‘);
93 header (‘Expires: 0’);
94 header (‘Pragma: public’);
95 echo $ data;
96}
Note that during the import and export process, because we are using the unified UTF-8 encoding, we must remember to transcode when encountering Chinese characters, otherwise Chinese garbled characters may appear.
98 Well, this article explains this, I will have an article in the following about PHP combined with mysql import and export excel, and XML import and export, stay tuned.

Import and export CSV files using PHP