Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a password protected EXCEL spreadsheet. I supplied the pwd, had the file open, built ODBC connection and successfully connected to the file with SQL SELECT. However the header I wanted was not the 1st row so the Header became Row 1 (Report 1, F2, F3, ....) and Row 2 and below became data.
How do I get the value of the Row 4 (EIN, WORKER NAME, LEGAL ENTITY) and make it as header? In non-encrypted EXCEL we can use Embedded Label but what do I do here?
If as you describe you want to load data from the Fourth row then check the load script and in load script add line header is 3 lines
LOAD
EIN,
[WORKER NAME],
[LEGAL ENTITY]
FROM
Data.xlsx
(ooxml, embedded labels, header is 3 lines, table is Sheet1);
Then you get fields EIN, [WORKER NAME], [LEGAL ENTITY] with data
Employee Id | Employee Name | Oracle Entity |
---|---|---|
123 | ABC | 535 |
456 | DEF | 535 |
Let me know if it works for you.
Hi Anand,
Thanks but with password protected spreadsheet, I must use ODBC and read file with SQL SELECT. I couldn't specify header line.
LOAD
Report Title 1,
F2,
F3
;
SQL SELECT *
FROM `$(vPath)\File.xlsx`.`'sheet1$'`;
Ok then you can try another way i paste the sample load script try to load data this ways.
ODBC CONNECT TO [Excel Files;DBQ=D:\Data.xlsx];
T1:
LOAD `Report Title 1`,
F2,
F3,
RowNo() as Rid;
SQL SELECT `Report Title 1`,
F2,
F3
FROM `D:\Data.xlsx`.`Sheet1$`;
NoConcatenate
LOAD *
Resident T1
Where Rid > 2 and Len(`Report Title 1`) > 0;
DROP Table T1;
Another way is
ODBC CONNECT TO [Excel Files;DBQ=D:\Data.xlsx];
T1:
LOAD `Report Title 1`,
F2,
F3,
RowNo() as Rid;
SQL SELECT `Report Title 1`,
F2,
F3
FROM `D:\Data.xlsx`.`Sheet1$`;
NoConcatenate
LOAD
`Report Title 1` as EIN,
F2 AS [WORKER NAME] ,
F3 AS [LEGAL ENTITY],
Rid
Resident T1
Where Rid > 2 and Len(`Report Title 1`) > 0;
DROP Table T1;
Do you have to use the names on line 4 dynamically? Or are they fixed. If fixed, you can do it like Anand suggested but I think a bit simpler like:
LOAD
`Report Title 1` as EIN,
F2 as [WORKER NAME],
F3 as {LEGAL ENTITY],
WHERE Recno() > 6
SQL SELECT
`Report Title 1`,
F2,
F3
FROM `D:\Data.xlsx`.`Sheet1$`;
If you need to deal with dynamic/changing names, let me know and I'll dig up the code for that.
-Rob
Thanks Anand and Rob. The field names will be dynamic so hardcoding is impossible. The row of the header could change too. The header could be row 4, row 5, or row 6. This is messy and thank you in advance!
- Jess