• 0
Sheila

Extract Data to Populate Table Columns

Question

I have raw data fields that contain many values that need to be extracted to their own columns on a table.  In Excel, I was using a nested If(isnumber(search function to extract the various values to their respective columns.

How do I do this in Caspio?  The attached file shows a couple of examples of before and after.

TIA!

S.

 

Help on Data Extraction.xls

Share this post


Link to post
Share on other sites

4 answers to this question

  • 0
On 10/9/2017 at 9:33 AM, Sheila said:

Can anyone answer or provide any input?  Thanks!

Hi,

Can you have a look at this post from the forum and try to implement your requirements with it's  help?

 

Share this post


Link to post
Share on other sites
  • 0

Hi Sheila,

This can be done in multiple ways:

  1. JavaScript: You might take a look at my other forum post below, although you might need to tweak the JavaScript a bit.  See the REFERENCES section below for the link.
  2. Triggers: This is a new feature in the 9.9 release. See below for the link.

REFERENCES:

  1. Multiple Values Separated by Commas Input: https://forums.caspio.com/topic/6770-multiple-values-separated-by-commas-input/?do=findComment&comment=21222
  2. Triggered Actions (aka Triggers Feature): https://howto.caspio.com/tables-and-views/triggered-actions/

Hope this helps.

-nightowl

Share this post


Link to post
Share on other sites
  • 0

Thanks to both of you.  My file is not comma delimited, so can't separate that way.  Also, the data has many variations.  I want 3 columns to be populated, but the values for each column are many.  For instance, column 1 only has 3 variations that could be populated, but column 2 has as many as 15 and column 3 has over 20 variations.

Here is an example of the function I use in Excel to populate column 1:

ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""Women"",RC[-7])),""Womens"",IF(ISNUMBER(SEARCH(""Men"",RC[-7])),""Mens"",IF(ISNUMBER(SEARCH(""Kids"",RC[-7])),""Kids"",IF(ISNUMBER(SEARCH(""unisex-child"",RC[-7])),""Kids"",IF(ISNUMBER(SEARCH(""Female"",RC[-7])),""Womens"",IF(ISNUMBER(SEARCH(""Male"",RC[-7])),""Mens"",""Other""))))))"

The result is column 1 only contains the values of Mens, Womens or Kids, even though the raw data may contain other values.

The other two columns work the same way, but with more and different variables.  Hope I'm explaining this right.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now