Validate list of data

[MSPhobe]

I am appending data to a database in batches.

The data starts in Calc, one record per row.

I can append these records to a table very easily... thank you brilliant Base!

(Guide to that at http://sheepdogguides.com/fdb/fdb8append_batch.htm

However, even the brilliant Base can't cope with the mistakes people make. Say, for instance, that the first field (both in the table we are appending to, and in the records in the calc sheet) is supposed to be a unique serial number for the widgets a manufacturer is turning out. It would make sense to use the serial number as the primary key for the table. And suppose that Widget 123 is already in the table, and suppose that, through a typo, the human has also put "123" as the serial number of a "new" widget whose record is to be appended to the table.

Base will, understandably, "choke" on a SECOND "123" going into the table.

But... two related problems:

a) Is there a method for "try to append these, and put a list somewhere of any you can't append, but append the ones you can"?

b) Suppose further that the widgets are made on multiple machines. And that the ID of the machine that made a particular widget is part of its record. And that there is a table with all of the valid machine IDs. (Each has a unique ID, of course.)

If I take just the "machine that made this one" column of data from the spreadsheet, the one holding the new records to be appended, save it in a textfile, one machine ID per line... then is there a way to "pass"(?) that textfile to Base, saying "Tell me if any of the values here do NOT appear in the records for "Machine ID" in the table in the database that has all of the machines' details"?

Should I have made this two threads? Seemed a pity, when both questions arise from one scenario. Perhaps it would help readers of the thread if answers began either "Handling invalid records-" or "Check textfile against table-"?

[FJCC]

A text file can be the basis of a table in HSQLDB, the native database engine of Base. Let's assume your text file is called MachineNumbers.txt and it includes a heading . Store the text file in the same directory as the Base file. From the Tools -> SQL menu of Base run the commands

Code: Select all

CREATE TEXT TABLE "MachineNum" ("ID" VARCHAR(100) NOT NULL PRIMARY KEY);

Code: Select all

SET TABLE "MachineNum" SOURCE "MachineNumbers.txt;ignore_first=true"

The first command defines a table called MachineNum with one column called ID and the second command links that table to the text file. You can now make a Foreign Key relationship between this table and the first table you described.

Edit: I didn't actually test those commands, just edited some commands I used to answer another question. If you get errors, assume I made a mistake and post the error here.

[MSPhobe]

Check textfile against table- Dear FJCC- Thank you for the interesting way to move a textfile to a table!

Have I got this right?... The idea was that by your technique (or other), the data in the textfile could be put in a "scratch" table. I could then make a report to tell me of anything in the scratch table which is NOT in the relevant column of the "big" table?

I was hoping for something a little more direct? But I do grasp that my hope may be optimistic, and that, once set up, the process of "1) text - > scratch table, 2) run report" not desperately unwieldly.

(I haven't (yet!) tested the suggested commands. If others are following this, don't assume that I have- sorry. (They make sense, and will surely work either as is or with only minor tidies.))

[FJCC]

Actually, I was thinking of establishing a formal relationship between the two tables so that all values of Machine ID in the main table must appear in the table based on the text file. This is a standard foreign key relationship and the database will not allow entries that don't match the list of allowed values. Try a web search on "database foreign key" and don't hesitate to ask questions if the concept isn't clear.

[MSPhobe]

Check textfile against table- Maybe I should re-cap the reason behind the question...?

