How to read in specific variables from a CSV based on user inputs

Stormi_Backus · August 2023

Howdy Folks,

I am trying to scan the first two columns of rows in a CSV using two numeric user inputs as the search key and find a match. Then, return the remaining two column values as variables required for creating a hub on a sprocket.

Every simplification/variation of the code that I have tried returns the same 'undefined' result.

I suspect this has to do with how my getValues function is reading through the CSV, but turn to you for guidance because I can't see how after troubleshooting on my own.

Notes to be aware of:
1. I have not attempted to achieve the same effect with a UI driven look up table because there are over 500 possible combinations and I think that may be a little too advanced for me, but I am open to suggestions.
2. I am curious if the specific issue is how I am accessing columns 3 and 4 of my CSV data, because when I added println(N) and println(ChainNumber) into the getValues for loop - it seemed to print every instance of the two search key in their columns, but nothing from column 3 or 4.

Anyway here's the link if you'd like to take a look:

https://cad.onshape.com/documents/68bc361652cad74b51dbe08f/w/8d33d613aa56dcd9d35cb2a8/e/02cf815098757569400fef30?renderMode=0&uiState=64d3a9727d09bc07a2394d22

Thanks in advance,

Stormi

Jacob_Corder · August 2023

FeatureScript 2091;
import(path : "onshape/std/common.fs", version : "2091.0");
HubData::import(path : "3be489a45fcddbb0b6bf996c", version : "8418c2e85c98dbbfde4767cb");

// UI AND FUNCTION DEFINITION
// HUB STYLE SELECTION DROPDOWN LIST
export enum Hub_Style_Choice
{
    annotation { "Name" : "B Hub" }
    B_Hub,
}

// NO. OF TEETH
export const Number_Of_Teeth =
{
            (unitless) : [5, 5, 500]
        } as IntegerBoundSpec;

// CHAIN NO. SELECTION DROPDOWN LIST
export enum Chain_Number_Choice
{
    annotation { "Name" : "Chain No. 25" }
    Chain_25,

}

// CHAIN NO. VARIABLES
export function Store_UI(Chain_Choice)
{
    var arr = makeArray(5, 0);
    if (Chain_Choice == Chain_Number_Choice.Chain_25)
        arr = [1 / 4, 0.130, 0.110, 0.265, 25];
    return (arr);
}

// FUNCTION TO SORT CSV FOR HUB DIAMETER AND DEPTH
function getValues(ChainNumber is number, N is number) returns map
{
    //since thie CSV data is actually an array of maps, we need to get the first one with values. 
    var HubData = HubData::BLOB_DATA;
    for (var row in HubData)
    {
        if(row.value is array)
        {
            HubData = row.value;
            break;
        }
    }    
    for (var row in HubData)
    {       
        if(row[0] is undefined)  continue;   
        if (row[0] is number && row[0] == ChainNumber && row[1] == N)
        {
            return { "HubDiameter" : valueAsNumber(row[2]), "HubDepth" : valueAsNumber(row[3])};
        } 
    }
    return {};//Return empty 
}
function valueAsNumber(value)
{
    if(value is number)return value;
    return stringToNumber(value);
}
annotation { "Feature Type Name" : "Roller Chain Sprocket", "UIHint" : UIHint.NO_PREVIEW_PROVIDED }
export const rollerChain = defineFeature(function(context is Context, id is Id, definition is map)
    precondition
    {
        annotation { "Name" : "Chain Number" }
        definition.ChainNum is Chain_Number_Choice;
        annotation { "Name" : "Number of Teeth" }
        isInteger(definition.myCount, Number_Of_Teeth);
        annotation { "Name" : "Hub Style" }
        definition.HubStyle is Hub_Style_Choice;
    }
    {

        // CHECKS FOR PRESENCE OF B HUB, RETURNS REQUIRED VARIABLES FROM CSV TO GENERATE THE HUB IN CONTEXT
        if (definition.HubStyle == Hub_Style_Choice.B_Hub)
        {
            const arr = Store_UI(definition.ChainNum);
            const ChainNumber = arr[4];

            const N = definition.myCount;
 
            const result = getValues(ChainNumber, N);
            
            if(result =={})
            {
                reportFeatureWarning(context, id, "There is no match in the data for chain: "~ChainNumber~ " with teeth count: "~N);
                return;
            }
            println(result);
            const HubDiameter = result.HubDiameter;
            const HubDepth = result.HubDepth;
        }
    });

