Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Combining Excel input and variable values into table output

  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Default Combining Excel input and variable values into table output

    hi,

    Struggling with this issue... using ... Kettle - Spoon Stable Release - 4.3.0

    I need to combine a variable value (a file name) with data extracted from an Excel input to put into a Table Output.

    When I attempt to use "Select Values" I can see the variable name, but I get "Couldn't find field 'theFile' in row!" error.

    Tried to apply this solution, but not quite sure why it isn't working...http://forums.pentaho.com/showthread...elds-to-stream

    Where am I going wrong?

    Thank you


    Name:  variables.jpg
Views: 778
Size:  21.7 KB

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by bront View Post
    When I attempt to use "Select Values" I can see the variable name, but I get "Couldn't find field 'theFile' in row!" error.
    Please explain what you mean by "I can see the variable name". In a lot of cases you cannot use a variable as a fieldname. The only places that variables are allowed will be marked with a grey/red diamond. In those places, hit control-space to insert one of the available variables.

    What I think you need to do is use a Get Variables step and convert the variable you want into a field. Set the name of the new field in the "Name" blank and hit control-space in the "Variable" blank. All the other blanks should be self explanatory.

    P.S. It is very difficult to read the red text in the image of your post because it is scaled down so much that zooming in doesn't help.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Sep 2012
    Posts
    5

    Default

    Thanks for your help (and sorry about the image... I didn't realize it would get scaled so much on upload).

    So, I do have a Step 1: "Get Variables" where I set a field name (X) for the variable (and when I "Write to log" in a hop attached to the "Get Variables" step I can see the correct value of X).

    Step 2: Import Excel data

    Step 3: Add "Select Values." And here I can access X when I click "Get Fields to Select." In other words, it seems to be working...

    Step 4: Output to text... this is where is where I see the error "Couldn't find field 'X' in row!"

    It seems like the "Select Values" step is missing X because it's not in the Excel file, but if it weren't possible to include it along with the Excel import, I wouldn't think it would be available at all.

    Again, thank you for your time and insight.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by bront View Post
    Where am I going wrong?
    If the variable holds the filename the "Text File Output" step should write to, don't add it to every input row.
    If by any chance you really want to see the filename in each row, the best way is to pipe the Excel output row into the Get Variables step.
    If you do it your way, the variable field is dropped, because Excel Input builds an all new field set.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Sep 2012
    Posts
    5

    Default

    Wonderful! I have it working.

    The steps are as follows:

    1. "Get Variables" -- need to get Excel filename
    2. "Excel Input"
    3. "Select Values" -- modify the Excel values
    4. "Get Variables" -- get the variables again... re-assigning the filename variable
    5. "Select Values" -- grab the Excel values *plus* the filename
    6. "Table Output"

    Also, by way of explanation, we process a number of Excel files and find it useful to store the source filename with the data.

    Thank you so much for your help.

  6. #6
    Join Date
    Sep 2012
    Posts
    5

    Default

    Perfect. That works. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.