ISSUE: XLSX Importer Fails when columns have no header

[Tim Smith]

Hi,

I'm trying to import numerous spreadsheets where some of the columns have no header.  In this situation, the importer incorrectly uses the first value in the column as the header/property or, more severely, it just skips the header row entirely and uses the first non-null value in each  column as the property.  Sometimes it does a combination of the two.  Below I describe an example.  I would expect the importer to either skip or assign a name to a column with no header.

Any suggestions?  I have dozens of spreadsheets to import that all have one or more columns with no headers on multiple sheets.

Tim

Using the attached spreadsheet, please open it in TBC 6.4.

The first sheet has five columns where column B and D have no header.  

Look at the Row0-0 instance and you will see that the values "B2" and "D2" have been used as column headers/properties.

Now look at the Row1-0 instance.  It is a complete disaster.  Note essentially the entire first data row was used as headers and in the case of the Alias For column, the importer skipped the header and picked the value in Row 3 to become the property for the column.  I've never seen this behavior before.

And finally... look at the Row2-0 instance.  This is from the third sheet in the workbook where I have manually populated the empty column headers found in the last example above.  It appears to work as expected.

[dprice]

Hi Tim,

You’re doing something that is not supported by EDG out-of-the-box.

The EDG SS importers requires headers that are mapped to properties of the selected class into which to map each row. The fact that the import SS by pattern pops up with “Spreadsheet columns” and “Target Property” is a good hint. The documentation is at:

https://doc.topquadrant.com/6.4/importing/

and then select Import Spreadsheet using Pattern.

Options:

1 - Add column headers, then use Import Spreadsheet using Pattern