The textfile contains the values from one column (field) of some records which I hope to add to an established database. The whole point of "extracting" them is to see **if** they are going to (in the next step) be acceptable to the constraints of the established database. (So that any records with unacceptable entries in that column can be edited BEFORE they are submitted, so that they aren't rejected when the batch is submitted.)

Either I'm misunderstanding the advice you are offering, or it answers a different want?

[FJCC]

The misunderstanding is on my part. My first reaction is that it would be better to use Data -> Validity on the spreadsheet. If the list of machine ids consistent, you can hard code the list in the spreadsheet. Or you can import the list from the data base with a query. Would that work for you?

[MSPhobe]

Check textfile against table-

Or you can import the list from the data base with a query...

Ah! Do you, I hope, mean that there's a clever way to get Calc to fill a column with the values of one field from all records of a Base table? (I can to that by hand... I think.) Is there then a way in Calc to produce a list of "differences", i.e. things that appear in one column (of the worksheet), but not the other? (That would be icing on the cake.)

If "yes" to first question, this may be what I wanted!! Funny how often it turns out that all it takes is asking the right question! I was trying to do this the other way 'round. But this way round would take care of my want nicely.

[FJCC]

This will work if you registered your database. First write a query to display the unique values of machine id. I happen to have a table with a column called
Name and my query to get the unique Name values looks like this
SELECT DISTINCT "Name" FROM "DATOS_TIMESTAMP" WHERE "Name" IS NOT NULLThis will work if you registered your database. First write a query to display the unique values of machine id. I happen to have a table with a column called
Name and my query looks like this

Code: Select all

SELECT DISTINCT "Name" FROM "DATOS_TIMESTAMP" WHERE  "Name" IS NOT NULL

In the Calc file select the menu View -> Data Sources. This will open a window above your data and on the left you should see your database listed. Expand the list
associated with the database by clicking the + next to it and then expand the list of queries. You should see the query you just wrote. Drag that query into the
spreadsheet in a convenient location. You should now have a list of unique machine ids. Select the menu View -> Navigator and expand the Database Ranges list. You should see the data you just imported listed as Import1.

To limit the entry of Machine IDs to the list Import1, Highlight the cells you want to limit to valid machine ids and then select the menu Data -> Validity. On the Criteria tab, choose Allow Cell Range and choose to Allow Empty Cells and to Show Selection List. In the Source box you could just write Import1. The problem with that is that it will allow the use of the
Header of the list of machine ids. To avoid that enter the following in the Source box
OFFSET(Import1;1;0;COUNTA(Import1)-1;1)
On the Error Alert tab of the Validity dialog, choose Show Error Message When Invalid Values are Entered and set the Action to Stop. Click OK

Clicking a cell in the selected range should now provide a drop down list with the valid choices.

If you already have cells filled with data, applying Data -> Validity will not flag the ones with invalid machine ids. You can do that with
Format -> Conditional Formatting. Select the cells you want to affect and notice which cell the cursor ends up in. Let's say it is B2. Go
to the menu Format -> Conditional Formatting and set the first drop down list to Formula Is. In the box next to that enter

Code: Select all

COUNTIF(Import1;B2) = 0

Then click the New Style button, give the style a name on the organizer tab and set other properties to something very noticeable. I would set
the background to red. Click OK and any cells with a machine id not in the Import1 list will get the properties that you set.

[MSPhobe]

BRILLIANT! Thank you so much for the "blow by blow", with all details explained. I can now go off and master that!!

(Anyone else have the answer to the "how to manage rejected bad records during append" part of the question? (Only some of the records can be prepared to succeed with the steps so beautifully explained by FJCC)

[chrisb]

i inadvertently duplicated this post but was unable to delete it!

[chrisb]

hello MSPhobe,

a suggestion on how to manage rejected records during append.
if the data to be appended was temporally placed in a table of its own then we could use SQL to check for data-validity before insertion.

i have called my table "tMy_Original_Table" & the field checked for duplicates is called "ID".
before you start i suggest you make a copy of your table & name it as "tMy_Original_Table".
in the code below you will need to replace all instances of "ID" with the name of the field which is to be checked for duplicates.

copy the data from calc which is to be appended.
paste it into the bottom of the 'Tables' pane making sure that no tables are selected.
name this new table as "Temp" & hit 'Create'. you don't require a primary key.

it's not necessary but you can run this query. it compares the value of the field "ID" contained in both the tables "tMy_Original_Table" & "Temp". it shows only those records where a match is found.
if duplicates are found & your database is reqistered then you could create a list by dragging this query into a writer document.

Code: Select all

select "ID", 'DUPLICATE'
from
"Temp"
join "tMy_Original_Table"
on "tMy_Original_Table"."ID" = "Temp"."ID"

execute the following code from 'menu:Tools>SQ'.
it will insert only those records from the table "Temp" into the table "tMy_Original_Table" where the value of "ID" does not already exist in "tMy_Original_Table".

Code: Select all

insert into "tMy_Original_Table"
select *
from
"Temp"
where "Temp"."ID" not in
(select "ID" from "tMy_Original_Table");

drop table "Temp";

after executing code in Tools>SQL always remember to >>under 'Database' hit the 'Tables' icon then 'menu:View>Refresh Tables'.

[MSPhobe]

Many thanks! Very clear and helpful... and the approach appeals to me, in that the main table is untouched until the records to be appended have had further testing.

A bit of a Catch-22 exists... If a record in the batch file has a flaw... too long for the field it is meant for, for instance, it will no more go into the "scratch" table than into the main table. (But of course the glass is more than half full, as the problem of finding values not in a limited list of possible values, or duplicates where none is acceptable, is well handled.)

I suppose I could make the length allowed in the fields of the scratch table much longer than the limit in the main table, and scan the records-to-be-appended, let the computer point out "too long" records.

Or maybe some SQL, to run through the proposed records, test each for "appendability", move flawed ones to a separate table? ((PS: I promise you... I really was writing this at the same time as chrisb was suggesting exactly that! His help with how it would be done much appreciated))

The quest continues, as ever!... Thanks for all the help, everyone. Hopefully this thread will have been of general use, too.