cancel
Showing results for 
Search instead for 
Did you mean: 

How to load CSV files into HANA

former_member182779
Active Contributor
0 Kudos

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.

Accepted Solutions (0)

Answers (33)

Answers (33)

Former Member
0 Kudos

Hi,

I have 3 csv files approximatly 30GB each. I can't upload these files on HANA maschine. Files are also not on my Local maschine infact they are on an Application server. In this case how can I load the data from these files into HANA?

Thanks and best regards.

Fahad

Former Member
0 Kudos
Hi Alvaro,

I am now trying to import the content of  PDF files into one column of my HANA table, I have explored for a while and found we can use python, JDBC, ODBC to implement this.

I just wonder for a fresh developer without any knowledge of script or open standard, is there any available mechanism or functions embeded in HANA studio to do this?

Thanks.

Former Member
0 Kudos

Hello,

I am trying to import a csv file into Hana. I am using Amazon Web Server.

I created a table called Random and created the ctl  file with the below mentioned command:

import data into table "MySchema"."Random

from 'Random.csv' 

record delimited by '\n' 

fields delimited by ','

optionally enclosed by '""'  

error log 'RandomFile.err'

And placed the ctl and the csv file in the '/sap/usr/HDB/HDB00/DATA_LOAD'  folder on the AWS.

Next I executed the

IMPORT from '/sap/usr/HDB/HDB00/DATA_LOAD/Random.ctl'  in my Sap Hana Studio.

However I am facing this error

Could not execute 'IMPORT from '/sap/usr/HDB/HDB00/DATA_LOAD/Random.ctl'' in 241 ms 839 µs .

SAP DBTech JDBC: [2]: general error: Table does not exist

Am I doing something wrong?

Thanks,

Indu Nair

mylesfenlon
Participant
0 Kudos

Hi Indu,

I am not sure that the method used in the original posting is still current. A lot has changed in HANA since November 2011

The HANA Developer Guide covers this topic, in section 4.5. Nowadays, I am sure you need a table import file, in addition to the CSV you want to load. You must also make sure that the content of the CSV matches your table format perfectly. The Developer Guide provides an overview of all the points to keep in mind, and a tutorial for you to follow. It's available here: http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf

Regards,

Myles

Former Member
0 Kudos

Thanks Myles, I am aware of the other method which is a lot more easier.

I was actually trying to create scripts which can be scheduled rather than doing a File > Import > Data From Local File.

I was more closely looking on a way automate the file loading in Hana through scripts. Or if you are aware of any such method please help.

Regards,

Indu

mylesfenlon
Participant
0 Kudos

Hi Indu,

I've done a bit of digging around, and Bill Ramos has a very good series of blog posts about loading usage data from Wikipedia into HANA, using scripts and CSV files. It's not my area of expertise, so I can't give you any extra advice, but I'd suggest reading through that information as it certainly seems like a similar situation to what you're trying to do. The second half of this blog covers the loading of CSV files, and the subsequent blog (linked within the link I provided) has some more information on working on the data. That might help answer your questions.

Regards,

Myles.

Former Member
0 Kudos

Thanks a ton Myles.

I shall go through the blog that you suggested and try it out

Regards,

Indu

Former Member
0 Kudos

Great tutorial!

I'm on the CloudShare HANA version that I created today. This does not work:

Select * from SYSTEM.FTP_SERVER;

Could not execute 'Select * from SYSTEM.FTP_SERVER' in 3 ms 509 µs . SAP DBTech JDBC: [259] (at 21): invalid table name:  Could not find table/view FTP_SERVER in schema SYSTEM: line 1 col 22 (at pos 21)

What could be the problem? I'd like to do the CSV upload.

By the way, when I logged in from the Hana Studio, I needed to specify SYSTEM as the user, whereas in this tutorial the user is a Pxxxxxx user ID. So there is either some name collision, or some missing component.

Robert

former_member182779
Active Contributor
0 Kudos

Robert:

This thread is based in the old offer of CloudShare.com where you need to use your SCN ID as User...with the new offer...you use SYSTEM.

Also, for the new offer, you don't need to use the FTP anymore...but load the file from HANA Studio itself...using the IMPORT option.

Greetings,

Blag.

Former Member
0 Kudos

Hi,

I am also on cloud share and trying to learn PAL. I need to connect to ftp but am having hard time in identifying the credentials. Can you please help me with where to look for the port? I guess the SYSTEM user will work and its just the port which I lack.

Thanks,

Atul

0 Kudos

Hi Alvaro,


  previously i was importing csv file to sap hana database using cloudShare.com. first i was uploading to

