Import CSV file from MS One Drive

Blackbeard87
Padawan

Hi all,
I am trying to import a CSV file from MS One Drive into Exasol. When trying this I get the following error message "[Code: 0, SQL State: 42636] ETL-6009: Number of columns in source (=1) and destination (=3) table differs (Session: 1714667066412065079)". As soon as I save the file locally and import it, it works. Any idea how I can fix this?

SQL query works (local file):

IMPORT INTO T_TEST LOCAL CSV
FILE 'C:\Users\XXX\Downloads\EXASOL_FUP.csv'
ENCODING = 'UTF-8'
SKIP = 15
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','

 

SQL query that doesn't work (One Drive)

IMPORT INTO T_TEST FROM CSV
AT 'https://og2gether-my.sharepoint.com/:x:/g/personal/XXX/'
FILE 'EXASOL_FUP.csv'    
ENCODING = 'UTF-8'
SKIP = 15
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','

 Best Regards

 

1 ACCEPTED SOLUTION

Accepted Solutions

mwellbro
Xpert

update: just as I finished writing this post there´s one thing that came to mind and I think I had a similar problem using S3 once - I believe you are supposed to put the folder path as part of the FILE clause ( similarly to how I used only the main address part in AT portion of the IMPORT ) - perhaps along the lines of:

select *
from (IMPORT INTO (c1 varchar(40000) )
FROM CSV AT 'https://og2gether-my.sharepoint.com/' FILE ':x:/g/personal/XXX/EXASOL_FUP.csv' SKIP = 15
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
);



Hi @Blackbeard87 ,

I tested the import from a public OneDrive using a community edition EXAOne (bridged network for INet-Access), seemed to do what it was supposed to:

 

select *
from (IMPORT INTO (c1 DECIMAL(10,0),c2 varchar(400),c3 decimal(1,0) )
FROM CSV AT 'https://onedrive.live.com/' FILE 'download?resid=973DC33086E3D394!124&authkey=!AFO4EaWDdc9vR04' SKIP = 1
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
);

 

mwellbro_0-1635539493393.png

Given that, if I load everything into on varchar column I end up with:

 

select *
from (IMPORT INTO (c1 varchar(4000) )
FROM CSV AT 'https://onedrive.live.com/' FILE 'download?resid=973DC33086E3D394!124&authkey=!AFO4EaWDdc9vR04' SKIP = 1
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = '|'
);

 

mwellbro_1-1635539664672.png

It´s indeed strange that you get the same error message for non-existing files...if I do that the return value in the 
"single column version" is the corresponding HTML code for "me doing somehting I´m not supposed to":

mwellbro_2-1635540025862.png


I´m however not sure how different the standard public OneDrive transfers are from the "sharepoint-based" variety that you
seem to be using - would test that as well, but setting it up in my own azure env would probably be beyond me for
the moment.

Hope something of this will prove useful to dig up that file of yours 🙂

Cheers,
Malte

View solution in original post

5 REPLIES 5

mwellbro
Xpert

Hi @Blackbeard87 ,

you´re positively, definitely sure that your OneDrive/SharePoint has the same config as your local system regarding CSV file separators ?

It seems like the "," isn´t seen as a separator when reading from sharepoint, maybe while the file "rests there" is kept in ";" separated form ?

Maybe you could switch line 7 to "COLUMN SEPARATOR = ';' and try that ?

Cheers,
Malte

Blackbeard87
Padawan

Hi @mwellbro,

thanks for quick reply. When I try it with ";" I get the same error message. Then I changed a bit the query and noticed that if I enter a different name for the FILE (a file name that doesn't exist), I still get the same error message. That is totally strange.

mwellbro
Xpert

update: corrected usage of reserved word, now using c1 instead of data as column name for import

Hmmm,

maybe check it like this, so we get what the actual contents are ( only works for remote, which is the case here - you can´t "inline import" from LOCAL CSV) :

select *
from (IMPORT INTO (c1 varchar(40000) )
FROM CSV AT 'https://og2gether-my.sharepoint.com/:x:/g/personal/XXX/' FILE 'EXASOL_FUP.csv' SKIP = 15
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
);

Cheers,
Malte

P.S.: if 40000 chars don´t suffice adjust as you see fit 😉

mwellbro
Xpert

update: just as I finished writing this post there´s one thing that came to mind and I think I had a similar problem using S3 once - I believe you are supposed to put the folder path as part of the FILE clause ( similarly to how I used only the main address part in AT portion of the IMPORT ) - perhaps along the lines of:

select *
from (IMPORT INTO (c1 varchar(40000) )
FROM CSV AT 'https://og2gether-my.sharepoint.com/' FILE ':x:/g/personal/XXX/EXASOL_FUP.csv' SKIP = 15
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
);



Hi @Blackbeard87 ,

I tested the import from a public OneDrive using a community edition EXAOne (bridged network for INet-Access), seemed to do what it was supposed to:

 

select *
from (IMPORT INTO (c1 DECIMAL(10,0),c2 varchar(400),c3 decimal(1,0) )
FROM CSV AT 'https://onedrive.live.com/' FILE 'download?resid=973DC33086E3D394!124&authkey=!AFO4EaWDdc9vR04' SKIP = 1
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = ','
);

 

mwellbro_0-1635539493393.png

Given that, if I load everything into on varchar column I end up with:

 

select *
from (IMPORT INTO (c1 varchar(4000) )
FROM CSV AT 'https://onedrive.live.com/' FILE 'download?resid=973DC33086E3D394!124&authkey=!AFO4EaWDdc9vR04' SKIP = 1
ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = '|'
);

 

mwellbro_1-1635539664672.png

It´s indeed strange that you get the same error message for non-existing files...if I do that the return value in the 
"single column version" is the corresponding HTML code for "me doing somehting I´m not supposed to":

mwellbro_2-1635540025862.png


I´m however not sure how different the standard public OneDrive transfers are from the "sharepoint-based" variety that you
seem to be using - would test that as well, but setting it up in my own azure env would probably be beyond me for
the moment.

Hope something of this will prove useful to dig up that file of yours 🙂

Cheers,
Malte

View solution in original post

Blackbeard87
Padawan

@mwellbro , thank you so much for your efforts and assistance. Your sql query on your test file also worked for me.

Unfortunately, all the steps you suggested don't work for me. I think I need to find out what the link for the file is on "onedrive.live.com" and not "og2gether-my.sharepoint.com".

Anyway, luckily I found a way to upload the files to Exasol automatically with a job scheduler.

 

Best Regards