5

I have a legacy workflow that I would like to optimize and automate (at least partially). The current workflow goes like this. I receive a MS Access .mdb file containing a single table. This table is then loaded into an an existing Access db that contained some macros ,which I will reference later, and renamed BTMUA. To the table BTMUA several fields are manually inserted into the middle of the table. Additionally two fields data types are changed from text to date to more accurately reflect the data they contain. Once these changes have been completed a series of queries, via two macros, are run to parse, trim, and concatenate data from the original fields into the new fields. Specifically Field A id parsed into fields A1 A2 using a left and right functions, likewise field B is parsed into field B1 and B2. Any whitespaces are removed from fields A1, A2, B1, and B2 fields are removed. Finally fields A1, A2, B1, B2, and C are concatenated in one of 7 different ways depending on which fields actually contain data. Here ends the data modification and my use of Access.

I then use FME Desktop load this data into an existing table in my MS Sql based SDE GDB (after first truncating the table).

I would like to redesign this workflow and remove Access from the equation although I cannot change the fact that the data will originate in a .mdb file. I would also like to automate the process as much as possible so it can run either with a few mouse clicks or on a fixed schedule.

Basically I am trying to figure out if it is best to use FME to load the data from the .mdb into my GDB and use either ArcCatalog and Model Builder or MS SQL Server management Studio an T-SQL to transform the data. Or is it more efficient to utilize FME Desktop to do the data trans formations.

Improve this question

2 Answers 2

Reset to default
5

If you are using FME to do the data load, I think you may as well use it to read the original table and do the transformation stage. Then it's all in one process. If you do the work in Workbench then there are transformers for parsing, trimming, and concatenating.

I won't say this is the best way, or compare it to any others, because I currently work for Safe Software (makers of FME) and I don't think that would be ethical on here. However, I do think it would be a definite improvement on the current process, and quite simple to set up too.

Improve this answer
2
  • Would he be able to schedule this process, too? I personally think that it would be legitimate for you to compare and contrast with other methods because you've disclosed your connection to the company - and hey, he's already got the software, so it's not like you're going to trick him into buying it :)
    – Matt Parker
    Jan 28, 2011 at 18:52
  • 2
    Yes, that could be scheduled. All FME processes can be kicked off by a command line argument, so any scheduling tool should be able to handle it. FME Server 2011 also has a scheduling tool built in, if you wanted to go that far.
    – Mark Ireland
    Jan 28, 2011 at 18:56
1

I'm by no means an expert at this kind of thing and I'm not at all familiar with FME Desktop, but if I were in your shoes, I'd bridge the two databases with a script that pulls the data over ODBC, completes all the modifications in-script, and then pushes the results to your SQL Server DB (again over ODBC).

I'd write that script in R, because it's what I know and the kinds of data cleaning you describe are trivial in it, but you could probably do that in just about anything with a decent ODBC library - Python might be more palatable for your organization. Scheduling that to run would then be trivial.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.