Importing CSV with Name & Code on file
For one of our models we have an import routine that brings in the below
for a variety of reasons it needs to be in this format. As there is both name / code when we go to import it doesn't actually make it because the of there being the name column and they're being used as time.
Beyond deleting the name columns prior to import does anyone have an idea as to getting it into the model .. bear in mind that is a user driven import ie run via button so there can't be any other technology used.
@Misbah - you may have already been contacted by my colleagues on this, would be good to get your thoughts here too!
Best Answer
-
I have a relatively bizarre solution that I used when I ran into a similar situation in the past. I will caveat this by saying, this is not the "correct" way to do it and that the correct method of fixing this is to get the data in the format you need it in, which I know you said is not an option.
You can create a dummy list with 1 item in it, something like "Total". Add that dimension to your module, and now your import will ask for mapping for that column. You would then need to map the items to "total". Since it is only one item it will not add any size.
Time for more caveats:
- Depending on how many items are in that list, and the frequency that they change, mapping all of them to total, might or might not be an option since they would need to manually be mapped and manually maintained.
- If this model is already built and this module gets referenced by a lot of places you might have to change the formulas to reference your new dimension as well. (Would make sense to put a top-level on the "total" list to eliminate this, but my point is there still could be additional work required to make it work)
It should work technically, but whether this makes sense in this particular case, is up to you!
Let me know if anything here is unclear and I can try to mock it up or add some more context.
Jason
1
Answers
-
Nope, nobody contacted me on this yet.
On your query you are saying that it doesn't make it "because the of there being the name column and they're being used as time." What does this mean? Will try my best to help here.
Misbah
0 -
Ah yes realise that what i've written doesn't make complete sense.
Basically because Time is mapped to the column headers then column 1 & 3 want to also be mapped as time but clearly can't be as they're the list item, and you can't have an unmapped column.
0 -
@andrewtye , it will be better to understand the issue if u will share the Import mapping screen prints.
And moreover, why do u want to map Column 1 & 3 (values: Region & Products) with Time?
0 -
I have come across this before and also found the import can completely fail if you have too many columns before the time months start. I don't remember finding an Anaplan solution but instead had to change the import file around, unfortunately.
1 -
@AWhitworth , Import works even if the file format contains multiple columns before dates e.g.
Snippet of the import file which we have imported in the model. Is it the one kind you were referring to?
But my concern on the original post is why to map value columns with Date columns?
0 -
Hi @jasonblinn
Had to read it a couple of times (might be not enough coffee yet today!) but yes that does make sense.
The lists in of themselves wouldn't change much but the values would and they spiderweb through the model as you would expect so might be quite the unravelling exercise to get it working in the "right" way.
When I talked it through with the superusers yesterday they were well if that's what we need to do ie deleting the columns that aren't mapped then so be it!
Andrew
1 -
It's not that i'm mapping the column names to time, but that because time are the column headers and there are columns that aren't going to be part of the import and so you end up with this error message because Anaplan thinks those columns are time formatted
0
