Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

masninetylb (IS/IT--Management) (OP)
4 May 06 16:46
Hi - I'm new to this and was hoping someone could help. I'm trying to import an excel file into Mas90. I want to make sure I only have the exact information I need in the file.  These are general ledger transactions. The first column I have a description of what the item is, in the 2nd column, the GL Account#, the 3rd column a Debit Amount and the 4th Column a Credit amount. Either the 3rd or 4th column will have the amount, but not both (one sided entry). Could someone please point me in the correct direction. Looks like this (these are not actual #'s) :
Comment     GL Account       DR        CR
Teacher     1234-56-78       10,461.68
coach       1234-56-78                  1,879


Thanks in advance.
Blorf (Programmer)
4 May 06 17:51
Hi.

Your VI Job is fairly simple if you put this into a CSV file.  I do something similiar, but I put it into a general journal entry, which means I have to post it.

Here is a few lines of my text file:

CODE

"0543","2006 Period 12 Allocations","03/31/06","491-02-00",26162.54
"0543","2006 Period 12 Allocations","03/31/06","491-04-00",-26162.54
"0543","2006 Period 12 Allocations","03/31/06","492-02-00",70625.24
"0543","2006 Period 12 Allocations","03/31/06","492-20-00",-70625.24
"0543","2006 Period 12 Allocations","03/31/06","493-02-00",40080.72

As you can see a positive or negative decides on debit or credit depending on the account type.  Our accounts are 3 segment accounts.

Following is a print out of my VI Job

CODE

Run Date: 05/04/2006  CRS                D/T CARSON ENTERPRISES, INC.                         Page: 1
V/I Date: 05/04/2006             JOB DEFINITION PRINTING FOR JOB: ALLOCATIONS                 Time: 02:50 PM
Posts Monthly Allocations

   CONFIGURATION INFORMATION
-------------------------------------------------------------------------------------------------------------
     FILE NAME:  GL.JOURNAL G/L Journal Posting File
   IMPORT FILE:  c:\gle.txt
     FILE TYPE:  DELIMITED              DELIMITER:  ,
           INSERT ALL REQUIRED FIELDS DURING SETUP: Y
       HEADER INFORMATION INCLUDED IN DETAIL LINES: Y
       PASSWORD: NO

   DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM   ITEM NAME         FG TYPE      OPERATION     COLUMN   IMPORT MASK               SUB-STRING
-------------------------------------------------------------------------------------------------------------
00001  SOURCE_JOURNAL    H  STRING    ASSIGN                 JE                        No
00002  BATCH_NUMBER      H  ZERO-FILL REPLACE       00001    Start: 1   Len: 4         No
00003  JOURNAL_COMMENT   H  STRING    REPLACE       00002    Start: 1   Len: 30        No
00004  POSTING_DATE      H  DATE      REPLACE       00003    MM/DD/YY                  No
00005  OUT_BAL_ACCPTD?   H  YES/NO    ASSIGN                 Y                         No
00006  ACCOUNT_NO        L1 ACCOUNT   REPLACE       00004    ###-##-##                 No
00007  POST_COMMENT      L1 STRING    REPLACE       00002    Start: 1   Len: 30        No
00008  POST_AMOUNT       L1 NUMBER    REPLACE       00005                              No

   RECORD SELECTION
-------------------------------------------------------------------------------------------------------------
ITEM   TYPE   DESCRIPTION                       COLUMN  START   LENGTH  RELATION  VALUE
-------------------------------------------------------------------------------------------------------------
00001    1    Account Record                    00005   00001   00005   <>        0

   VALIDATION RECORDS
-------------------------------------------------------------------------------------------------------------
ITEM   FIELD                         METHOD         SKIP  FILE/STRING/EXPRESSION
-------------------------------------------------------------------------------------------------------------
00001  SOURCE_JOURNAL                FILE            N    GL.SOURCE
00002  POSTING_DATE                  EXPRESSION      N    GL2$(7,6)<>DIM(6)
00003  OUT_BAL_ACCPTD?               STRING          N    YN
00004  ALLOCATION_NO                 FILE            Y    GL.ALLOC
00005  ACCOUNT_NO                    FILE            N    GL.ACCOUNT
00006  COMMENT_FLAG                  STRING          Y    /C

Hope this helps,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

masninetylb (IS/IT--Management) (OP)
4 May 06 18:42
Thanks bunches. I noticed that you have 5 data fields, yet your VI job shows that you're using 6 fields but that your data items shows 8 items. Is there a reason for this?
Blorf (Programmer)
4 May 06 19:04
Hi.  Just never cleaned it up.

OK, first thing, in my text data, field 2 is my header comment.  I use that field twice, as the JE entry has a header and detail comment.  In my case, they are they same, so both in my VI job point to field 2.  So my 6 fields are really 5 fields.

Also in my VI job, the first field is a forced value of "JE" not coming from my text file, so it is not in my text file.

Also, field 5 in my job "OUT_BAL_ACCPTD?" is forced value of Y for yes, although it should never be needed.  This is also not in my text file.

Hope this helps.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close