asked on

Iterate through temp records, process fields, then write to permanent table

Dear Experts,

I apologize that this is a rudimentary question, but my searches through the web have not offered a clear path to a solution.

First, I am an absolute newbie to Access. However, I have been a programmer for 27 years - C, C++, PHP, SQL etc. I say this ONLY because I want you to know I understand programming, so any responses can make the assumption I understand how to structure a program. Please don't flame me.

I have been asked by a friend/colleague to convert a reasonably small text file into a usable Access database. The import of the raw text file itself was very straightforward, and I do have a temporary table already created and loaded accurately. I loaded all raw data as short text

Where this gets confusing for me, is the data in the file contains signed over punch characters (COBOL) in the numeric fields so I need to process those values and write them in a final "processed" table so they are numerically correct for their reporting.

The over punch doesn't confuse me, but how to iterate the temp table, process the fields that have over punch characters, and write the result to the final table does. If this table did not contain the over punch characters, it would be a clean import without the need for processing.

Please consider the following sequence:

1. Raw data loaded to temp table - As mentioned, I have this completed and the import definition is saved. Incidentally, I would like to make this process seamless so when he gets a new raw data file he can kick off a program/module/macro (not sure the correct term) that will do this process "soup-to-nuts".

2. The temp table is iterated through and fields that contain punch data are converted to their numeric equivalent. For the sake of this request, let's just consider two fields - LastName and Balance (which would contain over punch characters).

3. Each column is evaluated at each iteration. Balance data is run through logic that converts the over punch to its numeric equivalent. Again, I know how to do this. I'm old. So, in pseudo code -

If(fieldname == balance) {
take last char of field, evaluate its value, covert to numeric equivalent;
store evaluated value in variable;
remove over punch value from end of original string;
place evaluated value from variable back at the end of original string for insert into permanent table;
}

4. The resulting data is inserted to the permanent table in the correct format from variable.

As I said, I'm a complete newb, so I'm not even sure where to start within Access. Is this a macro, VBA, etc.?

As in most cases, time is a factor, therefore I am assigning 500 points for a viable solution.

Many thanks in advance,
--Mike

IrogSinta

Sounds like this could just be done with a SQL query rather than through code. Can you give a clearer example of the conversion process with before and after results?

mwheeler_fsd

ASKER

Here is an example function of how to convert over punch to a numeric value. I would sincerely give credit to it's author, but I'm not sure exactly who it was. The link where I found this is:

http://www.mrexcel.com/forum/microsoft-access/348563-import-signed-numeric-data.html

Thank you and credit to whomever you are.

Function Signed_Num(theAmt As Variant) As Double
Dim thelast As String
Dim theFrnt As String

thelast = Right(RTrim(LTrim(theAmt)), 1)
theFrnt = Mid$(theAmt, 1, Len(theAmt) - 1)

If thelast = "{" Or thelast = "0" Then
thelast = "0"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "A" Or thelast = "1" Then
thelast = "1"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "B" Or thelast = "2" Then
thelast = "2"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "C" Or thelast = "3" Then
thelast = "3"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "D" Or thelast = "4" Then
thelast = "4"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "E" Or thelast = "5" Then
thelast = "5"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "F" Or thelast = "6" Then
thelast = "6"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "G" Or thelast = "7" Then
thelast = "7"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "H" Or thelast = "8" Then
thelast = "8"
Signed_Num = Val(theFrnt & thelast) / 100

ElseIf thelast = "I" Or thelast = "9" Then
thelast = "9"
Signed_Num = Val(theFrnt & thelast) / 100

'==========================================================

ElseIf thelast = "}" Then
thelast = "0"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "J" Or thelast = "1" Then
thelast = "1"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "K" Or thelast = "2" Then
thelast = "2"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "L" Or thelast = "3" Then
thelast = "3"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "M" Or thelast = "4" Then
thelast = "4"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "N" Or thelast = "5" Then
thelast = "5"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "O" Or thelast = "6" Then
thelast = "6"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "P" Or thelast = "7" Then
thelast = "7"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "Q" Or thelast = "8" Then
thelast = "8"
Signed_Num = Val(theFrnt & thelast) / -100

ElseIf thelast = "R" Or thelast = "9" Then
thelast = "9"
Signed_Num = Val(theFrnt & thelast) / -100

End If

End Function

So, considering two fields for the example in my question:

LastName = 'DOE';
Balance = '12{';

http://en.wikipedia.org/wiki/Signed_overpunch

The balance would actually be 120, DOE would simply be inserted as is. In the permanent table I have Balance defined as a number.

So, I'd like to read the raw temp table data, pass it through logic as above, then insert to the permanent table.

I hope this helps.

Thanks!
--Mike

gr8gonzo

Hah - it's been a while since I've seen a reference to signed overpunch values. Nice. Anyway...

1. Why Access if you're not familiar with it? There's nothing wrong with it for tiny situations, but it doesn't scale well, usually. If you're just looking for a database storage format without the overhead of a server, SQLite might be a better option (with more cross-platform support, too). Every time I've ever used Access as a backend, I've ended up having to convert it at some point. It's not a flame - it's just an observation.

