Solved

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

Posted on 2014-02-04
14
402 Views
Last Modified: 2014-02-05
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
0
Comment
Question by:mwheeler_fsd
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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?
0
 

Author Comment

by:mwheeler_fsd
Comment Utility
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
0
 
LVL 34

Expert Comment

by:gr8gonzo
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:gr8gonzo
Comment Utility
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

0
 

Author Comment

by:mwheeler_fsd
Comment Utility
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
0
 

Author Comment

by:mwheeler_fsd
Comment Utility
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
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
Comment Utility
Well, the function you posted is incorrectly written since it includes logical comparisons for the same value in 2 places for each number.  It still works because the amounts end in a letter or a bracket so that part of the logic is ignored.  Anyway, here's a shorter function that accomplishes the same thing:
Public Function SignedNum(theAmt As Variant) As Double
    Dim theLast As String
    Dim theFrnt As String
    Dim isNegative As Boolean
    
    theLast = UCase(Right(Trim(theAmt), 1))
    theFrnt = Left(theAmt, Len(theAmt) - 1)
    Select Case theLast
        Case "{"
            theLast = "0"
        Case "A" To "I"
            theLast = Asc(theLast) - 64
        Case "}"
            theLast = "0"
            isNegative = True
        Case "J" To "R"
            theLast = Asc(theLast) - 73
            isNegative = True
    End Select
        
    SignedNum = Val(theFrnt & theLast) / 100 * IIf(isNegative, -1, 1)
    
End Function

Open in new window

Just add the code above to a module.  And to update your table, just run an Update Query to update the field.   The SQL for your query should look like this:
Update NameOfTable Set [Balance] = SignedNum([Balance])

Open in new window

Ron
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mwheeler_fsd
Comment Utility
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
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
0
 
LVL 29

Expert Comment

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

Ron
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Nice Ron. There had to be a simple method to do this.

/gustav
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
0
 

Author Closing Comment

by:mwheeler_fsd
Comment Utility
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
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
Your welcome, Mike.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now