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

[timewaste]

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

A	B	C
This file contains test data as of 03/08/18
LVL1	B1	C1
LVL2	B2	C2
LVL3	B3	C3

Thanks!

[LordNeilLord · Accepted solution]

Hey @timewaste

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

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[LordNeilLord · Accepted solution]

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

[NicoleJohnson]

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