2 - Use an external tool to convert SS into RDF and then load that with EDG Import RDF (https://tarql.github.io/ is an example of such a tool)

3 - If you have access to TopBraid Composer you can open spreadsheets with “Semantic Tables”, do some conversion with SPIN/SPARQL/SHACL into ontology-based triples, and then EDG Import RDF.

Cheers,

David

On 4 Aug 2020, at 19:52, Tim Smith <smith...@gmail.com> wrote:

Hi,

I'm trying to import numerous spreadsheets where some of the columns have no header.  In this situation, the importer incorrectly uses the first value in the column as the header/property or, more severely, it just skips the header row entirely and uses the first non-null value in each  column as the property.  Sometimes it does a combination of the two.  Below I describe an example.  I would expect the importer to either skip or assign a name to a column with no header.

Any suggestions?  I have dozens of spreadsheets to import that all have one or more columns with no headers on multiple sheets.

Tim

Using the attached spreadsheet, please open it in TBC 6.4.

The first sheet has five columns where column B and D have no header.  

<image.png>

Look at the Row0-0 instance and you will see that the values "B2" and "D2" have been used as column headers/properties.

<image.png>

Now look at the Row1-0 instance.  It is a complete disaster.  Note essentially the entire first data row was used as headers and in the case of the Alias For column, the importer skipped the header and picked the value in Row 3 to become the property for the column.  I've never seen this behavior before.

<image.png>

<image.png>

And finally... look at the Row2-0 instance.  This is from the third sheet in the workbook where I have manually populated the empty column headers found in the last example above.  It appears to work as expected.

<image.png>

<image.png>

--
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/CAF0Wbn%2BvLfxnhN-TqVrWdeWiZZB1kN81_MytdEa5ObpiZ1ytSg%40mail.gmail.com.
<Spreadsheet Import Test - TBC 6.4.xlsx>

UK +44 (0) 7788 561308

US +1 (336) 283-0808‬

[Tim Smith]

Hi David,

Actually, I don't use EDG for SS import at all.  It is too limiting, especially when the need is repetitive.  Thus I'm using TBC directly and I would have expected TBC to properly handle spreadsheets with missing column headers.  I am writing my own construct queries and will use SWP or SPARQLMotion to iterate over the set of files.

Should I expect TBC to handle this import correctly?

Thanks,

Tim

To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/E36836AE-F90D-41FC-8087-9E3612052FDF%40topquadrant.com.

[dprice]

Apologies Tim … I didn’t look closely enough at your screenshots.

On 4 Aug 2020, at 21:30, Tim Smith <smith...@gmail.com> wrote:

Hi David,

Actually, I don't use EDG for SS import at all.  It is too limiting, especially when the need is repetitive.  Thus I'm using TBC directly and I would have expected TBC to properly handle spreadsheets with missing column headers.  I am writing my own construct queries and will use SWP or SPARQLMotion to iterate over the set of files.

Should I expect TBC to handle this import correctly?

I’ve done a lot with EDG but not that much with Composer. That said, if there are no headers then there is no schema-to-schema mapping possible and it’s all just up to your own logic AFAIK. If some cols have headers and others don’t in the same SS, then the logic is at the level of an individual row/cell rather than at the meta-level where there’s a “schema” based on column names.

Cheers,

David

To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/CAF0WbnK%2B0B1hM4_AeEioDHu6dfW%3DQXdt6GYFdc0yiGTSbDz86A%40mail.gmail.com.

[Irene Polikoff]

Importer will always look for some value in each column of the first row and will use it as the column name.

If it does not find values in the first row,  then it will try to find the next available row with all values. This is to accommodate situations where a first row(s) may be empty. It will definitely not try to generate a column name if none exist.

In your example, I see that it could get confused. We have not anticipated input like this. 

On Aug 4, 2020, at 2:52 PM, Tim Smith <smith...@gmail.com> wrote:

Hi,

I'm trying to import numerous spreadsheets where some of the columns have no header.  In this situation, the importer incorrectly uses the first value in the column as the header/property or, more severely, it just skips the header row entirely and uses the first non-null value in each  column as the property.  Sometimes it does a combination of the two.  Below I describe an example.  I would expect the importer to either skip or assign a name to a column with no header.

Any suggestions?  I have dozens of spreadsheets to import that all have one or more columns with no headers on multiple sheets.

Tim

Using the attached spreadsheet, please open it in TBC 6.4.

The first sheet has five columns where column B and D have no header.  

<image.png>

Look at the Row0-0 instance and you will see that the values "B2" and "D2" have been used as column headers/properties.

<image.png>

Now look at the Row1-0 instance.  It is a complete disaster.  Note essentially the entire first data row was used as headers and in the case of the Alias For column, the importer skipped the header and picked the value in Row 3 to become the property for the column.  I've never seen this behavior before.

<image.png>

<image.png>

And finally... look at the Row2-0 instance.  This is from the third sheet in the workbook where I have manually populated the empty column headers found in the last example above.  It appears to work as expected.

<image.png>

<image.png>

--
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/CAF0Wbn%2BvLfxnhN-TqVrWdeWiZZB1kN81_MytdEa5ObpiZ1ytSg%40mail.gmail.com.

[Irene Polikoff]

If you are using ‘Semantic Tables’ importer in TBC, it will always look for some value in each column of the first row and will use it as the column name.

If it does not find values in the first row,  then it will try to find the next available row with all values. This is to accommodate situations where a first row(s) may be empty. It will not try to generate a column name (e.g., Column B) if none exist.

Spreadsheet importers in EDG use the same approach.

With your example, I see that the importer could get confused. We have not anticipated input like this. 

On Aug 4, 2020, at 2:52 PM, Tim Smith <smith...@gmail.com> wrote:

Hi,

I'm trying to import numerous spreadsheets where some of the columns have no header.  In this situation, the importer incorrectly uses the first value in the column as the header/property or, more severely, it just skips the header row entirely and uses the first non-null value in each  column as the property.  Sometimes it does a combination of the two.  Below I describe an example.  I would expect the importer to either skip or assign a name to a column with no header.

Any suggestions?  I have dozens of spreadsheets to import that all have one or more columns with no headers on multiple sheets.

Tim

Using the attached spreadsheet, please open it in TBC 6.4.

The first sheet has five columns where column B and D have no header.  

<image.png>

Look at the Row0-0 instance and you will see that the values "B2" and "D2" have been used as column headers/properties.

<image.png>

Now look at the Row1-0 instance.  It is a complete disaster.  Note essentially the entire first data row was used as headers and in the case of the Alias For column, the importer skipped the header and picked the value in Row 3 to become the property for the column.  I've never seen this behavior before.

<image.png>

<image.png>

And finally... look at the Row2-0 instance.  This is from the third sheet in the workbook where I have manually populated the empty column headers found in the last example above.  It appears to work as expected.

<image.png>

<image.png>

--
You received this message because you are subscribed to the Google Groups "TopBraid Suite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to topbraid-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/CAF0Wbn%2BvLfxnhN-TqVrWdeWiZZB1kN81_MytdEa5ObpiZ1ytSg%40mail.gmail.com.

[Tim Smith]

Hi Irene,

With that explanation, the performance makes sense.  This is certainly an edge case.  Unfortunately the spreadsheets are provided by a large automation vendor and are a standard part of their documentation.  IMHO, it's rather irresponsible to publish them without proper column headers but that's just me.

I'll find a way to pre-process them using other applications that replace missing column headers with a placeholder (KNIME Is the best candidate).

Tim

To view this discussion on the web visit https://groups.google.com/d/msgid/topbraid-users/BDBA4B89-0E74-476C-82EE-D2AC582FCCD1%40topquadrant.com.