batch load into GL GJE
Stephen, Eve
We have a very large Journal Entry to run in not much time, and I'm thinking about trying to load it as a BATCH LOAD, since we have all the data in an Excel file. The regular BATCH LOAD process using SA DEF BATCH LOAD doesn't work for a scrolling screen (like GL GJE,) though. I haven't been able to come up with an Excel macro that would create the Batch Load Interface File format from the database type spreadsheet we're working with.
I have noticed the GL GJE FIXED screen, which looks like it might be doable for the SA DEF BATCH LOAD definition, but I haven't played with it before, and it has no help on the screen. I'm thinking we would need to do some manipulation of the spreadsheet to put it in a format for this screen as well, although it might be simpler formatting.
Do any of you have a cool macro or script that would create the interface format out of a line-by-line file? Or, if not, do you have any advice for me on how to do this journal entry?
Thanks,
Eve
________________________________
The Kirlin Group
Celebrating 50 years
1960-2010
________________________________
Please consider the environment before printing this e-mail.
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of The Kirlin Group. Finally, the recipient should check this email and any attachments for the presence of viruses. The Kirlin Group accepts no liability for any damage caused by any virus transmitted by this email.
John Bolland
Here at Mainzeal I have developed an Excel process that looks like the
GJE input screen and that includes macros that uploads the result via
ftp to the users LFD as a correctly formed batch load file.
I could prepare a copy for you (with the ftp client) if it would help
Regards
John Bolland | Programmer | Mainzeal Property and Construction Ltd |
Mobile 021 283 1011 | Phone 09 375 5386 | www.mainzeal.com
> You received this message because you are subscribed to the Google
> Groups "Profitool User Group" group.
> To post to this group, send email to PT...@ptug.org
> To unsubscribe from this group, send email to
> PT_Users+u...@googlegroups.com
> For more options, visit this group at
> http://www.ptug.org
>
The information contained in this e-mail may be privileged and confidential and is intended for the exclusive use of the addressee(s) indicated above. If you are not an addressee, any disclosure, reproduction, distribution or other dissemination or use of this communication is strictly prohibited. If you have received this message in error, please let us know immediately. - Richina Inc. and Subsidiaries - Mail Gateway
Bauman, Dan
We always use fixed screens for loads (we do them all the time). My suggestion is to make all columns the same width (say 20 characters so Excel is easy to deal with). Then rearrange your spreadsheet in the order you need the columns for the load. Just be sure to save the Excel file as "formatted Text (space delimited)". Depending on how fancy you want to get, each line will be a reference in the batch. If you want some help setting it up, drop me a note or give me a call, it will only take a few minutes.
Regards,
Dan
Hello ptug,
Thanks,
Eve
________________________________
________________________________
You received this message because you are subscribed to the Google
Groups "Profitool User Group" group.
To post to this group, send email to PT...@ptug.org
To unsubscribe from this group, send email to
PT_Users+u...@googlegroups.com
For more options, visit this group at
http://www.ptug.org
This e-mail may contain secrets or privileged, undisclosed or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation.
John Bolland
For the benefit of the group - the attached text file is an illustration
of a batch load file for GL GJE using the scrolling screen.
You will note that at the beginning of each data set (line of the
scrolling screen) there is an entry that indicates the line number - like
L 1
L 2
L 101 etc
Regards
John Bolland | Programmer | Mainzeal Property and Construction Ltd |
Mobile 021 283 1011 | Phone 09 375 5386 | www.mainzeal.com
On 11/01/2012 07:05, Stephen, Eve wrote:
> You received this message because you are subscribed to the Google
> Groups "Profitool User Group" group.
> To post to this group, send email to PT...@ptug.org
> To unsubscribe from this group, send email to
> PT_Users+u...@googlegroups.com
> For more options, visit this group at
> http://www.ptug.org
>
>
John Fishbone
Eve,
I'm not sure what you mean by large, but we use the scrolling GL GJE screen to BATCH LOAD several journal entries each month. Please see attached sample. The keys are to ensure EXACT column widths and appropriate column justifications (left/right)--see sample attached. Columns are JE#, line#, DR/CR, amt, j/l, job/ph, activity and memo. If I recall correctly, there may be a limit of 250 or so lines, which we hit in an entry to find out and our solution was to break it into two. As this is such as speedy method of data entry, having two entries becomes no big deal. Save as *.prn in Excel, load to your directory and at the prompt "chmod 666 filename.prn" to change the permissions on the file. In the program BATCH LOAD--see sample below (not the same as the Excel attached, but the rules are there).
Call me if you have any questions.
Thanks,
John Fishbone
Accountant
Weddle Bros. Construction Co., Inc.
Bloomington, IN 47404
-----Original Message-----
From: pt_u...@googlegroups.com [mailto:pt_u...@googlegroups.com] On Behalf Of Stephen, Eve
Sent: Tuesday, January 10, 2012 1:05 PM
To: Profitool User Group
Subject: {PTUG} - batch load into GL GJE
Hello ptug,
You received this message because you are subscribed to the Google Groups "Profitool User Group" group.
Stephen, Eve
John Bolland, I'd love to have a look at your macros - it's creating the batch load file in this format that is stumping me.
John Fishbone, Your batch load data looks perfect, but I'm not seeing a line# field on the GL GJE screen to load the line#s into. Do you have a custom screen?
Thanks,
Eve
Stephen, Eve
This is exactly what I do normally for PR and AP batches, but for the GL GJE batch, the reference needs to tie out, and so I would need at least 2 lines for each reference.
Thanks,
Eve
John Bolland
I have tried to send it but google groups has rejected the attachment.
It is a simple zip file
Please email me your email address and I will send it directly to you
Regards
John Bolland | Programmer | Mainzeal Property and Construction Ltd |
Mobile 021 283 1011 | Phone 09 375 5386 | www.mainzeal.com
John Fishbone
to place data.
John Fishbone
Accountant
Weddle Bros. Construction Co., Inc.
Bloomington, IN 47404
812.339.9500
www.weddlebros.com
John Fishbone
I'd love to see an example/sample of the Excel that you use for PR batch
load.
John Fishbone
Accountant
Weddle Bros. Construction Co., Inc.
Bloomington, IN 47404
812.339.9500
www.weddlebros.com
Stephen, Eve
I figured it out! The Line# goes into the PAGE field. There is a limit of 294 pages/lines.
HUGE thanks to all of you, I'd never have figured it out without your help!
John Bolland: I'd still love to see your macros, my email address is este...@jjkllc.com
Gary M. Tamblyn
Eve,
In case it is of any use to you, keep in mind that you can put in up to 99 "Pages" of GJE input that are all part of the same "Entry". Each page can have as few as one and as many as a screen full of Dr and Cr rows.
The individual "Pages" do not have to balance (and can thus be just one row). The only balance that is required is that all of the "Pages" must balance overall for any given "Entry"
Gary
To: "pt users" <pt_u...@googlegroups.com>
Sent: Tuesday, January 10, 2012 1:26:05 PM
Subject: RE: {PTUG} - RE: batch load into GL GJE