Importing Data into Your App from Excel, XML or CSV files
Data may be gathered for importion into your App from any outside source. Automating the importation process so users do not have to labouriously enter data can save valuable resources.
Due to the complexity of Orixa data the importation process often requires careful work and thought to ensure that the relational connections built into the database are preserved during the importation process.
For a one-off importation process the Developer can craft a single SQL Statement using the IMPORT TABLE SQL Keywords. For more frequent data-importation processes the Developer may wish to write a SQL Procedure which other users can run.
Before you begin
- Find the data you wish to import and if the process is to be run repeatedly ensure you have a set of steps that will always produce data with exactly the same format. A data importation procedure will fail if the source CSV File is not correctly formatted to its expectations.
- Review how you will generate the Orixa IDs or Codes in the CSV file that allow you to link data together in your App. It may be that users creating the CSV File must be given a reference file, containing IDs for them to use, or some other link-field must be used.
Importing using the IMPORT TABLE SQL Statement
In simple and one-off cases the Developer can write SQL to import data. This actually works in a similar way to the CSV Importation Utility, but as the Developer can also create temporary tables and run multiple SQL Statements allows more flexibility.
IMPORT TABLE [Table or View Name]
FROM [FileName]
IN STORE [StoreName]
[([ColumnName][,[ColumnName]])]
[FORMAT DELIMITED|XML]
[ENCODING AUTO|ANSI|UNICODE]
[DELIMITER CHAR [DelimiterChar]]
[QUOTE CHAR [QuoteChar]]
[DATE FORMAT [DateFormat]]
[TIME FORMAT [TimeFormat] [AM LITERAL [AMLiteral] PM LITERAL [PMLiteral]]]
[DECIMAL CHAR [DecimalChar]]
[BOOLEAN TRUE LITERAL [TrueLiteral] FALSE LITERAL [FalseLiteral]]
[USE HEADERS]
[MAX ROWS [MaxRowCount]]
Example of the IMPORT TABLE Statement
IMPORT TABLE "Valuations"
FROM "ImportsJune2021.csv"
IN STORE "Imports"
Note from the above that it is rarely necessary to use the additional keyword of the IMPORT TABLE SQL Statement, if the CSV file is well formed.
Worked Example: Importing Stock Valuations from an Online source into an Orixa App
Orixa has been used to make a Pension Fund Management App. The Pension Fund managers buy stocks and shares for their fund. The Fund managers want to keep records of the variation of stock-values over time so they can assess which stocks to buy.
The following example shows how they have done this. Your Developer can replicate this process within your Orixa App with other data sources.
CSV File with Stock Values
They are sent a daily CSV file containing hundreds of stock values. This file comes in a standard, simple format they know will always be the same, as shown in the image above.
- The first column of the CSV File contains the Stock "Symbol", a unique code that identifies the stock.
- Once of the other columns ("G" in this case) contains "today's price" for this stock.
Their Orixa App contains a "Valuations" data-table, with the following structure:
CREATE TABLE "Valuations"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"StocksID" INTEGER DESCRIPTION 'DefaultRI',
"DateDone" DATE DEFAULT CURRENT_DATE,
"Price" DECIMAL(19,4),
"DateCreated" TIMESTAMP DEFAULT Current_Timestamp,
"Complete" BOOLEAN DEFAULT false,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ID"),
CONSTRAINT "StocksID" FOREIGN KEY ("StocksID") REFERENCES "Stocks" ("ID")
DESCRIPTION 'Parent'
)
Note that this data-table contains a "StocksID" and a price. To import the data all that is needed is to copy in the Price data, together with a date, and the StocksID. However, the StocksID is not the same as the Stock "Symbol"
Their Orixa App contains a "Stocks" data-table, with the following structure:
CREATE TABLE "Stocks"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"Symbol" VARCHAR(10) COLLATE "ANSI",
"Name" VARCHAR(60) COLLATE "ANSI" NOT NULL,
"Description" CLOB COLLATE "ANSI",
[-table definition continues-]
To make the importation work write a procedure to:
- Import all the data from the CSV file into a temporary data-table. Note that the procedure must first test whether the temporary table already exists, and call DROP to remove it if it does.
- Run an INSERT statement which coalesces the data from the temporary data-table with data from the "Stocks" data-table, to match the Symbol and link it to the StocksID.
Full listing of the "ImportStockValuations" procedure
PROCEDURE "ImportStockValuations" (IN "aCSVFileName" VARCHAR(120) COLLATE "ANSI", IN "aDateDone" DATE)
BEGIN
DECLARE Crsr CURSOR WITH RETURN FOR Stmt;
DECLARE Names CLOB;
DECLARE Symbol VARCHAR;
DECLARE Stock VARCHAR;
--check existence of temp table and drop if present
PREPARE Stmt FROM
' SELECT * FROM Information.TemporaryTables
WHERE Name = ''ImportValuations'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
EXECUTE IMMEDIATE
' DROP TABLE "ImportValuations"';
END IF;
--create temp-table
EXECUTE IMMEDIATE
' CREATE TEMPORARY TABLE "ImportValuations"
("Code" VARCHAR(10) COLLATE "ANSI",
"Sedol" VARCHAR(50) COLLATE "ANSI",
"Stock" VARCHAR(100) COLLATE "ANSI",
"Units" VARCHAR(50),
"UnitCost" VARCHAR(50),
"Price" VARCHAR(50),
"PriceP" VARCHAR(50),
"ValueP" VARCHAR(50),
"CostP" VARCHAR(50),
"GL" VARCHAR(50),
"GLPercent" VARCHAR(50),
"DayChange" VARCHAR(50),
"DayChangePercent" VARCHAR(50)) ';
EXECUTE IMMEDIATE
' SET FILES STORE TO "Imports" ';
--find the proposed import file, to check the user has the name right
PREPARE Stmt FROM
' SELECT LIST(UPPER(Name)) as Names FROM Configuration.Files
WHERE UPPER(Name) LIKE ''%.CSV'' ';
OPEN Crsr;
FETCH FIRST FROM Crsr('Names') INTO Names;
IF POSITION(UPPER(aCSVFilename), ' ' + Names) > 0 THEN
EXECUTE IMMEDIATE
' IMPORT TABLE "ImportValuations"
FROM "' + aCSVFileName + '"
IN STORE "Imports"
FORMAT DELIMITED ';
EXECUTE IMMEDIATE
' RENAME FILE "' + aCSVFileName + '"
IN STORE "Imports" TO "' + REPLACE('csv' WITH 'OLD' IN aCSVFileName) + '"';
PREPARE Stmt FROM
' SELECT
Code,
REPLACE('''''''' WITH ''"'' IN Stock) AS "Stock"
FROM ImportValuations
WHERE NOT Code IN ( SELECT Symbol FROM Stocks ) ';
OPEN Crsr;
FETCH FIRST FROM Crsr('Code', 'Stock') INTO Symbol, Stock;
WHILE NOT EOF(Crsr) DO
IF ("Symbol" IS NOT NULL) AND ("Symbol" <> '') THEN
EXECUTE IMMEDIATE
' INSERT INTO Stocks
(Symbol, Name, WatchListID)
VALUES
(''' + Symbol + ''', CAST(''' + Stock + ''' AS VARCHAR(60)), 22168 ) ';
FETCH NEXT FROM Crsr('Code', 'Stock') INTO Symbol, Stock;
END IF;
END WHILE;
EXECUTE IMMEDIATE
' INSERT INTO Valuations
(StocksID, DateDone, Price)
SELECT
S.ID,
DATE ''' + CAST(aDateDone AS VARCHAR) + ''',
CAST(I.PriceP as FLOAT) / 100
FROM ImportValuations I
LEFT JOIN Stocks S ON S.Symbol = I.Code
WHERE I.Code IN
(SELECT Symbol FROM Stocks) ';
END IF;
END
What the different parts of the procedure do
PREPARE Stmt FROM |
Open a statement which tests whether the "ImportValuations" temporary table already exists. If it does DROP it. |
EXECUTE IMMEDIATE
|
Run a statement to CREATE the "ImportValuations" temporary table. Important: Note that the structure of this temporary table exactly matches the number of columns used in the CSV file.
|
EXECUTE IMMEDIATE
| Actually call the "IMPORT TABLE" SQL statement |
EXECUTE IMMEDIATE | This section of the procedure actually INSERTs data. Note how the ImportValuations Temporary table is JOINed to the permanent "Stocks" data-table to ensure that the Valuations data is imported with the correct StocksID. |
Worked Example: Importing Paypal Online Sales Transaction Data into an Orixa App
In this example a business sells their products online using Paypal as the transaction management system to handle payments from customers. A procedure has been written to import data from Paypal directly into the Orixa App to avoid re-keying sales information.
Paypal Raw Data in CSV File
The Paypal online portal allows users to export account data in CSV format. The above image shows and example of this.
- Customer name (blurred for privacy reasons).
- Line-item Type.
- Gross value in GBP of the sales item.
- Paypal's own Transaction ID. This is imported into the Orixa App to ensure it is possible to trace the order back to its Paypal data if needed.
- Email details for the customer (blurred for privacy reasons)
- Item ID, this is the unique code used in the Orixa App to identify the products that have been ordered.
SQL of the "PayPalImport" procedure
CREATE PROCEDURE "PayPalImport" (IN "aFileName" VARCHAR(200) COLLATE "ANSI")
BEGIN
DECLARE Crsr CURSOR FOR Stmt;
DECLARE ErrMessage VARCHAR;
PREPARE Stmt FROM
' SELECT * FROM Information.TemporaryTables
WHERE Name = ''ImportOnlineSales'' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
EXECUTE IMMEDIATE
' DROP TABLE "ImportOnlineSales" ';
END IF;
EXECUTE IMMEDIATE
' CREATE TEMPORARY TABLE "ImportOnlineSales"
("Date" DATE,
"Time" VARCHAR(20),
"Time zone" VARCHAR(20),
"Name" VARCHAR(60),
"Type" VARCHAR(50),
"Status" VARCHAR(50),
"Currency" VARCHAR(50),
"Gross" VARCHAR(20),
"Fee" VARCHAR(20),
"Net" VARCHAR(20),
"From Email Address" VARCHAR(120),
"To Email Address" VARCHAR(120),
"Transaction ID" VARCHAR(50),
"Shipping Address" CLOB,
"Item ID" VARCHAR(500),
"Reference TNX ID" VARCHAR(500),
"Quantity" VARCHAR(20),
"Receipt ID" VARCHAR(40),
"Contact Phone Number" VARCHAR(40)
) ';
EXECUTE IMMEDIATE
' SET Files Store TO "Imports"';
PREPARE Stmt FROM
' SELECT * FROM Configuration.Files
WHERE Name = ''' + aFileName + ''' ';
OPEN Crsr;
IF ROWCOUNT(Crsr) > 0 THEN
SET ErrMessage = 'Unable to import data, file-name "' + aFileName + '" may not be found.' + #13 +
'Please confirm this file-name is correct. It should be a simple name (no "path")
and the file must be present in the "Imports" store on the server';
EXECUTE IMMEDIATE
' IMPORT TABLE "ImportOnlineSales"
FROM "' + aFileName + '"
IN STORE "Imports"
FORMAT DELIMITED
DATE FORMAT ''DD/MM/YYYY''
USE HEADERS
';
EXECUTE IMMEDIATE
' UPDATE ImportOnlineSales
SET "Item ID" = ''DEL10''
WHERE (("Item ID" IS NULL) OR ("Item ID" = ''''))
AND Gross <> ''0'' ';
SET ErrMessage = 'Unable to ALTER "ImportOnlineSales" data-table.';
EXECUTE IMMEDIATE
' ALTER TABLE ImportOnlineSales
ADD COLUMN ProductsID INTEGER ';
SET ErrMessage = 'Unable to UPDATE "ImportOnlineSales" data-table. Stage 1 failed.';
EXECUTE IMMEDIATE
' UPDATE ImportOnlineSales
SET
"Gross" = REPLACE('','' WITH '''' IN "Gross"),
"Fee" = REPLACE('','' WITH '''' IN "Fee"),
"Net" = REPLACE('','' WITH '''' IN "Net"),
"Shipping Address" = REPLACE('', '' WITH '','' + #13 IN "Shipping Address"),
"Name" = "Name" + COALESCE(#13 + "Contact Phone Number", '''');';
SET ErrMessage = 'Unable to UPDATE "ImportOnlineSales" data-table. Stage 2 failed.';
EXECUTE IMMEDIATE
' UPDATE ImportOnlineSales IOS
SET ProductsID =
(SELECT ID
FROM Products
WHERE ProdCode = IOS."Item ID") ';
SET ErrMessage = 'Unable to UPDATE "ImportOnlineSales" data-table. Stage 3 failed.';
EXECUTE IMMEDIATE
' UPDATE ImportOnlineSales IOS
SET Quantity = Quantity / (SELECT CountUnits
FROM Products
WHERE ProdCode = IOS."Item ID") ';
END;
The above procedure generates an "ImportOnlineSales data-table, which is well formed and includes all needed data-fields to link into the Orixa App.
A second procedure works through the "ImportOnlineSales" data-table, row by row and INSERTS the records, as is done in the "Valuations" example above.