Importing data form Excel 2007

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Importing data form Excel 2007

    Hi all,
    Just new to Palo for Excel. I have been trying to import data from excel 2007 worksheet. First I did was export the excel worksheet to a csv. Then using the Palo import wizard, it imports the data row by row every time I click on the next button. But the problem is it places the data on the same cell in which previous data got replaced. And I just end up having just one row of data. How can I tell the wizard go down the rows? Any help. I am doing this for evaluation purpose and really want to get this set up so I can do a demo later for my college. Appreciate your help and have a great day.

    Kenny
  • RE: Importing data form Excel 2007

    Hi Kenny,

    The import is working as designed. Your next step is to create a "Setdata" function somewhere on the spreadsheet so that it reads the data from line 1 and stores it in Palo.

    Try the examples in the manual and you'll see what I mean.
  • RE: Importing data form Excel 2007

    Hi!

    The idea behind the import wizard is, it displays the contents of for example a flat file row by row and, after each row, recalculates any formulas that you have inserted in the spreadsheet.

    Your next task is therefore to insert a PALO.SETDATA() function that writes the values in the rows of your data source into a cube. That means, at this point you must have an appropriate cube that can hold your data.

    I believe it is a good idea to download the entire Palo manual, there is a good description in it.

    Have fun
    Holger
  • RE: Importing data form Excel 2007

    Hi holger_b,
    Thanks for the explanation. Really appreciate your reply, both you and jgibbs. I finally managed to download the advanced topics pdf from jedox and realized how much important info I had missed. Thanks again guys.

    Kenny
  • RE: Importing data form Excel 2007

    Hi there,
    I was walking through the 1st tutorial included in the manual and found my setdata (from the guess argument) is totally different than shown in the manual.

    I have:
    =PALO.SETDATA(value,FALSE,"localhost/Demo","Market","All Datatypes",D$2,"Units",$A$1,$B$1,$C$1)

    from manual
    =PALO.SETDATA(value, FALSE, "localhost/Demo", "Market", D$1, $C$1, $A$1, "Variance", "Units", $B$1)

    Even if I replaced the entire formula with the one from manual, it won't work either. Any suggestion folks?
  • RE: Importing data form Excel 2007

    Thanks for the pointer. I changed the thing accordingly but it still wouldn't work. I think it may have something to do with something else... Couldn't figure it out. In fact, I have been trying to importing some data from an excel spreadsheet. I have created a new database with a few dimensions , namely
    database name: GIS_data
    dimensions: Postal_Code, Count, Program_name, College_Code

    as well as a new cube Postal_n_Count containing Postal_Code and Count

    The spreadsheet is ready and has the above columns. Each column(dimension) has over a thousand records. Then I went off to insert 6 empty rows to the top of the worksheet. Enter "localhost/GIS_data in B1, Postal_n_Count in B2, and try to add Postal_Code and Count to B3 and B4 using the Paste Elements but it won't let me because they are dimensions, not elements. There are 1000+ types of elements in the worksheet, can't I just create an empty dimension and fill it with excel data? Any clue?
  • I'm confused by the dimensions in your "Postal_n_Count" cube. Count does not seem like a valid dimension.

    Anyway, if you're trying to lay out your dimensions on a spreadsheet and then trying to get Palo to read in the data sequentially using import wizard, it won't work.

    However, you could just use the setdata function. In the attached file I've defined the layout and in column D inserted the setdata function. You might want to let Palo guess it for you.

    I'm assuming that Post Code is a dimension and you want to measure how many addresses or applicants or whatever are in that postal code.

    Play with the formula in column D. When you get it to work (you'll know because it will return the value in column C) then copy the formula down column D. And watch out for your relative vs fixed references.

    It's crude but will work if you don't want to use the import wizard.

    The post was edited 1 time, last by jgibbs ().

  • Originally posted by jgibbs
    I'm confused by the dimensions in your "Postal_n_Count" cube. Count does not seem like a valid dimension.


    Thanks for your insight jgibbs. Would you tell why Count does not seem like a valid dimension?


    Anyway, if you're trying to lay out your dimensions on a spreadsheet and then trying to get Palo to read in the data sequentially using import wizard, it won't work.


    This is exactly what I was trying to do. Oh well...


    However, you could just use the setdata function. In the attached file I've defined the layout and in column D inserted the setdata function. You might want to let Palo guess it for you.


    does this work if you just have an empty dimension in the first place?


    I'm assuming that Post Code is a dimension and you want to measure how many addresses or applicants or whatever are in that postal code.


    There are many postal codes in the worksheet which are not in order. And the problem is many cells for Count do not have a value and Palo seems to ignore missing values in a cell and record only non-missing ones, is this right?


    Play with the formula in column D. When you get it to work (you'll know because it will return the value in column C) then copy the formula down column D. And watch out for your relative vs fixed references.

    It's crude but will work if you don't want to use the import wizard.


    Thanks for your reply. I haven't gotten the concept of importing data from excel worksheet using palo. Really need a thorough tutorial to walk me through.

    Regards,
    Kenny
  • Originally posted by knychw

    Thanks for your insight jgibbs. Would you tell why Count does not seem like a valid dimension?


    Because count is a result. When you define your dimensions in a view the intersection is where you would find count. Maybe it works for you, I just can't see it.


    does this work if you just have an empty dimension in the first place?

    It should. As long as you have the dimensions created and on the spreadsheet. Palo looks for the dimensions and guesses the formula. But why would you have an empty dimension? Haven't you already added all the postal codes?


    There are many postal codes in the worksheet which are not in order. And the problem is many cells for Count do not have a value and Palo seems to ignore missing values in a cell and record only non-missing ones, is this right?

    Correct. But not a problem in this case. If your cell is blank, you must also want it to be blank in Palo.

    If you've already added all the postal codes to in the "Postal Code" dimension, the order you send data to Palo is irrelevant. It will use the hierarchy already in the cube.

    Jason

    The post was edited 1 time, last by jgibbs ().

  • Thanks for your reply Jason. I guess I am having difficulty of understanding how excel worksheets can be imported to Palo. I guess I need more thorough tutorials and demos on that matter.

    Do you know any publications on Palo available out there? I haven't found any in English. I would like to learn more about Palo for Excel so that I would be able to do a demo for my college I am working for and make recommendations when my intern ends with them. The idea of having web-enabled report from Palo is really neat and sweet. Plus it's written in Java and running on Tomcat. Plus it's free. Love the product! Anyhow, thank you all for your replies and keep me posted about articles and tutorials. Have a good day!

    Kenny
  • Hi Holger,
    Thanks for the link. I am using Palo for Excel and I really wonder how I should organize my elements, dimensions, cubes etc accordingly so I can import the worksheet I have been trying to work on properly from Excel. Or should I organize the worksheet instead. I am still kinda lost there which direction I should go for....
  • following up.

    Hi all,
    I guess I now know my issues with importing from Excel. Let me explain what the issue is. As shown in the 1st attachment, it's a worksheet in Excel I am trying to import to Palo. The columns on this worksheet are YEAR, CAMPUS, PROGRAM, COLLEGE, PRGCODE, Q1, Q2, Q3, and Q4. I treat these columns as dimensions in a new database called KPI. So the structure looks like this,
    KPI
    -> YEAR
    -> CAMPUS
    -> PROGRAM
    -> COLLEGE
    -> PRGCODE
    -> Q1
    -> Q2
    -> Q3
    -> Q4

    But I wonder how to go about importing the data or should I make some tweak to this spreadsheet.
  • I believe your dimensions might look like this:

    Year
    Campus
    Program
    College
    Quarters

    The elements would be the entries in the respective columns.

    PrgCode is just an attribute (like an alias) of Program, so you can more or less skip it.

    What are the figures for? Attendants? Are there other measures than # of attendants?

    If you like, post your spreadsheet, I can set up a cube and spreadsheet import for you so you can see how you can go about.

    Cheers
    Holger
  • RE: following up.

    Kenny,

    find attached a little example which I set up to make things clearer (database and spreadsheet with SETDATA() formulas). Just stop the Palo service, add the "kenny" folder to your Palo\data folder, restart Palo and open the spreadsheet.

    Enjoy
    Holger

    The post was edited 1 time, last by holger_b ().

  • RE: following up.

    Hi Holger,
    I got your idea. So I need to sort and take out unique values from each column and paste these values into Palo (Paste elements). The challenge here is when I have more than a thousand of unique values (as elements) to paste into the palo server, they have to be modified to be "Strings" individually instead of their default being "Numeric". Doing so one by one is pretty time consuming but selecting them all is not an option though. Is there a way to quickly make them "strings"?

    Thanks,
    Kenny