Doubt export and import Query to excel!

How to get the best out of your database.
Post Reply
marcioagr
Posts: 59
Joined: Sun Aug 23, 2020 7:07 am
Company: Autônomo
City / Town: Pratos de Minas
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.
You do not have the required permissions to view the files attached to this post.
Last edited by marcioagr on Wed May 05, 2021 12:59 am, edited 1 time in total.
User avatar
jovivier
Site Admin
Posts: 919
Joined: Thu Mar 12, 2009 2:57 pm
Company: Lincoln Agritech Ltd, IRRICAD Software
City / Town: Lincoln
Location: Canterbury, 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

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
You do not have the required permissions to view the files attached to this post.
marcioagr
Posts: 59
Joined: Sun Aug 23, 2020 7:07 am
Company: Autônomo
City / Town: Pratos de Minas
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: 181
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.
You do not have the required permissions to view the files attached to this post.

Dan Crosby
Irricad Developer
Lincoln Agritech Ltd
marcioagr
Posts: 59
Joined: Sun Aug 23, 2020 7:07 am
Company: Autônomo
City / Town: Pratos de Minas
Product: Irricad Standalone

Re: Doubt export and import Query to excel!

Post by marcioagr »

OK friend!!
thank you very much for the information.
aridzv
Posts: 38
Joined: Tue Jun 29, 2021 6:05 pm
Company: Independent
City / Town: Tel Yossef
Product: Irricad Link (BricsCAD)

Re: Doubt export and import Query to excel!

Post by aridzv »

Hi.
I'm looking for a way to import a pipe list (complete pipe detailed list) in to the irricad data base.
I will be glad to get help with both issues :
1. the excel template.
2. the sql query string, especially with this part (the connection string to excel): [Excel 8.0;HDR=YES;IMEX=0;Database=D:\test2.xls].[Sheet1$]

thanks,
Ari.
User avatar
jovivier
Site Admin
Posts: 919
Joined: Thu Mar 12, 2009 2:57 pm
Company: Lincoln Agritech Ltd, IRRICAD Software
City / Town: Lincoln
Location: Canterbury, New Zealand
Contact:

Re: Doubt export and import Query to excel!

Post by jovivier »

Hi Ari,

Importing large amounts of product into the database is not something we would expect users to do. IRRICAD Databases have a precise format so it is easy to break the database.
However, to answer your question about the meaning of "Excel 8.0;HDR=YES;IMEX=0" - this relates to the version and format of the data. "8.0" relates to Office 97 format.

The IRRICAD Team
Post Reply