Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am planning the migration of a directory of image files plus a FileMaker-database containing the corresponding metadata into an Imagic IMS database (formerly known as ImageAccess). The vendor provides a Java “Datasheet to xml conversion tool” which creates an XML file for importing image metadata from a tab-separated file. The images' filenames are made up by their image reference numbers listed in the FileMaker database.

In the source FileMaker-database the image reference numbers are often contracted, making a sample export record describing four images look something like this:

ref        subject                    photographer
123/3-6    Building A, Living room    Photographer A

For the program converting the tab separated file to XML, these four images should be listed as:

ref        subject                    photographer
123/3      Building A, Living room    Photographer A
123/4      Building A, Living room    Photographer A
123/5      Building A, Living room    Photographer A
123/6      Building A, Living room    Photographer A

How can I best expand these contracted records into separate lines?

share|improve this question

closed as off topic by Leigh Riffel, dezso, RolandoMySQLDBA, Mark Storey-Smith, Marian Feb 5 '13 at 15:30

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

In the process of ETL (extract, transform, and load) you are in the transform stage.

Not being familiar with the vendor specific tools you mention my thoughts are that your going to have to custom build an intermediate step with your own tools or development environment to "transform" the xml file.

This would be possible with most any scripting/programming language. Do you have experience with .Net, Ruby, or other programming languages?

If I were doing this project I would write a .Net utility to scan through the XML file looking for hyphens in the ref column. I'd then parse the numbers on each side adding additional records as you've described to take the original XML from what it is to a new one in the format you want. Once done you can continue on with the "load" part of the ETL process.

Since you can conceptualize what your trying to accomplish if you can now select a framework such as .Net or Ruby to do it in, there's a lot of guys at who could help you get where you're needing to go.

share|improve this answer
Thank you for your quick reply! I was hoping there'd be a simple tool to do the job. Oh well, I might try some Perl, Number::Range looks promising. – Oliver Sep 25 '12 at 20:03
Funny, I meant perl above (both times) when I wrote ruby. Good luck. – RThomas Sep 25 '12 at 22:19

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