SAP HANA 30 days trial - Importing files from FTP-Server into HANA doesn't work

[Former Member]

Hello community!

I have got a problem with importing files from the FTP-Server of sapdevcenter.com into SAP HANA.

The following steps are already done:

1. I created 3 CSV-Files and 3-CTL-Files

CTL-File AMEX.ctl:

Import data

into table S0002866913."NASDAQ"

from 'AMEX.csv'

record delimited by ' '

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err

CTL-File NASDAQ.ctl:

Import data

into table S0002866913."NASDAQ"

from 'NASDAQ.csv'

record delimited by ' '

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err

CTL-File NYSE.ctl:

Import data

into table S0002866913."NASDAQ"

from 'NYSE.csv'

record delimited by ' '

fields delimited by ','

optionally enclosed by '"'

error log 'Text_Tables.err

2. I loaded up all 6 Files into my folder (S0002866913) via FileZilla

BUT:

- Now I am in the SAP HANA Studio

- I do right click on my schema (S0002866913) -> SQL Editor

- There I wirite the following code (first a, then executing, then b and then c):

a) IMPORT FROM '/dropbox/S0002866913/AMEX.ctl'

b) IMPORT FROM '/dropbox/S0002866913/NASDAQ.ctl'

c) IMPORT FROM '/dropbox/S0002866913/NYSE.ctl'

- After executing each line, I get the following result

c) SQL Statement IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' successfully executed 9.106 minutes - Rows Affected: 0

c) SQL Statement IMPORT FROM '/dropbox/S0002866913/NASDAQ.ctl' successfully executed 2:43.202 minutes - Rows Affected: 0

c) SQL Statement IMPORT FROM '/dropbox/S0002866913/NYSE.ctl' successfully executed 3:43.938 minutes - Rows Affected: 0

But there is no content in my table NASDAQ?

Please can you help me?

Best Regards

[Former Member · Accepted solution]

I just recognized, that there is also an ERROR-File in my folder on the FTP.

The file is called "Test_Tables.err".

It is about 481 MB and starts with:

Parsing error: Incorrect Date format value for DATE field : date,

exchange,stock_symbol,date,stock_price_open,stock_price_high,stock_price_low AND SO ON.

[Former Member · Accepted solution]

Hi,

You can get around the date format by either:

a. Changing the date fromat in your source file to 'YYYY-MM-DD

or

b. simply modify your import statement of the .ctl file to

IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' DATE FORMAT 'DD/MM/YYYY'

(Assuming your source file date format is DD/MM/YYYY)

Thanks,

Anooj

[Former Member · Accepted solution]

Hello Anooj.

Thank you for your answer.

I tried your alternative B.

I modified my SQL-STatement to:

IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' DATE FORMAT 'DD/MM/YYYY'

But there is a SQL-Syntax-Error @ "DATE FORMAT"...

Regards

[Former Member · Accepted solution]

Oh sorry I missed 'WITH'

try IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' WITH DATE FORMAT 'DD/MM/YYYY'

[Former Member · Accepted solution]

Hi Anooj.

Thank you for the advice, but it still doesn't work, the table is still empty...

Can you give me another suggestion please?

P.S.:

I followed this guide from SAP Developer Center.

[Former Member · Accepted solution]

Hi,

What does your error log say? Still showing you date format error? Is all your date values in the source data coming in the same format - DD/MM/YYY? If it is say coming in another format - say DD-MM-YYYY then that is what you need to specify in the IMPORT statement. HANA expects the date format to be YYYY-MM-DD unless you tell it explicity what your source date format is - which is why you are adding that in the IMPORT statement.

Very unlikely but If you are having a combination of different date formats in your source file, work on it to make it all uniform before loading it into HANA.

Also check the error log for other errors and fix them if any before loading the data again.

Thanks,

Anooj

[Former Member · Accepted solution]

Hello Anooj.

I just opened the 3 CSV-Files and the date format is always: YYYY-MM-DD.

But there is another strange problem I noticed:

I navigate with my Browser to: ftp://ftp.sapdevcenter.com

After I have logged in with the Username&Password, I can see the wohle folder list of the SAPDEVCENTER.

When I click on my folder (S0002866913) there I can see all my 6 files:

- AMEX.clv

- AMEX.ctl

- NASDAQ.csv

- NASDAQ.ctl

- NYSE.csv

- NYSE.ctl

- Test_Tables.err (which I didn't upload)

But my problem is, that i CAN NOT download any file in my folder, there is always the error:

550 Failed to open file.

The strange thing is, that I CAN download most of the files in the folders from the other users.

Regards

[rama_shankar3 · Accepted solution]

Union:

Please check your folder permission in FileZilla and make sure you have full access.

Regards,

Rama

[Former Member · Accepted solution]

Hello Rama.

Thank you for the advice.

Now I have full access to my folder and all files in it.

That means, that I can download now my own files too.

But the problem with importing the CTL-Files in my HANA Studio is still the same

I tried both suggested variants:

- IMPORT FROM '/dropbox/S0002866913/AMEX.ctl'

- IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' WITH DATE FORMAT 'DD/MM/YYYY'

But when I do: SELECT COUNT(*) FROM NASDAQ, there are still no results / rows.

Please can you help me, because I would like to go on with this interesting tutorial.

Regards

[former_member195156 · Accepted solution]

Hi,

I took a look at your AMEX.csv file, and the dates are in the format MM/DD/YYYY, not DD/MM/YYYY.

Try IMPORT FROM '/dropbox/S0002866913/AMEX.ctl' WITH DATE FORMAT 'MM/DD/YYYY'

Also, you may need to change your .ctl file.

Change this line:  record delimited by ' '

To: record delimited by '\n'

Jody

[Former Member · Accepted solution]

Hello Jody.

Thank you so much for the answer.

I had to change the line record delimited by ' ' to record delimited by '\n' and now it works.

I didn't have to add the statement with the date-format, so the data is in correct format without the line (WITH DATE FORMAT 'MM/DD/YYYY').

Regards and have a nice weekend.

[Former Member]

Can someone help

I have a very basic question. How to create .CTL file. Is it like we paste the IMPORT code in notepad and then name the file as .CTL or is there any other way.

[rama_shankar3]

Yes, you are correct. You create it in notepad.

[deepakshenoy]

while saving in the notepad, type the name of the file between the double quotes.

Eg : if your file is example.ctl then type "example.ctl". Incase if you don't give these D-quotes, the file will still be saved as example.ctl.txt and you will get error while FTP.