FTP server(ftp.sapdevcenter.com) from there i uploaded to the hana database, now the platform was

changed from cloudShare.com to cloudShare.com.pro version now how should i upload a csv file to the

hana database.

Thank you.

0 Kudos

hi Alvaro,

my import query is this one

IMPORT DATA

INTO TABLE 'P1746364466'.'A'

FROM 'C:/Users/Administrator/Downloads/demo.CSV'

record delimited by '\n' 

fields delimited by ','

optionally enclosed by '"'

ERROR LOG 'C:/Users/Administrator/Downloads/USERS.ERR'

and i am getting this error. please help me for this error.

Could not execute 'IMPORT DATA INTO TABLE 'P1746364466'.'A' FROM 'C:/Users/Administrator/Downloads/demo.CSV' record ...' in 77 ms 43 µs . SAP DBTech JDBC: [257] (at 13): sql syntax error: incorrect syntax near "INTO": line 2 col 1 (at pos 13)

thanks in advance.

former_member182779
Active Contributor
0 Kudos

Ibran:

The problem is that you can't import something from you local computer using a script...you must upload your CSV file to the FTP Server and create a ctl file...like is detailed in here...

http://scn.sap.com/message/10826060#10826060

If you want to load from your local computer you need to do it like I told you here...

http://scn.sap.com/message/13837462#13837462

Greetings,

Blag.

0 Kudos

hi  Alvaro Tejada Galindo,

this import statement will work when number of columns are same in csv file and table. but how to load data to a hana database if csv file columns and table columns are not matching.

please help me for this.

Thanks in advance.

former_member182779
Active Contributor
0 Kudos

Bevinahalli:

If you need to do that, then IMPORT it's not for you...

You need to the following...in the SAP HANA Studio...

Choose File --> Import. Then on the pop-up window choose SAP HANA Content --> Data from Local File. Choose your target system. Load your Excel/CSV file and choose your schema and table.

After this, you will get a window were you can map the columns from the file to your table.

There it doesn't matter if your file and table doesn't have the same number of columns...

Greetings,

Blag.

0 Kudos

hi  Alvaro Tejada Galindo,

Thank you very much for your reply,

can i load this type of data(means column mismatch in table and csv file) through query,

because i am loading data through java program so please give me a query so that i can load data to hana database from java program instead of a manually loading data.

thank you once again.

former_member182779
Active Contributor
0 Kudos

Ibran:

If you want to upload data into SAP HANA from a Java program, you need to first establish the JDBC connection...then read the CSV file and then send it to SAP HANA using the INSERT command...

You can check this presentation:

http://www.slideshare.net/Blag/happy-sap-hana-friends

For an example on how to do it using Python...you could easily adapted to a Java program.

Just keep in mind that using an external program to load data in SAP HANA is going to be way slower than using SAP HANA Studio...because you will need to pass one INSERT at a time...

Greetings,

Blag.

0 Kudos

hi alvaro,

thank you for your reply,

Ya i can use insert statement but we uploading daily 2MB of data to database, if i used insert statement then it will be slow only. and till now we are using load query of mysql to upload data to mysql from csv file, same how can i achieve using HANA.

thank you.

Ibran.

former_member182779
Active Contributor
0 Kudos

Ibran:

As far as I know...there's not an option like LOAD QUERY on SAP HANA...or at least...I'm not aware of it...you upload data into SAP HANA using SAP HANA Studio, Data Services, STL or another similar tool...if you use any external programming language like Java, Python or Ruby...you must upload the data record by record...

Greetings,

Blag.

0 Kudos

hi Alvaro,

again Thanks for your reply,

If you get any thing about similar or related to load query of mysql in hana please let me know.

Thanks,

Ibran.

Former Member
0 Kudos

Hi Blag,

Thanks for your reply on how to give access..My bad that I troubled without reading this complete page. The solution was already on the page. So for now I loaded my table using the IMPORT option from Quick launch.

Next Problem:

I created the package P1738443363 and now I am trying to define analytic view on the NASDAQ table which I just loaded. As and when I right click on package and select New->Analytic View. I get a wizard which says NEW INFORMATION VIEW and not NEW ANALYTIC view and I do not get an option to select my schema. Please see the screen shot. Please let me know how to resolve this and thanks for all your help.

former_member182779
Active Contributor
0 Kudos

Kaustubh:

The environment of SAP HANA Studio has changed with the introduction of SPS5...when you create an Information View, on the view type you can choose if you want to create an Analytical View or an Attribute View...in this case, just assign a name and you will be able to continue...

Please read this...it will be guide you better

