What is the best way to generate data from my database for some spreadsheet?

Asked

Viewed 357 times

6

I have a database with 40,000 data (with a bang of columns), I wanted to generate a report.

The problem is that in .xls it takes too long to generate the data inside the spreadsheet, there is some better way to implement? .csv is faster to open?

php

1 answer

-1

Save your spreadsheet with the CSV extension. This format allows major databases to easily import their data.

How you work with PHP, it would be interesting to opt for Mysql as a database.

  1. Save your spreadsheet with the CSV extension.
  2. Create your database in Mysql.
  3. Download Heidisql, a very user-friendly open-source front end for managing your banks - http://www.heidisql.com/download.php
  4. Connect Heidisql to your Mysql server (localhost if on your local machine, or on the remote server IP)
  5. Create your database, create the tables that will receive the data. Heidi offers very intuitive tools to create your database.
  6. With the database and tables created, click the Tools > Import CSV Data menu.
  7. With the imported data, connect your PHP file to the database created.

Another solution is to choose SQL Server as a database. It is possible to import spreadsheets directly into the command line.

SELECT * INTO CADASTRO FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=c:ARQUIVO_A_SER_IMPORTADO.xls', 
'SELECT * FROM [NOME_DA_PLANILHA$]')

If this command does not work immediately, make the following changes to your SQL Server;

EXEC sp_configure 'show advanced options',1
GO
reconfigure with override
GO
EXEC sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure with override
GO

Browser other questions tagged php

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