Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Load an excel file's name and top most cell's data into an Oracle table

timewaste
8 - Asteroid

Hello,

 

I would like to know a way in which we can load a file's name (Excel File) as data into one of the columns. In addition to that, while doing so, my file top most cell (Cell A1) would have some comments which I want to store as well into another column. Is there a way we can do it? Please advise.

 

Sample data of my excel sheet:

 

File Name: TEST_0308181033

ABC
This file contains test data as of 03/08/18  
LVL1B1C1
LVL2B2C2
LVL3B3C3

 

Thanks!

3 REPLIES 3
LordNeilLord
15 - Aurora

Hey @timewaste

 

To load the file name as column in you data you can use "Output filename as field" in the input options:

 

FileName.PNG

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

LordNeilLord
15 - Aurora

To get the contents of A1 in to a column you can do something like this:

 

Headers.PNG

NicoleJohnson
ACE Emeritus
ACE Emeritus

Absolutely, there's a way! Probably several, actually :) But the way I would do it is as follows:

 

1. In your Input Tool for the Excel file, there is an option in the configuration window to include FileName in your input data - select "File Name Only" from the "Output File Name as Field" dropdown, and this will create a field with your Excel file name in the data of your workflow.

 

2. To select the upper left cell, you can use a combination of Select (to deselect all except the first column and the Filename) and Sample (to choose the First 1 Records) to narrow down your data to just the upper left field.

 

Alternatively, you can modify your Input Tool to choose the first cell by changing the text in the configuration to read something like the following (select your sheet first, and then switch to SQL Editor mod): SELECT * FROM 'Sheet1$A1:A1'. This basically sets up a data range of one cell for your input data, and then with the File Name Only selection in step 1 above, you should get the data you are looking for.

 

Hope that helps!

 

Cheers,

NJ

Labels