http://scn.sap.com/community/developer-center/hana/blog/2013/01/27/codejam--sap-hana

Greetings,

Blag.

Former Member
0 Kudos

Hi Blag,

Thanks for the info. Now atleast I am able to add table by select Add Object and then searching for the NASDAQ table. Earlier I was searching for P1738443363. Now the problem which I am facing is first i am creating attribute view and searching for NASDAQ in add object. When I am trying to add NASDAQ(P1738443363) I cannot add it or the columns and table structure is not showing up. But if select NASDAQ(S000...) I am able to see the table structure in Data foundation to do the further ground work.

Please let me know do I need to grant some privileges on my P1738443363 NASDAQ table or schema to get it added when I am doing in attribute view or whats the problem.

Also one more thing without creating attribute view when I am directly getting in to creating analytic view I am not able to see NASDAQ or P173844363 in the add object search screen which pops up. I am dedicating lots of effort and your help at any point is a great guidance.

Please let me know how to proceed further for resolution.

Former Member
0 Kudos

Hi Blag,

I was able to add NASDAQ using add object in attribute view. The problem was I tried to do mapping of schema where I mapped P173844363 to ECC6 when I removed it i was able to NASDAQ(P1738443363) in attribute view.

Now I am done with attribute view..when I come to analytic view and do add object and try to search for NASDAQ i get no results. I am not sure why the same is getting searched in attribute view add object and not in analytic view add object, I am still searching for solution maybe something stupid on my part. But if you know how to do that let me know. Again a big big thanks for you...as your help is extraordinary.

former_member182779
Active Contributor
0 Kudos

Kaustubh:

You don't need to "find" the table...just go up to the navigator section on the right....find your schema and drag and drop the table. If you're going to an Attribute View on the Analytical View...do the same...just drag and drop it...

Greetings,

Blag.

Former Member
0 Kudos

Blag,

Appreciate your help at all stages and big thanks again and again. As you said I was able to do most of the things. Now I am able to create the attribute and analytic view the terminology has changed a great deal in SPS05 actually i am using the below blog and trying to replicate the same things:

http://scn.sap.com/people/r.konijnenburg/blog/2011/12/26/sap-hana-developer-access-beta-program--get...

there are no more private attribute and measures any more in SPS05. I am really confused in how to put things in order to do the same things. I referred your code jam but that is using a different table which is flights. Can you throw some light on how to replicate the same scenario in analytic view like putting exchange, stock and date in private attributes and putting all other fields in Measure and then activating the view and displaying the results in BO or Excel.

Thanks,

KP

former_member182779
Active Contributor
0 Kudos

Kaustugh:

It's true...SPS5 has changed a big deal...however, you don't need to worry about private attributes and measures...when you select the fields, the ones that can be calculated are going to behave automatically as measures, so there's no need to worry about them...just select all the fields you need and you shouldn't have any problem at all with BO or Excel.

Just to make things more clear to you...private attributes are just the fields that you use to join the tables...so no big deal about them...they will show up as columns with an icon next to them...

Greetings,

Blag.

Former Member
0 Kudos

Hi Blag,

I am trying to run APRIORI in cloudshare environment now as per SPS5 we have to first create params, table and few other stuff in _SYS_AFL schema. But I cannot create it because of insufficient priviliges. Can you please guide me how we can run PAL algorithms like APRIORI and others in cloud share environment.

Thanks,

KP

former_member182779
Active Contributor
0 Kudos

Kaustubh:

In CloudShare.com you can only use your own schema, due to security issues as each server is shared by hundred of users. So sadly I don't think you can use PAL algorithms...for that you should create an account on AWS or KT ucloud http://scn.sap.com/docs/DOC-31722

Greetings,

Blag.

Former Member
0 Kudos

<whisper>we are going to announce a change to the trial program very soon. It will have consequences for data loading, schemes and PAL. Stay tuned and check back on the dev center every day, every hour - but not every minute, not sure our servers can handle that</whisper>

former_member184871
Contributor
0 Kudos

Seems something good is coming up...

I used to check dev center every minute once , but every hour is a good option or rather every three hour.

Regards

Kumar.

former_member182779
Active Contributor
0 Kudos

And the Code Whisperer has spoken Expect some nice news

Greetings,

Blag.

0 Kudos

hi Alvaro,

when loading data from csv file using import query, how to create a folder in ftp site and which file i have to copy there.

Thank you,

Ibran.

0 Kudos

hi Alvaro,

Can i use this

import data

into table XXX."YYY"

from 'ZZZ.csv'

record delimited by '\n' 

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err'

statement in jdbc program to load a data to hana database.

Thanks,

