This is my meltano config file:

version: 1
default_environment: dev
send_anonymous_usage_stats: false
plugins:
  extractors:
  - name: tap-spreadsheets-anywhere
    variant: ets
    pip_url: git+<https://github.com/ets/tap-spreadsheets-anywhere.git>
  loaders:
  - name: target-csv
    variant: hotgluexyz
    pip_url: git+<https://github.com/hotgluexyz/target-csv.git@0.3.3>
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-spreadsheets-anywhere
        config:
          tables:
          - path: <https://datos.madrid.es/egob/catalogo>
            pattern: 300193-7-licencias-urbanisticas.xlsx
            name: obras_2022
            format: excel
            key_properties: []
            worksheet_name: Listado de Licencias concedidas
            start_date: '2017-05-01T00:00:00Z'
      loaders:
      - name: target-csv
        config:
          delimiter: ;
- name: staging
- name: prod

Edit: Removed an extra slash at the end of `path`URL

And this is the error during discovery process:

meltano-obras-local % meltano invoke tap-spreadsheets-anywhere --discover
2022-06-17T14:15:30.498375Z [info     ] Environment 'dev' is active
INFO Assembled <https://datos.madrid.es/egob/catalogo//300193-7-licencias-urbanisticas.xlsx> as the URL to a source file.
INFO Checking 1 resolved objects for any that match regular expression "300193-7-licencias-urbanisticas.xlsx" and were modified since 2017-05-01 00:00:00+00:00
INFO Processing 1 resolved objects that met our criteria. Enable debug verbosity logging for more details.
INFO Sampling 300193-7-licencias-urbanisticas.xlsx (1000 records, every 5th record).
ERROR Unable to write Catalog entry for 'obras_2022' - it will be skipped due to error Bad magic number for central directory
{
  "streams": []
}%

Following up the advice/tip on the other thread, I tried downloading the file manually using curl and then outputing the headers along the way:

curl -s -o /dev/stdout --dump-header /dev/stderr <https://datos.madrid.es/egob/catalogo/300193-7-licencias-urbanisticas.xlsx> | file -
HTTP/2 302 
x-frame-options: SAMEORIGIN
x-xss-protection: 1; mode=block
x-content-type-options: nosniff
location: <https://datos.madrid.es:443/datosabiertos/URBANISMO/INFORMACION_URBANISTICA/LICENCIAS/2022/05/I150_202205.xlsx>
content-encoding: gzip
x-ua-compatible: IE=8
content-length: 20
content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
date: Mon, 20 Jun 2022 14:31:37 GMT
set-cookie: ROUTEID=.app01; path=/
strict-transport-security: max-age=15768000

Then, I renamed file

I150_20205.xlsx

to

obras.gzip

and tried to decompress the file using Python. This is the script I used:

#!/usr/bin/python3
from zipfile import ZipFile

zf = ZipFile("./obras.gzip")
zf.extractall(path = './input/')
zf.close()

I visited the the extraction path and I could see all components inside the file. So downloading the file externally and using zipfile to decompress the file works (file doesn’t seem to be corrupt or have bad CRC)

But tap-spreadsheets-anywhere uses smart_open, so I am not even sure if they are using the same libraries under the hood.

Maybe I am reading the docs wrong. According to https://docs.meltano.com/reference/command-line-interface.html#elt: •

meltano elt

is the only way to get the logger messages from the taps being used.. If you want to get the message from the singer taps this is the only option. •

meltano run

is the recommended option to run cross-plugin workflows, but you can’t get the output of the taps using this option. •

meltano invoke tap-xxx --discover

enables you to build the catalog. But if catalog creation fails, you can’t get the tap log info in the output. In my particular case, my catalog is not created plus the tap debug info doesn’t appear on the output (as in the example shown in the docs)

