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

IMPORT FROM CSV AT <loc> FILE 'name with wildcards'

Klaus_Strahl
Contributor

Hi everybody,

I need to import CSV Files, that are dropped off by an external party on our SFTP site. However the naming convention contains both a timestamp and a version number ....

Is there any possibilty from within Exasol to either query the file name on the SFTP Server before the import is done

or use wildcards in the filename?

I hope, I didn't miss a post that covers that topic already during my search for any information on this....

I appreciate any help or idea, thank you very much

Klaus

4 REPLIES 4

exa-Aleksandr
Team Exasol
Team Exasol

Hi @Klaus_Strahl ,

Maybe the following option of IMPORT command can help you?


If you specify a folder, the result contains one row for each file in the given folder with one column containing the filename.

 

 

Klaus_Strahl
Contributor

Thanks for your quick response @exa-Aleksandr !

I had considered that one, however

IMPORT INTO dwhstg.temp_filelist_kst
FROM CSV AT s3_sftp
FILE 'lido/' ;

does not return any rows although the connection works, the folder lido exists and there is even a file in it (which I can load if I specify the full name)...

Did I misunderstand the command syntax to "specify a folder" ?

exa-Aleksandr
Team Exasol
Team Exasol

Actually, I'd expect from the form of statement that you've passed.

I checked now, and generally, this approach works for me with

IMPORT INTO
	(col1 VARCHAR(1000))
FROM
	CSV AT 'sftp://***.net'
	USER 'demo'
	identified BY 'password'
	FILE '/pub/example/'
;

Maybe your FTP server doesn't expose some methods?

If file listing doesn't work for you, then maybe the following UDF can help:

https://github.com/exasol/database-migration/blob/master/s3_to_exasol.sql

?

Klaus_Strahl
Contributor

Thanks for the hint @exa-Aleksandr  - I will dive into it and let you know whether I could get the pythons working 🙂