Ibran

former_member182779
Active Contributor
0 Kudos

In this document http://scn.sap.com/docs/DOC-27981 you will find a detailed step-by-step

Greetings,

Blag.

former_member182779
Active Contributor
0 Kudos

Nope...that it's the content of a ctl file that it's read by SAP HANA, hence you can't use it outside SAP HANA...specially because the file that want to upload is located in the FTP and not in your laptop

Greetings,

Blag.

0 Kudos

hi Alvaro,

Now i can create a folder in ftp site, but how to load csv file and how to create a .ctl file in that. and when loading data to hana database from .ctl file which file path should i give.

Thanks,

Ibran.

0 Kudos

Hi Alvaro,

import statement is working for me, now i can import data from csv file to hana database using FTP

server, please give me an example on import query with SKIP FIRST <number_of_rows_to_skip>

ROW  and COLUMN LIST ( <column_name_list> ) fields and use of these fields.

Thanks,

Ibran

0 Kudos

hi  Alvaro Tejada Galindo,

this import statement will work when number of columns are same in csv file and table. but how to load data to a hana database if csv file columns and table columns are not matching.

please help me for this.

Thanks in advance.

0 Kudos

hi  Alvaro Tejada Galindo,

this import statement will work when number of columns are same in csv file and table. but how to load data to a hana database if csv file columns and table columns are not matching.

and you can give me answer for this to mail id also, my mail id is - ibran@retigence.com

please help me for this.

Thanks in advance.

Former Member
0 Kudos

Hi Blag,

Need your help.

I am also facing the same issue which most of the other folks explained earlier. I believe the reason is the file is not properly loaded to the dropbox. I tried to load the .CTL and the .CSV files many times using FILEZILLA then using good old DOS but I do not know why it is not working.

For Ex: I loaded .CTL file only and then tried to get the .CTL back. It errored out saying could not open file in DOS. The put command was successful then GET command should get me file back from dropbox but it is not getting file.

In FileZilla as soon as I upload I can see .CTL file sometime but as soon as .CSV is uploaded the data gets refreshed and there are no files under my folder.

Can you please help me out in moving my files to ftp site?

I know all the info related to host name; username and password and I am able to make a successful FTP connection every time.

My ID is P1738443363 and I have folder in dropbox with same id.

Thanks,

KP

Former Member
0 Kudos

I had lot of issues with filezilla and overall FTP mapping..Always put all your files in your username folder under root. I was putting them under /dropbox/<username folder>....but root is mapped as /dropbox...so you need not put files under dropbox folder under FTP....

Also, try importing them using import wizard in SPS05...rather than ctl...

former_member182779
Active Contributor
0 Kudos

Kaustubh:

When working with Filezilla, you need to give full permissions to your folder, just right click on your folder and select everything...after that, you should be able to see your files and most importantly, you will be able to call them from SAP HANA Studio.

Also, make sure that your .CSV file is well formatted.

Greetings,

Blag.

former_member182779
Active Contributor
0 Kudos

Abhijeet:

That's right Root folder is "/dropbox" so you need to create a folder under "/dropbox" and then put your files in there...most of the time the problem is that people create a "/dropbox/" folder and then another folder inside it...so in the end it ends up being "/dropbox/dropbox/folder"...

Import wizard is a great way to go as well...easier and faster

Greetings,

Blag.

Former Member
0 Kudos

Hi Blag,

From last 3 hours I am stucked in the analytic view part. I am not able to map my table to the view as already described i am not getting any option to connect my schema P1738443363 to the analytic view i am defining.

I thought it must be a grant issue and I ran the grant select to schema P1738443363 to _SYS_REPO with grant option but nothing worked out. I tried to see every kind of help but nothing is working out. Every video, every blog says that create new analytic view and select schema from dropdown.

Seems to be some access issue. the big thing is everywhere in screen shot it is the new analytic view wizard where what i am getting is new information view. I do not know how to resolve completely struct and not able to move.

Request you early help.

Thanks,

Kaustubh

Former Member
0 Kudos

I'm loading amex file into my table under P067673. SQL says; statement executed successfully but doesn't load any data into HANA. I runs in like 10 seconds...I've set up right permissions to files under /dropbox/P067673 folder...Can someone check my amex.ctl file?

Former Member
0 Kudos

Hello,

following Vinod Nair's approach, I get the error:

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized.

The table I add to is in the schema, that belongs to my user and the tables has been created by myself. Thus, I should have all needed rights.

Nair's approach is the best for me, since I do not want to create an additional *.ctl file (why should anybody actually do that?). But even when using the ctl approach, I get the following error with the command "

