Hello everyone, tell me please, how can a request transfer data from the server to the excel.xlsx file? Now I do it by hand through the import / export wizard.

источник Π΄Π°Π½Π½Ρ‹Ρ…: SQL Server Native client 10.0 Π½Π°Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅: Microsoft Excel

    2 answers 2

    Consider two cases:

    1. When we need to generate all the data for a file every time
    2. When we need to write data to a file once and then periodically update it

    First case

    Clean the file before recording and write new data

     UPDATE t SET t.Артикул = '', t.НаимСнованиС = '', t.Π¦Π΅Π½Π° = '', t.Π”Π°Ρ‚Π° = '' FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;', 'SELECT * FROM [Лист1$]') AS t; INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;', 'SELECT * FROM [Лист1$]') SELECT s.code [Артикул], s.name [НаимСнованиС], s.price [Π¦Π΅Π½Π°], s.date [Π”Π°Ρ‚Π°] FROM _table AS s;

    This solution, although it works, but sometimes works crookedly - it happens that new data is inserted after cleared rows. Therefore, I prefer to work like this:

     EXEC master..xp_cmdshell 'del filepath\sales.xlsx'; GO EXEC master..xp_cmdshell 'copy filepath\template.xlsx filepath\sales.xlsx'; GO INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;', 'SELECT * FROM [Лист1$]') SELECT s.code [Артикул], s.name [НаимСнованиС], s.price [Π¦Π΅Π½Π°], s.date [Π”Π°Ρ‚Π°] FROM _table AS s;

    I keep the file template and each time before writing, I delete the old file and create a new one according to the template.

    Second case

    We update the data in the file, identifying lines by (in this case) article.

     UPDATE t SET t.Артикул = s.[Артикул], t.НаимСнованиС = s.[НаимСнованиС], t.Π¦Π΅Π½Π° = s.[Π¦Π΅Π½Π°], t.Π”Π°Ρ‚Π° = s.[Π”Π°Ρ‚Π°] FROM OPENROWSET('Microsoft.ACE.OLEDB.10.0', 'Excel 12.0;HDR=YES;Database=filepath\sales.xlsx;', 'SELECT * FROM [Лист1$]') AS t JOIN ( SELECT code AS Артикул, name AS НаимСнованиС, price AS Π¦Π΅Π½Π°, date AS Π”Π°Ρ‚Π° FROM _table ) AS s ON s.Артикул = t.Артикул;

    In both cases , the following conditions must be met:

    1. The file must be closed.
    2. The columns in the file should be named exactly as in the request and go in the same order.

    PS

    Microsoft.ACE.OLEDB is a 64-bit provider. In case you need 32-bit, you should use Microsoft.Jet.OLEDB .

    Microsoft.Jet.OLEDB.4 & Microsoft.ACE.OLEDB.12 This is the Microsoft.Jet.OLEDB.4.0 provider is a 32-bit SQL Server for Excel 2003 files & the Microsoft.ACE.OLEDB. 12.0 provider is used with SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.

    Here you can download drivers for the provider.
    Find out the version of your server SELECT @@VERSION .

    • This is getting data from Excel to SQL Server. The author needs the opposite effect. - minamoto
    • @minamoto, this is exactly what the author needs. Do not be misleading. - Anatol
    • Yes, sorry, inattentively looked at your request. I agree, it will work. But your request for updating data, not for insertion will work. - minamoto
    • Well, implies the presence of some data in the file at the time of treatment. Tomorrow I will add the answer. - Anatol
    • @Anatol, do I have the right to write a query of the form select * from table, or do I have to list all the fields? - Malice

    In Excel, you can make a pluggable data source and set up periodic synchronization, incl. and with MS SQL Server.

    Customization depends on your version of Office. On the example of the Russian version of Excel 2013:

    1. Create an empty Excel document, go to the "Data" tab, press the "From other sources" button, select "From SQL Server".
    2. Specify the server name, login credentials, click "Next."
    3. Select the desired database, remove the checkbox "Connect to a specific table", click "Finish".
    4. When prompted to replace the connection file, click "Yes". In the "Select Table" window that appears, select any (we will not use it), click "OK".
    5. In the "Import data" window that appears, if necessary, specify the way of presenting the data and where you need to put the data - by default we leave it as it is, click the "Properties" button.
    6. In the "Connection properties" window that appears, go to the "Definition" tab, select the "SQL" command type, insert the SQL query in the "Command text" field to get the necessary data, click the "OK" button, click on "Yes" to change the connection , in the "Import Data" window, click "OK".
    7. We are waiting for the end of the loading process - depending on the "severity" of the request and the amount of data, the loading time can be very different.
    8. If the request needs to be changed - on the "Designer" tab, from the pop-up menu next to the "Update" button, select "Connection Properties", on the "Definition" tab, change the request, apply the changes - the data will be downloaded again.
    • I note, but the fact is that users need to upload data on certain conditions. The users themselves do not know SQL, but a special interface has been written for them, through which they set the conditions for uploading. - Malice
    • @Malice, you can write a macro that will trigger a query with certain parameters. An example of use is, for example, here: mssqltips.com/sqlservertip/3436/… - minamoto