Bulk import data with VBA and PHP

    Bulk import data with VBA and PHP

    Hi,

    I have an Excel sheet with many values which I have to load from/save to DB. I use an excel addin to controll my excel sheet with VBA. I could fasten the loading process by using a few PALO.DATAV formulas instead of many PALO.DATA formulas. Now I try to fasten the import which uses round about 10.000 PALO.SETDATA calls to save the values. But this is very slow and produces many overhead.

    I was looking for a bulk import function in VBA, something like SETDATAV. But the Jedox support mentioned that there isn't such a function in VBA. But in PHP there is a function called Setdata_bulk.

    I'm new in PHP. I know, that PHP is a scripting language. I figured out that I have to install a web server like apache with PHP/Palo support. But this is not the way I want to go. Is there a opportunity to use a PHP script controlled/started from VBA, e.g. on a local PHP interpreter?

    Greetings

    CNX
    Andreas Mueller
    FORECAST Unternehmensplanungen GmbH, Berlin, Germany
    www.forecast.de

    RE: Bulk import data with VBA and PHP

    Hi,

    I haven't used much PHP so I can't really help you with that aspect of things.

    However, have you considered importing via Palo ==> Data Import.

    Basically you just have your formulas based on the first line, and let excel cycle through your text file line by line.

    I can't seem to recall when you have to set the data (all 10,000 of them) all at the same time.

    There is a sample of how to do this in the manual.

    Your other alternative is the Palo ETL server which is quite easy to operate. Otherwise try other ETL tools such as cubeware Importer or Pentaho Kettle.

    Regards,

    Jimmy
    Hi,

    thanks for reply. Unfortunately I haven't a flat text file.

    The thing that I have a large, well layouted spreadsheet in which I show values to users. Users can open an object, edit values and probably he want to save his changes.

    These are the steps:
    1. I load all relevated value via VBA and DATAV to fill the cells.
    2. User can edit these values.
    3. I have to save these values and all excel calculated values to PALO.

    Each cell have it's parameters for PALO in column or row heads. I had tried to use PALO formulas direct in these cells. But this was not the best and savest way for this case. Now I'm back by using VBA to save values. But using SETDATA is to slow for my customer. Thats why I'm searching for a faster methode.

    Regards,

    CNX
    Andreas Mueller
    FORECAST Unternehmensplanungen GmbH, Berlin, Germany
    www.forecast.de
    Hi,

    It appears that you are not using Palo properly.

    You shouldn't have to load in Account 03. You can create a "Consolidation Element" with the children as Account 01, and Account 02.

    Secondly, a Palo spreadsheet can often be reduced, by having drop down boxes.

    What I normally find is that people will have a Palo spreadsheet with each region (Eg. North, South, East, West as well as a total) instead of having one spreadsheet, with the user changing the location as they navigate. When they want to see the total, they simply select total (Just by doing this, you instantly reduce your spreadsheet by 5 times the size).

    If you don't mind, post your spreadsheet skeleton(ie. No numbers), and I'll see if there is any way to improve it. It just seems to me odd with the problems you are currently facing.

    Kind Regards,

    Jimmy
    What you can use is an ETL tool to import your bulk data. There are open source tools available for free.

    Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes.

    For more information: talend.com/
    Originally posted by sebhm
    if it is not too late, i may help you...

    it's never to late to learn ;)

    ok, let me discribe the scenario:
    I'm in a VBA routine and have many data in an excel-sheet, which I have to save to server. I used the palo.setdata function. But this is to slow. That's why I search for a bulk insertion function.
    Andreas Mueller
    FORECAST Unternehmensplanungen GmbH, Berlin, Germany
    www.forecast.de