LOAD FROM '/tmp/load_csv.ctl'":

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

Double quote do not change anything.

The ctl. file is there, readable via less is I already changed the user to hf1adm and it is located in /tmp/. So there should not be a problem.

Just in case, the .ctl:

IMPORT DATA

INTO TABLE "schemaxxxxx"."input"

FROM "/tmp/csv_input.csv"

record delimited by '\n'

fields delimited by ','

error log 'csv_input.err'

Has anybode had similar problems? I searched the SCN and found at least 5 different syntaxes for the thing. It's really annoying that they cannot come up with a "final guide including final commands". 😕

Former Member
0 Kudos

Martin, I have learnt that the syntax are different in SPS3 and SPS4 I am on SPS4 i.e v38.

For the privilege issue you need to have IMPORT System privileges added to your user

-Thanks

Vinod Nair

vinod.nair@cognilytics,com

Former Member
0 Kudos

Thaaaaaaaank you! Works like a charm and it's quite fast as well. Perfect.

So I anyone else stumbles, here is what you need to do:

- the importing user has to get the "System Privilege" named "IMPORT" (login as SYSTEM (your administrative user), go on the left to security -> users, chose the user and add the IMPORT privilege)

Afterwards the import works just perfect.

I also tested an import from my home directory (e.g. on my server /home/martin/hana_testing/csv_transaction.csv) and it worked just fine. Just ensure that the path and file is readable for the users, you don't need to change the user or put it in some strange dropbox folders.

Thanks a lot Vinod!

Former Member
0 Kudos

I have successfully loaded 17+MM in less than a min using bulk inser...here is the syntax I had a txt file...version of HANA is 36

-- Import from flat file

IMPORT FROM CSV FILE '/db/data/xxxx/xxxxx.txt' INTO "xxxx"."xxxxIS500"

WITH

THREADS 10

BATCH 10000

SKIP FIRST 1 ROW

RECORD DELIMITED BY '\n'

FIELD DELIMITED BY '|';

-Thanks

Vinod Nair

Former Member
0 Kudos

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

Former Member
0 Kudos

That is what I was looking for!

I was wonder why CVS file in local should be uploaded to FTP server to do import into HANA studio.

Thank ou Roel!

former_member182779
Active Contributor
0 Kudos

Changhoon:

Basically because the FTP is pointing to the server where HANA resides. The SAP HANA studio will load it from the server, hence it makes sense

Greetings,

Blag.

Former Member
0 Kudos

Alvaro, thank you advice.

Ye, uploading the files one by one is time consumming.

Former Member
0 Kudos

Hi Roel, sorry to bother you..

I went with this option for importing my data.

But I'm getting an error message in the last part: Data Load - Batch information: Batch from record 2 to 9 failed.

But I don't get more details. Have you experienced this before? Can you help me?

Many thanks!!

Rosana

Former Member
0 Kudos

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

Former Member
0 Kudos

I just followed all the post and FTPed couple files; but they are not visible.  I FTPed from both WinSCP and Remote Desktop - both same.  Files are not getting visible; it seems like they are getting wiped out.  In SQL Editor I've executed Import statement - it's getting executed successfully, but Rows impacted coming as ZERO.  Also, I can't see data in Data Preview. 

There seems to be a link in this blog responses - but Page NOT found error is getting displayed to review that FAQ.  Any help is greatly appreciated.

Former Member
0 Kudos

Hi Sat!

I used FileZilla as FTP client. After you crate your folder get shure you set proper access rights. It should be 755 or 777.

I guess if Import statement worked without any error message then your file uploaded successfully.

Alse check if you add "error log 'your_file.err'" row in your *.CTL file. It should appear anyway after you run IMPUT statement.

Hope it helps.

Former Member
0 Kudos

Thanks for your help Yaroslav!  That was it.  I was missing Permissions on the folder that I created on FTP server.  Thanks for your help.  I'm now able to see all my files; successfully loaded the data into HANA table.

Thanks

Sat

Former Member
0 Kudos

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.

Former Member
0 Kudos

Hi Yaroslav,

Even i am facing the same issue. Can you please tell what exactly you did to solve the problem.

Thanks

Dinesh

Former Member
0 Kudos

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.

Former Member
0 Kudos

When i import the file i get the below message:

"Statement 'select count(*) from AMEX' successfully executed in 70 ms 349 µs "

But when i executed count statement, i do not see any records.

"Fetched 1 row(s) in 0 ms 14 µs"

What could be the reason.

Thanks

USER:P862883 Server-03

former_member182779
Active Contributor
0 Kudos

Dear Ram:

