hotgluexyz / target-csv Goto Github PK

View Code? Open in Web Editor NEW

This project forked from singer-io/target-csv

5.0 3.0 8.0 39 KB

Write Singer data to CSV files

License: GNU Affero General Public License v3.0

Python 100.00%

target-csv's Introduction

target-csv

A Singer target that writes data to CSV files.

How to use it

target-csv works together with any other Singer Tap to move data from sources like Braintree, Freshdesk and Hubspot to CSV-formatted files. It is commonly used for loading data into tools like Excel or simply storing a backup of the source data set.

Install

We will use tap-exchangeratesapi to pull currency exchange rate data from a public data set as an example.

First, make sure Python 3 is installed on your system or follow these installation instructions for Mac or Ubuntu.

It is recommended to install each Tap and Target in a separate Python virtual environment to avoid conflicting dependencies between any Taps and Targets.

 # Install tap-exchangeratesapi in its own virtualenv
python3 -m venv ~/.virtualenvs/tap-exchangeratesapi
source ~/.virtualenvs/tap-exchangeratesapi/bin/activate
pip install tap-exchangeratesapi
deactivate

# Install target-csv in its own virtualenv
python3 -m venv ~/.virtualenvs/target-csv
source ~/.virtualenvs/target-csv/bin/activate
pip install target-csv
deactivate

Run

We can now run tap-exchangeratesapi and pipe the output to target-csv.

~/.virtualenvs/tap-exchangeratesapi/bin/tap-exchangeratesapi | ~/.virtualenvs/target-csv/bin/target-csv

The data will be written to a file called exchange_rate-{timestamp}.csv in your working directory.

› cat exchange_rate-{timestamp}.csv
AUD,BGN,BRL,CAD,CHF,CNY,CZK,DKK,GBP,HKD,HRK,HUF,IDR,ILS,INR,JPY,KRW,MXN,MYR,NOK,NZD,PHP,PLN,RON,RUB,SEK,SGD,THB,TRY,ZAR,EUR,USD,date
1.3023,1.8435,3.0889,1.3109,1.0038,6.869,25.47,7.0076,0.79652,7.7614,7.0011,290.88,13317.0,3.6988,66.608,112.21,1129.4,19.694,4.4405,8.3292,1.3867,50.198,4.0632,4.2577,58.105,8.9724,1.4037,34.882,3.581,12.915,0.9426,1.0,2017-02-24T00:00:00Z

Optional Configuration

target-csv takes an optional configuration file that can be used to set formatting parameters like the delimiter - see config.sample.json for examples. To run target-csv with the configuration file, use this command:

~/.virtualenvs/tap-exchangeratesapi/bin/tap-exchangeratesapi | ~/.virtualenvs/target-csv/bin/target-csv -c my-config.json

target-csv's People

Stargazers

Watchers

Forkers

eldarsil autoidm visch sindhu1407 asbonatto hyperlex jennleon leag

target-csv's Issues

End of Line on Windows Default giving "CR CRLF" for every new line

Getting 3 characters for new lines. CR and CRLF on Windows for each record :/ . If it was just unix style line feeds that'd be workable for me. Not sure what's causing this

Push data to SFTP

Not sure where the right place for this conversation is.

https://meltano.slack.com/archives/C013EKWA2Q1/p1619166461259200 was a conversation a while back in the Meltano Slack about this.

Use case: Push CSVs to SFTP sites.

There's other potential targets here:

FTP
FTPS
SFTP
CURL Posting of a file to some endpoint

After the file is created we want to do something things with it potentially.

Few issues with this today:

File name has a timestamp so scripting this is a bit painful
Have to write a separate script with separate binaries which pulls me out of the singer ecosystem to get this done, not ideal

Unicode issue

2022-07-15T07:30:16.759125Z [info     ]   File "D:\AutoIDM\Job\.meltano\loaders\target-csv\venv\lib\site-packages\target_csv.py", line 97, in persist_messages cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.764525Z [info     ]     writer.writerow(simplejson.loads(r)) cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.770989Z [info     ]   File "C:\Python39\lib\csv.py", line 154, in writerow cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.775307Z [info     ]     return self.writer.writerow(self._dict_to_list(rowdict)) cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.779057Z [info     ]   File "C:\Python39\lib\encodings\cp1252.py", line 19, in encode cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.783375Z [info     ]     return codecs.charmap_encode(input,self.errors,encoding_table)[0] cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-07-15T07:30:16.794802Z [info     ] UnicodeEncodeError: 'charmap' codec can't encode character '\u0117' in position 83: character maps to <undefined> cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv

Fixed by with open(filename, 'a', newline='', encoding='utf-8')

For some reason setting PYTHONIOENCODING="utf-8" doesn't work

Windows: Delimiter of \t still leads to a file delimited with commas

config.json example

{                                  
  "destination_path": "load/",     
  "delimter": "\t"                 
}

Option for no Datetime on end of file

I want to pick up this file at a destination with the same name on a scheduled basis. Having a datetime doesn't help me accomplish that.

Plan is to make the datetime be configurable

No File created when 0 Records provided

There's times when your stream is going to have 0 records. The CSV should be created (with a header record if applicable), and the CSV should have 0 records

1.17 is not a multiple of 0.0001

13:41:07,19 INFO: stderr from second_pipe_cmd: b'jsonschema.exceptions.ValidationError: 1.17 is not a multiple of 0.0001\r\n'
13:41:07,20 INFO: stderr from second_pipe_cmd: b'\r\n'
13:41:07,20 INFO: stderr from second_pipe_cmd: b"Failed validating 'multipleOf' in schema['properties']['balance1']:\r\n"
13:41:07,21 INFO: stderr from second_pipe_cmd: b" {'inclusion': 'available',\r\n"
13:41:07,21 INFO: stderr from second_pipe_cmd: b" 'multipleOf': 0.0001,\r\n"
13:41:07,21 INFO: stderr from second_pipe_cmd: b" 'type': ['null', 'number']}\r\n"
13:41:07,22 INFO: stderr from second_pipe_cmd: b'\r\n'
13:41:07,22 INFO: stderr from second_pipe_cmd: b"On instance['balance1']:\r\n"
13:41:07,23 INFO: stderr from second_pipe_cmd: b' 1.17\r\n'

To replicate

Take this file

{"type": "SCHEMA", "stream": "stream", "schema": {"properties": {"primary_key": {"inclusion": "available", "minimum": -9223372036854775808, "maximum": 9223372036854775807, "type": ["null", "integer"]}, "testfield": {"inclusion": "available", "multipleOf": 0.0001, "type": ["null", "number"]}}, "type": "object"}, "key_properties": []}
{"type": "RECORD", "stream": "stream", "record": {"primary_key":123, "testfield": 1.1700}, "version": 1626119374393, "time_extracted": "2021-07-12T19:49:34.400771Z"}

And pipe it into the target-csv.

My guess this is something to do with decimal precision

ModuleNotFoundError: No module named 'fs.sshfs' - Setup.py install not working for some reason

Thread here althonos/fs.sshfs#49

There's not much I can do on my end, as the whole python setup.py install is kinda deprecated in favor of pip. On yours, you can try using pip install -e . instead of python setup.py install, which will install your project in "editable" mode, which will install the dependencies in the virtualenv.

Maybe recommending running locally with pip -e instead of pip install. Maybe this is all just from me install with python setup.py install and there's a better way to run local dev?

Document Column Ordering

We should document how this can be accomplished

Ejaz ul karimi Today at 9:15 AM
I tested pipelinewise-tap-mssql to export from MS SQL Server to csv (target-csv), export worked however position of columns in csv going arbitrary from one run to another therefore having impersistent csv structure. Is anyone faced this issue and found a workaround?

Meltano yaml

fixed_headers:
        fullstreamname:
        - Attribute1
        - Attribute2

Windows Invalid Format String

Traceback (most recent call last):
  File "C:\Python38\lib\runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Python38\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "C:\Users\VISCHDW\Desktop\code\d11co\meltano\.meltano\loaders\target-csv\venv\Scripts\target-csv.exe\__main__.py", line 7, in <module>
  File "c:\users\vischdw\desktop\code\d11co\meltano\.meltano\loaders\target-csv\venv\lib\site-packages\target_csv.py", line 149, in main
    state = persist_messages(config.get('delimiter', ','),
  File "c:\users\vischdw\desktop\code\d11co\meltano\.meltano\loaders\target-csv\venv\lib\site-packages\target_csv.py", line 52, in persist_messages
    o = singer.parse_message(message).asdict()
  File "c:\users\vischdw\desktop\code\d11co\meltano\.meltano\loaders\target-csv\venv\lib\site-packages\singer\messages.py", line 63, in asdict
    result['time_extracted'] = u.strftime(as_utc)
  File "c:\users\vischdw\desktop\code\d11co\meltano\.meltano\loaders\target-csv\venv\lib\site-packages\singer\utils.py", line 43, in strftime
    dt_str = dtime.strftime(format_str)
ValueError: Invalid format string

Seems similar to transferwise/pipelinewise-tap-oracle#14