Hierarchical import advice

[dandelion]

Hi

I am trying to set up a spreadsheet to carry out hierarchical imports, using macros to translate a flat file (ie my customer's own spreadsheet with one record per row including a lot of extra unneeded stuff) into an Actinic friendly hierarchical file with Custom Properties.

My biggest headache is trying to work out how to handle the Sections and Subsections. Some of the products are at section level and some at subsection level, so my current thoughts are that I should set up a second section-defining sheet holding Section description and image filenmae, and then use HLOOKUP or something similar to generate the Section rows. But I will also need to know whether I need to write End,Section once or twice depending on whether I am coming out of a Section or a Subsection. Arrggggh!

Can anyone advise on the best approach to this please?

Thanks, Dan

[cdicken]

I have to admit that I have never tried this. I always find the easiest way to product a hierarchical import is to import the stuff into Actinic, arrange it, and then export (but I understand if this is not possible).

As you are just doing it because of custom properties, I would simply forget about sections and import all the products and custom properties all into one section. Then I would arrange them into sections in Actinic and export them as the hierarchical file.

This file can then be the file that customers have to manage.

[Jan]

I would suggest importing all of the stuff that you can import using a flat file format - it is easy to represent sections and subsections then, it is also easier to generate them. Then write macros to import the bits that have to be hierarchical, all you need to tie them up to a product is the reference.

Then when you want to import you can import the flat file using the replace option and then import the hierarchical bits using the update option.

Of course if this is a one off job then I would do as chris suggests and then rearrange the sections.

Regards,

[dandelion]

Thanks Chris and Jan

I have achieved (most of) my aims now. I use OpenOffice, as opposed to MS, which has a version of BASIC, so I've sussed out enough of the syntax differences between that and VB to do it. I used variables to hold the last written section and subsection name, then had three cases based on section depth level, each of which tests whether the section and subsection names have changed between import rows. A section depth variable which increments and decrements as you go in and out of sections enables the correct numbers of End, Section lines to be written. The custom properties, if present, take a new line.

I haven't done the section lookup bit yet, but this is no longer a daunting prospect!

The macro is a little clunky, my BASIC experience is limited, and could be streamlined a bit undoubtedly, but it does the job.

If anyone's interested I'd be happy to post it.

Thanks again, Dan