How are you loading the file? When I go to the FTP I can't see any folder with your username...and when I check the /dropbox folder I can see a folder with your username, but it's empty...

Greetings,

Blag.

Former Member
0 Kudos

Hi Alvaro,

Even I'm having the same issue. I ran the import statement and says it is successfully executed but there are no records in the NASDAQ table. I even checked with count statement and open data preview.

What could be the reason?

Please advise.

Thanks

John

Former Member
0 Kudos

Hi Guys,

i am unable to load data using import statement, i get following error

Could not execute 'IMPORT FROM  '/dropbox/P1476911020/BBB.ctl''

SAP DBTech JDBC: [2]:general error: cannot open control file, '/dropbox/P1476911020/BBB.ctl

i have copied files using FTP and also set permission to 777 on all,

Please help.

Regards,

Kishore.

Former Member
0 Kudos

Hi Kishore,

I looked into your directory on the FTP server, and I have a simple explanation to offer - you called your file BBB.cpl, not BBB.ctl - easily overlooked, but not for the computer 😉

--juergen

Former Member
0 Kudos

Hi Juergen,

Thats a quick reply, thanks a lot man, not sure how my eyes missed it

Former Member
0 Kudos

Hi,

I have a very strange problem. I downloaded the nasdaq files from infochimp and merged that into one file. Later renamed it to NASDAQ.csv. I also created NASDAQ.ctl with the right code. I then used the ftp details and created a folder(/P1459049831) under root. I then uploaded NASDAQ.csv and NASDAQ.ctl and Filezilla showed successful transfer. However I don't see the csv or the ctl file inside my folder? What could be the reason?

Kind Regards

Ravi.Nataraju

Former Member
0 Kudos

Hello Ravi,

Please right click on the root folder and select file attributes. Then check the option-Recurse into subdirectories and choose apply to all folders and directories. This should help you.

Regards,

Rahul

Former Member
0 Kudos

Hey Rahul,

Thank you for your reply. Could you please confirm again that you are asking me to apply the changes on the root folder which consists of all users directories within it or should I only apply the changes to my directory which I have created on the ftp server. Thank you in advance for your reply.

Kind Regards

Ravi.Nataraju

Former Member
0 Kudos

Hi Rahul,

Sorry for the question. I have now checked/enabled the read access to owner, silly such a small mistake. I can now see my files. Thank you for your time.

Kind Regards

Ravi.Nataraju

Former Member
0 Kudos

Hi,

I am getting the following error while trying to upload csv data into table:

Could not execute 'IMPORT FROM '/dropbox/p1121413/NASDAQ.ctl''

SAP DBTech JDBC: [2] (at 15): general error: The recrod terminator is NULL

Can someone check this??

Greetings,

Rahul

Former Member
0 Kudos

Hi Rahul,

i dont see anyone answered this question, i faced same issue today, since there is no row delimeter in the file just remove RECORD DELIMITED BY from your ctl file.

Former Member
0 Kudos

Hi,

same for me

loaded data to

P1343556667

and want to run on hanasvr-07

IMPORT FROM '/dropbox/P1343556667/gutenberg.ctl'

But get

Could not execute 'IMPORT FROM '/dropbox/P1343556667/gutenberg.ctl''

SAP DBTech JDBC: [2] (at 34): general error: Cannot open Control file, /dropbox/P1343556667/gutenberg.ctl

Can someone check this?

Former Member
0 Kudos

Is solved - the issue was the filezilla created the new dir with 766 permission but 755 is required

Former Member
0 Kudos

Hi All,

we tried to upload .csv file into HANA Table.

"it is executed successfully  with 0 rows  affcted". we were unable to find where the problem persist.

Please help us.

Thanks in advance.

Phani

former_member182779
Active Contributor
0 Kudos

Phani:

Even when you load 5 million records, the system is always going to say "with 0 rows affected"...not sure why, but it's just a message...doesn't mean your data wasn't load...if it says "executed successfully" then, it should be there

Greetings,

Blag.

Former Member
0 Kudos

Hi,

I am on hana server 7:  hanasvr-07 00

I have been following the NASDAQ guide from the wiki. I retrieved the data from infochimps and uploaded it to the folders:

/S0004092614 and  /dropbox/S0004092614

I tried following the various guides for importing the data; i continuously keep getting the error. I followed the guide posted here:

http://scn.sap.com/people/r.konijnenburg/blog/2011/12/26/sap-hana-developer-access-beta-program--get...

I then followed the guide for the table for USERS in this guide:

http://wiki.sdn.sap.com/wiki/display/inmemory/Importing+CSV+files+into+SAP+HANA

