Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
CK_WAKE
Contributor III
Contributor III

Load data from excel from specific rows

Hi There I want to load the excel data from specific rows, however i am getting an error stating not able to find the column names. At the moment i am using the below code to read the file. Appreciate your help.

custodian_list:
Load
"col 1" AS "column1",
"col 2" AS "column2",

"col 3" AS "column3",

"col 4" AS "column4",

"col 5" AS "column5",

"col 6" AS "column6",

"col 7" AS "column7",


FROM excelsheet.xlsx', ooxml, embedded labels, table is [CUSTODIAN LIST])
where RecNo()>=5
;
exit Script;

The error i am getting is 

The following error occurred:
Field 'col 1' not found. 
If i use as Load * it is not recognising the col 1 at all and it read from second column.

I have uploaded the dummy file here.
Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

You may adjust the approach by skipping the header and referencing directly to the columns, like:

(ooxml, no labels, table is XYZ)

and then you get A, B, C ... as field-names which you could rename like:

A as Col1,
B as Col2,
...

Again use the wizard to simplify the work.

View solution in original post

4 Replies
marcus_sommer

Your where-clause isn't suitable to define the header-row else it needs to be specified within the file-format statement like: (ooxml, embedded labels, header is 5 lines, table is XYZ).

Just use the load-wizard to specify the correct statement - it might be less than 5 rows because real empty row are skipped.

CK_WAKE
Contributor III
Contributor III
Author

Hi There, the number of records are more than 300 and it is controlled by the business. So importing the data from the excel file as it is, is a better option.

marcus_sommer

You may adjust the approach by skipping the header and referencing directly to the columns, like:

(ooxml, no labels, table is XYZ)

and then you get A, B, C ... as field-names which you could rename like:

A as Col1,
B as Col2,
...

Again use the wizard to simplify the work.

CK_WAKE
Contributor III
Contributor III
Author

Thanks for the help