Importing 3 prices from Flat File to hierarchical file

[toolman.co.uk]

Hi,

I have a flat file with three price bands for each product which I will set up customer accounts to assign each customer to a certain price band.
I have also set up the three price bands in customer groups.
We are trying to convert this flat file to a hierarchical file via the convertor. However, the extra prices will not be imported as they are not Valid Fields for Importing. I dont fancy manually adding 15,000 prices X2 in every product.
I also have a column for my reps % of commission they get on each product, which I was hoping that could be uploaded too so that it could be worked out in reports (I realise this last bit may be ambitious but surely it can be done).

Looking at the rules of converting..
"Most people have their data in a 'flat-file' format, but if the file contains any custom fields those fields will not be importable via a flat file. The flat file will need to be converted to a hierarchical file." (which seems to be a bit of a catch 22 situation).

I have manually put in a single product with all three prices and exported a hierarchical file to look at the layout, but at the moment it looks impossible not to do this manually..

With prices changing daily in the modern world, it would take 10,000 times less effort to change on excel, convert and upload..

Any solution to this would be grateful

[leehack]

The ability to do the conversion will probably rely on very good excel skills, i'm sure it would be possible. If a real cul-de-sac, possibly look to simplify and give groups set %es off prices and have it all done auto for you is maybe an option?

[toolman.co.uk]

Cheers Lee but the cul-de-sac option is not possible, as I have read other people with similar pricing structures. Ours are not set and cannot be set at certain percentages otherwise it would be so simple...

[leehack]

Perhaps your next step is to illustrate what you have and more importantly the format you need to get it into, i'm sure excel and/or macros will be possible somehow, i'm yet to meet anything that cannot be done in excel tbh.

[toolman.co.uk]

if I say it, its simple.. doing it?

Convert to the hierarchical all three price bands that will show up in the prices page on actinic product page, add another column so that it can somehow be accessed in reports..

[fleetwood]

OK, let me start by saying I don't have any direct experience with exactly what you are trying to achieve, nor do I fully understand what you want, so this is just random ideas based on how I would start to tackle this.

If you add custom variables to your products, and then export the product database, and open it in excel, don't those custom variables now appear as new columns in the speadsheet?

If so (and I think this is the case), can't you then adapt these new columns to take the data you are trying to import.

You would export as a hierarchal file, then play around with the excel spreadsheets sort order, so that you could directly copy across from your flat file to your newly exported file.
By this I mean, find a common field in both your flat file, and the exported file, and sort both spreadsheets on this common field. Then you have two spreadsheets in the same sort order, and can easily copy across from one to the other.
Its important to start by preserving the original sort order of the hierachal file, by inserting a new column to the left, and numbering each line sequentially. That way, you can always return the spreadsheet to its original sort order, by sorting on column A. This is important, otherwise, you lose the hierachal layout. You need to remember to delete column A, when you have finished.

You will be working with a hierachal file, so no need for any convertor. Once edited to include all your new data, you would import the hierachal file back in.

If this is completely different to what you are looking for, just file under 'ramblings from fleetwood', and move on

[fergusw]

Dan,
The way I would tackle this for you would be to create a macro which resides as a menu item/button option inside Microsoft Excel.
The macro would be a custom written piece of code which, when run, would scan through the current, active spreadsheet and directly update the Actinic database i.e. price field, pricing fields for customer accounts and any custom variable fields required e.g. reps commission.

The method for updating would be; open spreadsheet, bring in/update values, click button, once complete upload Actinic.

The flat-file to hierarchical methods already posted would possibly be another way round this however getting a customised macro developed in Excel for example may save you hours of time in the long run if you are updating regularly.

[toolman.co.uk]

Fleetwood: Thanks, we have already tried this with a single product and exported a hierarchical file.. All this below is in column A, it does not seperate the price bands into columns [eg:] h, i & j so our master price can be easily copied and pasted.. which is why I am stumped..

"PriceSchedule ""Priceband1"" Retail 0 0.000000"
"PriceSchedule ""Priceband3"" Retail 0 0.000000"
"PriceSchedule ""Priceband2"" Retail 0 0.000000"

