Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

*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.

Students Click Here


Trying to import excel file into mas90 with VI

Trying to import excel file into mas90 with VI

Trying to import excel file into mas90 with VI

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.

RE: Trying to import excel file into mas90 with VI


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

     FILE NAME:  GL.JOURNAL G/L Journal Posting File
   IMPORT FILE:  c:\gle.txt
     FILE TYPE:  DELIMITED              DELIMITER:  ,

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

00001    1    Account Record                    00005   00001   00005   <>        0

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,

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

RE: Trying to import excel file into mas90 with VI

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?

RE: Trying to import excel file into mas90 with VI

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.


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

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