I have a large CVS file and need to write it to my database the first line is the fields, which I have to validate if they are right and make some changes for example
first line NAME, DATANASCIMENTO, NOMEMAE second line GUILHERME FREIRE, 02/15/1987, MARIA CLARA third line PEDRO ANTUNES, 04/20/1990, PATRICIA SA
So that's fine, but the first line may be different and I want to validate this it can be as NOMECOMPLETO, DTNASCIMENTO, MAE ... ETC
How do I do it with the best efficiency possible?
You can create 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 the CSV and converts it to a create statement giving the name of the columns with what it has in the first line, and then you execute it in mysql.
Or on the hand if it is not too many columns. Basically you need to have the table created with all the fields you will need.
Then you execute 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: link