Decision Optimization

Expand all | Collapse all

How to import Excel data to CPLEX

  • 1.  How to import Excel data to CPLEX

    Posted Sat February 20, 2021 05:41 AM

    Hi,

    I have a question. I tried to import Excel data to CPLEX but it doesn't work.

    There is a problem accessing the Excel file. Can anyone help me how to solve this problem? Here I give the file.data of my file

    depot={"D1", "D2"};

    customer={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12" };

    node={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "D1", "D2" };

    vehicle={"V1", "V2"};

    SheetConnection excelsheet("Exceldata.xlsx");

    dist from SheetRead(excelsheet, "dist");

    fcost = [100, 100];

    D= [20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20];

    VC=[140, 140];

    VD=[280, 280];

    Thank you in advanced.



    ------------------------------
    Farahanim Misni
    ------------------------------


  • 2.  RE: How to import Excel data to CPLEX

    Posted Mon February 22, 2021 04:51 AM
    Hi,

    in https://www.linkedin.com/pulse/making-decision-optimization-simple-alex-fleischer/

    let me use https://github.com/AlexFleischerParis/zooopl/blob/master/zooexcel.mod

    where you can see

    SheetConnection s("zoo.xlsx");
    params from SheetRead(s,"params!A2");
    buses from SheetRead(s,"buses!A2:B3");
    results to SheetWrite(s,"buses!E2:F3");
    
    or if we prefer to rely on named range
    
    SheetConnection s("zoonamedrange.xlsx");
    params from SheetRead(s,"nbkids");
    buses from SheetRead(s,"buses");
    results to SheetWrite(s,"result");​



    in your example your named range may be wrong, can you try with somthing like E2:F7 ?

    Or share your files so that other users could try

    regards



    ------------------------------
    [Alex] [Fleischer]
    [EMEA CPLEX Optimization Technical Sales]
    [IBM]
    ------------------------------



  • 3.  RE: How to import Excel data to CPLEX

    Posted Mon February 22, 2021 10:55 AM
    Your command to access excel file looks okay.

    You need to make sure 
    1. The excel file exists already
    2. Its in the same location as the model file (or point to the location )

    ------------------------------
    Nzube Nzube
    ------------------------------



  • 4.  RE: How to import Excel data to CPLEX

    Posted Mon February 22, 2021 09:18 PM
      |   view attached
    I already put the Excel file in the same location of the project file. However it is still not working.
    Here I attach the file.mod , file.data and also the Excel file to be checked if something wrong that i did in the coding. 
    Thank you. Regards.

    file.mod:

    {string} depot=...;

    {string} customer=...;

    {string} vehicle=...;

    {string} node=...;

    int N=card(customer);

    float dist[node][node]=...;

    float fcost[depot]=...;

    float D[customer]=...;

    float VC[vehicle]=...;

    float VD[depot]=...;

    dvar boolean x[node][node][vehicle];

    dvar boolean z[depot];

    dvar boolean y[depot][customer];

    dvar float+ u[customer][vehicle];

    dexpr float facility=sum(i in node, j in node:j!=i, k in vehicle)x[i][j][k]*dist[i][j];

    dexpr float travel=sum(i in depot)fcost[i]*z[i];

    dexpr float cost=facility + travel;

     

    minimize cost;

    subject to {

    forall(j in customer) sum(i in node, k in vehicle)x[i][j][k]==1;

    forall(k in vehicle) sum(i in depot, j in customer)x[i][j][k]<=1;

    forall(k in vehicle) sum(i in node, j in customer:j!=i)D[j]*x[i][j][k]<=VC[k];

    forall(i in node, k in vehicle) (sum(j in node)x[i][j][k])- (sum(j in node)x[j][i][k])==0;

    forall(i in depot) sum(j in customer)D[j]*y[i][j]<=VD[i]*z[i];

    forall(j in customer) sum(i in depot)y[i][j]==1;

    forall(l in customer, j in customer, k in vehicle) u[l][k]-u[j][k]+N*x[l][j][k]<= N-1;

    forall(i in depot, j in customer, k in vehicle) (sum(h in node)(x[i][h][k]+x[h][j][k]))-y[i][j]<=1;

    }

    file.mod

    depot={"D1", "D2"};

    customer={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12" };

    node={"C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "D1", "D2" };

    vehicle={"V1", "V2"};

    SheetConnection excelsheet("Exceldata.xlsx");

    dist from SheetRead(excelsheet, "dist");

    fcost = [100, 100];

    D= [20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20];

    VC=[140, 140];

    VD=[280, 280];





    Attachment(s)

    xlsx
    Exceldata.xlsx   8 KB 1 version


  • 5.  RE: How to import Excel data to CPLEX

    Posted Tue February 23, 2021 02:46 AM
    Hi

    with

    dist from SheetRead(excelsheet, "Excel1!D3:Q16");​


    Your model works fine

    regards




    ------------------------------
    [Alex] [Fleischer]
    [EMEA CPLEX Optimization Technical Sales]
    [IBM]
    ------------------------------



  • 6.  RE: How to import Excel data to CPLEX

    Posted Tue February 23, 2021 04:30 AM
      |   view attached
    Hi

    I had a look the code seems okay.

    Looking at the excel file, the range was not named. 

    Alex's reply will work just fine.
    Also i have gone ahead to name the range on the excel file. 



    ------------------------------
    Nzube Nzube
    ------------------------------

    Attachment(s)

    xlsx
    Exceldata.xlsx   9 KB 1 version


  • 7.  RE: How to import Excel data to CPLEX

    Posted Sat February 27, 2021 02:23 AM
    Hi,
    Actually I already changed the name according to Alex's suggestion but still got the same problem which is "problem accessing excel file". I tried to do a new opl project and create a new excel file, but still didnt work as well. Is it because of my excel file or my programme system in my laptop? Or i did wrong in steps to import the excel file.
    Thank you. Regards.






  • 8.  RE: How to import Excel data to CPLEX

    Posted Thu March 11, 2021 11:30 PM
    Hi,
    I think something is wrong with my excel file. Still until now, CPLEX has a problem accessing my Excel file. Can I import other types of files such as csv files from Matlab to my CPLEX?
    I think I cannot extract any data from my Excel. I need to do some other alternatives.
    Thank you. Regards.

    On Sat, Feb 27, 2021 at 3:20 PM FARAHANIM BINTI MISNI . <farahanim@ump.edu.my> wrote:
    Hi,
    Actually I already changed the name according to Alex's suggestion but still got the same problem which is "problem accessing excel file". I tried to do a new opl project and create a new excel file, but still didnt work as well. Is it because of my excel file or my programme system in my laptop? Or i did wrong in steps to import the excel file.
    Thank you. Regards.






  • 9.  RE: How to import Excel data to CPLEX

    Posted Fri March 12, 2021 12:28 AM
    Hi,
    1. Yes you can import csv files
    2. You can always include the data in your .dat file.
    3. I will suggest you confirm no other application is accessing (locked) the excel file. 
    4. have you tried running some of the examples that include excel files? that might help too.


    ------------------------------
    [Nzube] [Nzube]
    [Optimisation Research]
    ------------------------------



  • 10.  RE: How to import Excel data to CPLEX

    Posted Fri March 12, 2021 03:07 AM
    Is it because of my version? I used CPLEX version 12.10.0. Or can I upgrade my version of CPLEX without reinstalling it?
    Thank you. Regards.


    "Think Green. Keep it on the screen.
    If printing is necessary, please print it on both sides."

    The information contained in this e-mail message and any accompanying files is or may be confidential. If you are not the intended recipient, any use, dissemination, reliance, forwarding, printing or copying of this e-mail or any attached files is unauthorized. This e-mail is subject to copyright. No part of it should be reproduced, adapted or communicated without the written consent of the copyright owner. If you have received this e-mail in error please advise the sender immediately by return e-mail or telephone and delete all copies. UMP does not guarantee the accuracy or completeness of any information contained in this e-mail or attached files. Internet communications are not secure, therefore UMP does not accept legal responsibility for the contents of this message or attached files.





  • 11.  RE: How to import Excel data to CPLEX

    Posted Fri March 12, 2021 04:05 AM
    Hi,

    again in https://www.linkedin.com/pulse/making-decision-optimization-simple-alex-fleischer/

    you have an example about read / write csv

    CSV files



    ------------------------------
    [Alex] [Fleischer]
    [EMEA CPLEX Optimization Technical Sales]
    [IBM]
    ------------------------------