Create a CSV from a HANA table using XSJS and FTP via Node.js in HCP cloud Foundry platform in another Server

[pallab_haldar]

Today I will discuss about generate and sending a CSV FTP via Node.js in HCP cloud Foundry platform in HANA. It is very useful in lots of business scenarios -

A. Create a CSV from a HANA table using XSJS :

• 1st approach to create an XSJS  getExcelData.xsjs without using UTIL API -

//Generate excel :

function getExcelData(){

	

	var todaysDate = new todaysDate().toString().slice(4, -24);

	var body = 'Employee ID, Employee Name,Credit Threshold \n';

	var query = "SELECT  \"EMP_ID\",\"EMP_NAME\",\"CRD_THRD\"  FROM \"_SYS_BIC\".\"CreditHistory/E_CREDIT_HISTORY\" ";

	var conn = $.db.getConnection();

    var pstmt = conn.prepareStatement(query);

    pstmt.execute();

    var rs = pstmt.getResultSet();

    

    while(rs.next()) {        

		body += rs.getString(1)+

		","+rs.getString(2).replace(/,/g,' ')+

		","+rs.getString(3).replace(/,/g,' ')+"\r\n";

			}

var excelData = body ;

}

 

 

Call the generated file to save it in the server space by creating the below writeExcel.xsjs

```javascript

function writeExcel()

{

  var excelData = getExcelData();

  var filename = "/usr/sap/HXE/HDB90/work/Credit_history.csv"; // Replace with your desired file location

  var file = new $.net.File(filename);

  file.open("w");

  file.write(excelData );

  file.close();

  return "File created";

}

 

• Another way is to  generate excel using util  API-

 

function generateExcelData()

{



	var todaysDate = new todaysDate().toString().slice(4, -24);

	//var body = 'Employee ID, Employee Name,Credit Threshold \n';

	var query = "SELECT  \"EMP_ID\",\"EMP_NAME\",\"CRD_THRD\"  FROM \"_SYS_BIC\".\"CreditHistory/E_CREDIT_HISTORY\" ";

	var conn = $.db.getConnection();

    var pstmt = conn.prepareStatement(query);

    pstmt.execute();

    var rs = pstmt.getResultSet();

    

    while(rs.next()) {

        

		body += rs.getString(1)+

		","+rs.getString(2).replace(/,/g,' ')+

		","+rs.getString(3).replace(/,/g,' ')+"\r\n";

			}

 

  var workbook = XLSX.utils.book_new();

    

  // Create a worksheet from the data

  var data = [

    ['Employee_ID', 'Employee_Name','Credit_Threshold']

  ];

  

  

  // Add data rows to the data array (make sure to replace commas with spaces)

  while (rs.next()) {

    data.push([

               rs.getString(1)+

		","+rs.getString(2).replace(/,/g,' ')+

		","+rs.getString(3).replace(/,/g,' ')+"\r\n";

    ]);

  }

  

  var worksheet = XLSX.utils.aoa_to_sheet(data);

  

  

  // Add the worksheet to the workbook

  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

  

  

  // Write the workbook to a file

  var fileName = 'Credit_History' + ' ' + todaysDate + '.xlsx';

}

 

 

 

B. Using SFTP in Node.js application transfer the

1. Create a Node.js application using SAP Business Application Studio. Use the below link of one of my blog create -

https://blogs.sap.com/2023/01/10/implement-a-node.js-module-without-xsjs-support-to-populate-with-ha...

2.  Now we need to install the API which will connect to HANA DB i.e. @ssh2-sftp-client -

 Install the required package:



```bash



user: demonodejs $ npm install  ssh2-sftp-client

```

3. Create a  index.js file and write the code the below  and customized as per your server using

 

3. Set up the configuration for connecting to the SAP HANA server:



```javascript

const sftpConfig = {

  host: 'hana_server_address',

  port: 'sftp_port', // Default is 22

  username: 'your_username',

  password: 'your_password',

};



// We have already write teh file in XSJS but you can write the file 

//using node.js also using below code

```javascript

const csvData = 'your_csv_data_here'; // Replace with your actual CSV data

const csvFilePath = 'path_to_your_csv_file.csv'; // Replace with the path to your CSV file

fs.writeFileSync(csvFilePath, csvData, 'utf8');

```



//Below codeConnect to the SAP HANA server using SFTP and upload the CSV file:



```javascript

const sftp = new Client();



sftp.connect(sftpConfig)

  .then(() => {

    return sftp.put(csvFilePath, '/path/to/server/location/filename.csv');

  })

  .then(() => {

    console.log('File uploaded successfully!');

    sftp.end(); // Close the SFTP connection

  })

  .catch((err) => {

    console.error('Error:', err.message);

    sftp.end(); // Close the SFTP connection

  });

```

.

4. . The execute command – npm start and the output will be available in the json format

 

user: demonodejs $ npm start

 

In the next section I will discuss about Java application that took data from HANA server.