Lookup table generation tool

[WillyC]

EDIT: I had earlier attached a .xls file, but I just tried to open it and got garbly-gook. Here it is zipped up; this should work better. Also, In the example I've attached, it's just a linear transformation (y=x) and is basically useless as is. But you can change the spreadsheet formulas to generate whatever lookup table you like.


If you've been generating lookup tables for your program, you know that it's not much fun to have to manually type all of the raw values into your code.

I generate all of my lookup tables in Excel, and I wrote this Excel macro to export the data to a text file so that it's much easier to just cut and paste the lookup table declarations from the text file into the C code.

Enable macros when you open up the .xls file, and use the Excel spreadsheet/math functions to create your lookup table. Then just specify the filename and hit the Export button. If you want to customize the VBA code for the macro, from Excel just go to View-Toolbars-Visual Basic and then click on the Visual Basic Editor button. The VBA code is pretty self-explanatory.

Maybe this will save you a few minutes of tedious typing...

EDIT: I had earlier attached a .xls file, but I just tried to open it and got garbly-gook. Here it is zipped up; this should work better. Also, In the example I've attached, it's just a linear transformation (y=x) and is basically useless as is. But you can change the spreadsheet formulas to generate whatever lookup table you like.

[Ryan Cumings]

If you use a lot of values it can be easy to write a PERL script to generate the c file for you.

[WillyC]

Originally Posted by Ryan Cumings

If you use a lot of values it can be easy to write a PERL script to generate the c file for you.

Yep, that's a good idea too. I like Excel because you can plot your data, and it has good math functions built in. Either way, whatever saves you time is good, right?

[mtrawls]

Well, you can plot graphs of your data in Perl, you just have to know what you are doing Besides, who doesn't love a command line? Well, to do it in Perl is rather easy, and very customizable ... I'm sure ya'll can figure it out! Here's an example, sans formatting (e.g., float lookup_table[], etc.).

use warnings;
use strict;

use Math::Trig;

open (OUT, ">your_file.txt") or die "No go!";

for (0..90) {
  print OUT sin($_);  print OUT ", " unless $_ == 89;
}

Edit: and you can use the handy printf function, instead of print, for (as the f probably implies already) formatting. (e.g.,printf ("%.3f", sin (3.14159/4)); )

[KevinB]

Hey ... I like this!

Beware though ... you may need to manually remove decimal values from the generated tables.

Also, you need to ensure that the values that you generate are under 256 (unless you change how the array is defined.)

[WillyC]

Originally Posted by KevinB

Hey ... I like this!

Beware though ... you may need to manually remove decimal values from the generated tables.

Also, you need to ensure that the values that you generate are under 256 (unless you change how the array is defined.)

Thanks Kevin, hopefully it helps. I like excel because you can change formatting, copy formulas etc really easily. I have set both of the data columns to show 0 decimal places, so you shouldn't have to manually edit any numbers. You can also use the Round() worksheet function to specify how many decimal places to use.

[KevinB]

Originally Posted by WillyC

I have set both of the data columns to show 0 decimal places, so you shouldn't have to manually edit any numbers.

In the .txt file generated, the numbers are not rounded.

[WillyC]

Originally Posted by KevinB

In the .txt file generated, the numbers are not rounded.

Really?? Hmm. Here's a suggestion: in column B you're probably calculating some formula based on the data in column A. Wrap the following around your formula for column B: "=Round( (your formula) , 0)" The ,0 specifies that you want 0 decimal places. That should take care of it.

[KevinB]

Originally Posted by WillyC

Really?? Hmm. Here's a suggestion: in column B you're probably calculating some formula based on the data in column A. Wrap the following around your formula for column B: "=Round( (your formula) , 0)" The ,0 specifies that you want 0 decimal places. That should take care of it.

It does. Thanks again for your tool.

[Tom Bottiglieri]

i cant get this to work. I input all the values i want.. then click on export. It brings up vb code windows. It also gives me a error that says macros cannot be used in this project. I have tried everything i know about excel (very little) to get this to work. What should i do?

[WillyC]

Originally Posted by ToMMan b182

i cant get this to work. I input all the values i want.. then click on export. It brings up vb code windows. It also gives me a error that says macros cannot be used in this project. I have tried everything i know about excel (very little) to get this to work. What should i do?

Excel has a security setting that disables all macros, and I think this is the problem. Within Excel, go to Tools > Macro > Secuity and set the check box to 'Medium'. Then close Excel and re-open it. Now it should prompt you and you should select 'Enable Macros'. That should do it. If not let me know...

[Tom Bottiglieri]

ok i've gotten this to work.. but how exactly would i use this in my code?

[WillyC]

Originally Posted by ToMMan b182

ok i've gotten this to work.. but how exactly would i use this in my code?

This isn't intended to be used in the code. It's intended to save you time if you're putting lookup tables into your code. Here's how I use it... First I use the spreadsheet/math functions in Excel to generate the values for my lookup table (for example, y = sin(x)). Then to save me the trouble of manually typing each and every lookup table value into my code I wrote that little macro to just dump all the values into a .txt file. Then I open the text file and copy and paste the lookup table into my code. It's just a time-saving device. It really proves its worth for another use though. We use a non-linear transfer function in the form of a lookup table to de-sensitize our joysticks (see this thread). Using Excel we can model the shape of the function to tailor the joystick response to the driver's liking. The automated generation of the lookup table saves us time so that we can quickly prototype new functions and dump the lookup table into the code during the testing phase.

Anyway, hopefully you can find a use for it and it saves you some time.

Cheers,