Skip to Content

How to load CSV files into HANA

1.- Create an CSV file with your data

2.- Copy the file to dropbox/yourname inside the HANA box

3.- Create a table in HANA with the structure of your file

4.- create the control file BBB with the following information:

import data

into table XXX."YYY"

from 'ZZZ.csv'

record delimited by '\n'

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err'

Where XXX is your schema, YYY is your HANA Table and ZZZ is your file

5.- Open an Script File and write the following:

LOAD FROM '/filer/dropbox/yourname/BBB.ctl';

where BBB is the name of your control file

Greetings,

Blag.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

33 Answers

  • avatar image
    Former Member
    Sep 12, 2012 at 09:03 AM

    We found the Import function from the Quick Launch to be very useful. Also the csv files can easily be copy-pasted from laptops to the CloudShare environment.

    From the top of my head the steps to be taken (Modeler -> Quick Launch -> Import):

    - Choose Data from Local File

    - Select the HANA system

    - Locate the csv file

    - Set delimiter to ';' if applicable

    - Choose a new or existing table

    - Map the fields of the csv file to the target table (usually 1-on-1)

    - Go!

    Cheers, Roel

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Rosana,

      Sorry to hear that. I myself have not had this error message. Did you check these lines for 'strange' characters? Maybe saving the Excel sheet as an older format?

      Sorry I can't be of any more help 😔

      With kind regards,

      Roel van den Berge

  • avatar image
    Former Member
    Jul 17, 2012 at 03:49 PM

    Hi, everybody!

    i'm trying to load data, but unfortunately get error:

    Could not execute 'IMPORT FROM '/dropbox/S0009733663_YLN/DOKSM.ctl''

    SAP DBTech JDBC: [2]: general error: Error processing a statement at "IMPORT"

    I've cheked all names, paths and folder authority.

    Server: hanasvr-02

    *.CVS and *.CTL files encoded in UTF-8. If chage it to ANSI i get another error:

    Could not execute 'IMPORT FROM '/dropbox/S0009733663_YLN/DOKSM.ctl''

    SAP DBTech JDBC: [139]: current operation cancelled by request and transaction rolled back: CSV file has some bad lines and cannot create bad file. some records are loaded.

    Problem solved:

    I experimented with file, delimeters and encoding and finally i managed to import data.

    It was successfull after i encoded the files into ANSI again.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Dinesh,

      What i did was i encoded my csv-files to utf-8 and ctl-files to ansi. Thats all as i remember

      Check you csv and ctl for non-printable characters.

      Hope this helps.

  • Nov 04, 2011 at 07:51 PM

    Hello,

    stupid question - what is dropbox? Never heard about it... 😊

    Tomas

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2011 at 02:39 PM

    With the recent upgrade to the HANA SP3...the method to upload files to HANA has been slightly changed...so here's the updated tutorial:

    1.- Create an CSV file with your data
    
    2.- Copy the file to the HANA Dev Center
    
    3.- Open a FTP connection to ftp.sapdevcenter.com 
    (You can find the user name and password by doing a select to SYSTEM.FTP_SERVER) 
    and....*Don't paste the Username or password here or any other place!*
    
    4.- Create a folder with your name on the FTP site and copy the file there
    
    4.- Create a table in HANA with the structure of your file
    
    5.- create the control file BBB with the following information:
    
     import data
     into table XXX."YYY" 
     from 'ZZZ.csv'
     record delimited by '\n' 
     fields delimited by ','
     optionally enclosed by '"'
     error log 'Text_Tables.err'
    
     Where XXX is your schema, YYY is your HANA Table and ZZZ is your file
    
     5.- Open an Script File and write the following:
    
     IMPORT FROM '/dropbox/yourname/BBB.ctl';
    
     where BBB is the name of your control file
    

    I just test it and it works...if it doesn't work for you, please let me know

    Greetings,

    Blag.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      Take into account that root server in ftp "/" appears as "/dropbox" for HANA database, so when transfering with FTP you create your personal folder under dropbox as instructed, for doing import you need to use this:

      IMPORT FROM '/dropbox/dropbox/<user>/<file>.ctl';

  • avatar image
    Former Member
    Nov 27, 2011 at 03:51 PM

    Edited by: Anand Gupta on Nov 28, 2011 4:55 PM

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 30, 2011 at 03:14 AM

    Hey Blag,

    Has anyone else reported issues uploading to this FTP drop? I tried to create a folder a few times and upload files with the user in the table on the box and it creates a weird copy of the folder under itself and won't let me delete.

    The issue can be seen in the ev, ev2, and S0006858445 folders. It's weird I used a shell and Cyberduck with the same result.

    Any guidance is appreciated here.

    Thanks.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Thomas,

      I got to fix the issue. It seems that it was because of a proxy set in my company intranet. I repeated the procedure installing Filezilla in the sandbox system provided and... there we go!

      Thank you.

      Regards,

      Giovanni

  • Dec 02, 2011 at 11:38 AM

    Hi guys,

    I just made a screenrecording on how to execute the mentioned steps:

    [How To Load CSV files into HANA|http://youtu.be/TzANTmNG5sw]

    Hope it's usefull to someone.

    Ronald.

    Add comment
    10|10000 characters needed characters exceeded

    • Please help.

      Having same issue with control file ...tried everything so far suggested in the threads on this very question. Thanks in advance,

      IMPORT FROM '/dropbox/S0000384916/NYSE.ctl'

      SAP DBTech JDBC: [2] (at 13): general error: Cannot open Control file, /dropbox/S0000384916/NYSE.ctl

      Could not execute 'Import from '/dropbox/S0000384916/NYSE.ctl''

  • avatar image
    Former Member
    Jan 31, 2012 at 12:22 PM

    Hi,

    From last couples of weeks i am trying to load my CSV file into HANA Table, but i am unable to succeed.

    I am getting error "Cannot open Control file, /dropbox/P1005343/CRM_OBJ_ID.CTL". I have followed wach and every step in this thread, still I could not load data into my HANA table.

    FTP: /dropbox/P1005343

    SQL Command:

    IMPORT FROM '/dropbox/P1005343/CRM_OBJ_ID.CTL'

    Please help me on this

    Regards,

    Praneeth

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 01, 2012 at 07:38 AM

    Thank you for the tips.

    I do what you say. But I meet the error:

    Could not execute 'load from '/usr/sap/HAN/HDB01/work/invoice.ctl'threads 4 batch 10000'

    SAP DBTech JDBC: [2]: general error: Unknown keyword, import

    or

    Could not execute 'import from '/usr/sap/HAN/HDB01/work/invoice.ctl'threads 4 batch 10000'

    SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "import": line 1 col 1 (at pos 1)

    I build my own HANA sand box in VMWARE.

    Need help!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 14, 2012 at 09:01 AM

    Hi all, for host hanasvr-05 and userid - P1121834, unable to query table FTP_server as it does not exist under the SYSTEM schema. Could someone help?

    Edited by: vinsapbw on Feb 14, 2012 10:01 AM

    Edited by: vinsapbw on Feb 14, 2012 12:40 PM

    Add comment
    10|10000 characters needed characters exceeded