cancel
Showing results for 
Search instead for 
Did you mean: 

How to load a CSV file into Hana?

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

I have followed all the steps required for loading into HANA using hdbsql.

I am able to laod the data too into HANA without any issue. But when the data in .csv file is invalid, all the load is failing and its not even creating error file on HANA. How would i know what went wrong?

Find below the steps i performed

1. created a table in our Hana server

2. uploaded data file into Server ( .csv file)

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

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

Heres how my control file look.

IMPORT DATA

INTO TABLE TESTIMPORTTABLE

FROM 'HanaImport.csv'

RECORD DELIMITED BY '\n'

FIELD DELIMITED BY ','

OPTIONALLY ENCLOSED BY '""'

ERROR LOG 'ErrorLog.err'

One more thing i need to know, is there a way to convert date format in control file before loading in HANA. (If its possible can any one be able to provide a same control file)

As in my .csv file the date format i have is mm-dd-yyyy and it seems HANA only accepts YYYYMMDD.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

I want to pass the HANA DB username and password at run time. I dont want to save password in a file, is there a way to do it in this approach? Please help......

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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?

Former Member
0 Kudos

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

Former Member
0 Kudos

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*

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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?

Former Member
0 Kudos

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.

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

the same problem

S0009120978, HANA hostname: hanasvr-03

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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 ?

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

@ 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!!

Former Member
0 Kudos

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:

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

Former Member
0 Kudos

Thanks Ferry, it solved the problem.

Appreciate your help.

Former Member
0 Kudos

Ferry,

Thanks! It works!

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

The folders have 755 and the file has 777 permission and the schema in which I am executing also has the Import permission.

But still getting the same error "general error: cannot open file"

Thanks

Gayathri

Former Member
0 Kudos

Hi Gayathri,

Your old error with control file was "general error: Cannot open Control file"

I think you get a new error with the csv file now: ""general error: cannot open file", so you need to change the file permission to 777 on your CSV file as well.

Regards,

Ferry

Former Member
0 Kudos

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