Import Excell XLS Spreadsheet and write to Mysql database

Asked

Viewed 646 times

0

NOTE: First excuse some ignorance, but I am beginner and do not understand in advanced PHP and SQL

PROBLEM:

I have some spreadsheets where I get some information, which is updated every 6 hours. The main difficulty is when I need to compare some information between the last 2.3 spreadsheets for example, because I need to open one and then the other and see the values, etc..

I would like to import and record this information in a Mysql database, because then I can better manipulate the information.

INITIAL IDEA:

Create a Mysql database and a specific table with the same worksheet columns, ADDING some extra columns and save the information in that database. But for lack of advanced knowledge in PHP and SQL, I could not find the best way to do this process.

I even found some examples but they were a little complex and I couldn’t understand.

Example: Summary of the structure of the TABLE in the database:

inserir a descrição da imagem aqui

To summarize: I need to import the XLS file and save to the database by inserting some columns that are not present in the file.

php javascript html sql

  • What you’ve already done?

  • @Christianfelipe , for now nothing but simple INPUT and INSERT testing, was seeing the examples I found. So I didn’t post anything to see the examples and ideas that you have experience to do the best way.

1 answer

2


you can connect to the XLS spreadsheet via an ODBC connection, example:

$excel = odbc_connect("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq={$caminhoComplePlanilhaXLS};DefaultDir=C:\\" , '', '');

Done this you will get the variable $excel with the connection Resource, then you can query the sheet data as if it were a database table, example:

$sel = 'Select * from [MinhaPlanilha$A1:D500]';
$rs = odbc_exec($excel, $sel);

while (odbc_fetch_row($rs)) {
   $col1 = odbc_result($rs, 1);
   $col2 = odbc_result($rs, 2);
}

This is a very simple way... If not so recommend using the Phpexcel.

Browser other questions tagged php javascript html sql

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