32 Replies Latest reply: Jan 19, 2012 1:18 AM by Ferry Lianto RSS

How to load a CSV file into Hana?

Gayathri Loganathan
Currently Being Moderated

These are the steps we followed for loading the CVS file into HANA

 

1. created a table in our Hana server(not sap demo server)

2. uploaded data file into Server(did not use ftp)

3.created a text file with import command and placed the file in the same path as the data file in the Server

4. Execute command "Import from '/folder1/folder2/file.ctl' " from the hana sql editor

 

I am facing the error "general error: Cannot open Control file"

 

Please Help. Thanks in advance

  • Re: How to load a CSV file into Hana?
    Juergen Schmerder
    Currently Being Moderated

    Hi Gayathri,

     

    Usually this error message means that the file cannot be found. A common mistake is to ignore the fact that Linux file systems are case-sensitive (whereas Windows is not). So please check if "/folder1/folder2/file.ctl" is really spelled exactly like this and not "/Folder1/Folder2/file.CTL". If this doesn't help, check the permissions of file.ctl - user <sid>adm must at least be able to read the file.

     

    --Juergen

  • Re: How to load a CSV file into Hana?
    Gayathri Loganathan
    Currently Being Moderated

    Thanks Juergen

     

    I am still getting the same error. I checked the file permissions and also the case sensitivity of the path and file name.

     

    1. Is the steps we followed correct or are we missing something??

     

    2. Do the schema in which I am executing the Import statement needs any special permission(I am not using the sys<adm>)

     

    3. Do i need to mention the server name in the IMPORT statement

          like

           " Import from 'sever name:/folder1/folder2/file.ctl "

         instead of

         "Import from '/folder1/folder2/file.ctl"

     

     

    Thanks,

    Gayathri

    • Re: How to load a CSV file into Hana?
      Ferry Lianto
      Currently Being Moderated

      Hi Gayathri,

       

      The command is correct:

      import from '/folder1/folder2/file.ctl'

       

      You need to ensure that the folder and filename is the same, since it's case-sensitive in SLES 11.

       

      The error that you get is because the <SID>adm user (the HANA instance O/S user) does not have access to the file.ctl that you created (either the file permission or the directory permission).

      For file permission:

      Please ensure that the file and directory permission is opened with read and execute access to the "world", so that the <SID>adm user can access your file.ctl (you can run the command: chmod 644 file.ctl using your userid).

       

      For directory permission:

      Make sure that the directory (all the way from root folder) is also readable and accessible (chmod 555), so that <SID>adm user can access to the folder "/folder1/folder".

       

      As for the Import access on HANA instance, please ensure that your HANA user has System Privileges "IMPORT" (Please contact your HANA system administrator to grant you this security privilege).

       

      Regards,

      Ferry

  • Re: How to load a CSV file into Hana?
    Gayathri Loganathan
    Currently Being Moderated

    Hi Ferry,

     

    Below is the exact error I am getting now (Guess the same error from starting this thread)

     

    Could not execute 'IMPORT FROM '/HANA/FOLDER1/ZEKBZ_CSV_LOAD.ctl''

    SAP DBTech JDBC: [2]: general error: Cannot open the file, /HANA/FOLDER/EKBZ.subset

     

    EKBZ.subset is the sample file with 200 records which I am trying to load.

     

    All folders are having 755 or 777 including the ctl file as well. made sure caps or small lettters as per the server.

     

    Is there a issue bcz the host name was not given when trying to execute the IMPort commnd from SQL editor.

     

    Once again thanks for your prompt and quick response!!

     

    Regards,

    Gayathri

    • Re: How to load a CSV file into Hana?
      Ferry Lianto
      Currently Being Moderated

      Please ensure that the csv file is in the same folder as the the CTL file, and just specify the filename, and not the full path of the directory where the CSV file reside:

      import data

      into table XXX."YYY"

      from 'ZZZ.csv'

      record delimited by '\n'

      fields delimited by ','

      optionally enclosed by '"'

      error log 'Text_Tables.err'

       

       

      Can you post the content of the current folder (run "ls -l /HANA/FOLDER"), and also post the content of your CTL file?

      • Re: How to load a CSV file into Hana?
        Gayathri Loganathan
        Currently Being Moderated

        error log 'Text_Tables.err' --> Should I create this or will this created automatically....I will try to change the ctl file to remove the data file path as suggested and will revert back to you

         

        Once again thanks for the quick reply

      • Re: How to load a CSV file into Hana?
        Gayathri Loganathan
        Currently Being Moderated

        Tthe following is the content of my control file "ZEKBZ_CSV_LOAD.ctl

         

        Import data

        into table "schema1"."EKBZ"

        from 'EKBZ.subset'

        record delimited by '\n'

        fields delimited by '|'

        optionally enclosed by '"'

        error log 'Text_Tables.err'

         

         

         

        Both the EKBZ.subset and the control file is placed in the path /HANA/Folder2

         

        Both has the permission 777 and the folders has 755

         

        Schema1 has IMPORT previlege

         

        when Execute the statment

        "IMPORT FROM '/HANA/Folder2/ZEKBZ_CSV_LOAD.ctl'"

         

        I am getting the error

        *Could not execute 'IMPORT FROM '/HANA/Folder2/ZEKBZ_CSV_LOAD.ctl''

        SAP DBTech JDBC: [2]: general error: Cannot open the file, /HANA/Folder2/EKBZ.subset*

        • Re: How to load a CSV file into Hana?
          Ferry Lianto
          Currently Being Moderated

          Hi Gayathri,

           

          The Error logfile will be generated when the import command encounters incorrect data with the input data file.

           

          Your error message shows that the import control file can be accessed by the adm user canot open the subset file. It means that the directory permission is accessible, but that subset file permission is incorrect.

           

          I will give you an example test that I created on my test sandbox to replicate your issue:

           

          /usr/sap/export/index/TEST/TE/TEST01 # cat file.ctl

          import data

          into table "TEST"."TEST01"

          from 'data.subset'

              record delimited by '

          '

              field delimited by ','

              optionally enclosed by '"'

          error log 'data.err'

           

          /usr/sap/export/index/TEST/TE/TEST01 # ls -l file.ctl data.subset

          -rw------- 1 test users  18 Jan 10 13:54 data.subset

          -rw-r-xr-x 1 test users 166 Jan 10 17:22 file.ctl

           

          Run the Import command from HANA Studio:

          IMPORT FROM '/usr/sap/export/index/TEST/TE/TEST01/file.ctl'

          Could not execute 'IMPORT FROM '/usr/sap/export/index/TEST/TE/TEST01/file.ctl''

          SAP DBTech JDBC: : general error: Cannot open the file, /usr/sap/export/index/TEST/TE/TEST01/data.subset

           

          /usr/sap/export/index/TEST/TE/TEST01 # chmod 604 data.subset

          /usr/sap/export/index/TEST/TE/TEST01 # ls -l file.ctl data.subset

          -rw--r 1 test users  18 Jan 10 13:54 data.subset

          -rw-r-xr-x 1 test users 166 Jan 10 17:22 file.ctl

           

          Run the Import command from HANA Studio:

          IMPORT FROM '/usr/sap/export/index/TEST/TE/TEST01/file.ctl'

          Statement 'IMPORT FROM '/usr/sap/export/index/TEST/TE/TEST01/file.ctl'' successfully executed in 35 ms 179 µs  - Rows Affected: 0

           

          As you can see, the only difference is the file permission on the file "data.subset".

           

          Regards,

          Ferry

           

          Edited by: Ferry Lianto on Jan 11, 2012 5:38 PM

           

          • Re: How to load a CSV file into Hana?
            Gayathri Loganathan
            Currently Being Moderated

            Thanks Ferry for the detailed explanation and prompt / quick.....

             

            We solved the problem after looking at your control file

             

            Now I am getting the below mentioned error and trying to sort this out how to address!!

             

            Same set of files are loading perfectly fine thru BO DS flat file load method. so there is no mistake in the data file

             

            The exact error found in the error file "Text_Tables.err"

             

            invalid number:

            "500","0030000000",00010,145,01,"1",2010,"6004124481",0001,"C",2010-11-17,10.000,NULL,4.46,NULL,4.36,NULL,"USD",NULL,"S",NULL,NULL,NULL,20101117,"051557",NULL,NULL,NULL,"ZDCH",10.000,NULL,NULL,,,"D",NULL,,,,8954,,NULL,NULL,

             

            All records errored out from the input file.

             

             

            Thanks

            Gayathri

            • Re: How to load a CSV file into Hana?
              Gregory Misiorek
              Currently Being Moderated

              how many records get written to the error file vs how many get loaded? does each rejected record come with the same error message? you can scrub your error file and try reuploading. if you do so, what is your new error count? is it lower? if so, by how many records?

              • Re: How to load a CSV file into Hana?
                Gayathri Loganathan
                Currently Being Moderated

                All my records got written into my error file "Text_Tables.err"

                 

                There are 200 records in total.

                Zero records loaded in to the hana tables.

                The error count is also 200 with the same error message as below

                 

                The exact error found in the error file "Text_Tables.err"

                 

                invalid number:

                "500","0030000000",00010,145,01,"1",2010,"6004124481",0001,"C",2010-11-17,10.000,NULL,4.46,NULL,4.36,NULL,"USD",NULL,"S",NULL,NULL,NULL,20101117,"051557",NULL,NULL,NULL,"ZDCH",10.000,NULL,NULL,,,"D",NULL,,,,8954,,NULL,NULL,

                 

                 

                 

                Even after scrubbing  the error file and reloading it, I got he same 200 record error count with the same error message as above.

                • Re: How to load a CSV file into Hana?
                  Pradeep Banwar
                  Currently Being Moderated

                  Hi all,

                   

                  I am also trying to import the control file in Hana by using the following statement:

                  IMPORT FROM '/dropbox/S0006819325/DATAHANA.ctl'

                  Here S0006819325 is the folder i have created in ftp://ftp.sapdevcenter.com/ environment and DATAHANA.ctl is the name of my control file. This statement is throwing the following error:

                  Could not execute 'IMPORT FROM '/dropbox/S0006819325/DATAHANA.ctl''

                  SAP DBTech JDBC: [2]: general error: Cannot open Control file, /dropbox/S0006819325/DATAHANA.ctl

                   

                  As mentioned earlier in the post, i have checked the permission for directory and file, it is 777 (all access) but it does not resolve the issue. Please help.

                   

                  Regards,

                  Pradeep.

                  • Re: How to load a CSV file into Hana?
                    Ferry Lianto
                    Currently Being Moderated

                    Hi Pradeep,

                     

                    It looks like the DCC instance (hanasvr-03) has an issue with the directory setup for the dropbox folder on the server.

                    I have reported this issue and the resolution to Juergen, and we will let you know when it's fixed.

                     

                    Thanks,

                    Ferry

                    • Re: How to load a CSV file into Hana?
                      Ferry Lianto
                      Currently Being Moderated

                      Hi Pradeep,

                       

                      The issue with the dropbox directory is fixed for DCC (hanasvr-03).

                      You should be able to run the data import with the control file that you created:

                      /dropbox/S0006819325/DATAHANA.ctl

                       

                       

                      Regards,

                      Ferry

                      • Re: How to load a CSV file into Hana?
                        Archit Agrawal
                        Currently Being Moderated

                        Hi Ferry,

                         

                        Even I am getting the same error. My folder name is S0007815542 in dropbox folder. I have checked the case errors (upper case/lower casee) or authorization error (given 777 access to folder and files) .

                         

                        But while running I am getting same old error "SAP DBTech JDBC: [2] (at 13): general error: Cannot open Control file, /dropbox/S0007815542/shop_facts.ctl'

                         

                        Please help me.

                        • Re: How to load a CSV file into Hana?
                          A.D. MOLOTILOV
                          Currently Being Moderated

                          the same problem

                           

                          S0009120978, HANA hostname: hanasvr-03

                          • Re: How to load a CSV file into Hana?
                            Ferry Lianto
                            Currently Being Moderated

                            Hi A.D. Molotilov,

                             

                            Please double-check your directory/file permission and also you didn't have a control file in your dropbox folder:

                            hanasvr-03:/dropbox> ls -l | grep S0009120978

                            d-wxrw-rwx  2   1117 users          19 2012-01-16 08:14 S0009120978

                            hanasvr-03:/dropbox> ls -l S0009120978

                            total 40

                            --w-rw-rw- 1 1117 users 40384 2012-01-16 08:15 mm.csv

                             

                            Regards,

                            Ferry

                            • Re: How to load a CSV file into Hana?
                              A.D. MOLOTILOV
                              Currently Being Moderated

                              Hi,

                               

                              I use this folder /dropbox/S0009120978/

                              I check attributes for this folder and files(csv and ctl), but when i execute import

                              IMPORT  FROM '/dropbox/S0009120978/MM.ctl';

                               

                              Could not execute 'IMPORT FROM '/dropbox/S0009120978/MM.ctl''

                              SAP DBTech JDBC: [2] (at 44): general error: Cannot open Control file, /dropbox/S0009120978/MM.ctl

                               

                              Any ideas ?

                              • Re: How to load a CSV file into Hana?
                                Archit Agrawal
                                Currently Being Moderated

                                Hi,

                                 

                                In your control file the code you have written is:

                                 

                                IMPORT DATA

                                INTO TABLE S0009120978.ZMM

                                FROM '/dropbox/S0009120978/MM.csv'

                                RECORD DELIMITED BY ';'

                                ERROR LOG '/mm.ERR'

                                 

                                Please change it to the mentioned format:

                                 

                                IMPORT DATA INTO TABLE S0009120978."ZMM" FROM 'MM.csv'

                                record delimited by '\n'

                                fields delimited by ';'

                                optionally enclosed by '"'

                                error log 'mm.err'

                                 

                                I am assuming that the Schema contains table 'ZMM'.

                                 

                                Regards

                                Archit

                                • Re: How to load a CSV file into Hana?
                                  A.D. MOLOTILOV
                                  Currently Being Moderated

                                  Hi.

                                   

                                  I change ctl file, the same error.

                                  I think, if code in ctl file was wrong i have not this error:

                                   

                                  Could not execute 'IMPORT FROM '/dropbox/S0009120978/MM.ctl''

                                  SAP DBTech JDBC: [2]: general error: Cannot open Control file, /dropbox/S0009120978/MM.ctl

                                   

                                  Maybe i haven't permissions on ctl file, but already check attributes of csv and ctl file - 777.

                                • Re: How to load a CSV file into Hana?
                                  Archit Agrawal
                                  Currently Being Moderated

                                  @ Ferry: As suggested by you, I ran the import from command and was able to load data to SHOP_FACTS. Thanks..

                                   

                                  Now I am trying to load data to article_lookup but again facing the same issue.

                                   

                                  Control file name: article.ctl.

                                  CSV file: article_lookup.

                                  folder name: /dropbox/S0007815542

                                   

                                  I am unable to understand, if it is running successfully for one file why with the same settings I am getting problem to load another file.

                                   

                                  Please help!!

                                  • Re: How to load a CSV file into Hana?
                                    Ferry Lianto
                                    Currently Being Moderated

                                    Hi A.D. MOLOTILOV  and Archit,

                                     

                                    Ok, I found the problem that both of you reported.

                                    When you connect to FTP folder, please put the ctl file and csv file under your account folder (for example /S0009120978), but not under /dropbox/S0009120978.

                                    The reason is because the FTP folder gets mounted to the HANA server as /dropbox.

                                    Please refer to the following blog for clarification: SAP HANA Developer Access Beta program - get the best out of your test-drive!

                                     

                                    So, the error on the ctl file was because the files that you put on the FTP folder /dropbox/S0009120978 is translated as /dropbox/dropbox/S0009120978.

                                    Here's the current folder "/dropbox/S0009120978" look like on the HANA server:

                                    "FTP server: /dropbox/S0009120978": INCORRECT DIRECTORY

                                    hanasvr-03:/dropbox/dropbox/S0009120978> ls -l

                                    total 44

                                    -rwxrwxrwx 1 1117 users 40384 2012-01-16 09:55 MM.csv

                                    -rwxrwxrwx 1 1117 users   154 2012-01-17 13:07 MM.ctl

                                     

                                    "FTP server: /S0009120978": CORRECT DIRECTORY

                                    hanasvr-03:/dropbox/dropbox/S0009120978> ls -l /dropbox/S0009120978

                                    total 40

                                    --w-rw-rw- 1 1117 users 40384 2012-01-16 08:15 mm.csv

                                     

                                    I have moved the files "MM.ctl" and "MM.csv" from the incorrect directory to the correct one.

                                     

                                    Archit, I have moved your files as well:

                                    Before file move:

                                    hanasvr-03:/dropbox/dropbox/S0007815542> ls -rlta

                                    total 3988

                                    -rwxrwxrwx  1 1117 users     177 2012-01-16 10:05 shop_facts.ctl

                                    -rwxrwxrwx  1 1117 users 4052480 2012-01-16 10:05 test.csv

                                    drwxrwxrwx 18 1117 users    4096 2012-01-17 11:21 ..

                                    -rwxrwxrwx  1 1117 users   14140 2012-01-17 12:03 article_lookup.csv

                                    -rwxrwxrwx  1 1117 users     211 2012-01-17 12:26 article.ctl

                                    drwxrwxrwx  2 1117 users      85 2012-01-17 12:30 .

                                    hanasvr-03:/dropbox/dropbox/S0007815542> ls -l /dropbox/S0007815542

                                    total 3964

                                    -rwxrwxrwx 1   1117 users      177 2012-01-16 10:29 shop_facts.ctl

                                    -rwxrwxrwx 1   1117 users  4052480 2012-01-16 10:30 test.csv

                                    -rw-rr 1 dccadm sapsys       0 2012-01-17 08:17 Text_Tables.err

                                     

                                    After file move:

                                    hanasvr-03:/dropbox/dropbox/S0007815542> ls -l /dropbox/S0007815542

                                    total 3984

                                    -rwxrwxrwx 1   1117 users      211 2012-01-17 12:26 article.ctl

                                    -rwxrwxrwx 1   1117 users    14140 2012-01-17 12:03 article_lookup.csv

                                    -rwxrwxrwx 1   1117 users      177 2012-01-16 10:05 shop_facts.ctl

                                    -rwxrwxrwx 1   1117 users  4052480 2012-01-16 10:05 test.csv

                                    -rw-rr 1 dccadm sapsys       0 2012-01-17 08:17 Text_Tables.err

                                     

                                    You should be able to run the import command correctly now.

                                     

                                    Regards,

                                    Ferry

                        • Re: How to load a CSV file into Hana?
                          Ferry Lianto
                          Currently Being Moderated

                          Hi Archit,

                           

                          Your dropbox directory and file permission looks correct on hanasvr-03, and I was able to view your control file:

                          hanasvr-03:/dropbox> ls -l | grep S0007815542

                          drwxrwxrwx  2   1117 users          64 2012-01-16 16:35 S0007815542

                          hanasvr-03:/dropbox> cd  S0007815542

                          hanasvr-03:/dropbox/S0007815542> ls -l

                          total 3964

                          -rwxrwxrwx 1   1117 users      177 2012-01-16 10:29 shop_facts.ctl

                          -rwxrwxrwx 1   1117 users  4052480 2012-01-16 10:30 test.csv

                          -rw-rr 1 dccadm sapsys       0 2012-01-16 16:35 Text_Tables.err

                          hanasvr-03:/dropbox/S0007815542> cat shop_facts.ctl

                          IMPORT DATA INTO TABLE S0007815542."SHOP_FACTS" FROM 'test.csv'

                          record delimited by '\n'

                          fields delimited by ';'

                          optionally enclosed by '"'

                          error log 'Text_Tables.err'

                           

                          It seems that the import command was executed against DCC instance on 2012-01-16 at 16:35, because the error log file "Text_Tables.err" was generated by the dccadm O/S user .

                           

                          Regards,

                          Ferry

                • Re: How to load a CSV file into Hana?
                  Ferry Lianto
                  Currently Being Moderated

                  Hi Gayathri,

                   

                  I'm assuming that you are doing this data import on your own HANA instance, then I cannot help further in troubleshooting.

                  Based on the error message with 200 rows of data, I'm guessing that one of the data is conflicting with the data type in the target table.

                  I only can suggest you to check the data types for each column and use one row of data to narrow down the incorrect data value.

                  Looking at the error message, try to check on this data "20101117", which looks like date type.

                   

                  HTH,

                  Ferry

  • Re: How to load a CSV file into Hana?
    Gayathri Loganathan
    Currently Being Moderated

    Hi all,

     

    My data file sample record

     

    500|0030000000|00010|145|01|1|2010|6004124481|0001|C|20101117|10.000 |4.46 |4.36 |USD||S||||20101117|051557||||ZDCH|10.000 ||CAD||CXB8954|S|||700||||||||

     

    my ctl file

     

    Import data

    into table "schema1"."EKBZ"

    from 'EKBZ.subset'

    record delimited by '\n'

    fields delimited by '|'

    optionally enclosed by '"'

    error log 'Text_Tables.err'

     

    when i executed the import statement,The exact error found in the error file "Text_Tables.err"

     

    invalid number:

    "500","0030000000",00010,145,01,"1",2010,"6004124481",0001,"C",2010-11-17,10.000,NULL,4.46,NULL,4.36,NULL,"USD",NULL,"S",NULL,NULL,NULL,20101117,"051557",NULL,NULL,NULL,"ZDCH",10.000,NULL,NULL,,,"D",NULL,,,,8954,,NULL,NULL,

     

     

    There are 200 records in total.

    Zero records loaded in to the hana tables.

    The error count is also 200 with the same error message as above repeated for all the records

     

    Even after scrubbing the error file and reloading it, I got he same 200 record error count with the same error message as above.

     

     

    Same set of files are loading perfectly fine thru BO DS flat file load method. so there is no mistake in the data file

     

    when I compared my error file to the data file, I found that wherever there is a space before the  "|" symbol an extra NULL value added and is considered a new fields.

     

    I don't want to touch the data file,since the original data file I will be loading will have approximately 130 million records

     

    please let me know if anyone has any idea why a NULL field is added and how to get rid of it?.

     

    Thanks

    Gayathri

    • Re: How to load a CSV file into Hana?
      Ferry Lianto
      Currently Being Moderated

      Hi Gayathri,

       

      You can create a new csv file with no space before the "|" sign using sed command in Unix.

      Here's a quick example that I did to remove the space before "|":

      Unix> cat test.csv

      123|abc |def |ghi|456|789 |jkl

      Unix>  sed -e 's/ |/|/g' < test.csv > test_clean.csv

      Unix>  cat test_clean.csv

      123|abc|def|ghi|456|789|jkl

       

      So, just create a new csv file, and modify your ctl file to load with the new csv file.

       

      Regards,

      Ferry

  • Re: How to load a CSV file into Hana?
    Gayathri Loganathan
    Currently Being Moderated

    Thanks Ferry for the quick reply. If it is only 200 record file i can do it.

     

    200 record file is a sample file.

     

    Will this method work for a 130 million record file which is appox 270 GB??

     

    If yes, can you tell appox how much time it this command will take for the 270 GB file??

     

    the reason I am asking the above question is that using BO DS it is taking  approx 10 days to load 50 million records into the table with 200 fields

     

    We have atleast 5 tables in the same criteria

     

    To increase the performance, we are just trying to load the CVS using the SQL Import method.

     

    --Gayathri

    • Re: How to load a CSV file into Hana?
      Ferry Lianto
      Currently Being Moderated

      Hi Gayathri,

       

      The Unix command should work with large file, but I don't have the exact time on how long it takes to process 270GB file.

      It really all depends on the disk configuration of your Unix machine, and also keep in mind that this Unix command will read your original file and create a new file, so the disk I/O may be high with read and write when you execute the sed command in Unix.

       

      Regards,

      Ferry.

       

      Edited by: Ferry Lianto on Jan 18, 2012 7:24 PM

       

      Edited by: Ferry Lianto on Jan 19, 2012 1:17 AM

Actions