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)
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.
43 comments
zovrelioptor
15 Giugno 2021 at 20:16
You are a very clever person!
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.
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.
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.
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.
Marie
24 Agosto 2021 at 21:03
Wonderful, what a web site it is! This website presents useful data to us, keep it up.
gym around me
20 Settembre 2021 at 8:12
I see something truly special in this internet site.
website speed optimization tutorial
14 Ottobre 2021 at 5:56
I conceive this web site holds very good written articles posts.
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.
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!…
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!
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!
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
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.
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!
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.
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.
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 🙂
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.
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.
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.
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.
ドックカフェ 滋賀
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!
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 🙂
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?
enoteca numero uno
17 Novembre 2021 at 21:49
Woh I love your articles, saved to fav! .
Parola Chiave : Autospurgo Roma - Pronto intervento fognature Roma
21 Novembre 2021 at 12:40
Hello. excellent job. I did not expect this. This is a fantastic story. Thanks!
bandar togel
22 Novembre 2021 at 18:42
I believe you have mentioned some very interesting details , thanks for the post.
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.
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!
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.
how to use elementor in website
10 Gennaio 2022 at 23:31
Thankyou for this terrific post, I am glad I observed this internet site on yahoo.
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!
how to build a website 2022
11 Gennaio 2022 at 7:06
I am always searching online for posts that can facilitate me. Thanks!
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?
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
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
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.
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…
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.
hire a hacker to catch cheating spouse
21 Giugno 2022 at 20:34
WONDERFUL Post.thanks for share..more wait .. …
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.
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.