Trying to import excel file into mas90 with VI

[masninetylb]

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]

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:

"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

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]

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]

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.