batch load into GL GJE

2 views
Skip to first unread message

Stephen, Eve

unread,
Jan 10, 2012, 1:05:20 PM1/10/12
to Profitool User Group
Hello ptug,

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

unread,
Jan 10, 2012, 1:28:08 PM1/10/12
to pt_u...@googlegroups.com
Eve

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

unread,
Jan 10, 2012, 1:32:51 PM1/10/12
to pt_u...@googlegroups.com, Profitool User Group
Eve -

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

unread,
Jan 10, 2012, 1:48:37 PM1/10/12
to pt_u...@googlegroups.com
Happy new year to all

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

data.txt

John Fishbone

unread,
Jan 10, 2012, 1:58:59 PM1/10/12
to pt_u...@googlegroups.com

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

812.339.9500

www.weddlebros.com

 

 

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

image003.jpg
BATCH UPLOAD FORMAT-JE.xls

Stephen, Eve

unread,
Jan 10, 2012, 3:15:14 PM1/10/12
to pt_u...@googlegroups.com
Wow, you guys are awesome!

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

unread,
Jan 10, 2012, 3:16:20 PM1/10/12
to pt_u...@googlegroups.com
Dan,

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

unread,
Jan 10, 2012, 3:23:15 PM1/10/12
to pt_u...@googlegroups.com
Eve

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

unread,
Jan 10, 2012, 3:25:10 PM1/10/12
to pt_u...@googlegroups.com
no custom screen. line # is not on screen, but is required to let PT know
to place data.

John Fishbone
Accountant 
Weddle Bros. Construction Co., Inc.
Bloomington, IN 47404
812.339.9500
www.weddlebros.com

John Fishbone

unread,
Jan 10, 2012, 3:26:05 PM1/10/12
to pt_u...@googlegroups.com
Dan,

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

unread,
Jan 10, 2012, 3:53:37 PM1/10/12
to pt_u...@googlegroups.com
All,

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

unread,
Jan 10, 2012, 4:04:32 PM1/10/12
to pt users

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


From: "John Fishbone" <jfis...@weddlebros.com>
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
Reply all
Reply to author
Forward
0 new messages