Question about monarch capabilities

[J]

I have been pointed to Monarch as a tool to convert the format of some data. My end goal is to load the data in to a SQL database, and this would be an intermediate step. The data currently has one field per line beginning with the field name, and the fields can repeat indicated by a semi-colon on the beginning of the next line, and/or some times fields will not exist at all. I want to end up with all of the fields for a record in a row rather than a column, so that I can import in to a database.

Is this going to be an easy task for me to accomplish, and is it worth it for me to purchase Monarch, and do I need the standard or professional edition?

Any direction would be greatly appreciated, as I am not very familiar at all with the product. I would be happy to provide a sample of the data if that would help my explanation.

Thanks in advance!

[Mike Urbonas]

J, if you can post some of your data here on the forum that would be helpful.

Mike

[J]

Here is a sample of the data. It includes four records delimited by a $. As you will notice the fields may change in each record, and may occur multiple times. The first word in the line is the field name, and if it is a semi-colon, then it is an additional field from the line above. Ex. multiple PROD and SHIP fields.

BX 5981
FILE Sample Inc.
DOCTYPE Shipping Notice
THEIM Y
UNV N
AP N
AR Y
PROD Kwik
CO Sample,Inc.
SHIP Tokyo, Japan
DATE 1987
ID 1
$
BX 5981
FILE Core,FL
DOCTYPE Shipping Notice
THEIM Y
UNV N
AP N
AR Y
PROD Sample1
; Sample2
; Sample3
; Sample4
CO Core,FL
SHIP Core,FL
DATE 1989
ID 5
$
BX 5981
FILE Trucking Company
DOCTYPE Shipping Notice
THEIM Y
UNV N
AP N
AR Y
PROD Mud Compound
; Sand
; Part
; cement
; Mud
CO Trucking
SHIP Salem, MA
; Boston, MA
DATE 2004
ID 8
$
BX 5981
FILE Correct Foundry
CMTS Folder Empty
ID 7
$

[Mike Urbonas]

OK...Since this data is ultimately going to go into an SQL database, you do not need to capture all of the data in one Monarch model template, though I think you could do so. But by far the quickest way for you to accomplish your goal with Monarch would be to create a few different models as follows:

(1) Create a Monarch model to capture all data that exists in each record, only once (looks like you have a lot of Yes/No "Y"/"N" data for example).

(2) Create two models to grab data that may occur more than once, like the PROD data. Use one model just to grab the first product data right after the word "PROD", and another model to grab any multiple data after the ;.

(3) Create models that focus on a particular non-recurring datatype, such as FILE or CMTS.

For all of these models I'd be sure to always grab the "BX" number, which looks like your "primary key" of data.

Once you export the data from all of these models (many format options: MDB, XLS, etc.), you can combine them together into one table, if you wish, using Monarch V7 Pro. Monarch V7 Pro lets you do "lookups" (left outer joins) to combine up to 9 different data sources. For this reason I would encourage you to buy Monarch Pro.

Bottom line, yes, Monarch can get this data for you and export it in an SQL database-friendly manner. Datawatch even offers model building services at a nominal cost; see: http://www.datawatch.com/vortexmlsupport/model_building.htm

Good luck,
Mike

[J]

Thanks a lot for your reply. It sounds like it is going to take some work, but I think I will give the product a try.

Thanks again!

[J]

Thanks a lot for your reply. It sounds like it is going to take some work, but I think I will give the product a try.

Thanks again!

[Grant Perkins]

J,

Does your data get much more complex than your sample - for instance are there a number of other types (names) of fields which may appear sometimes? Or perhaps more of the fields have multiple occurrence extensions?

If your example format is pretty much as complex as it gets and with, perhaps, a little work in the area of the multiple line fields dependant on how you want the information to look, it may be possible to run this as a single model 'conversion' using Monarch V7.

Should that prove too much of a modelling challenge when working with the full data input, Mike's multi-model provides a solid solution anyway.

Good luck.

Grant



Originally posted by J:
It sounds like it is going to take some work, but I think I will give the product a try.



[ September 25, 2003, 09:21 AM: Message edited by: Grant Perkins ]