Import flat file(CSV) with HDB Table – Table Import Configuration
Welcome again to the next tutorial where we learn how to link a CSV file to an HDB table. This is the third and last part of the data load to HANA table tutorial series. This method only works with HDB Tables. Although this is the longest method, it is the recommended one. Why? Read on to understand.
Be sure to check out our other tutorials on the other data load methods to SAP HANA:
Importing CSV data file to SAP HANA repository
In this approach, the CSV file will reside on your SAP HANA server and not the local desktop. Open HANA studio and go to the repositories tab. If you don’t see this tab, you are not in the developer perspective.
Under your package, right click and go to New-> Other from the context menu.
The below window pops up wherein you should select “File” and click Next.
Enter a file name. I have provided it as customer_revenue.csv. Press Finish when done.
Depending on your HANA Studio default configuration, either a text editor opens up or ideally an excel window will be opened. I prefer not to use excels for CSV files as they tend to corrupt the CSV format sometimes. So instead of filling in the table data here, we just save it blank.
On pressing save, you get the below message. Press Yes.
Now, exit the Excel and excel throws the below message. Press Don’t save here.
You would notice a new file can be seen inside our package now. The grey diamond symbol on the file icon means that it is currently inactive.
Right click on the file and then press Open With -> Text Editor.
Sometimes you would get an error on the right side pane saying “The resource is out of sync with the file system”
In such cases, right click on the file name and click refresh to fix this problem.
Now in the editor that opens up, we paste the CSV data from our tutorial on flat file upload, copied by opening it in notepad.
Once done, press the activate button marked below.
Now the CSV file would have become active. Notice that the grey diamond icon has gone away.
Now we have an HDB table we created in an earlier tutorial by the name CUST_REV_HDB.
Linking the SAP HANA HDB table with this CSV file – HDBTI configuration
To do this, stay in the repository tab and right click on your package. Again select New-> Other as shown below.
Write ‘configuration’ in the search bar as shown below. A list of options will open up. Click on Table Import Configuration inside the Database Development folder and press Next.
Provide a name to this configuration file and press Finish.
The editor opens up on the right and a file is created in the package as well. Notice the grey diamond again.This means that this file is inactive.
The syntax of this hdbti file is as given below
import = [
{
hdbtable = “<hdbtable_package_path>::<hdbtable_name>”;
file = “<csvfile_package_path>:<csvfilename>”;
header = <header_existence>;
delimField = “<delimiter_sumbol>”;
}
];
<hdbtable_package_path> refers to the package where your HDBtable was created. If your package was called Fruits, your package path will be “Fruits. But if you had a package Orange inside the package Fruits and the HDB Table was inside this Orange package, the path would be “Fruits”.”Orange”
<hdbtable_name> Refers to the table that we wish to link this file to
<csvfile_package_path> Package path where your csv file was created.
<csvfilename> The filename of your CSV flat file.
<header_existence> Can take value true if you have a header row in your excel. Otherwise, it’s false.
<delimiter_sumbol> refers to the data separator/delimiter which in our case was commas. Check your data file carefully to understand whether it is a comma or a semicolon or anything else. Enter it here.
In our case, the code would look like the below. Once done, press activate.
As seen below, the grey diamond symbol has gone away from the hdbti file. This confirms that it’s active.
To confirm if the data is now linked, we go back to the systems tab and right click on our table to do a data preview.
The Raw data tab confirms that it is working.
This concludes our three part tutorial of data loads to custom tables on SAP HANA. For client delivery always use this method as the CSV file also can be transported to further systems and it also means that the flat file is always in your HANA server and not the local desktop.
Help this website grow by sharing this document on social media by using the icons below.
Happy Learning!
Dear Admin,
I am exactly performing the similar Upload.
But in myy cs i have around 90000 records and these records are sorted by Date field in CSV file.But while importing to table these are not inserting in the order how the csv file has.
Could you please tell me how to upload the records as in csv
Hi Mantri,
Thank you for reading this post.
Before I answer your question, let’s talk about data modeling and understand what it means.
Data modeling involves taking relevant data from a source data set (usually tables and views) and cleansing/transforming it into a data model that has all the information needed for an application/report to use the data as it needs to satisfy the business need.
The point being that as a data modeler, properties of data like ordering are highly irrelevant at the table level. That’s why even while loading data from flat file , the system only make sure it imports all data rows without caring much about the order. It usually sorts by the key fields if you have defined them in your target table.
Anyways, the order of data at this point is not important at all. All you should be worried about is applying the correct logic in calculation views/procedures you are using these tables for.
Cosmetic properties of the information you deliver should be left to the end application that consumes the HANA view.
Thank you for asking this question. Please ask more so that it helps you and others who have similar questions.
Also, please share at least one document on social media to extend your support for my hard work to keep this free of cost for everyone.
Shyam
Hi Shyam,
I have a csv file and i need to upload all the records into the tables in same way without changing the order of row.
Is there anyway where i can upload all the records as like csv.
No. The HANA system does not automatically do it since the order of data is irrelevant at the entry point of data modeling.
Any idea how this works internally, and thus its refresh rate and speed with large files? Is the table accessable while it updates too? Will it keep copying if there aren’t changes or does it detect changes and only copy if there are etc?
The hdbti file is a link between the data and the table. Whenever the csv data file is changed, it becomes inactive. Upon reactivation, data is updated in the table.
The hdbti file is a link between the data and the table. Whenever the csv data file is changed, it becomes inactive. Upon reactivation, data is refreshed,
Many thanks for your swift reply. Just wanted to also say, these tutorials are absolutely great. I did HA100 and HA300 official SAP courses, and honestly learnt more useful stuff from your tutorials here. Keep up the good work!
Thanks. Glad this helped you. Please support the site by sharing at least 1 tutorial on social media.
EXCELLENT SHYAM… ITS REALLY GOOD. THANKS SHARMA
Thanks! Please support the site by sharing at least 1 tutorial on social media.
can I now the difference between the three approaches in order to create the tables
and you said that the approach was better when compared to the first one, why this approach was better can you explain it
He explained in last para, in this case csv file can be transported across q and prod system.
HI AJ
You said that it can be to transported easily from one system to another but my question is ,only the structure is transformed or data can also be transformed.
Nothing transforms. It is just moved as it is in development system.
The cleansing/transformation only occurs as part of the view that you create on top of these tables.
Transformation is not done as part of the tables.
Hi Shyamuthaman,
Then what is the use of hdb method,we can use the import method as it is easy when compared to HDB process.
In DEV we are maintaining the flat file data and by using the HDB process we are bring the flat file into hana system and when the view which is build on this flat file data is moved from DEV TO QAT , In QAT again we have to do the HDB process are not ?
if yes then what is the majar difference between the import method and HDB method.
In HDB method, you can transport the data and table separately. A change request travels from Dev to Q to Prod. Data can be changed on the flat file and transported to Prod without transporting the table again.
Hi Shyam,
Excellent Blog, I have been following your Site and its very useful in real time.
I have a query, How to handle if a flat file if it is dynamic eg., need to be loaded on weekly basis in auto mode.
Should I go with BW, create process chain and lcovert to HANA tables (or) create procedure & run it daily to load in HANA HDB tables from flat file. Appreciate your thoughts on this please.
Thanks,
Mahesh
Check out my tutorial on XS jobs in the SQL section.
hello Shyam,
nice information, i have a excel file data imported to hana, but nulls are imported as zero.
do u have any solution.
thanks
Is there a difference between HDB tables and HDBDD/HDBCDS tables ?
And would the above explained method work with HDBDD/HDBCDS tables as well?
Regards
Saurabh D.