It still gives an error:
SAP DBTech JDBC: [2]: general error: Cannot open Control file, /dropbox/S00040926142/USERS.CTL

No error log is generated either .Am i missing something?

rama_shankar3
Active Contributor
0 Kudos

Alvaro: thanks - I wish I had read this blog earlier - spent a lot of time late night figuring things out...in SP3...

Former Member
0 Kudos

Was resolved by inmemorydevcenter. thanks.

Former Member
0 Kudos

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

Former Member
0 Kudos

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!

0 Kudos

Hi Frank,

It looks like you are missing the "with" keyword. Try:

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

Cheers,

Jody

(The complete syntax for the import statement is in the SQL Reference Manual availabe from http://help.sap.com/hana_appliance)

Former Member
0 Kudos

Hello Everyone,

Do we have any sample .csv file with data. i would like to load huge data into HANA.

it would be very helpful, if anyone can share.

Regards,

Venkatesh.

RonaldKonijnenb
Contributor
0 Kudos

Think you might want to take a look at my new blog

[How to generate "Big test data" with a little help from SQL|http://weblogs.sdn.sap.com/pub/wlg/28538]

Former Member
0 Kudos

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

0 Kudos

Hi Praneeth,

I've answered your question on the other thread (http://forums.sdn.sap.com/thread.jspa?forumID=491&threadID=2127752)

Jody

RonaldKonijnenb
Contributor
0 Kudos

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.

former_member182779
Active Contributor
0 Kudos

Ronald:

What can I say? Thank you! This is going to be very useful for a lot of people -:) Good job!

Greetings,

Blag.

Former Member
0 Kudos

Thank you for putting together this video, Ronald. We are going to post the link to your video in the [SAP HANA Developer Center website|http://www.sdn.sap.com/irj/scn/index?rid=/webcontent/uuid/c022341c-5ed1-2e10-0b98-9b6a3314dd25]. Great job!

Cheers,

Gigi

RonaldKonijnenb
Contributor
0 Kudos

How nice!

I also written an article called SAP HANA Developer Access Beta program - 7 steps towards your first HANA report. Unfortunately I cannot upload it at the moment as the content submission system seems to be out.

Gr.,

Ronald.

former_member182779
Active Contributor
0 Kudos

I also written an article called SAP HANA Developer Access Beta program - 7 steps towards your first HANA report. Unfortunately I cannot upload it at the moment as the content submission system seems to be out.

That's because we're migrating to the new SCN -;) You should be able to upload your article soon...looking forward to read it -:)

Greetings,

Blag.

Former Member
0 Kudos

how did you come up with 7? i really like that number.

RonaldKonijnenb
Contributor
0 Kudos

Hi Blag,

That explains

Drop me a mail if you want to have a copy in advance!

tomas-krojzl
Active Contributor
0 Kudos

Hello,

I created wiki page regarding the import:

http://wiki.sdn.sap.com/wiki/display/inmemory/ImportingCSVfilesintoSAP+HANA

I am interested in constructive criticism - please suggest what can be improved.

@R. Konijnenburg

Would you consider making new video along the steps of the procedure?

Tomas

former_member182779
Active Contributor
0 Kudos

I am interested in constructive criticism - please suggest what can be improved.

Excellent Tomas! -;)

Greetings,

Blag.

Former Member
0 Kudos

how would the result of this

SELECT COUNT(*) FROM

differ from the records count in the table "Runtime Information"?

maybe, there's a way to eliminate this step and reduce the number of steps from 8 to 7.

tomas-krojzl
Active Contributor
0 Kudos

Hello,

how would the result of this SELECT COUNT(*) FROM differ from the records count in the table "Runtime Information"?

I believe it is same - however SQL Editor was mentioned in procedure several times so users following the steps should know what to do while trying to send them to "Runtime Information" tab in table properties might be confusing... (my opinion)

maybe, there's a way to eliminate this step and reduce the number of steps from 8 to 7.

Well my intention was not to keep it as short as possible but as simple as possible. Last step is of course optional and can be skipped. Maybe I should highlight it is optional... but I guessed it is clear from the step description.

However if I would have just 7 steps I could follow Ronald's example and rename the wiki page to:

SAP HANA Developer Access Beta program - 7 steps towards your first imported table

    • just kidding ** 😄

Anyway thanks for feedback.

Tomas

Former Member
0 Kudos

SAP HANA Developer Access Beta program - 7 steps towards your first imported table

what keeps you and Ronald from publishing it in the new and redesigned SDN blog page?

just make sure you have a backup, but that's a middle name for any Basis guy, so this sentence is redundant.

