Import CSV\Flat file to Hana Database
Through SAP ADT or Hana Studio, we can load the CSV\Flat file without the need to use SAP S/4, facilitating the process and gaining speed in execution. Let’s see how to do this procedure.
Imagine performing the following task:
Import some CSV\Flat files to Z tables in the S/4 Hana environment.
The most common possibilities for this import are through the LSMW transaction (which requires a certain time to prepare the data and scenario) or carrying out the development of a program for this purpose (which generates a single-use program), however, there is a third alternative with great performance and ease of use: import via Hana Studio or SAP ADT directly into the Hana database.
With SAP ADT or Hana Studio, using an access to the Hana database, it is possible to import CSV\Flat files into the database directly, through a wizard.
Step by step
1 – Access your Hana environment through SAP ADT (using Hana Modeler Perspective) or Hana Studio
Figure 1 – SAP ADT Hana Connections (click to enlarge)
2 – Go to menu File > Import… On the Import screen, type CSV in filter to make the search easier. Select Data from Local File option and click Next
Figure 2 – SAP ADT Import wizard (click to enlarge)
3 – On the next screen, select the Target System and click Next
Figure 3 – SAP ADT Import environment (click to enlarge)
4 – On this screen it is necessary to inform the import data:
- Select File: file to be imported
- File encoding: encoding for reading and processing data
- Field delimiter: field separator character, which can be comma, semicolon, colon or tab
- Select worksheet: data worksheet, in the case of an excel file with more than one worksheet
- Header row exists: check if the file contains a header row
- Header row: number of the row in which the header is
- Import all data: check in case of importing all the lines of the file
- Start line and end line: inform start and end line for data import – doesn’t work if Import all data field is not checked
- Ignore leading and trailing white-space(s) in file: disregard whitespace
- New schema\table name: in case the table does not exist and must be created from the file structure
- Existing: schema name and target table (can be used filter in the Select table button
Figure 4 – SAP ADT Import file difinitions (click to enlarge)
5 – On the next screen is where the mapping between source x destination fields will be performed.
Select a field from Source File and drag it to field in Target Table. This must be done for all fields
For fields not present in the source file (like MANDT in this example), it is possible to define a fixed content for it, by right clicking on the desired field and using Make as Constant option
Just below there is a display of the first records of the source file, for review. When finished mapping, click Next
Figure 5 – SAP ADT Import mappging fields (click to enlarge)
6 – Final screen shows a view of data in the target tables, just for validation of the mapping performed. Just click Finish to start import process
Figure 6 – SAP ADT Import preview (click to enlarge)
The import process contains several consistencies, among the main ones:
- Field Size: destination field is not big enough to enter the source information
- Field format: destination field does not have the source information format
- Key and null: mandatory field without source information
- Relationship: in the case of foreign-key, if the information does not exist in the related table
In event of any consistency error, the load information is cancelled.
After click in Finish, mport process will start and can be followed by the Job Log view.
Figure 7 – SAP ADT job log (click to enlarge)
At end of the process, loaded data can be consulted via SQL Console in SAP ADT or SE16 in SAP GUI.
Figure 8 – SAP ADT SQL console (click to enlarge)
Using the new SAP ADT tools, it was possible to import data from an external file into tables in the S/4 environment without the need to use LSMW or create load programs, accelerating the process.
Only with these few steps the CSV file was informed, configured and loaded to an S/4 table 😀
Don’t forget to share with others and, if you like it, like the post.
Follow my profile for future posts (profile)