Converting flat csv's to hierarchical csv's

[alexwren]

Hi,

I am desperately needing to convert flat csv files to hierarchical csv files for both the product and customer accounts.

The reason for this is that I need to import customvars associated with the products (I have read that you cannot do this in the flat csv import) and also take single record account details and import them into Actinic Business.

Does anyone know any good example/resources that they might be able to point me to so that I can achieve this. Ideally excel would be the best program to do this in if possible.

Thanks in advance.

[TraceyG]

Hi,

If you already have all your products in your software, then exporting your catalog will give you a hierarchical .csv file (File | Export). You can also find examples of hierarchical files within your site folder:

SampleCustomerImportFile.txt
SampleHierarchicalCatalogImportFile.txt
SampleHierarchicalBusinessImportFile.txt

These files can be imported into Excel - to get the formatting to look right you should go to 'Data | Get External Data | Import Text File' (within Excel) and browse to the file. Set the delimeter to 'comma'.

I hope this helps.

[alexwren]

Hi Tracey,

Thanks for your info but I have already been through all of these steps. I do not currently have any products in Actinic in fact I need to import about 1000 but I need to also import the extra customvar info that a flat file will not allow.

I also need to import customer data which can only be done in a hierarchical format.

What I really need is some knowledge of how I can convert a flat csv into the hierarchical format that Actinic requires. The only way I can do it is by hand (not quick) so I need to learn some new skills.

I would be really grateful if anybody has got any examples as to how this might be achieved with Excel- I have heard that VBA is possibly the answer, using FileSystemObject???

This all a bit greek to me but it seems I might have to dabble to find a solution. I find it hard to believe that nobody else suffers from these importing problems as a hierarchical file does not seem an obvious export from other stock/accounting systems.

Thanks in advance

[TraceyG]

Hi,

I do not currently have any products in Actinic

It may be easier if you import the flat file that you do have and then export your catalog to get the hierarchical format and just add the customvar bits to that. There is still a bit of work involved but maybe not as much as doing it from scratch.

I'm afraid that I don't know of a way of easily converting the customer accounts flat file, my knowledge of VBA is not that good. Maybe there is someone else out there who could help with that one.

[alexwren]

Thanks again Tracey,

I think I might keep digging as I think that I am going to see this issue again and would rather have a solid answer that can handle thousands of products and more importantly the customer accounts.

[s-j-r]

Hi Alex,

I had a similar issue with loading up Actinic from an existing data base. Tracey's idea of building Actinic from the flat file you have and then re-exporting it as hierarchical is a good one.

After the flat file import you could manually change just one or two records with your "custom" data - that way when you export you will see what the Hierarchical file looks like ............................but warning ........... You won't like it !!! .... It was a pig (for me at least) to create such a non-flat structure out of Excel, without a lot of work.

And then, the other option - here speaks the geek - is to put as much as you can into the data base, including your "sample" manual additions - and then look at hacking the data base directly - if you have used "Access" no problem, if not, make sure you have a good backup - hey do that anyway.

Good Luck

Steve

[alexwren]

Hi Steve,

Thanks for your comments.

Unfortunately I need to use Excel anyway to do some data conversion (Vertical lookup tables etc) so I have to take this route.

[s-j-r]

Originally posted by alexwren
[Unfortunately I need to use Excel anyway

Alex,

I wasn't trying to put you off (well perhaps a bit !!!).

What Tracey suggested, and I strongly agree with, is that you set up some bits of data in Actinic by hand, using the normal manual process ---- and then ---- do a hierarchical export.

That way you see all the data you will need laid out in the hierarchical structure.

Then, its up to you how you amend your own "real" csv to bring it into line with the hierarchical structure.

One further suggestion, perhaps, is to remember that you can build up separate "bits" of the csv and then merge them together before importing it into your data base.

I am using EXCEL to hold all of my sites details and then building outputs (various) using EXCEL macros - it works great (just not all on line yet).

Steve

[alexwren]

Hi again Steve,

Yes I agree with you about getting the format correct by doing an export - that is a great idea.

Its the "up to you" bit that i am stuck on...

I like your merge idea, I had not considered that.

I would love to see your Macros if you are in the mood to share them?

[s-j-r]

Alex,
send me an Email, I will be pleased to help - you have your send Email disabled so I can only contact you through this forum.

I can respond to any Email when I get back home - slaving away (or something) at the day job at the moment !

Steve

[alexwren]

I have sent you an email.

Alex

[Jan]

Hi Alex,

I often do this by using flatfile to import all the things you can import with a flatfile structure and then using hierarchical to import the extras such as customvars - you only need to know the product reference to import things using a hierarchical file.

Sor