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!