Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: GL Upload from Excel

  1. #1

    GL Upload from Excel

    We are looking for solutions/workarounds for our month end uploads of 5k+ line Journal Entries to E1. We have tried the built in options: Z-tables, AIS/Orchestrator and both have some limitations. Appreciate any input on this.

  2. #2
    Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    63
    Hi, and welcome to JDEList!
    It's usually a good idea to add your JDE version and platform information into your signature or at least into the test of your question, as the capabilities vary considerably from the oldest to the most recent versions of JDE. Regardless, my gut instinct whenever a large import into the G/L needs to be made is to use the Z file process. How have you been trying to load your z file, with a grid upload or a flat file import? The grid upload has a limit that can be modified - my company has the maximum set at 3000 rows for example. If you export Excel into a CSV or other flat file format, a table conversion works well for loading the z file, just be wary of embedded punctuation in description or other string columns (especially the same character being used as a field delimiter).
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  3. #3
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,582
    I agree with Kim, for a large amount of JEs like you are talking about I would think the Z-Tables would be your best option. What limitation are you running into with Z-tables?
    Brian Oster
    Application Development Manager
    E1: 9.0 (TR9.1.5.1) / 9.2 (TR9.2.2.2)
    JAS/BSSV: Weblogic 12.1.2 / Weblogic 12.2
    ES: Win2008 / Win2016
    DB: MSSQL 2014 / 2016
    WebDev Client: Win7Pro / Win10Pro

  4. #4
    Thank you for your responses Kim and Brian!

    We are on E1 9.2, Tools Release 9.2.3, Windows servers using Oracle database.

    We have two use cases: Users uploading data manually and an automated process. In both cases, Z-table process is cumbersome for us: It is a disconnected process with multiple steps, and the error handling is through the Work Center. The upload has multiple journal entries and cross referencing the messages to the line in the file can take time.

  5. #5
    You mentioned Excel in the title of this thread. Are both the user and automated uploads in Excel or a different format?
    Regards,

    Hari Sharma
    Aellius
    EnterpriseOne: Integration (.Net, Web Services) | Output Management | Monitoring

  6. #6
    Member
    Join Date
    Feb 2002
    Location
    Florida, USA
    Posts
    179
    One way to handle the multi-step z-file process is to create a custom program that reads the external data, parses, scrutinizes and then populates the z-file table. It then calls the z-file processor (and version) which in turn creates the journal entries (F0911). In the initial stages of developing the custom program, you will need to test out all possible scenarios that may cause errors. The source of the data format must always be the same and theoretically should always be. You can also have the journal entry batch program run automatically in the same job stream but this is dependent on user requirements. Most of my past clients preferred to be able to review, approve, and post as they do with manually entered journal entries. The above custom program should have an error listing just in case. But remember, if the original format of the source is always the same and you have rigorously tested out all scenarios there should be no errors. If so, keep working at it. There should also be tight controls on making sure the source of the data remains in the same format. My last client simply chose an option on an E1 page and voilŕ, thousands of journal entries appeared a couple minutes later!
    JD Edwards E1 and World
    Blasenheim Solutions, LLC
    Technical and functional consultant specializing in Financials

  7. #7
    The user upload is in Excel. The automated one is in CSV.

  8. #8
    Thanks for the detailed explanation. I will look into this. At your last client that implemented the E1 page, where does the user upload the file to? In a shared folder that is picked up by the program?

  9. #9
    Member
    Join Date
    Feb 2002
    Location
    Florida, USA
    Posts
    179
    Quote Originally Posted by carterjde View Post
    Thanks for the detailed explanation. I will look into this. At your last client that implemented the E1 page, where does the user upload the file to? In a shared folder that is picked up by the program?
    The external CSV is picked up in a shared but secured folder.
    JD Edwards E1 and World
    Blasenheim Solutions, LLC
    Technical and functional consultant specializing in Financials

  10. #10
    Quote Originally Posted by carterjde View Post
    The user upload is in Excel. The automated one is in CSV.
    If you are open to a third party solution, look at LynX Business Integrator and its integrated components LynX Office and LynX File Exchanger, which let you upload files directly to E1 from Excel and files respectively. PM me directly or submit a post to our forum if you have any questions regarding this.

    https://www.aellius.com/lynx-business-integrator/
    Regards,

    Hari Sharma
    Aellius
    EnterpriseOne: Integration (.Net, Web Services) | Output Management | Monitoring

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.