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

Import from FBV file using Pyexasol

NicT
Padawan

I am writing because I've been having a hard time trying to upload an FBV file to Exasol using pyexasol. Doing this directly with Exasol itself is not a problem, but it's not clear to me that it's possible in pyexasol.

First of all, to make sure it's clear what I'm talking about, an FBV file is a NON-delimited file, in which fields occupy specific column ranges. For example, a line might look like this:

012345Doe, John              98765Frankfurt                       11001011Germany

The first field here might be "01234", the next, just "5", the next "Doe, John             " and so on.

I've been trying to do this using pyexasol's import_from_file function. I can't seem to find any direct documentation or example of a way of importing an FBV file using pyexasol, but given that it's part of Exasol functionality, I thought if I played about with the parameters I might get there. Unfortunately, I'm now getting an error message along these lines:

pyexasol.exceptions.ExaQueryError:

(

    message     =>  ETL-6009: Number of columns in source (=2) and destination (=12) table differs (Session: 12345678901234567)

    dsn         =>  xx.xx.xx.xx..xx:xxxx

    user        =>  [user]

    schema      =>

    session_id  =>  12345678901234567

    code        =>  42636

    query       =>  IMPORT INTO [schema].test_table FROM CSV

AT 'http://xx.xx.xx.x:xxxxx FILE '000.csv'

ENCODING = 'ISO-8859-15'

ROW SEPARATOR = 'CRLF'

)

The words 'FROM CSV' here are not promising: does import_from_file only work for csv (i.e., delimited) files? Is what I'm trying to do possible at all?

(The alternative, which I've actually already implemented, is to write a custom reader using python / pandas. This works, but it's slow. If there is a pyexasol option, I'd prefer to use that.)

 

Thank you!

 

1 REPLY 1

littlekoi
Xpert

Pyexasol only supports CSV at this moment, since FBV is a very exotic and inefficient format.

The possible workaround is:

1) Parse FBV into Pandas: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_fwf.html

2) Import it into Exasol using .import_from_pandas() function.

As long as data types are compatible, and data fits in memory, it should work just fine.