XML or SQL Item Import

[Fishhead714]

Hi,
We're trying to import our Item file into Macola ES, and are having a tough time getting to proccess to work. I think we've now given up on importing though the SQL server, we don't have the support or the mapping to get this done, and now are trying to work though the XML server. Does anyone have any advice or tips??

 

[dgillz]

Are you a progression user? If so why are you not running the Progression to ES conversion utility to do this?

If you are not using progression, what are you currently using? This may be an all day task to map fields required, but this should not be too difficult with the help of your Macola reseller.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[Fishhead714]

No we are not using progression, the company has been running on FileMaker Pro and MYOB. And we are not getting alot of support from our reseller, mostly because the Macola ES package was purchased over a year ago and we are just getting to the implimentation.

 

[grossow]

When you say XML server, do you mean the XML utility in MacolaES?

You will need to use MS Access or a program like Data Junction. The best way to work this out is to create a new empty company and enter in an item manually. Then query the imitmidx_sql and look how Macola populated the record. Be on the lookout for the upc_cd field. This needs to be populated with spaces. Once you get your items in, run the file validation on your item tables. MacolaES is very unforgiving if it expects to find a value and there is none.

Why not use Macola Progression?

 

[MacolaHelp]

Are you aware that you need item location records as well as item master records for each item in your inventory? Have you requested file layout documentation from your reseller or macola? It's important to understand which fields are required, which fields must be completely filled, and the valid values in each field to have an import work successfully.

Being an old DOS person, I prefer the flat text file import method. Once you get the data you'd like to import into the fixed field length string, the import is a piece of cake. The method of creating a sample record in each table through the macola interface, then exporting, will usually give you a good idea of fields that are populated even though you don't realize it in the user interface. There are very inexpensive tools available to help create text files once you have determined which values from your file maker pro & myob tables can be populated in the maola tables.

Depending on the number of items in question, it may be more economical to hire a temp and key them in. Then you will have no issues of potential file corruption or missing fields of information.

 

[dgillz]

If a temp keys in the Item Master records, the Item Location records will be automatically created for the default location.

If you import the records into Macola ES, there is a utility to mass create the item location records for ranges of items, or all items, and it is usually very easy to create the default item location records with it.

One thing I did 2+ years ago on one of the first Macola ES implementations was to convert their legacy data (Peachtree) to Macola Progression for their main master files - Items, Item Locations, Bills of Material, Customers and Vendors only -- No history or transactional data was converted at all.

We then ran file validation reports in Progression to insure we had no errors in this conversion (a piece of functionality that is unfortunately lacking in Macola ES). Finally, we ran the Progression to ES conversion utility to create the Macola ES database. It actually worked very well, if only because we made no attempt at converting history or transactional data.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[Fishhead714]

Thanks for all the advice, we have been thinking that keying the Items in might be the best way to move forward, however I'd still like to attempt using the XML import utility. I pulled my items file into Access and then exported it as an XML file, I'm using Stylus studio (trial version) to do the mapping. Question: Do I need to have a culomn in my source file for every column in the exact-schema.xsd, or just map the ones in the .xsd that have a red check mark?

 

[grossow]

I may be wrong, but I think the exact-schema is for use with the item tables associated with Synergy not the item tables for use with inventory in MacolaES.

 

[dgillz]

I would look at the table definition in SQL Server Enterprise Manager and make sure that you have a column for every field that is a required field.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[TLeaders]

If I was going to move the data to Macola:
1st. Move the data from Access to SQL server using DTS. The import/export wizard will make a mirror image of the access file in SQL. You could use linked server but I prefer DTS.
2nd. Next create a SQL script in Query Analyzer to move the data from the temp table (created from Access) to the Macola layout. The Macola item file layout is in the file c:\program files\exact software\sql\imitmidx_sql.sql. Populate the fields that you know by mapping the item number, description, etc. from the temp table. You can add a new item in Macola and review the data in Query Analyzer, use this information to default the fields that exist in Macola but not in your old system.
3rd. Run the file validation program bring up Macola ES. It is in System/Utilities/General/File Validation. This should report the errors in any of the fields that need

Rerun steps 2 and 3 until you get the errors removed.

I hope this helps.
Tom