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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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

Originally Posted by Surreall

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.

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

Originally Posted by Paul McKenzie

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

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

Originally Posted by Paul McKenzie

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

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

Originally Posted by Surreall

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

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

Originally Posted by D_Drmmr

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:

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

Originally Posted by Surreall

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.

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.

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.

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

Originally Posted by OReubens

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).

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?

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?