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-hana-db-artifacts-in-hana-xsa/

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.