Import and Export excel format (csv) data using php

When writing a program, you need to import a large amount of data into the database. for example, test scores and phone books are generally stored in excel. when writing a program, you need to import a large amount of data into the database, for example, exam scores and phone books are generally stored in excel. in this case, we can export the excel file to a csv file and then import the data to the database in batches using the following program, upload the csv file and import it to the database. the test is successful (some codes are not standard, for example, to rewrite PHP_SELF to $ _ SERVER ["PHP_SELF"]).

$ Fname = $ _ FILES ['myfile'] ['name']; $ Do = copy ($ _ FILES ['myfile'] ['tmp _ name'], $ fname ); If ($ do ){ Echo "data imported successfully "; } Else { Echo ""; } ?> Error_reporting (0); // import a CSV file $ Connect = mysql_connect ("localhost", "a0530093319", "123456") or die ("cocould not connect to database "); Mysql_select_db ("a0530093319", $ connect) or die (mysql_error ()); $ Fname = $ _ FILES ['myfile'] ['name']; $ Handle = fopen ("$ fname", "r "); While ($ data = fgetcsv ($ handle, 10000 ,",")){ $ Q = "insert into test (code, name, date) values ('$ data [0]', '$ data [1]', '$ data [2]') "; Mysql_query ($ q) or die (mysql_error ()); } Fclose ($ handle ); ?>

Export the database to excel using php. the test is successful.

$ DB_Server = "localhost "; $ DB_Username = "root "; $ DB_Password = ""; $ DB_DBName = "ishop "; $ DB_TBLName = "oi_mall_payment "; $ Savename = date ("YmjHis "); $ Connect = @ mysql_connect ($ DB_Server, $ DB_Username, $ DB_Password) or die ("Couldn't connect ."); Mysql_query ("Set Names gbk"); $ file_type = "vnd. ms-excel "; $ File_ending = "xls "; Header ("Content-Type: application/$ file_type; charset = big5 "); Header ("Content-Disposition: attachment; filename =". $ savename. ". $ file_ending "); // Header ("Pragma: no-cache "); $ Now_date = date ("Y-m-j H: I: s "); $ Title = "database name: $ DB_DBName, Data Table: $ DB_TBLName, backup date: $ now_date "; $ SQL = "Select * from $ DB_TBLName "; $ ALT_Db = @ mysql_select_db ($ DB_DBName, $ Connect) or die ("Couldn't select database "); $ Result = @ mysql_query ($ SQL, $ Connect) or die (mysql_error ()); Echo ("$ title \ n "); $ Sep = "\ t "; For ($ I = 0; $ I <mysql_num_fields ($ result); $ I ++ ){ Echo mysql_field_name ($ result, $ I). "\ t "; } Print ("\ n "); $ I = 0; While ($ row = mysql_fetch_row ($ result )){ $ Schema_insert = ""; For ($ j = 0; $ j If (! Isset ($ row [$ j]) $ Schema_insert. = "NULL". $ sep; Elseif ($ row [$ j]! = "") $ Schema_insert. = "$ row [$ j]". $ sep; Else $ Schema_insert. = "". $ sep; } $ Schema_insert = str_replace ($ sep. "$", "", $ schema_insert ); $ Schema_insert. = "\ t "; Print (trim ($ schema_insert )); Print "\ n "; $ I ++; } Return (true ); ?>