Setting Up Importdb Automatic? (LawsonTalk)

Title: Setting Up Importdb Automatic?
Description: 2 Files APCINVOICE / APCDISTRIB

Mil0n023 - October 24, 2006 05:06 PM (GMT)

Ok, so I have the EPIC to LAWSON Pt Refung and GL processing perfectly. Now, for the testing here I had to manually import the .csv files to the appropriate tables (GLTRANSREL, APCINVOICE, & APCDISTRIB)

Question: Can I set up an automatic process to run these imports from a specified directory? I am hoping that EPIC can just ftp the file to $LAWDIR/prod/apps/work/file.csv1 ... etc

:cof:

trezaei - October 24, 2006 06:28 PM (GMT)

Yes absolutely you can. In fact there are probably too many ways to do so.
From your use of $ instead of % I am assuming you're a UNIX client. So I would recommend writing a small Perl script that does what you need to do.

You can then either have cron or the Lawson Job Scheduler kick off your script at a certain time.

If you have BCI you can run the scanfileclient to have the files picked up as they're placed on the server.

you can also do this using shell script but I like Perl better. It's a matter of personal pref.

Mil0n023 - October 24, 2006 07:05 PM (GMT)

Would you happen to already have one I can copy? (I don't really know how to do it)

:bow: :bow:

Milo - October 24, 2006 08:03 PM (GMT)

Here is one of our import scripts. It's designed to be run manually, however it can be automated, as Trezaei said. By "Manually" I mean that it has been "tokenized" and the user just clicks on the menu item. Setup is done in TokenDef - User Form IDs. Also note that we have some user variables in there as well. (Not exactly what you wanted, but still useful.)

If you're interested in the support scripts, specifically the simple but powerful logging system that I wrote to control log files, shoot me an email at mtsukroff at-sign phcs.com, and I'll send them over to you. Otherwise, just use this as a reference.

Sorry, this bulletin board system screws up the fancy indenting.

CODE

# PHLAW.JATimport.sh rev W3
# script for import of JAT ADPFILENBR and ADPDEDINFO files
# W2 - MTsukroff 11-11-04 Add ADPDEDGOAL file load.
# W3 28-Nov-2005 MTsukroff Added std log file

# Check for presence of product line parameter
if [ $# -ne 1 ]
then
echo "usage: PHLAW.JATadpfno.sh [prod | test ]"
exit 1
fi
IPRDLINE=$1

# CREATE LOG FILE HEADER
. PHLAW.logfile.sh JATimport W3
# Returns LAWSHLOG

echo "" >> $LAWSHLOG
echo "=====> JAT Employee and Deduction Control File Import - Target: $IPRDLINE" >> $LAWSHLOG
echo "" >> $LAWSHLOG

# Check for and move to Import Directory
cd $LAWUDMP
if [ ! -d "payexp" ]
then
echo "Cannot find Import Directory $LAWUDMP/payexp!" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG
echo "Terminating abnormally!" | tee -a $LAWSHLOG
exit 1
fi
cd payexp

# -----------------------------------------------------------------------------
# Check for Employee Import File, record create date
if [ ! -f "adpfilenbr.csv" ]
then
echo "Cannot find import file $LAWUDMP/payexp/adpfilenbr.csv" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG
echo "Terminating abnormally!" | tee -a $LAWSHLOG
exit 1
fi

echo "" | tee -a $LAWSHLOG
echo "Importing JAT Employee Control Data to Product Line $IPRDLINE" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

echo "Source File Specifics:"
ls -l adpfilenbr.csv | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

# Perform the specified import
importdb -af $IPRDLINE adpfilenbr $LAWUDMP/payexp/adpfilenbr.csv 2>&1 | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

# -----------------------------------------------------------------------------
# Check for Deduction Import File, record create date
if [ ! -f "adpdedinfo.csv" ]
then
echo "Cannot find import file $LAWUDMP/payexp/adpdedinfo.csv" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG
echo "Terminating abnormally!" | tee -a $LAWSHLOG
exit 1
fi
echo "Importing JAT Deduction Control Data to Product Line $IPRDLINE" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

echo "Source File Specifics:"
ls -l adpdedinfo.csv | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

# Perform the specified import
importdb -af $IPRDLINE adpdedinfo $LAWUDMP/payexp/adpdedinfo.csv 2>&1 | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

# -----------------------------------------------------------------------------
# Check for Goals and Limits Import File, record create date
if [ ! -f "adpdedgoal.csv" ]
then
echo "Cannot find import file $LAWUDMP/payexp/adpdedgoal.csv" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG
echo "Terminating abnormally!" | tee -a $LAWSHLOG
exit 1
fi
echo "Importing JAT Goals and Limits Control Data to Product Line $IPRDLINE" | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

echo "Source File Specifics:"
ls -l adpdedgoal.csv | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG

# Perform the specified import
importdb -af $IPRDLINE adpdedgoal $LAWUDMP/payexp/adpdedgoal.csv 2>&1 | tee -a $LAWSHLOG
echo "" | tee -a $LAWSHLOG
echo "Hit 'Esc' (Escape Key) to return to PHCS Employee Data Interface Menu" >> $LAWSHLOG
lashow $LAWSHLOG

# -----------------------------------------------------------------------------
# end of shell script

lawson - October 24, 2006 08:09 PM (GMT)

Hi Milo,

In the future you can use th CODE tags (see button when composing) to paste things with nice formatting such sas:

CODE

sub myFunc {
print "Hello World\n";
}

I took the liberty to fix your post ... :)

Mil0n023 - October 24, 2006 08:17 PM (GMT)

Um, ok. Am I just replacing the table names? :blink:

trezaei - October 24, 2006 08:28 PM (GMT)

Not exactly :)

It looks to me that script is customized to Milo's server. You can use it as an example to write your own. What you're asking for is really about 1 day of consulting to get a custom solution for your site. Or if you have someone in house (like your sysadmin person) that knows shell scripting ... they can definitely understand and help.

Just be careful when testing cause you could have some unintentional consequences ...

View Full Version: Setting Up Importdb Automatic?