@Stormi_Backus

Just replace your code with this. It works fine now

Stormi_Backus · August 2023

@Evan_Reesewe chatted about this briefly, tagging you as requested!

EvanReese · August 2023

Can you share a peek at the structure of the CSV? I can't see it and the doc is view-only. Or better yet, make the doc public if you're willing so I can mess around with a copy.

Stormi_Backus · August 2023

@Evan_Reese I just made the document public.

As for the CSV, I expect that making the doc public will have answered your questions - but it is a four column document with numeric entries and about 550 rows.

EvanReese · August 2023

Got it, I think this is the perfect application for a lookup table, personally. You want someone to pick Chain no. 35 and only see tooth-counts for that option, right? Lookup tables don't have to be all that scary. Check out the example from the documentation here.

I think if you knew some other code you might be able to get it all formatted right that way, but I'd probably just used a bunch of concatenation functions etc in a spreadsheet to get it formatted, then copy/paste it into the feature studio from there. You could also use find/replace with the regex functions to help with formatting (I have to google exactly how every time, but it's a good way).

Of course, you could also just type it all manually. If you get stuck lemme know and I'll make an example when I have more time.

Stormi_Backus · August 2023

Hi @Evan_Reese , I have been digging into it and I see that the look up table would be a quicker fix than struggling with the CSV read in.

I will take you up on that example if you have time.

If not, would my best course of action be to set up a top level lookup table const definition and set it equal to sub entries for each chain number, then build sub entries within chain number for possible numbers of teeth, and finally sub-sub-sub entries within each tooth number for resultant hub diameter and hub depth?

Jacob_Corder · August 2023

A look up table will require you to convert and continue to maintain the data in 2 sources possibly. I think you need to do this

function getValues(ChainNumber is number, N is number) returns map

