Load an Excel tag name as a filed name

[#1]

Hi,

I have a .xlsx file whith several tags.

I am able to load each one calling each one like this:

FROM

(ooxml, embedded labels, table is SbBPP);

But I would like to load all the tags addíng a field which would be the tag name.

In the exemple above would be adding a field called Tagname = SbBPP

Give me a tip please.

Thanks

[manojkulkarni]

didn't understand what is the tag name ? can you send sample file ?

[miguelbraga]

Hi Luis,

By tag name you refer a value that exists in a field?

Regards,

MB

[settu_periasamy]

Hi,

Not Sure. But , I think you are talking about TableName with FieldName.

SbBPP.Field1, SbBPP.Field2  Like this? If so, You can try with Qualify, Make sure your Keyfield Should not be Qualify. You Need to Unqualify it.

Example.

Qualify *;

Unqualify '%*';

SbBPP:

Load

          %KeyField

          Field1,

          Field2

FROM

(ooxml, embedded labels, table is SbBPP);

You will Get the result table Like

%KeyField, SbBPP.Field1, SbBPP.Field2

---                -----                   ---

[awhitfield]

Do you mean TAB rather than TAG?

Andy

[jonathandienst · Accepted solution]

If I understand you correctly, you are looking for something like this:

Set vSourceData = '<...path to Excel file...>';

ODBC CONNECT32 TO [Excel Files;DBQ=$(vSourceData)];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

For zi = 0 To NoOfRows('SpreadsheetData')

  Let zSheet = Peek('TABLE_NAME', zi, 'SpreadsheetData');

  Data:

  LOAD *,

  '$(zSheet)' As TagName

  FROM [$(vSourceData)]

  (ooxml, embedded labels, table is [$(zSheet)]);

Next

DROP Table SpreadsheetData;

This script assumes that all the sheets (I assume this is what you mean by 'tags') contain the same set of fields. The sheet name will be in the field "TagName".

[#7]

Hi Luis,

I think you mean to read all of the excel worksheets using a while cycle or similar.

Te refieres a cargar todas las hojas de la planilla desde la primera hasta la última, sin preocuparte de cuántas hojas sean?

Regards

[#8]

Sorry to every body:

I explained myself very bad.

In que exemple 'Tag_name' would be the new field name, and its values would be every excel tag name, 'SbBPP',...,etc.

I apologise.

[#9]

Correctly,

I want to load all the worksheets (which have the same fields or columns to be stacked) adding a field called tag_name.

Exemple

worksheet Excel 'A' containing column1, column2 y column3.

worksheet Excel 'B' containing column1, column2 y column3.

worksheet Excel 'C' containing column1, column2 y column3.

Resulting Qview Table : Four Fileds, TagName (containing values A,B,C), a field called column1, a field called column2 and a field called column3

[#10]

The solution is to do what Jonathan Dienst said. You have to cycle throu the sheets. I took the same script and added the columns you mentioned. tagname is assigned the name of the actual sheet.

For zi = 0 To NoOfRows('SpreadsheetData')

  Let zSheet = Peek('TABLE_NAME', zi, 'SpreadsheetData');

  Data:

  LOAD *,

  '$(zSheet)' As TagName,

  column1,

  column2,

   column3

  FROM [$(vSourceData)]

  (ooxml, embedded labels, table is [$(zSheet)]);

Next

Syntax may not be perfect. Hope it helps.