PHP31 Agosto 2016by Daniele Carnovale44Da excel a mysql. Converti xlsx in csv e importa in mysql

Converti da excel a mysql

La seguente guida descrive i passi da seguire per caricare il contenuto di un file excel del formato di Office 2010 .xlsx su database mysql.

Ambiente:

Piattaforma linux server 14.04 php 5.5

(La versione di linux può anche essere diversa, ma quella php non inferiore)

Comandi shell

Se non presente installare unoconv

sudo apt-get install unoconv

Requisiti

dare i permessi di esecuzione sudo senza password al comando unoconv

aggiungi i seguenti righi al file /etc/sudoers

nano /etc/sudoers

Righi da aggiungere alla fine del file appena aperto con il comando nano (per gli amanti di VIM o CAT non cambia niente) # Give apache sudo privileges to run /usr/bin/unoconv script
www-data ALL=NOPASSWD: /usr/bin/unoconv

aggiungere permessi alla cartella per eseguire il file csv da mysql

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Righi da aggiungere alla fine del file appena aperto

/run/mysqld/mysqld.pid rw,
/run/mysqld/mysqld.sock w,
/var/www/** rwk,
/sys/devices/system/cpu/ r,

Aggiorniamo

sudo /etc/init.d/apparmor reload

__________________________________________

Nel caso di conversione di file grandi ricordare di cambiare i valori di default nel file php.ini

post_max_size=20M
upload_max_filesize=20M

alla fine sempre un bel restart

/etc/init.d/apache2 restart

__________________________________________

codice php da excel a mysql

dare i permessi ai file csv

$result = @move_uploaded_file($_FILES['xlsxfile']['tmp_name'], $filetmpimport); // upload it
// $file=$filetmpimport;

//require_once 'xlsx2csv.php';
$start=microtime(true);
// $command = "/usr/bin/ssconvert /var/www/html/xlsx2csv/Sampleformslist.xlsx /var/www/html/xlsx2csv/fileimportato3.csv";
// $command = "sh /var/www/html/xlsx2csv/mysqript.sh";

$command="sudo unoconv -f csv /var/www/html/xlsx2csv/fileimport.xlsx";

exec( $command , $output, $ret );
$fp=fopen("/var/www/html/xlsx2csv/fileimport.csv","r");

if($line=fgets($fp,65535)) {
$line= str_replace(' ','_',$line);

$line= str_replace(',',' varchar(250) DEFAULT NULL,',$line);

$line='CREATE TABLE tempImport.temporanea ('.$line.' varchar(250) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;';
echo $line . '</br>';
}
if($line){

$result = mysql_query('DROP TABLE IF EXISTS tempImport.temporanea;');

$result= mysql_query($line);
// die;

if ($result) {
echo "create successfully";
} else {
echo "Error create table: ";
}

}else {

echo 'Importazione fallita';
$end=microtime(true);
fclose($fp);
$delta1=($end - $start);
echo $delta1 . '</br>';
die;
}
$end=microtime(true);
fclose($fp);
$delta1=($end - $start);

// echo $delta1 . '</br>';

// $queryInsertdafileCsv="LOAD DATA INFILE '/var/www/html/xlsx2csv/fileimport.csv' INTO TABLE tempImport.temporanea FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;";

// echo $queryInsertdafileCsv . '</br>';

$result= mysql_query('LOAD DATA INFILE "/var/www/html/xlsx2csv/fileimport.csv" INTO TABLE tempImport.temporanea FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" LINES TERMINATED BY "\n" IGNORE 1 LINES;');

if ($result) {
echo "Import successfully";

$warningDetailResult = mysql_query("SHOW WARNINGS");
if ($warningDetailResult ) {
while ($warning = mysql_fetch_assoc($warningDetailResult)) {

echo print_r($warning).'<br/>';

}
}

} else {
echo "Errore Importazione tabella: ".mysql_error();;
}
echo 'Importazione eseguita correttamente';

 

Considerazioni finali ed errori frequenti

Il codice di esempio citato, per importare un file da excel a mysql, funziona correttamente per file di formato .xlsx
Da notare che il file da importare deve contenere sul primo rigo il nome delle colonne della tabella che verrà creata sul database.
Gli errori possono derivare da caratteri speciali non previsti sul primo rigo o se il nome di qualche colonna è vuoto.

44 comments

  • zortilonrel

    22 Giugno 2021 at 10:13

    Im not certain the place you are getting your information, however great topic. I must spend a while studying more or understanding more. Thank you for great info I used to be in search of this information for my mission.

    Reply

  • zortilonrel

    6 Luglio 2021 at 20:58

    I’m not certain where you’re getting your info, however good topic. I must spend a while learning more or figuring out more. Thanks for great information I was on the lookout for this information for my mission.

    Reply

  • Yanira

    19 Agosto 2021 at 2:50

    Hi there everyone, it’s my first go to see at this website, and post is actually fruitful in favor of me, keep up posting such posts.

    Reply

  • Marsha

    24 Agosto 2021 at 19:15

    Hi to all, how is everything, I think every one is getting
    more from this site, and your views are pleasant for new viewers.

    Reply

  • Marie

    24 Agosto 2021 at 21:03

    Wonderful, what a web site it is! This website presents useful data to us, keep it up.

    Reply

  • gym around me

    20 Settembre 2021 at 8:12

    I see something truly special in this internet site.

    Reply

  • togel singapore data akurat

    21 Ottobre 2021 at 19:18

    Normally I do not learn article on blogs, however I wish to say that this write-up very compelled me to check out and do it! Your writing style has been surprised me. Thanks, very great post.

    Reply

  • miniso in new york

    22 Ottobre 2021 at 13:39

    I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!…

    Reply

  • the runit dome

    22 Ottobre 2021 at 19:20

    It’s the best time to make a few plans for the long run and it is time to be happy. I’ve read this put up and if I may just I want to counsel you few interesting things or advice. Perhaps you can write next articles regarding this article. I wish to learn more things approximately it!

    Reply

  • situs slot online

    2 Novembre 2021 at 10:08

    What i do not understood is in reality how you’re no longer actually a lot more neatly-preferred than you may be now. You’re so intelligent. You understand thus considerably on the subject of this matter, made me for my part consider it from a lot of various angles. Its like men and women don’t seem to be interested unless it is one thing to accomplish with Lady gaga! Your own stuffs excellent. Always take care of it up!

    Reply

  • slot online

    2 Novembre 2021 at 10:45

    Your style is so unique compared to many other people. Thank you for publishing when you have the opportunity,Guess I will just make this bookmarked.2

    Reply

  • situs slot online

    2 Novembre 2021 at 11:26

    Good day! I know this is kinda off topic but I was wondering which blog platform are you using for this website? I’m getting tired of WordPress because I’ve had issues with hackers and I’m looking at alternatives for another platform. I would be awesome if you could point me in the direction of a good platform.

    Reply

  • slot

    2 Novembre 2021 at 12:03

    Have you ever thought about adding a little bit more than just your articles? I mean, what you say is fundamental and all. However think about if you added some great graphics or video clips to give your posts more, “pop”! Your content is excellent but with pics and clips, this website could undeniably be one of the greatest in its field. Amazing blog!

    Reply

  • situs judi slot online

    2 Novembre 2021 at 12:40

    Great line up. We will be linking to this great article on our site. Keep up the good writing.

    Reply

  • curly girl gel

    2 Novembre 2021 at 22:12

    I very thankful to find this website on bing, just what I was looking for : D likewise saved to favorites.

    Reply

  • Colpi di sole biondo

    6 Novembre 2021 at 23:28

    I like what you guys are up also. Such intelligent work and reporting! Carry on the superb works guys I’ve incorporated you guys to my blogroll. I think it will improve the value of my website 🙂

    Reply

  • ceste natalizie

    7 Novembre 2021 at 0:14

    Hiya, I’m really glad I have found this info. Today bloggers publish just about gossips and net and this is actually frustrating. A good website with interesting content, that is what I need. Thank you for keeping this web site, I’ll be visiting it. Do you do newsletters? Cant find it.

    Reply

  • affitto magazzini a Como

    7 Novembre 2021 at 1:31

    I conceive other website proprietors should take this website as an model, very clean and good user genial design.

    Reply

  • avvocato Milano centro

    7 Novembre 2021 at 2:05

    Thanx for the effort, keep up the good work Great work, I am going to start a small Blog Engine course work using your site I hope you enjoy blogging with the popular BlogEngine.net.Thethoughts you express are really awesome. Hope you will right some more posts.

    Reply

  • cerco milf

    8 Novembre 2021 at 12:11

    Hi, Neat post. There’s a problem with your web site in internet explorer, would test this… IE still is the market leader and a good portion of people will miss your wonderful writing due to this problem.

    Reply

  • ドックカフェ 滋賀

    8 Novembre 2021 at 14:02

    This design is incredible! You obviously know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!

    Reply

  • https://unique-italia.casino/

    11 Novembre 2021 at 12:50

    I like what you guys are up too. Such intelligent work and reporting! Carry on the excellent works guys I¦ve incorporated you guys to my blogroll. I think it’ll improve the value of my web site 🙂

    Reply

  • Server VPS Italia

    13 Novembre 2021 at 5:42

    F*ckin’ amazing things here. I am very glad to see your article. Thanks a lot and i’m looking forward to contact you. Will you kindly drop me a mail?

    Reply

  • bandar togel

    22 Novembre 2021 at 18:42

    I believe you have mentioned some very interesting details , thanks for the post.

    Reply

  • gravidanza eboli

    23 Novembre 2021 at 17:03

    Its fantastic as your other articles : D, appreciate it for posting. “Experience is that marvelous thing that enables you to recognize a mistake when you make it again.” by Franklin P. Jones.

    Reply

  • wordpress tutorial 2022

    7 Dicembre 2021 at 0:17

    Wonderful site. Plenty of useful information here. I?¦m sending it to a few pals ans also sharing in delicious. And obviously, thank you in your effort!

    Reply

  • day spa near me

    7 Dicembre 2021 at 11:56

    I like this web site so much, saved to fav. “Respect for the fragility and importance of an individual life is still the mark of an educated man.” by Norman Cousins.

    Reply

  • Amelie

    11 Gennaio 2022 at 1:07

    You should take part in a contest for one of the most useful sites on the net.
    I’m going to highly recommend this site!

    Reply

  • Sherman

    26 Gennaio 2022 at 3:33

    Thank you, I’ve just been looking for info about this subject for a while and yours is the greatest I have found out so far.
    However, what in regards to the bottom line? Are
    you sure concerning the source?

    Reply

  • Tayla

    27 Gennaio 2022 at 18:59

    Neat blog! Is your theme custom made or did you download it
    from somewhere? A theme like yours with a few simple tweeks would really make my blog stand out.
    Please let me know where you got your design. Bless you

    Reply

  • Colin

    28 Gennaio 2022 at 19:25

    Hey! This is kind of off topic but I need some advice
    from an established blog. Is it hard to set up your own blog?
    I’m not very techincal but I can figure things out pretty quick.
    I’m thinking about setting up my own but I’m not sure where to begin. Do
    you have any points or suggestions? Cheers

    Reply

  • houston junk car buyer

    4 Maggio 2022 at 4:41

    Do you have a spam problem on this blog; I also am a blogger, and I was curious about your situation; many
    of us have developed some nice practices and we are looking to swap solutions with others,
    please shoot me an e-mail if interested.

    Reply

  • houston junk car buyer

    5 Maggio 2022 at 12:55

    I wanted to thank you for this excellent read!!
    I absolutely loved every little bit of it. I have got you book
    marked to check out new stuff you post…

    Reply

  • Garage Door Service in Avondale

    11 Maggio 2022 at 12:21

    I am usually to running a blog and i really appreciate your content. The article has really peaks my interest. I am going to bookmark your web site and hold checking for brand new information.

    Reply

  • zorivare worilon

    29 Giugno 2022 at 3:17

    This is a very good tips especially to those new to blogosphere, brief and accurate information… Thanks for sharing this one. A must read article.

    Reply

  • zoritoler imol

    4 Novembre 2022 at 15:52

    With everything that appears to be developing throughout this area, many of your points of view are actually quite refreshing. Having said that, I beg your pardon, but I do not give credence to your entire idea, all be it radical none the less. It seems to everyone that your commentary are not completely rationalized and in reality you are your self not entirely convinced of the argument. In any case I did appreciate examining it.

    Reply

  • Nakliyat

    9 Settembre 2023 at 8:08

    Highly descriptive article, I liked that bit. Will there
    be a part 2?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

previous
Eseguire php da un altro file php in modo asincrono, PHP Asincrona
next
Raspberry Pi installa raspbian jessie: preparare una card SD con MAC