How to load unstructured data from text file into HANA using SQLScript

[er_sharmasunil]

Hi Community Members,

I have a requirement where I need to extract data from the text file and load it into the HANA tables. The text file is contained data as shown in the attached file.

Highlighted one show fields and corresponding data for example:

data Input # :

"Slot1:Profibus (Dual, MS)

Slot2:No Interface

Slot3:15V User (Single, Isolated)"

The needed output as below

Would anyone like to share an experience or suggest how can it be done?

It will be a great help and appreciated.

Thank You

Sunil Sharma

[former_member362452]

Are you using standalone HANA? Are there any other SAP/Non SAP tools in you landscape that could write this data to HANA table?

This could be achieved if you were using BWoH easily

Thanks

Abhishek Shanbhogue

[er_sharmasunil]

Yes, It's standalone HANA DB, we don't have BWoH system. And Data has to extract from flat file as I mentioned which is not in proper format(Non Tabluar). Currently I am trying to read each row and putting extract data into Tables.

I am not sure if there is any other way to handle this situation/ complexity. Any suggestions or help would be appreciated. We can connect offline if you would like to know what the actual scenario is.

We don't have any other tool, these files generate directly from operations.

Thank You for response.

Sunil

[former_member362452]

We had a similar situation to load large file to HANA DB directly with some data transformations to it. We leveraged SAP PO that was already setup in our landscape to transform/cleanse the data and then send it to HANA file share where a CRON job would load it to HANA table. But this was couple of years ago and might not be an ideal solution.Have a look at these Q&As for some pointers

If your source cant cleanse the data and send, you ideally use a middleware to do that for you and within your client place there must be something that can either do of of it a) Pick the files from source & send it securely to desired destination securely b) Perform data cleansing/transformation. If there is no middleware then your only option is to do it in HANA

https://answers.sap.com/questions/453442/automate-data-load-from-flat-file-into-sap-hana.html

https://answers.sap.com/questions/10437968/how-to-automate-the-csv-files-to-load-the-updated-.html

https://answers.sap.com/questions/11364709/how-to-import-data-from-csv-into-sap-hana-at-a-sch.html

https://answers.sap.com/questions/9469333/how-to-run-batch-file-to-invoke-a-ctl-file-to-impo.html

Also it might be worth checking

- SDI for acquiring and cleansing by setting up flowgraphs

- XS advanced job scheduler

Thanks

Abhishek Shanbhogue

[er_sharmasunil]

Thank you so much, Abhishek for your response again. The client has changed the requirement. Now data will be the same but format in HANA has changed completely. If you are okay we can connect offline for your assistance.

.