Field name with carriage return

[#1]

I am importing about 100 Excel spreadsheets. Some of the field names have carriage returns in them. I can import them with an "*" but how do you reference the field name in script? They work fine in objects. Is there a way of referencing them by field number, for example? There is also some inconsistancy in the worksheet names. Can you you reference worksheets by their sheet number?

Vous pouvez me répondre en français si vous êtes plus à l'aise.

[boorgura · Accepted solution]

Yes you can reference the fields by numbers.

At load, you can change the "embedded labels" to "explicit" and then change header size to 1 so that it will not read the header names as data.

But, I am not sure if we can refer the sheets by numbers.

[#3]

Thanks Rocky,

This definitely answers the first part of my question. I've haven't worked much with Excel files. I would still like to know how to reference worksheets generically.

[#4]

I spoke too soon. It turns out that the location of the columns is inconsistant as well. I can't use explicit fields names. At least the field names used seems to be consistant. If l load with an "*" and concatenate everything, at least the fields line up.

There must be someway of refering to a field by it's number. I've tried FieldName(16,'Projects') by this only returns the name of the field and not the contensts.

[Miguel_Angel_Baeyens]

Hello Kevin,

You can use

LOAD @1 AS Field1, @2 AS Field2FROM [File.xls]WHERE Left(@3, 2) = 'AA'

That will use first, second and third columns of the selected sheet.

Hope that helps

[#6]

That would work if all the columns where in the same location. The problem I have is that I have hundreds of Excel files and at some point an extra column or 2 was added. If I "concatenate load" using the existing field names it combines the fields with the same name regarless of the column number.

The problem field has 2 carriage returns in it. Is there a way of refering to a field with a carriage return in it? I can dump the field in an object and it works just fine. I would like to be able to refer to the same field in a "Load Resident" statement.

[Miguel_Angel_Baeyens]

Hi Kevin,

I'm likely missing something here. You can load (as the Table Files dialog will show) a field with carriage returns with something like

LOAD Field1, [Field With CRs] AS SomeOtherNameFROM ...

Hope that helps

[#8]

Thanks Miguel,

I've tried that and I'm getting a field not found error on the field with the carriage returns.

Kevin

[Miguel_Angel_Baeyens]

That may be because in my example I indented the field with spaces, so it could be

LOAD Field1, [Fieldwith noblanks in lines] AS WhateverFROM

[#10]

Thanks again,

I let Qlikview format the load statement so I didn't have any leading spaces. I was thinking along the same lines as you so I played with the spacing but without success.