Doubt export and import Query to excel!

How to get the best out of your database.
Post Reply
marcioagr
Posts: 43
Joined: Sun Aug 23, 2020 7:07 am
Company: Fazendas Reunidas Laia & Souza
City / Town: INHAPIM
Product: Irricad Standalone

Doubt export and import Query to excel!

Post by marcioagr »

Hi!
I am already quite understanding of the Query editor in the database, now I am not able to code to export and import to excel.
I would like to know if this is possible, as my demand would be to make price updates in excel and using the Query function to import everything with just one click and update the items in Dbase.
Attachments
Mostra Diâmetros de Tubulação e HW(Sem Tapes).txt
(260 Bytes) Downloaded 79 times
01_Query.PNG
01_Query.PNG (95.3 KiB) Viewed 1389 times
Last edited by marcioagr on Wed May 05, 2021 12:59 am, edited 1 time in total.

User avatar
jovivier
Site Admin
Posts: 674
Joined: Thu Mar 12, 2009 2:57 pm
Company: LVL
City / Town: Blenheim
Location: Lincoln, New Zealand
Contact:

Re: Doubt export and import Query to excel!

Post by jovivier »

A query can be written to update the different cells with the required information from a spreadsheet.

As an example for the Tutorial database,
I have a spreadhsheet called Test4.xls saved on my C:drive which contains the following data:-
Warehouse Code Wholesale Retail
3-6-4 EH 111 112
3/4EVALVE 221 222
1EVALVE 331 332
3EVALVE 441 442
7-4 PT 551 552
WH4ACTIVE 661 662
4GVALVE 771 772
test4.xls
(25.5 KiB) Downloaded 86 times

To update the relevant items items with the new pricing based on warehouse codes each item must have unique warehouse codes.
The query I used was
DROP TABLE Temp~SELECT T1.* INTO Temp FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\test4.xls].[Sheet1$A1:U65536] AS T1~UPDATE Components INNER JOIN Temp ON Components.[Warehouse Code] = Temp.[Warehouse Code] SET Components.Reals7 = Temp.Wholesale WHERE (((Components.[Warehouse Code])=[Temp].[Warehouse Code]))~UPDATE Components INNER JOIN Temp ON Components.[Warehouse Code] = Temp.[Warehouse Code] SET Components.Reals8 = Temp.Retail WHERE (((Components.[Warehouse Code])=[Temp].[Warehouse Code]))~SELECT Description, [Warehouse Code], Reals7 AS Wholesale, Reals8 AS Retail FROM Components WHERE [Database Number] = 2

To explain each part of the query

Read the info into the temp table
SELECT T1.* INTO Temp FROM [Excel 8.0;HDR=YES;IMEX=1;Database=D:\test4.xls].[Sheet1$A1:U65536] AS T1

Update wholesale prices
UPDATE Components INNER JOIN Temp ON Components.[Warehouse Code] = Temp.[Warehouse Code] SET Components.Reals7 = Temp.Wholesale WHERE (((Components.[Warehouse Code])=[Temp].[Warehouse Code]))

Update retail prices
UPDATE Components INNER JOIN Temp ON Components.[Warehouse Code] = Temp.[Warehouse Code] SET Components.Reals8 = Temp.Retail WHERE (((Components.[Warehouse Code])=[Temp].[Warehouse Code]))

Check the result
This option will list all of the valves (Database Number 2)
SELECT Description, [Warehouse Code], Reals7 AS Wholesale, Reals8 AS Retail FROM Components WHERE [Database Number] = 2


This format can be edited for different requirements.

Note that the above is for XLS files only. This will not work for XLSX files.

The IRRICAD Team

marcioagr
Posts: 43
Joined: Sun Aug 23, 2020 7:07 am
Company: Fazendas Reunidas Laia & Souza
City / Town: INHAPIM
Product: Irricad Standalone

Re: Doubt export and import Query to excel!

Post by marcioagr »

Ok Jo!
Understood.
Thank you very much.

User avatar
Dan Crosby
Site Admin
Posts: 140
Joined: Thu Apr 30, 2009 9:28 am
Company: Lincoln Agritech Ltd.
City / Town: Lincoln
Location: Lincoln, New Zealand
Contact:

Re: Doubt export and import Query to excel!

Post by Dan Crosby »

Hi Marcio,

In addition to Jo's import query, you may find the equivalent export query useful to get data from your database into an Excel spreadsheet:

INSERT INTO [Excel 8.0;HDR=YES;IMEX=0;Database=D:\test2.xls].[Sheet1$] SELECT Description, [Warehouse Code] AS WarehouseCode, Reals7 AS Wholesale, Reals8 AS Retail FROM Components WHERE [Database Number] = 2

Also note that these queries (in this form) only work with XLS Excel files, not XLSX ones.
Attachments
test2.xls
(24 KiB) Downloaded 89 times

Dan Crosby
Irricad Developer
Lincoln Agritech Ltd

marcioagr
Posts: 43
Joined: Sun Aug 23, 2020 7:07 am
Company: Fazendas Reunidas Laia & Souza
City / Town: INHAPIM
Product: Irricad Standalone

Re: Doubt export and import Query to excel!

Post by marcioagr »

OK friend!!
thank you very much for the information.

Post Reply