2. It seems like it might be better to preprocess the data file and THEN load the final results into the database, into their correct final columns. Skip the temp tables and all that. You'll likely get better performance and make the process more portable (in case you ever need to change databases - you're not relying on anything DB-specific).

Ultimately, I wouldn't expect Access to do much of this kind of work for you. Access is pretty much your average flat-file database (a la xBase), with a few enhancements, a front-end for building forms and reports, and some support for queries. It can feel like a Swiss army knife in the beginning, but as with most apps that try to do everything, you end up shortchanged on a lot of features. Even worse, you usually end up investing a lot of time into it and by the time you reach its limits, you have a dead-end database store with a bunch of reports and forms that can't be easily ported.

All that said, if Access is the "chosen one" for the time being (we all know how it is sometimes), it'll still store your data for a while. I'd just keep pushing the idea of preprocessing the data file. Just get the file, loop through it, convert your values and then push the final, prepared record into the database.

gr8gonzo

Here's a quick PHP class that should do the conversion, too:

<?php

// Some examples
echo NumberConverter::signedOverpunch2Numeric("6705N") . "\n";
echo NumberConverter::signedOverpunch2Numeric("77}") . "\n";
echo NumberConverter::signedOverpunch2Numeric("12A") . "\n";

// The class
class NumberConverter
{
  public static $sopTable = array(
    "}" => array(0,-1),
    "J" => array(1,-1),
    "K" => array(2,-1),
    "L" => array(3,-1),
    "M" => array(4,-1),
    "N" => array(5,-1),
    "O" => array(6,-1),
    "P" => array(7,-1),
    "Q" => array(8,-1),
    "R" => array(9,-1),
    "{" => array(0,1),
    "A" => array(1,1),
    "B" => array(2,1),
    "C" => array(3,1),
    "D" => array(4,1),
    "E" => array(5,1),
    "F" => array(6,1),
    "G" => array(7,1),
    "H" => array(8,1),
    "I" => array(9,1)
  );
  public static function signedOverpunch2Numeric($in)
  {
    $code = substr($in,-1);
    $val = $in;
    if(isset(self::$sopTable[$code]))
    {
      $digit = self::$sopTable[$code][0];
      $sign = self::$sopTable[$code][1];
      $val = (substr($in,0,-1) . $digit) * $sign;
    }

    return ($val / 100);
  }
}

Open in new window

mwheeler_fsd

ASKER

Hi gr8gonzo,

Sincerely, this is a request from a colleague that would like it in Access. I totally agree with your comments. In fact, I wrote the original parsing of this file 20 years ago when it had 200K+ records (record size is roughly 1000 bytes) and we stored it in a MS SQL database. The file has since attrited to roughly 8000 records and he just wants a small and simple way to report on it.

It's interesting you made the comment about "pre-processing" because I immediately went into my head with "what I know" and was going to whip up a small C executable to format the data, but I thought I was over-thinking it and felt Access must have a way to do it within.

Perhaps that is the best route? When he gets a new file, run my exe, and then do a straight load into Access, or even Excel given the small footprint?

I'm flexible. :)

Thanks!
--Mike

mwheeler_fsd

ASKER

Hi again gr8gonzo,

The only issue with doing it in PHP is this isn't on a server that has the interpreter running. He is looking for a simple desktop solution.

Nice class though. I do a ton of PHP programming and that is tasty!

Thanks,
--Mike

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mwheeler_fsd

ASKER

Hello Ron,

Thank you for catching that bug!

Can you please elaborate on the process for me? Sorry, again I'm a newb on Access. In other words the firing sequence I need for the program/module. And, actually, is this something I need to use VBA for, macro?

pseudo code:

begin

select * from temp table

for each( record) {

if field = balance {
do conversion code
}

insert into permanent table (XXX) values (XXX)
}

Thank you!
--Mike

IrogSinta

As I mentioned earlier, there's no need to iterate through the table. Just run an Update Query using the SQL string I gave you. The code should go in a module and that function will be used by the update query. Also I don't see a need for a temp table. After you get your data into a table just run the query to update this table.

If you're unfamiliar with creating a query, just go to the Create tab on the Ribbon, and select Query Design. Then select SQL View in the upper left of the ribbon and paste the SQL string I gave you. Of course you need to use your actual table name instead. Then just click on the Run icon and your table will be updated with the correct amounts.

Ron

IrogSinta

Let me add that updating a table via SQL update is immensely faster than iterating through and updating each record.

Ron

Gustav Brock

Nice Ron. There had to be a simple method to do this.

/gustav

IrogSinta

Thanks Gustav, without a doubt you would have posted something similar if you had got here before me.

@mwheeler_fsd,
I'm not certain of your level of familiarity with Access and whether my previous explanation was clear enough so if you're still having problems, please let us know and we'll walk you through with more details.

Ron

mwheeler_fsd

ASKER

Hello Ron,

Sorry for the delay, I had to step away from this to work on another project. I completely misunderstood your original answer, but your clarification put me on the right track. This worked perfectly!

Thank you Ron, and thanks to all for the help.

Best regards,
Mike

IrogSinta

Your welcome, Mike.