Updating a table
Submitted by markhughes on Mon, 2007-02-19 14:18.
I was wondering....
If I had a text file of borrower numbers, and would like to update a table (e.g. the borrower table) using this text file, how would I go about writing the SQL to take it's input from the text file?
E.g. If I wished to update the Borrower Type from a text file containing a list of borrower numbers.
Can this be done?
Cheers
Mark
updating from a text file
There are a number of ways although I don't think it would be a SQL statement in itself.
The best approach would probably be a PERL script to do the input stuff and then using SQL statements.
It could also be done in UNIX shell scripts I guess.
However it may be possible using the borrower import script providing the input information is correctly formatted e.g. the right tags. It should be possible to set up borrower import so that data is not overwritable except for the borrower type although it is something that needs to be tested (on the MIS server) to be certain.
However with any import of data the format of the incoming data is going to be critical e.g. what is the delimiter, are you using borrower type codes, names or TYPE_STATUS values etc and how can you change the format of the incoming file.
With any type of database update you would have to be very careful.
Thanks
Brian Crampton
Developer, Talis
Updating from a text file
Hello,
I'm wanting to do something along these lines as part of our RFID implementation. I need to update a table we have created on Talis from a text or csv file generated from the portable RFID workstation, this file contains barcodes or ID's of tagged items. I could then cross reference this table against the item table and place traps for on loan items or create lists of the stock that still requires tagging. I'll also be able to use the process as a stock check at the same time and remove any items not found and not on loan.
Any help would be much appreciated
Regards
John
inserting data from a file
It may not be necessary to insert the data from the file into a database table. The data can be read into an array within the PERL script and then the array can be manipulated. This approach obviously depends on exactly what you are wanting to achieve and if you need to keep a record of the items in the database.
Even if are going to insert the data into a table I tend to put the data into an array first and then use the data in the array to insert into the table. It allows you to check the data and remove any anomalies before the database inserts.
Below is a extract from a script that would read in data from an pipe-delimited file and create an array called @rows. The first data element is the item barcode followed by undefined data elements.
The @rows array can then be processed. In the example it is just updating the item type.
($path_file) = "/scratch/input_file.out";
($delimiter) = "~";
open(INPUT_FILE, $path_file);
while ()
{
($record) = "";
chop;
next if /^#/;
next if ($_ eq "");
($position01,$position02,$position03,
$position04,$position05,$position06)= denull(despace(split('\|',$_)));
$position01 .= $delimiter;
$position02 .= $delimiter;
$position03 .= $delimiter;
$position04 .= $delimiter;
$position05 .= $delimiter;
$position06 .= $delimiter;
($record)= $position01 . $position02 . $position03 . $position04 . $position05 .
$position06;
push(@rows,$record);
}
foreach $row(@rows)
{
($barcode,$variable02,$variable03,
$variable04,$variable05,$variable06) = denull(despace(split('~',$row)));
print "$barcode,$variable02,$variable03,$variable04,$variable05,$variable06\n";
$sql($d,"update ITEM set TYPE_ID=20 where BARCODE like "$barcode"");
}
As I said this is a section of a possible script and I haven't included the code that would be required around the example. I can look at creating an article to expand on this concept but I can't guarantee when this would be available so please post any questions and I will try to expand.
For your specific example it does depend on your exact requirements both in terms of the structure of the data file and also what you mean by traps and tagging.
Thanks
Brian Crampton
Developer, Talis
Hi Mark, I would echo
Hi Mark, I would echo Brian's warning to be careful, as this sort of thing, if it goes wrong, can do a lot of damage in a very small fraction of a second... However a perl script containing something like this:
sub Do_main
{
open (INPUT, "/users/report/filename");
while () {
chop;
($barcode) = unpack("A8", $_);
($result) = &sql($d,"
UPDATE BORROWER
SET WHATEVER_FIELD = 'some value'
WHERE BARCODE='$barcode'
");
}
ammended as required locally, could do the trick.
Here's a chunk of a perl
Here's a chunk of a perl script that reads in people's barcodes and registration numbers from an external field and then finds the record from the barcode and updates the registration number field. The input data in our case is an 8 character barcode, a comma and a 7 character reg number:
sub Do_main
{
# Main processing loop.
open (INPUT, "/users/report/addreg.data");
# Assign data from the file to variables
while () {
chop;
($barcode,$comma,$regno) = unpack("A8A1A7", $_);
($result) = &sql($d,"
UPDATE BORROWER
SET REGISTRATION_NUMBER='$regno',
NOTE=NOTE+' Regno ammended 12.10.06'
WHERE BARCODE='$barcode'
");
print LOG "BARCODE:$barcode Regno:$regno \n";
# End of processing that line - get next line
}
# End of processing file
close INPUT;
print LOG "\n\n\nFinished running regno change ... ";
Use with care!!!
Steve