RonaldKonijnenb
Contributor
0 Kudos

Apparently we cannot subit articles yet. Go live on the 12th.

tomas-krojzl
Active Contributor
0 Kudos

Hello,

what keeps you and Ronald from publishing it in the new and redesigned SDN blog page?

Actually I believe that wiki is more suitable for this... (so I created wiki page)

Tomas

former_member182277
Contributor
0 Kudos

Hi All,

we cannot directly upload the file into HANA. To do so, Please follow the following steps:

1. Firstly place the file on the application Server.

2. Then, a file called ".ctl" is prepared on the Linux platform.

3. In this file, the source and destination path should be given and then excute the file.

4. file will load into HANA

I hope this information helpful for all.

Regards,

Neha

RonaldKonijnenb
Contributor
0 Kudos

Blog is up!

[7 steps to your first HANA report|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/27995]

Former Member
0 Kudos

Hello Experts,

Is there a possibility to check the directory in the HANA server which has been created with the same user name?

If so please help with the navigation as I need to check the transferred files from FTP in the HANA system.

Regards

Raman

Former Member
0 Kudos

good

Former Member
0 Kudos

Hi Ronald,

I saw your youtube video for flat file uploading and found it really helpful.

I need a favour from you. Can you please send me the CSV file that you used for the recording in youtube? I saw the number of records are quite huge. I want to try the upload with this huge data.

Regards,

Harsh

rama_shankar3
Active Contributor
0 Kudos

Good stuff guys.. thanks...

.As part of building my custom model...I figured out the below SQL syntax to use dates within SAP HANA tables. Wanted to share with you all:

*Creating HANA tables with date and time columns(syntax below):*

Create column table xxx.sales_fact_rs

(region_id integer, item_id integer, sales_amount double, order_dt TIMESTAMP, delivery_dt TIMESTAMP);

Inserting dates into HANA tables (syntax below):

Insert into xxx.sales_fact_rs values

(1,6,100,

'2011-02-01 17:07:10',

'2011-02-02 17:07:10');

Also refer to other date and time options supported by HANA in the " SAP HANA DB - SQL Reference Guide-011000358700000604922011.pdf page#14"

Enjoy working in HANA Guys !

Cheers!

rama_shankar3
Active Contributor
0 Kudos

[Click her to review SAP HANA & SAP HANA research summary|http://www.sendspace.com/filegroup/smbF3Mp4YMSL7vTk%2F9lhjg]

Former Member
0 Kudos

Ronald,

This link really helped a lot in real time ...Good Job

Thanks

Balaji Karunakaran

alfonso
Explorer
0 Kudos

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''

former_member186155
Participant
0 Kudos

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.

former_member182779
Active Contributor
0 Kudos

Eric:

AFAIK no one has complaint yet...I used myself Filezilla, but I don't think the client has anything to do...right now I have some troubles with my Cloudshare user, so can't test it right now...sorry about that...hopefully Juergen will kick in a shed some light...

Greetings,

Blag.

former_member186155
Participant
0 Kudos

No worries. It's funny you posted now. I was literally just going in to reply I was able to upload to the root folder, just not a sub-folder. Odd.

Thanks anyway. I just loaded my first table. Woo hoo!

former_member182779
Active Contributor
0 Kudos

Cool! -;) Sometimes it's not easy to handle those FTP folders...that's why I used Filezilla -:)

Greetings,

Blag.

Former Member
0 Kudos

Hi guys,

I am trying myself to upload some files to the Hana server using Filezilla but I don't know the port number I need to specify in order to access the server.

I am using the Hana sandbox system..

Thank you all!

Giovanni

tomas-krojzl
Active Contributor
0 Kudos

Hello,

port is default FTP - port 21

Tomas

Former Member
0 Kudos

Hi Tomas,

thank you for the quick reply but I am still facing an issue. With Filezilla, I try to connecting to ftp.sapdevcenter.com using the parameters I got from SQL statement SELECT * FROM SYSTEM.FTP_SERVER. However, I get ECONNREFUSED error. Everything seems to be set correctly. Any ideas?

Thanks.

Giovanni

tomas-krojzl
Active Contributor
0 Kudos

Hello,

Is problem in invalid credentials? There is possibility that information in table FTP_SERVER is not up to date. Can you paste here FTP log from FileZilla? (Just replace user/pass with ***** to not publish this information as was requested by SAP.)

Tomas

Former Member
0 Kudos

Here are the results from the SELECT:

HOSTNAME | USER | PASSWORD

ftp.sapdevcenter.com ftpuser H******

Former Member
0 Kudos

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