Getting data in to C++ from excel, any help would be greatly appreciated
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 4 1234 LastLast
Results 1 to 15 of 54

Thread: Getting data in to C++ from excel, any help would be greatly appreciated

  1. #1
    Join Date
    Jan 2013
    Posts
    27

    Getting data in to C++ from excel, any help would be greatly appreciated

    Hi there,

    I am very very new to C++. A bit of background. I have been writing in excel vba for large number crunching, and the code is now taking quite a while to run. A friend of mine suggested i start writing in C++, so i read up on it. And downloaded Code:Blocks.

    My VBA Code is:

    Code:
    Sub arrayss()
    
    Dim NameArray As Variant
    Dim datarray As Byte
    Dim DirectionArray As Variant
    Dim WinArr As Variant
    Dim Results As Variant
    Dim iColumns As Long
    Dim jRows As Long
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim q As Long
    Dim tradevalue As Long
    Dim noofrowsWin As Long
    Dim noofcolumnsWin As Long
    Dim cell1position As Long
    Dim cell2position As Long
    Dim cell3position As Long
    Dim cell4position As Long
    Dim cell1value As Long
    Dim cell2value As Long
    Dim cell3value As Long
    Dim cell4value As Long
    Dim y As Long
    Dim z As Long
    
    
    
    'Range("AN:BBB").Delete
    
    Application.Calculation = xlCalculationManual
    
    dataarray = ThisWorkbook.Names("Data1").RefersToRange.Value
    NameArray = ThisWorkbook.Names("NameArray").RefersToRange.Value
    DirectionArray = ThisWorkbook.Names("DirectionArray").RefersToRange.Value
    
    cell1position = Sheets("Test").Cells(2, 3)
    cell2position = Sheets("Test").Cells(3, 3)
    cell3position = Sheets("Test").Cells(4, 3)
    cell4position = Sheets("Test").Cells(5, 3)
    cell1value = Sheets("Test").Cells(7, 3)
    cell2value = Sheets("Test").Cells(8, 3)
    cell3value = Sheets("Test").Cells(9, 3)
    cell4value = Sheets("Test").Cells(10, 3)
    tradevalue = Sheets("Test").Cells(12, 3)
    jRows = 1
    iColumns = 1
    a = 3
    b = 1
    y = 0
    z = 0
    i = 1
    j = 1
    Sheets("Final").Cells(3, 1) = "=Counta(2:2)-1"
    Sheets("Final").Cells(1, 2) = "=COUNTA(A:A)"
    noofcolumnsWin = ((Sheets("Final").Cells(3, 1)) - 1) ^ 2 - (Sheets("Final").Cells(3, 1) - 1)
    noofrowsWin = Sheets("Final").Cells(1, 2) + 1
    
    ReDim Results(1 To 500, 1 To 16)
    For q = 1 To UBound(dataarray, 2) - 1
        
        For c = 1 To UBound(dataarray, 2) - 1
            If c = q Then
            GoTo labelend3
            End If
            For jRows = 1 To UBound(dataarray, 1) - cell4position
                If IsEmpty(dataarray(jRows, q)) Or DirectionArray(jRows, 1) = 15 Or DirectionArray(jRows, 1) = 45 Then
                ElseIf dataarray(jRows + cell1position, c) = cell1value And dataarray(jRows + cell2position, c) = cell2value And dataarray(jRows + cell3position, c) = cell3value And dataarray(jRows + cell4position, c) = cell4value Then                 ' cell check
                    If tradevalue = dataarray(jRows, q) Then            ' win or lose
                        y = y + 1
                    Else
                        z = z + 1
                    End If
                End If
            a = a + 1
    labelend:
            Next
            
            If z = 0 Then
                z = 1
            End If
            
            If y = 0 Then
                y = 1
            End If
            
            If y > 20 And y / z > 5 Then
                Results(i, j) = NameArray(1, q)
                Results(i, j + 1) = NameArray(1, c)
                Results(i, j + 2) = y
                Results(i, j + 3) = z
                Results(i, j + 4) = y / z
                Results(i, j + 5) = cell1position
                Results(i, j + 6) = cell2position
                Results(i, j + 7) = cell3position
                Results(i, j + 8) = cell4position
                Results(i, j + 9) = ""
                Results(i, j + 10) = cell1value
                Results(i, j + 11) = cell2value
                Results(i, j + 12) = cell3value
                Results(i, j + 13) = cell4value
                Results(i, j + 14) = ""
                Results(i, j + 15) = tradevalue
                i = i + 1
            End If
            
            y = 0
            z = 0
            a = 3
            b = b + 1
            '''  PUT IN HERE THE TEST FOR COUNTING 1's and zeros or below lol
            ''''
    labelend3:
        Next
    a = 3
    labelend1:
    Next
    
    
    Sheets("Test").Select
    Range("an:an").End(xlDown).Offset(1, 0).Select
    a = ActiveCell.Row
    Range(Range("an" & a & ""), Range("an" & a & "").Offset(500, 16)).Value = Results
     
    'Range(Range("m1"), Range("m1").Offset(10000, 16)).Value = Results
    'Range(Range("g1"), Range("g1").Offset(noofrowsWin - 1, noofcolumnsWin - 1)).Value = WinArr
    'Range(Range("g1"), Range("g1").Offset(noofrowsWin - 1, noofrowsWin - 1)).Value = Application.Transpose(WinArr)
        
    Application.Calculation = xlCalculationAutomatic
    
    
    End Sub
    Ultimately i would like to recode this to C++, but my first and probably silly question is how do i get the data from Excel to use in C++. I was thinking either to put the data in 3 csv files and convert into three Arrays in C++. Or maybe create a library of the data in C++. Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.

    Does that make sense?

    The data is like this in excel: (don't know how to create a table)

    ABC DCA GFE THE
    15 0 1 0 1
    30 1 0 1 0
    45 1 1 0
    00 0 0 1
    15 1 1 0
    15 1 0 0 1
    15 0 1 1 1
    30 1 0 0
    45 1 1 1

    So the headers would be in one array, the 15's, 30's etc would be in another array and the 1's and 0's and Empty ( i need it to record an empty cell) would be in another array

    Rgds

    Surreall

    PS and thankyou for your time

  2. #2
    Join Date
    Apr 1999
    Posts
    27,429

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    Hi there,

    I am very very new to C++. A bit of background. I have been writing in excel vba for large number crunching, and the code is now taking quite a while to run. A friend of mine suggested i start writing in C++, so i read up on it. And downloaded Code:Blocks.
    C++ is not a trivial language. You should learn very basics first before even thinking about "speed".

    Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
    Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.
    Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.

    There are tons of CSV parsers written in C++, but you need to understand C++ to use them.

    Regards,

    Paul McKenzie

  3. #3
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Paul McKenzie View Post
    C++ is not a trivial language. You should learn very basics first before even thinking about "speed".

    Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
    Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.

    There are tons of CSV parsers written in C++, but you need to understand C++ to use them.

    Regards,

    Paul McKenzie
    I understand i need to learn C++ first

    But not to worry i have been reading up about reading from csv file. And have written code that reads all the values from the csv file, still a long way to go. Was just wondering what would be quick way of doing it for future reference as the data is getting big quick. But as you say i need to learn C++ first. I will go away and learn it. Sorry to bother you with silly questions, my mistake

    Rgds

    Surreall

  4. #4
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Paul McKenzie View Post
    C++ is not a trivial language. You should learn very basics first before even thinking about "speed".

    Second, C++ isn't like VBA or some other language, where you have a lot of firewalls and escape hatches when you make a mistake. You make a mistake in VBA such as an out of bounds access, you get a nice error message. You make the same type of mistake in C++, you may not get any error, and your program seems to run, but with that fault in it that you don't know about.
    Honestly, that whole idea of trying to recode something in C++ without knowing C++ is IMO wrought with all sorts of problems. Again, C++ is not a trivial language, it has a lot of nuances that the beginner is not aware of, and requires experience to understand how to write code that is not only efficient, but maintainable.

    There are tons of CSV parsers written in C++, but you need to understand C++ to use them.

    Regards,

    Paul McKenzie
    I understand i need to learn C++ first

    But not to worry i have been reading up about reading from csv file. And have written code that reads all the values from the csv file, still a long way to go. Was just wondering what would be quick way of doing it for future reference as the data is getting big quick. But as you say i need to learn C++ first. I will go away and learn it. Sorry to bother you with silly questions, my mistake

    Rgds

    Surreall

  5. #5
    GCDEF is offline Elite Member Power Poster
    Join Date
    Nov 2003
    Posts
    12,106

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    So do you need to read CSV files or directly from Excel? If it's CSV, mentioning Excel is irrelevant, so first define your problem properly.

    If you're using Visual C++, you can use ODBC and the CRecordset class to read directly, but as has been mentioned, you'll have a really steep learning curve.

  6. #6
    Join Date
    Jul 2005
    Location
    Netherlands
    Posts
    2,013

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    Ultimately i would like to recode this to C++, but my first and probably silly question is how do i get the data from Excel to use in C++. I was thinking either to put the data in 3 csv files and convert into three Arrays in C++. Or maybe create a library of the data in C++. Ultimately it is speed i am looking for, so before i start recoding i wanted to start with the best way.
    There are three possibilities.
    1. Loose coupling. You create a program in C++ that reads some files and processes the data in them. You can generate the input files with VB or whatever. This is the easiest option to implement, but it requires data exchange via disk and a fixed format for the data.
    2. Run C++ DLL from VBA. You can create a C++ DLL that exports a function that does the data processing. You call this function from VBA and pass it the required data.
    3. Run from C++. You can read the data from an Excel file in your C++ program. This requires some interop with Excel, or to use a library which can read Excel files.

    Depending on how complex your data processing is, you may be able to achieve the first option with only a limited knowledge of C++. However, you need to master the basics well in order to be able to write highly efficient programs.
    Cheers, D Drmmr

    Please put [code][/code] tags around your code to preserve indentation and make it more readable.

    As long as man ascribes to himself what is merely a posibility, he will not work for the attainment of it. - P. D. Ouspensky

  7. #7
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by D_Drmmr View Post
    There are three possibilities.
    1. Loose coupling. You create a program in C++ that reads some files and processes the data in them. You can generate the input files with VB or whatever. This is the easiest option to implement, but it requires data exchange via disk and a fixed format for the data.
    2. Run C++ DLL from VBA. You can create a C++ DLL that exports a function that does the data processing. You call this function from VBA and pass it the required data.
    3. Run from C++. You can read the data from an Excel file in your C++ program. This requires some interop with Excel, or to use a library which can read Excel files.

    Depending on how complex your data processing is, you may be able to achieve the first option with only a limited knowledge of C++. However, you need to master the basics well in order to be able to write highly efficient programs.
    Many thanks for your help.

    I have written some code now....and the reading in doesnt actually take long less than half a second. So all the time is going to be taken up with anaylising the arrays i have created. So not too worried about it.

    Anyways here is where i have got to now, i did make functions initally to do the each array build, but it didnt make any difference in time. So i left them now in the main body.

    The part that takes a little while, although it is gone from approx 6 seconds in vba to around 0.1 seconds in C++ (very very very chuffed about c++). I would still like to get this quicker if possible.

    So any suggestions for speeding up the following code would be greatly appreciated:

    Code:
    #include <fstream>
    #include <iostream>
    #include <sstream>
    #include <string>
    
    using namespace std;
    
    int main()
    {
    
    
    // this code finds the number of rows in a csv file
    ifstream data("Data1.csv");
    string line;
    int rows = 0;
    while (getline(data,line,'\n'))
    {
        rows++;
    }
    //cout << "Counted " << rows << " rows." << endl;
    data.close();
    
    
    // this code finds the number of rows in a csv file
    ifstream data1("Data1.csv");
    string line1;
    int columns = 0;
    getline(data1,line1);
    stringstream ss(line1);
    string field;
    while (getline(ss,field,','))
        columns++;
    //cout << "Counted " << columns << " columns." << endl;
    data.close();
    
    
    
    /////This populats the dataarray with data1.csv crap
    int dataarray[rows][columns];
    
    ifstream data2("Data1.csv");
    string line2;
    int rows1= 0;
    while (getline(data2,line2,'\n'))
    {
        stringstream bb(line2);
        string field1;
        int columns1 = 0;
        while (getline(bb,field1,','))
        {
            istringstream buffer(field1);
            int value = 2;
            buffer >> value;
            dataarray[rows1][columns1] = value;
            columns1++;
        }
        rows1++;
    }
    //cout << "Datarray element, " << dataarray[3][0] << endl;
    data.close();
    
    
    // do the same for directionarray
    int directionarray[rows];
    ifstream direction("direction.csv");
    string line3;
    int rows2 = 0;
    while (getline(direction,line3, '\n'))
        {
        istringstream buffer1(line3);
        int value1 = 0;
        buffer1 >> value1;
        directionarray[rows2] = value1;
        rows2++;
        }
    //cout << "Directionarray element, " << directionarray[0] << endl;
    
    // do the same for headerarray
    //int headerarray[columns];
    
    string headerarray[columns];
    ifstream header("Header.csv");
    string line4;
    int columns2 = 0;
    while (getline(header,line4, ','))
    {
        headerarray[columns2] = line4;
        columns2++;
    }
    
    //cout << "Headerarray element, " << headerarray[36] << endl;
    
    //i have now created arrays, dataarray, directionarray & headerarray with correct elements
    
    int indicator1position = 4;
    int indicator2position = 5;
    int indicator3position = 6;
    int indicator4position = 7;
    int indicator1value = 1;
    int indicator2value = 1;
    int indicator3value = 1;
    int indicator4value = 1;
    
    int trade1position = 4;
    int trade2position = 5;
    int trade3position = 6;
    int trade4position = 7;
    int trade1value = 1;
    int trade2value = 1;
    int trade3value = 1;
    int trade4value = 1;
    
    int tradevalue = 1;
    int numberofcells = 4;
    
    float y = 0;
    float z = 0;
    
    //string results[50000][18];
    
    
    //for (trade1position = 4;trade1position < 12;trade1position++){
    
    float resultsarraynumbers[3000][20];
    string resultsarraywords[3000][20];
    int resultsrow=0;
    int resultscolumn=0;
    
    //for (trade1position = 4;trade1position<13;trade1position++)
    //{
    for (trade2position = 1;trade2position<9;trade2position++)
    {
    for (int q = 0;q < columns - 1; q++)
        {
        for (int c = 0;c < columns - 1; c++)
            {
            if (q==c) {} else
                {
                for (int jRows = 0;jRows < rows - 1; jRows++)
                    {
                    if (dataarray[jRows][q]==2 || directionarray[jRows]==15 || directionarray[jRows]==45){}
                    else if (numberofcells == 4)
                        {   if (dataarray[jRows + indicator1position][c]==indicator1value && \
                            dataarray[jRows + indicator2position][c]==indicator2value && \
                            dataarray[jRows + trade1position][q]==trade1value && \
                            dataarray[jRows + trade2position][q]==trade2value)
                            {
                            if (tradevalue == dataarray[jRows][q])
                                {y++;} else {z++;}
                            }
                        }
                    }
                    if(y==0){y = 1;} else if (z==0){z = 1;}
                    if ( y/z > 1.5 && y > 10 )
                    {
                    resultsarraynumbers[resultsrow][resultscolumn] = y/z;
                    resultsarraynumbers[resultsrow][resultscolumn +1] = y;
                    resultsarraynumbers[resultsrow][resultscolumn +2] = z;
                    resultsarraynumbers[resultsrow][resultscolumn +3] = indicator1position;
                    resultsarraynumbers[resultsrow][resultscolumn +4] = indicator2position;
                    resultsarraynumbers[resultsrow][resultscolumn +5] = trade1position;
                    resultsarraynumbers[resultsrow][resultscolumn +6] = trade2position;
                    resultsarraynumbers[resultsrow][resultscolumn +7] = numberofcells;
                    resultsarraynumbers[resultsrow][resultscolumn +8] = indicator1value;
                    resultsarraynumbers[resultsrow][resultscolumn +9] = indicator2value;
                    resultsarraynumbers[resultsrow][resultscolumn +10] = trade1value;
                    resultsarraynumbers[resultsrow][resultscolumn +11] = trade2value;
                    resultsarraynumbers[resultsrow][resultscolumn +12] = tradevalue;
    
                    resultsarraywords[resultsrow][resultscolumn] = headerarray[q];
                    resultsarraywords[resultsrow][resultscolumn + 1] = headerarray[c];
                    resultsrow++;
                    }
                    y = 0;
                    z = 0;
                }
            }
        }
    }
    //}
    
    cout << "Results number, y/z=" << resultsarraynumbers[0][0] << endl;
    cout << "Results number, y=" << resultsarraynumbers[0][1] << endl;
    cout << "Results number, z=" << resultsarraynumbers[0][2] << endl;
    cout << "Results trade, " << resultsarraywords[0][0] << endl;
    cout << "Results indicator, " << resultsarraywords[0][1] << endl;
    cout << "Results, y= " << y << endl;
    
    return 0;
    }
    As far as i can figure the bottle neck is here:

    Code:
                    if (dataarray[jRows][q]==2 || directionarray[jRows]==15 || directionarray[jRows]==45){}
                    else if (numberofcells == 4)
                        {   if (dataarray[jRows + indicator1position][c]==indicator1value && \
                            dataarray[jRows + indicator2position][c]==indicator2value && \
                            dataarray[jRows + trade1position][q]==trade1value && \
                            dataarray[jRows + trade2position][q]==trade2value)
                            {
                            if (tradevalue == dataarray[jRows][q])
                                {y++;} else {z++;}
                            }
                        }
    By the way i am loving C++ for its quickness, great stuff. And thanks for the help previously. I know my code is butchered but i have had one day of learning it so far so bear with me please

    Rgds

    Surreall

  8. #8
    Join Date
    Apr 1999
    Posts
    27,429

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    I have written some code now....and the reading in doesnt actually take long less than half a second. So all the time is going to be taken up with anaylising the arrays i have created. So not too worried about it.
    What exactly are you trying to accomplish with the arrays? There are probably better ways to handle the code so that it is maintainable.

    Here is the issue I have with your code.

    1) You have hard-coded magic numbers. What significance is 2, 15, 45, etc.?

    2) This is not legal C++:
    Code:
    int dataarray[rows][columns];
    //..
    string headerarray[columns];
    You cannot declare arrays using a variable as the number of rows and columns. What you are probably using is some extension of C++, not ANSI C++. You are more than likely using gcc or some compiler based on gcc. If you set the compiler switches to ANSI for that compiler, you will see that your code will fail to compile. It will definitely not compile with any version of Visual C++. Since it is an extension, you now need to read up on all the quirks and caveats of using such an extension.

    The std::vector<T> class is used by standard C++ to declare a dynamic array. This works across all ANSI C++ compilers, is standard, is guaranteed to be on every ANSI C++ compiler, is documented on how it must work, etc.

    3)
    Code:
    resultsarraynumbers[resultsrow][resultscolumn] = y/z;
                    resultsarraynumbers[resultsrow][resultscolumn +1] = y;
                    resultsarraynumbers[resultsrow][resultscolumn +2] = z;
                    resultsarraynumbers[resultsrow][resultscolumn +3] = indicator1position;
                    resultsarraynumbers[resultsrow][resultscolumn +4] = indicator2position;
                    resultsarraynumbers[resultsrow][resultscolumn +5] = trade1position;
                    resultsarraynumbers[resultsrow][resultscolumn +6] = trade2position;
                    resultsarraynumbers[resultsrow][resultscolumn +7] = numberofcells;
                    resultsarraynumbers[resultsrow][resultscolumn +8] = indicator1value;
                    resultsarraynumbers[resultsrow][resultscolumn +9] = indicator2value;
                    resultsarraynumbers[resultsrow][resultscolumn +10] = trade1value;
                    resultsarraynumbers[resultsrow][resultscolumn +11] = trade2value;
                    resultsarraynumbers[resultsrow][resultscolumn +12] = tradevalue;
    If you know the layout of the data, then you define a single struct and create an array of that struct instead of a two dimenisonal array.
    Code:
    #include <vector>
    struct MyData
    {
       int y;
       int z;
       int indicator1position;
       //...
    };
    
    typedef std::vector<MyData> AllMyData;
    //...
    AllMyData mData( number_of_data_elements );
    mData[resultsrow].indicator1position = indicator1.position;
    //...etc.
    This eliminates the +1, +2, +3, etc. magic numbers.

    Regards,

    Paul McKenzie
    Last edited by Paul McKenzie; January 31st, 2013 at 05:02 AM.

  9. #9
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Thats a lot to take in, thankyou for your time.

    I am using Code::Blocks editor and on the compiler settings menu, it had automatically selected GNU GCC Compiler. Which compiler should i chose, there are over 30 different types. Is gcc bad? I don't mean bad, but i presume i want to be coding in basic c++? (sorry i am a little confused)

    for the magic numbers 2, 15, and 45.

    What i am acheiving with the arrays, is comparing corresponding 1's and 0's in each column of dataarray.
    or matrix of dataarray example:

    011
    110
    101

    Eg. if element[1][1] = 1 and element[2][1] = 1 & if element[0][0] = 1, the set (as above) y = 1. Adding in the magic numbers ignore all of this if the corresponding direction array is equal to 15 or 45

    In the main dataarray, it is filled with 0's, 1's and 2's. On each iteration i want to ignore it when the dataarray == 2. The same with 15 and 45 in the direction array. I am only interested in the other ones. I cannot delete them as i need the rest of the 0's and 1's that are in the other corresponding rows.

    I know i am not explaining too well its hard to get ya head round it

    Rgds

    Surreall

  10. #10
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Okay after lots of searching, got the -ANSI now. And as you said it doesnt like variable length for array

  11. #11
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    3,917

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    you have 4 nested 4 loops.
    From a quick glance, it doesn't look like the innermost code has any obvious shortcuts or way to speed things up (much).

    If you want even more speed, then the trick now is to figure out a better method for achieving the same results rather than finding a few tweaks here and there.
    Without knowing what your code actually tries to accomplish, there's not much we can do there to help though.

    q and c both iterating over columns with an exception if q and c are identical makes me believe you are probably doing things that can be removed.

  12. #12
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by OReubens View Post
    you have 4 nested 4 loops.
    From a quick glance, it doesn't look like the innermost code has any obvious shortcuts or way to speed things up (much).

    If you want even more speed, then the trick now is to figure out a better method for achieving the same results rather than finding a few tweaks here and there.
    Without knowing what your code actually tries to accomplish, there's not much we can do there to help though.

    q and c both iterating over columns with an exception if q and c are identical makes me believe you are probably doing things that can be removed.
    Sounds fair enough. With the q and c if you look at my little 3x3 matrix above. I compare column1(ie q) with the second and third column (ie c). I then compare column 2 (q) with the first and third column (ie c) and lastely compare column3 (q) with the first and second column(c).

    The actual array has 39 columns, so lots and lots of iterations

    Rgds

    Surreall

  13. #13
    Join Date
    Jan 2013
    Posts
    27

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Here is the updated code:

    Code:
    #include <fstream>
    #include <iostream>
    #include <sstream>
    #include <string>
    #include <vector>
    
    using namespace std;
    
    int main()
    {
    /////This populats the dataarray with data1.csv crap
    int dataarray[6077][37];
    
    ifstream data2("Data1.csv");
    string line2;
    int rows1= 0;
    while (getline(data2,line2,'\n'))
    {
        stringstream bb(line2);
       string field1;
        int columns1 = 0;
        while (getline(bb,field1,','))
        {
            istringstream buffer(field1);
            int value = 2;
            buffer >> value;
            dataarray[rows1][columns1] = value;
            columns1++;
        }
        rows1++;
    }
    
    // do the same for directionarray
    int directionarray[6077];
    ifstream direction("direction.csv");
    string line3;
    int rows2 = 0;
    while (getline(direction,line3, '\n'))
        {
        istringstream buffer1(line3);
        int value1 = 0;
        buffer1 >> value1;
        directionarray[rows2] = value1;
        rows2++;
        }
    //cout << "Directionarray element, " << directionarray[0] << endl;
    
    // do the same for headerarray
    //int headerarray[columns];
    
    string headerarray[37];
    ifstream header("Header.csv");
    string line4;
    int columns2 = 0;
    while (getline(header,line4, ','))
    {
        headerarray[columns2] = line4;
        columns2++;
    }
    
    
    //i have now created arrays, dataarray, directionarray & headerarray with correct elements
    
    int indicator1position = 4;
    int indicator2position = 5;
    
    int indicator1value = 0;
    int indicator2value = 1;
    
    int trade1position = 4;
    int trade2position = 5;
    
    int trade1value = 1;
    int trade2value = 1;
    
    int tradevalue = 1;
    int numberofcells = 4;
    
    int q = 0;
    int c = 0;
    
    float y = 0;
    float z = 0;
    
    //float resultsarraynumbers[5000][18];
    vector<float> resultsarraynumbers;
    vector<string> resultsarraywords;
    
    int jRows = 0;
    int rows = 6077;
    int columns = 37;
    int rows3 = 0;
    
    
    for (indicator1position = 4;indicator1position < 13; indicator1position++)
    {
    for (indicator2position = 1 + indicator1position;indicator2position < 9+ indicator1position; indicator2position++)
    {
    for (trade1position = 4;trade1position < 13; trade1position++)
    {
    for (trade2position = 1 + trade1position;trade2position < 9 + trade1position; trade2position++)
    {
    for (q = 0;q < columns - 1; q++)
        {
       for (c = 0;c < columns - 1; c++)
            {
            if (q==c) {} else
                {
                for (jRows = 0;jRows < rows - 50; jRows++)
                    {
                    if (dataarray[jRows][q]==2 || directionarray[jRows]==15 ||  directionarray[jRows]==45){}
                    else if (numberofcells == 4)
                        {   if (dataarray[jRows + indicator1position][c]==indicator1value && \
                            dataarray[jRows + indicator2position][c]==indicator2value && \
                            dataarray[jRows + trade1position][q]==trade1value && \
                            dataarray[jRows + trade2position][q]==trade2value)
                            {
                            if (dataarray[jRows][q]==tradevalue)
                                {y++;} else {z++;}
                            }
                        }
                    }
                    float t = (y/z);
                    if(y==0){y = 1;} else if (z==0){z = 1;}
                    if ( t > 4 && y > 10 )
                    {
                    //cout << t << endl;
                    resultsarraynumbers.push_back(t);
                    resultsarraynumbers.push_back(y);
                    resultsarraynumbers.push_back(z);
                    //rows3++;
                    resultsarraywords.push_back (headerarray[q]);
                    resultsarraywords.push_back (headerarray[c]);
                    }
                   }
                    y = 0;
                    z = 0;
                }
            }
    }
    }
    }
    }
    
    //for (vector<int>::iterator it = v.begin(); it!=v.end(); ++it) {
    //printout
    //   cout << *it << ", " << endl;
    //}
    
    cout << "Results number, t=" << resultsarraynumbers[0] << endl;
    cout << "Results number, y=" << resultsarraynumbers[1] << endl;
    cout << "Results number, z=" << resultsarraynumbers[2] << endl;
    cout << "Results trade, " << resultsarraywords[0] << endl;
    cout << "Results indicator, " << resultsarraywords[1] << endl;
    cout << "Results, y= " << y << endl;
    cout << "Results, z= " << z << endl;
    
    return 0;
    }
    Which runs pretty darn well, albeit takes around 6 minutes to finish. And i still have one more for loop to add :/

    But question is i would now like to output the results into a csv file. But stupidly i have coded it so that resultsarraynumbers is a float and resultsarraywords is a string. (only way i could get it to work)

    But i need to list the first 3 elements of the float and then the first 2 elements of the string in first line of a csv file.

    Then on the next line in the csv i need to put the next 3 of float followed by the next two of elements etc till the end of the vectors.

    Is there a way to combine the vectors in this manner? And then export that to csv file?

    Rgds

    Surreall

  14. #14
    Join Date
    Dec 2012
    Location
    England
    Posts
    2,449

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Perhaps the easiest way is to define a struct to contain the 3 numbers and the two strings and then declare a vector of this struct. The example below illustrates a possible method

    Code:
    #include <vector>
    #include <string>
    #include <fstream>
    using namespace std;
    
    //define structure to contain what is required for each line in the csv file
    struct results {
        double t, y, z;
        string q, c;
    };
    
    int main()
    {
    vector<results> resultsarray;
    
    results oneres;
    
    //Open output file for the csv
    ofstream ofs("results.csv");
    
    //Put some data into the result vector for test
    	for (int l = 1; l < 20; l++) {
    		oneres.t = l + 3.4;
    		oneres.y = l + 3.5;
    		oneres.z = l + 3.6;
    		oneres.q = "s1";
    		oneres.c = "s2";
    		resultsarray.push_back(oneres);
    	}
    
    //Write result vector to output csv file
    	for (vector<results>::iterator it = resultsarray.begin(); it != resultsarray.end(); ++it) {
    		ofs << it->t << "," << it->y << "," << it->z << "," << it->q << "," << it->c << endl;
    	}
    	ofs.close();
    	
    	return 0;
    }
    I note that you are now using fixed size arrays and that there is no error detection in case your data contains more rows/columns than expected. As c++ doesn't as standard provide bounds checking for arrays, may I suggest that when you populate these arrays you check that the bounds aren't exceeded?

  15. #15
    Join Date
    Apr 1999
    Posts
    27,429

    Re: Getting data in to C++ from excel, any help would be greatly appreciated

    Quote Originally Posted by Surreall View Post
    Which runs pretty darn well, albeit takes around 6 minutes to finish.
    First, are you running an optimized build, not a debug build? If you're running a debug build, it should not be used for timing purposes. Always time a fully optimized build.

    6 minutes to do something means you have either:

    1) a huge amount of data (millions of records), or
    2) the code you wrote is ineffficient, or
    3) you're running a debug build, or
    4) some combination of the above 3 items.

    Regards,

    Paul McKenzie

Page 1 of 4 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center