Doubt export and import Query to excel!

[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.

[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

[marcioagr]

Ok Jo!
Understood.
Thank you very much.

[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.

[marcioagr]

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

[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.

[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