What is the best way to generate data from my bank for some spreadsheet? [closed]

6

I have a database with 40,000 data (with a column hit), I wanted to generate a report.

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

    
asked by anonymous 29.05.2015 / 17:38

1 answer

-1

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

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

  • Save your spreadsheet with the CSV extension.
  • Create your database in MySQL.
  • Download HeidiSQl, a very friendly open-source front-end to manage your banks - link
  • Connect HeidiSQL to your MySQL server (localhost if it is on your local machine, or on the remote server's IP)
  • Create your database, create the tables that will receive the data. Heidi offers intuitive tools for building your bank.
  • With the database data and tables created, click the Tools menu > Import CSV Data.
  • With the imported data, connect your PHP file to the created database.
  • Another solution is to opt for SQL Server as the database. You can import spreadsheets directly from 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
    
        
    30.08.2016 / 17:50