on 01-10-2012 4:40 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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*
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
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
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.
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.
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
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.
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
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
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 ?
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
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.
@ 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!!
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.