CSV file saved in the database

Asked

Viewed 63 times

0

I have a large CVS file and I need to record it in my database the first line are the fields, which I have to validate if they are right and make some changes for example

first line NAME, DATE BIRTH, NOMEMAE second line GUILHERME FREIRE, 15/02/1987, MARIA CLARA third line PEDRO ANTUNES, 20/04/1990, PATRICIA SA

So far so good, but the first line can be different and I want to validate this it can be as FULL NAME, DTNASCIMENTO, MAE ... ETC

how do I do this with the best possible efficiency?

php mysql array csv

1 answer

2


You can create the CREATE STATEMENT like this:

#!/bin/sh
# pass in the file name as an argument: ./mktable filename.csv
echo "create table $1 ( "
head -1 $1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"

This shell code takes the first line of CSV and converts it into a create statement by naming the columns with what you have in the first row, and then you run it in mysql.

Or at hand if not too many columns. Basically you need to have the table created with all the fields you will need.

Then you run this query in mysql:

LOAD DATA LOCAL INFILE '/pasta/arquivo.csv' INTO TABLE nome_tabela
CHARACTER SET UTF8
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
IGNORE 1 LINES;

For more details see the Mysql documentation: http://dev.mysql.com/doc/refman/5.7/en/load-data.html

Browser other questions tagged php mysql array csv

You are not signed in. Login or sign up in order to post.