Are you using visual integrator to export data from Sage 100?
Have you ever wanted to pull information from Sage 100 and get it into Excel format, allowing you to manipulate the data, look for trends, or even alter data to utilize for bulk import to change mass amounts of data in Sage 100?
The Visual Integrator in Sage 100 allows for the export and import of data within Sage 100. The following will discuss how to use Visual Integrator to export information from Item Maintenance.
The best way to think of the exporter is a tool for pulling information from Sage’s Database into Excel format. There are other ways to do this, such as using the Explore feature found at the bottom of the modules, but it gives you access to all the tables within Sage. Also, learning this can make importing information easier, since they have many similarities.
View this Sage 100 step by step software tip in a video format (7:46)
View this Sage 100 step by step software tip with individual screen shots below.
Enter Visual Integrator Exporter and create the export.
1. In modules, go to Visual Integrator >Main> Export Job Maintenance
2. Enter a name for your import. For this example, it is going to be CI_Item_Example
Note: Export Job Names cannot exceed 15 characters. You can find previous exports with the magnifying glass .
3. Enter the Sage Table you wish to pull from by clicking the magnifying glass and selecting the table you want to export from. For this example, I am using CI_Item.
- A good resource to find which tables show what information is https://help-sage100.na.sage.com/2024/FLOR/index.htm#File_Layouts/File_Layouts_Overview.htm?TocPath=File%2520Layouts%257C_____0
4. Select Accept
5. Select Accept.
6. A message box will pop up asking if this is a new job. Select Yes.
7. In the Export Job Maintenance, a few parts will need to be filled out for the export to work.
- Table Name: This should have the name of the table you selected. If the wrong table was selected, this can be fixed by selecting the correct one by using the magnifying glass.
- Long Description: is an optional field where the date created and purpose can be entered, along with anything else relevant to the export.
- File Type: controls what type of file you want exported. For CSVs, LEAVE AS DELIMITED
- Delimiter: is what separates the values for the export. If the export has commas in the data, a different delimiter can be set. This will make reading the CSV, Comma Separated Value, in Excel harder later, but the data will be preserved. Good alternatives to the comma can be ^,*,~, or `, depending on what the data being exported does not contain. This example uses the ^ for demonstration purposes.
- Export File: must be filled in and point to where the final export should be directed.
- Options: will open a window. It is suggested that “Export Column Names as First Record” and “Display Read Only Fields for Export” are selected. Then close the window.
- Much of the rest of this page is outside the scope of this example.
8. Select Data in the tabs at the top of the window
9. Now, the desired data can be pulled from the Available Fields on the left and moved to the right. Scrolling down, more tables that are connected to CI_Item can be seen such as AP_Vendor, IM_Product Line, and IM Warehouse. All of which can hold helpful information. This example is staying in CI_Item and pulling the ItemCode, ItemCodeDesc, and Inactive item.
10. The columns on the right contain
- Field Name: which is the name of the field within the database
- T: shows what type of data is presented
- Oper: Stands for the operation you want the exporter to use to export the data.
- Col/Pos: shows what column in Excel the information will show up in.
- Len/Msk/Val: shows the length of the data and any default data that is populated if the field is empty.
11. Choose Select in the tabs at the top of the window. This tab allows the filtering out of information. For this example, the filter will only show regular items.
12. Click and drag ItemType from the available fields.
13. Double-click the new field on the right
14. Set Relation to “Equal (=)”
15. Set Selection Value to “1”
- The reason for this is because ItemType is stored as a number in Sage 100
- 1=Regular Item, 3=Charge Item, 4=Comment Item, and 5=Miscellaneous Item
16. Click Accept
17. Click Accept in Export Job Maintenance.
18. In the new message box asking, “Execute this job?”, select “Yes”.
19. In the new window:
- Export File Name: Shows where the export will go.
- Job Maint: this will close this window and reopen job maintenance for this export.
- Log: this will show the log for the last time the export ran.
- Test: will show the result of the export without running it and display any errors.
- Accept: will run the export.
- Cancel: will close the export.
20. Select Accept.
21. In the message box asking, “Do you wish to Export records from the company (Company code here)”, Select “Yes”.
22. It can be seen here that some records were skipped. These were the non-regular items.
23. Navigate to where the export put the csv.
24. Open in Excel.
25. If the delimiter was kept as a comma, it should look fine, if not, the csv will need to be separated.
26. In Excel, go to Data at the top of the page.
27. Select the first column.
28. Click Test to Columns.
29. Ensure Delimited is selected and click next.
30. Ensure the Other check box is selected.
31. Enter your delimited value.
32. Click Finish.
Now, the Excel document can be saved and manipulated.
If you have questions or would like assistance using the Visual Integrator to export data from Sage 100, please contact our Sage 100 support team at 260.423.2414.
Register for our Sage 100 newsletter today!
Copyright 2024 DWD Technology Group | All rights reserved. This article content may not be reproduced (in whole or in part), displayed, modified or distributed without express permission from the copyright holder.