In the docs about debugging (https://docs.meltano.com/reference/command-line-interface.html#debugging ) it says: Additionally, all Singer messages output by the tap and target will be logged, identified by

<plugin name> (out)

prefixes: This is the sample output from the docs (note the column on the left, specifying the plugin source)

tap-gitlab         | INFO Starting sync
tap-gitlab (out)   | {"type": "SCHEMA", "stream": "projects", "schema": {"type": "object", "properties": {...}}, "key_properties": ["id"]}
tap-gitlab (out)   | {"type": "RECORD", "stream": "projects", "record": {"id": 7603319, "name": "Meltano", ...}, "time_extracted": "2020-08-05T21:30:22.988250Z"}
tap-gitlab (out)   | {"type": "STATE", "value": {"project_7603319": "2020-08-05T21:04:59.158000Z"}}
tap-gitlab         | INFO Sync complete
target-jsonl (out) | {"project_7603319": "2020-08-05T21:04:59.158000Z"}
meltano            | INFO Incremental state has been updated at 2020-08-05 21:30:26.669170.
meltano            | DEBUG Incremental state: {'project_7603319': '2020-08-05T21:04:59.158000Z'}
meltano            | INFO Extract & load complete!

And this is the output I am getting:

022-06-20T21:49:20.868063Z [info     ] INFO Sampling 300193-7-licencias-urbanisticas.xlsx (1000 records, every 5th record). name=tap-spreadsheets-anywhere stdio=stderr type=discovery
2022-06-20T21:49:21.963271Z [info     ] ERROR Error trying to access '[{'key': '300193-7-licencias-urbanisticas.xlsx', 'last_modified': datetime.datetime(2022, 6, 5, 22, 32, 23, tzinfo=<UTC>)}]' name=tap-spreadsheets-anywhere stdio=stderr type=discovery
2022-06-20T21:49:21.963911Z [info     ] ERROR Unable to write Catalog entry for 'obras2022' - it will be skipped due to error Bad magic number for central directory name=tap-spreadsheets-anywhere stdio=stderr type=discovery

There is the name parameter in the output, but it doesn’t look as neat as the provided example in the docs.

Can anyone confirm if

meltano --log-level=debug elt tap-xx target-xx

outputs the Singer messages to the output, along with meltano messages? I can’t get the sample out provided in the docs

As far as I see, the tap is able to access the file but returns the error when processing it. This is the output when the right URL is passed (It doesn’t reach to the point to read the worksheet name. Given the right or wrong worksheet name returns the same error):

meltano invoke tap-spreadsheets-anywhere --discover
2022-06-21T16:02:44.466512Z [info     ] Environment 'dev' is active
INFO Assembled <https://datos.madrid.es:443/datosabiertos/URBANISMO/INFORMACION_URBANISTICA/LICENCIAS/2021/12/I150_202112.xlsx> as the URL to a source file.
INFO Checking 1 resolved objects for any that match regular expression "I150_202112.xlsx" and were modified since 2017-05-01 00:00:00+00:00
INFO Processing 1 resolved objects that met our criteria. Enable debug verbosity logging for more details.
INFO Sampling I150_202112.xlsx (1000 records, every 5th record).
ERROR Unable to write Catalog entry for 'obras2022' - it will be skipped due to error Bad magic number for central directory
{
  "streams": []
}%

If I pass the wrong URL, I get a different message. So it seems it’s downloading and trying to open the file for sampling. This is the error when wrong URL passed:

meltano invoke tap-spreadsheets-anywhere --discover
2022-06-21T16:03:09.938206Z [info     ] Environment 'dev' is active
INFO Assembled <https://datos.madrid.es:443/datosabiertos/URBANISMO/INFORMACION_URBANISTICA/LICENCIAS/2021/11/I150_202112.xlsx> as the URL to a source file.
ERROR Unable to write Catalog entry for 'obras2022' - it will be skipped due to error Configured URL <https://datos.madrid.es:443/datosabiertos/URBANISMO/INFORMACION_URBANISTICA/LICENCIAS/2021/11/I150_202112.xlsx> could not be read.
{
  "streams": []
}%

I have also tried erasing all pyc files as per https://stackoverflow.com/questions/45121132/bad-magic-number-for-file-header-error-while-using-openpyxl Same error is returned.

Some advances about this issue. I was able to setup VSCode to debug the tap. So now I can get a bit of extra information. This is the trace where exception is thrown. Origin is BadZipFile:

To test the tap, I created this launch.json config file:

{
  "version": "0.2.0",
  "configurations": [
    {
      "name": "Python: archivo actual",
      "type": "python",
      "request": "launch",
      "program": "${workspaceRoot}/__init__.py",
      "console": "integratedTerminal",
      "args": [
        "--config",
        "${workspaceRoot}/config.json"
      ],
      "justMyCode": true
    }
  ]
}

And then tried several different XLSX files from Open Data Sources in Spain.. Long story short: All the datasets I tried, taken from three different public administrations falled. The only XLSX I could sample properly is the one in the test folder of the tap-spreadsheet-anywhere tap! Sample config.json file configurations (Open Data Barcelona) (same error about Magic number)::

{
  "tables": [
    {
      "path": "<https://opendata-ajuntament.barcelona.cat/data/dataset/368c71a0-55ef-4760-b388-93c75d3de458/resource/9fbc550b-c928-4f25-871f-684b6e5d218d/download>",
      "name": "dades2020",
      "format": "excel",
      "key_properties": [],
      "worksheet_name": "resum 2020",
      "start_date": "2017-05-01T00:00:00Z",
      "pattern": "2020_dades_sindicals.xlsx"
    }
  ]
}

From National Institute of Statistics (same error about Magic number):

{
  "tables": [
    {
      "path": "<https://www.ine.es/jaxi/files/_px/xlsx/t20/e244/avance/p02>",
      "name": "dades2020",
      "format": "excel",
      "key_properties": [],
      "start_date": "2017-05-01T00:00:00Z",
      "pattern": "5mun16.xlsx"
    }
  ]
}

From tap-spreadsheets-anywhere tests folder (Works!):

{
  "tables": [
    {
      "path": "<https://github.com/ets/tap-spreadsheets-anywhere/raw/master/tap_spreadsheets_anywhere/test>",
      "name": "testnoerrors",
      "format": "excel",
      "key_properties": [],
      "worksheet_name": "sample_with_bad_newlines",
      "start_date": "2017-05-01T00:00:00Z",
      "pattern": "excel_with_no_errors.xlsx"
    }
  ]
}

I am looking for scripts to detect the encoding of the files from the files themselves. I think I’ll also share the config files on Singer slack to see if anyone has faced the same issues.