Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Export to csv output filename

ikutle
Contributor

Hello everyone,

Is there a simple solution to manipulate string parameter when calling Export to csv function in such manner it automatically adds timestamp to filename? 

Here's an example of what I am trying to get:


EXPORT <table_name>
INTO LOCAL CSV FILE 'C:\exportfile_<current_timestamp>.csv'

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

przemek
Xpert

Hi @ikutle,

You are right, this feature is not supported with the "INTO LOCAL CSV FILE" condition.

https://docs.exasol.com/db/latest/sql/export.htm

local_file_export.jpg

If you want to export the data to a local file system, then there is only the possibility via JDBC driver or EXAplus.
Spontaneously come to mind the solution via a bash script.
Something like that...

#!/bin/bash

host="192.168.56.101"
port="8563"
user="sys"
pwd="exasol"

echo -e "EXPORT INTO LOCAL CSV FILE"
timestamp=`date +%s`
./exaplus -c $host:$port -u $user -p $pwd -sql "EXPORT <SCHEMA>.<TABLE> INTO LOCAL CSV FILE '/tmp/exportfile_$timestamp.csv';"

Greetings
Przemek

View solution in original post

5 REPLIES 5

mwellbro
Xpert

Hi @ikutle ,

currently on vacation so I don´t have an exa "as ready as usual", but: I think the "easiest" way would be to wrap the export into a script, as far as I know there is no "out of the box" way to put a parameter like the current_timestamp directly into the export command (i.e. as part of the FILE clause ).
Might be easier to pass it from a (shell-)script that´s calling the export and construct the export statement there ?

Are you looking for something to make your life easier when you work with a sql client or something that is more of an integrated thing into scripts and/or scheduler-type of thing ?

Cheers,
Malte

ikutle
Contributor

Hello @mwellbro,

Thanks for the answer. If I try to wrap it in UDF Script it throws an error:
"Feature not supported: IMPORT and EXPORT of local files is only supported via JDBC (except prepared statements) or EXAplus". 
Any advice on how to go around this?

Kind regards

przemek
Xpert

Hi @ikutle,

You are right, this feature is not supported with the "INTO LOCAL CSV FILE" condition.

https://docs.exasol.com/db/latest/sql/export.htm

local_file_export.jpg

If you want to export the data to a local file system, then there is only the possibility via JDBC driver or EXAplus.
Spontaneously come to mind the solution via a bash script.
Something like that...

#!/bin/bash

host="192.168.56.101"
port="8563"
user="sys"
pwd="exasol"

echo -e "EXPORT INTO LOCAL CSV FILE"
timestamp=`date +%s`
./exaplus -c $host:$port -u $user -p $pwd -sql "EXPORT <SCHEMA>.<TABLE> INTO LOCAL CSV FILE '/tmp/exportfile_$timestamp.csv';"

Greetings
Przemek

mwellbro
Xpert

Hi @ikutle ,

I think Przemeks answer is about as easy as it´s going to get - normally I´d say something about my favorite feature ( SQL pre-processor ) but in case of EXPORTs the FILE-parameter is transformed from the actual value into an UUID so appending a timestamp isn´t an immediate option - I do believe one could use an ADAPTER-Script to create a (very) special virtual schema that might automatically kick in and append / enhance your EXPORT...but that´s just me guessing...I should have a look at that 🙂

edit: on second thought the virtual schema idea probably has the same problem as the "use a wrapper script" one, since most if not all of what´s happening is on the server-side...
Maybe post an IDEA to extend the syntax of EXPORT to become more flexible ?

Cheers,
Malte

P.S.: is what Przemek suggested viable for you ? And sorry for forgetting about the limitation when going for LOCAL exports...must be all this vacationing I´m currently engaging in 😛

ikutle
Contributor

Hello  @przemek and @mwellbro,

thank you for your answers. This seems to be the easiest way to do so. 

Kind regards