"Section ""Saws"" """" """" """" """" 1 0 ""Saws.html"" 0 1 0 0 0 """" 1 ""Standard"" """" """" """" """" """" """" ""Image Single Add To Cart Button"" """" """" """" """" """" """" """" """" """" """" ""Standard Fragment Image"" """" """" """" """" ""Standard Extended Information Links"" """" """" """" """" """" """" """" """" """" """" """" """" """""
"Product ""1"" ""TM668 TOOLMAN 22"""" 2G Hand Saw"" ""Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price Priceband3 325 0 1 1 """" """" 0"
"Price Priceband2 265 0 1 1 """" """" 0"
"Price Priceband1 205 0 1 1 """" """" 0"

Fergus: Thanks, I thought of this, but Im about as good as writing Macros as I am at walking on the sun without getting burnt! Its getting someone to write me a macro that I can use all the time for constant uploads!

[fleetwood]

Hi Dan

Sorry, but I can't fathom whats been posted - maybe its just the lack of formatting in the forum post, but that just looks like a csv file.

What do you mean when you say "all this is in column A"?

Is that all you can see, just one column?

If so, you are just opening the export as a csv file, and need to import the csv into excel. You should see lots of columns, with 10 rows of headers at the top.

My Actinic exports automatically open as a excel spread when I double click them, but if you are not finding this, open Excel first and create a new spreadsheet, and then go file - open and navigate to your export, and open it. You may then get an import wizard opening, which will help you import a csv into a spreadsheet.

This does assume that you have excel, or another spreadsheet program installed on your PC.

And just in case, please snapshot before doing any import work like this - otherwise, you risk screwing your database.

[fergusw]

Dan,

If you're looking for a spreadsheet that is laid out like:

Prod Ref | Price | PriceBand1 | PriceBand2 | PriceBand3

which then updates directly into Actinic at the click of a button in Excel then, yes, the Macro is the way to go, and it's something we could help you with. PM me for more information if you wish.

[nileshpa]

Dan,

How about this.

Create an excel spreadsheet with column A to G as follows which you maintain regularly.

productID | Detail1 | ShortDesc | FullDesc | PriceBand1 | PriceBand2 | PriceBand3

In column J , setup a concatenation formula looking something like this:

="""Product"","""&A2&""","""&B2&""","""&C2&""","""&D2&""" "&CHAR(13)&"""Price PriceBand1 "&E2&" 0 1 1 """""""""" 0"""&CHAR(13)&"""Price PriceBand2 "&E2&" 0 1 1 """""""""" 0"""&CHAR(13)&"""Price PriceBand3 "&E2&" 0 1 1 """""""""" 0"""


Couple of things to bear in mind. Firstly, in excel, a linefeed/carraigeReturn is CHAR(13) & to get a double quote, you will need two double quotes.

Then, when you are ready, highlight all rows of column J & copy it. Open up WordPad (not notepad) and paste it. Wordpad handles format effectors which notepad doesn't. You then save the file as text with CSV extension. To Check the output, open up the csv file in notepad, you will see something like this which is ready to import as a hierarchical file.

"Product","1","TM111 TOOLMAN 11","2G HandSaw","Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price PriceBand1 100 0 1 1 """"" 0"
"Price PriceBand2 100 0 1 1 """"" 0"
"Price PriceBand3 100 0 1 1 """"" 0"
"Product","2","TM222 TOOLMAN 22","2G HandSaw","Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price PriceBand1 200 0 1 1 """"" 0"
"Price PriceBand2 200 0 1 1 """"" 0"
"Price PriceBand3 200 0 1 1 """"" 0"
"Product","3","TM333 TOOLMAN 33","2G HandSaw","Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price PriceBand1 300 0 1 1 """"" 0"
"Price PriceBand2 300 0 1 1 """"" 0"
"Price PriceBand3 300 0 1 1 """"" 0"
"Product","4","TM444 TOOLMAN 44","2G HandSaw","Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price PriceBand1 400 0 1 1 """"" 0"
"Price PriceBand2 400 0 1 1 """"" 0"
"Price PriceBand3 400 0 1 1 """"" 0"
"Product","5","TM555 TOOLMAN 55","2G HandSaw","Our DIY saw features a hardened and tempered blade with set and sharpened teeth"
"Price PriceBand1 500 0 1 1 """"" 0"
"Price PriceBand2 500 0 1 1 """"" 0"
"Price PriceBand3 500 0 1 1 """"" 0"


Hope that helps. Its a technique I've used in the past when I wanted to import attributes, choices & permutations for particular products.

Please note that I haven't looked at where commas go or what the heading line should be. I'm sure you can sort that out.

rgds
Nilesh