{

const HubData = HubData::BLOB_DATA;

// println(HubData);

// println(ChainNumber);

// println(N);

for (var row in HubData)

{

// println(ChainNumber);

// println(N);

if (stringToNumber(row[0]) == ChainNumber && stringToNumber(row[1]) == N)

{

return { "HubDiameter" : stringToNumber(row[2]), "HubDepth" : stringToNumber(row[3]) };

}

I believe the CSV data is coming in as a non numeric value so == will not evaluate a string and number.

Give it a try and see.

You can create an excel equation to output the data as is needed for a lookup table, I have done this in the past, however, i cannot find the function. Perhaps i wrote a macro, I cannot remember.

Stormi_Backus · August 2023

@Jacob_Corder thank you for the suggestion...I've been needing that stringtToNumber function, but unfortunately implementing it here hasn't quite fixed my problem (see error image below).

That said, there is definitely an issue with how the CSV data is being read. I tried simply copy and pasting the data from println(HubData) as a reference array instead of calling the CSV directly and the script worked perfectly.

While this IS technically a solution, its not the most efficient as far as updating a data table for design changes/new products...I'll keep troubleshooting!

Image: https://us.v-cdn.net/5022071/uploads/editor/n4/cd3nozivtz7j.png

EvanReese · August 2023

I think Jabob's approach here is great!

@Stormi_Backus Without looking at your code, the error message says you gave it an undefined value instead of a string, which probably means there's an error with how you're addressing the value, or that there's an empty row or something like that.

Jacob_Corder · August 2023

FeatureScript 2091;
import(path : "onshape/std/common.fs", version : "2091.0");
HubData::import(path : "3be489a45fcddbb0b6bf996c", version : "8418c2e85c98dbbfde4767cb");

// UI AND FUNCTION DEFINITION
// HUB STYLE SELECTION DROPDOWN LIST
export enum Hub_Style_Choice
{
    annotation { "Name" : "B Hub" }
    B_Hub,
}

// NO. OF TEETH
export const Number_Of_Teeth =
{
            (unitless) : [5, 5, 500]
        } as IntegerBoundSpec;

// CHAIN NO. SELECTION DROPDOWN LIST
export enum Chain_Number_Choice
{
    annotation { "Name" : "Chain No. 25" }
    Chain_25,

}

// CHAIN NO. VARIABLES
export function Store_UI(Chain_Choice)
{
    var arr = makeArray(5, 0);
    if (Chain_Choice == Chain_Number_Choice.Chain_25)
        arr = [1 / 4, 0.130, 0.110, 0.265, 25];
    return (arr);
}

// FUNCTION TO SORT CSV FOR HUB DIAMETER AND DEPTH
function getValues(ChainNumber is number, N is number) returns map
{
    //since thie CSV data is actually an array of maps, we need to get the first one with values. 
    var HubData = HubData::BLOB_DATA;
    for (var row in HubData)
    {
        if(row.value is array)
        {
            HubData = row.value;
            break;
        }
    }    
    for (var row in HubData)
    {       
        if(row[0] is undefined)  continue;   
        if (row[0] is number && row[0] == ChainNumber && row[1] == N)
        {
            return { "HubDiameter" : valueAsNumber(row[2]), "HubDepth" : valueAsNumber(row[3])};
        } 
    }
    return {};//Return empty 
}
function valueAsNumber(value)
{
    if(value is number)return value;
    return stringToNumber(value);
}
annotation { "Feature Type Name" : "Roller Chain Sprocket", "UIHint" : UIHint.NO_PREVIEW_PROVIDED }
export const rollerChain = defineFeature(function(context is Context, id is Id, definition is map)
    precondition
    {
        annotation { "Name" : "Chain Number" }
        definition.ChainNum is Chain_Number_Choice;
        annotation { "Name" : "Number of Teeth" }
        isInteger(definition.myCount, Number_Of_Teeth);
        annotation { "Name" : "Hub Style" }
        definition.HubStyle is Hub_Style_Choice;
    }
    {

        // CHECKS FOR PRESENCE OF B HUB, RETURNS REQUIRED VARIABLES FROM CSV TO GENERATE THE HUB IN CONTEXT
        if (definition.HubStyle == Hub_Style_Choice.B_Hub)
        {
            const arr = Store_UI(definition.ChainNum);
            const ChainNumber = arr[4];

            const N = definition.myCount;
 
            const result = getValues(ChainNumber, N);
            
            if(result =={})
            {
                reportFeatureWarning(context, id, "There is no match in the data for chain: "~ChainNumber~ " with teeth count: "~N);
                return;
            }
            println(result);
            const HubDiameter = result.HubDiameter;
            const HubDepth = result.HubDepth;
        }
    });

@Stormi_Backus

Just replace your code with this. It works fine now

Stormi_Backus · August 2023

Thank you @Jacob_Corder the array of maps issue makes sense, the solution not as intuitive so I'm glad you gave an example!

Stormi_Backus · August 2023

@Evan_Reese and @Jacob_Corder you two helped me a lot with this sprocket situation. I'm adding more features/removing user traps everyday but it has really come together so I thought I'd take a moment to share my progress and say THANK YOU!

https://cad.onshape.com/documents/3aefd1dd7bc37fe1460671df/w/9c62d134d495635179004df9/e/7d70c9dc8cb503ed2b351d9f

How to read in specific variables from a CSV based on user inputs

Best Answer

Answers

Howdy, Stranger!

Quick Links

Categories