Import Excell XLS Worksheet and write to MySQL database

Question

Import Excell XLS Worksheet and write to MySQL database

Navigation

#1 by (2 votes)

0

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 worksheets for example, since I need to open one and then the other and see the values, etc.

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

INITIAL IDEA:

Create a MySQL database and a specific table with the same columns of the worksheet, ADDING some extra columns and saving 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 bit complex and I could not understand.

Example: TABLE summary of the bank:

In short: I need to import the XLS file and save it to the bank by inserting some columns that are not present in the file.

php sql javascript html

asked by anonymous 20.03.2017 / 13:52

1 answer

How to render children in React? Pick up only numbers from a String (Tokenizer)

score 2 · Accepted Answer

You can connect to the XLS worksheet through an ODBC connection, for example:

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

When you do this you will have the $excel variable with the connection resource, so you can query the data in the spreadsheet as if it were a database table, for 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, I recommend using PHPExcel .