Skip to content

x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 46 subscribers
  • Views 298 views
  • Users 0 members are here
Browse Forums
Announcements
77
topics
94
replies
Customization and Integration
132
topics
452
replies
Financials, Projects, and Administration
302
topics
876
replies
General Discussion
343
topics
921
replies
Installation and Technical Issues
294
topics
1k
replies
Manufacturing, Distribution, and Applications
262
topics
698
replies
Reporting and Analytics
105
topics
487
replies
dburckhardt

GL IMPORT utility and transaction import format

SOLVED
Posted By

Attempting to create a journal entry outside of mas500 and import into sage500 general ledger.  I downloaded the sample je templates provided by sage on the local installation folders.  There's an asc (ascii) and del (delimited) template.  I was able to edit both of the files using notepad and modify so that the import was successful using either import format. 

So the template worked successfully importing into a copy of the live database, but I wanted to use the template so that I could enter large amounts of data using excel and then import the data into sage500.  Editing a CSV using NOTEPAD is very IN-efficient.  Using the DEL template that I already successfully imported I renamed the to a CSV and opened in excel.  All the headers/footers/detail lines appeared correct. Opening in excel the data does NOT show quote-marks or commas as did the DEL template.  Changed a few data fields again using the excel CSV file so I could prove that modifying the template in excel as a CSV and saving that the data could be imported into sage500.

After making changes to the excel CSV file I attempted to save the file and excel prompted to save as a CSV format which it already was.  I saved the updated file, copied to a new file and then renamed to a DEL extension, the same as the original template.  I opened the updated CSV and DEL files using NOTEPAD and both had the same layout.  There were no "quotes" on each side of the "commas" as well as there were commas in the both the header and footer for each field in the detail section.  Those additional commas were not in the original template. 

Regardless of the updated DEL format not looking correct I attempted to the updated file and that import failed to import any data. See import log below.

QUESTIONS:

how can a DEL template be changed to a CSV file opened in excel as a CSV edited/updated saved as a CSV file renamed to DEL format and imported into mas500 GL using the gl import utility? 

import log :

DEL gl import that imported correctly and opened using NOTEPAD.  The format is exactly the same if file copied and renamed to CSV file extension.

DEL gl import that DID NOT import correctly and opened using NOTEPAD.  This format was first opened in excel as a CSV format, updated and saved again in excel as a CSV.  That CSV renamed to a DEL extension and opened in NOTEPAD.  As can be seen the format is NOT the same as the previous print screen of the DEL file that was successfully imported using GL IMPORT.   The format is exactly the same if file copied and renamed to CSV file extension.

Parents
  • 0

    Have you considered using the Data Import Manager module to do this?  It supports Excel spreadsheets directly.

    You would need Excel to be installed locally on your machine (as opposed to Web only).  I works along the lines of the traditional import, but was originally designed to use Excel directly, and like the traditional import, it creates a pending GL batch that can be reviewed and adjusted which can then be posted just like any other GL batch.  It generally does not run into the comma, quotes, delimiter issues that CSV and ASC files might since it uses Excel.

    While I am not sure of the licensing around the DI module but I think it might suit your needs better.

    On most systems sample files can be found at "C:\Program Files (x86)\Sage Software\Sage MAS 500 Client\DI\Samples\MinimumMappedPendingGLTransaction.xls".  Take a look at the file and see if it can work better for you.  The DI module is the preferred method of importing, and some of the traditional imports are no longer updated in favor or the DI imports.

    DI does require some setup and a little different import mindset, but once it is set up it can import a variety of transactions, including GL transactions.

    On my system, the DI module looks something like:

    If I remember right, it basically sets up SSIS job on your SQL Server, which is run manually each time you want to do it.  As it is a job, I believe it can also be scheduled for a more automated regular process.  Single run jobs can be run from the Sage 500 ERP desktop, while scheduling may require some additional SQL knowledge.  Some users can do this on their own, but you can also contact your qualified Sage 500 ERP reseller for more information.

    Good luck,

    Ramon

  • 0 in reply to Ramon M.

    Please open a ticket with Sage 500 to troubleshoot the Import File template. For a more user-friendly import procedure, especially for those familiar with Excel, consider using the Data Porter. This tool utilizes Excel for populating tables.

    To proceed, follow these steps:

    1. Navigate to GL / Activity / Process Journal Transactions and generate a sample journal entry.
    2. Click the Excel icon (Data Porter) and wait for the Excel template to display.
    3. In Excel, go to Add-ins, then select DP Map Control  (like graph) and DP Export Data . (like Camera)
    4. Follow the sample entry in the first line to add additional entries and use DP Import Data. (like Red Exclamation Point)

    For more detailed instructions, please visit Sage University.

  • 0 in reply to wilson wygonzales

    We do not own data porter.

    Had to use the mas500 *.del (delimited) template, open in excel, update/enter the journal entry data, save as csv, use Apache Open Office to save the csv file with quotes and commas required for the *.del import.  Appears to work, but was a PIA.

  • 0 in reply to dburckhardt

    Typically, the only time you need to quote the values is when the text value of the cell contains the delimiter. If you define the cells as Text (highlight cell or column, right-click, Format Cells), then Excel should do this for you whenever it encounters a comma when you save as csv. You should test that scenario to verify it works for you.

  • 0 in reply to dburckhardt

    I would suggest buying Data Porter, it's worth the ease of use and can be used in many integrations like this one.

Reply Children
No Data

*Community Hub is the new name for Sage City