CSV file import database: D, csv import database

CSV file import database: D, csv import database

CSV import database summary:

1. Open the CSV file and analyze the file structure:

After a rough look, we found that all the fields are relatively short. to quickly create a table, we can simply use varchar (20) to define the type of each field (! Note: In actual development, You must select a suitable field type based on your data type. Here we are lazy and sacrifice performance and storage space, of course ).

2. Create a table based on the csv field:

Right-click Copy on the first line and copy all field headers (separated by blank spaces ).

Script generation database creation code:

<?php$columns_line = "Name   CardNo  Descriot    CtfTp   CtfId   Gender  Birthday    Address Zip Dirty   District1   District2       District3   District4   District5   District6   FirstNm LastNm  Duty    Mobile  Tel Fax EMail   Nation  Taste   Education   Comp    any CTel    CAddress    CZip    Family  Version id";$columns = preg_split("/\s+/",trim($columns_line));$chars   = "create table kf_info(";foreach( $columns as $col ){           $chars .= "$col varchar(20) not null default '' ";           if($col != end($columns)){                    $chars .= ",";           }           $chars .= PHP_EOL;}$chars .= ") engine=myisam default charset=utf8;";echo $chars;

Create table SQL statement:

create table kf_info(Name varchar(20) not null default '' ,CardNo varchar(20) not null default '' ,Descriot varchar(20) not null default '' ,CtfTp varchar(20) not null default '' ,CtfId varchar(20) not null default '' ,Gender varchar(20) not null default '' ,Birthday varchar(20) not null default '' ,Address varchar(20) not null default '' ,Zip varchar(20) not null default '' ,Dirty varchar(20) not null default '' ,District1 varchar(20) not null default '' ,District2 varchar(20) not null default '' ,District3 varchar(20) not null default '' ,District4 varchar(20) not null default '' ,District5 varchar(20) not null default '' ,District6 varchar(20) not null default '' ,FirstNm varchar(20) not null default '' ,LastNm varchar(20) not null default '' ,Duty varchar(20) not null default '' ,Mobile varchar(20) not null default '' ,Tel varchar(20) not null default '' ,Fax varchar(20) not null default '' ,EMail varchar(20) not null default '' ,Nation varchar(20) not null default '' ,Taste varchar(20) not null default '' ,Education varchar(20) not null default '' ,Company varchar(20) not null default '' ,CTel varchar(20) not null default '' ,CAddress varchar(20) not null default '' ,CZip varchar(20) not null default '' ,Family varchar(20) not null default '' ,Version varchar(20) not null default '' ,id varchar(20) not null default '') engine=myisam default charset=utf8;

You can also directly execute mysql_query in your php script.

The database is finally built:

 

3. Import Data

Now, you can import csv files. Mysql provides load data infile for easy import:

load data infile 'D:/2000W/200W-400W.csv'   into table kf_info    fields terminated by ','  optionally enclosed by '"' escaped by '"'   lines terminated by '\r\n';

Unfortunately, an error is reported:

The red part shows that this is the BOM header of the UTF-8.

Try again after removing BOM.

Search records:

Select Name, CtfId, Birthday, Address, Mobile from kf_info where address like "Beijing %" and gender = 'F ';

Emma has everything, including phone numbers, addresses, ID card numbers, and networks. This is a terrible thing.

 

Do you still believe in network security?

Ps: This article is purely technical. You can understand it as: How to import CSV files into the database.