Data Transforms

Bulk edit the dataset before pushing it to your system.

The Data Transforms feature in CSVbox empowers you to modify and manipulate the data before it is uploaded to your app. Using JavaScript, you can apply custom transformations to reshape, sanitize, or enhance your data in real-time. Whether you need to perform simple tasks like capitalizing text or formatting dates, or handle complex business logic, Data Transforms gives you full control to customize your dataset to meet your app's specific needs.

Data Trasforms

How it works

When a CSV file is uploaded, CSVbox parses the data and applies your transformation logic row-by-row or column cell-by-cell. The JavaScript function you write defines how each row or field should be transformed. Once the transformation is complete, the modified data is passed to the next stage for validation. There are two main types of Data Transforms available in CSVbox: Row Transforms and Column Transforms.

• Row Transforms apply transformation logic row-by-row, processing each row individually from top to bottom. These are especially useful when the transformation of one cell depends on the values of other cells within the same row. For instance, if you need to combine data from multiple cells into a single cell, or if a calculation requires input from different columns within the same row, Row Transforms are ideal. This approach allows you to apply logic dynamically based on relationships within each row of data.

• Column Transforms operate on a single column or a set of selected columns, applying transformations to all entries in the specified columns at once. These transforms are most effective when you need to analyze or modify data across the entire column rather than row-by-row. For example, if you're looking to identify duplicate entries in a column and replace each duplicate with a unique identifier, a Column Transform can help by analyzing all values in that column collectively before applying changes. This approach is efficient for transformations that rely on examining the column as a whole, such as sorting, aggregating, or deduplication tasks.

Using these two transformation types, you can precisely target and manipulate your data based on your application's requirements, whether you need to perform intra-row calculations or make adjustments to column-wide data sets.

Selecting Column Transforms can impact importer performance, as the entire column dataset is loaded into memory for processing. This means that, especially with large datasets, using Column Transforms may slow down the import process. For optimal performance, consider using Row Transforms for operations that do not require analyzing the entire column, reserving Column Transforms for cases where column-wide data processing is essential, such as deduplication or aggregation tasks.

Row Transforms

Adding Row Data Transforms

1. Go to the edit sheet page > Data Transforms tab > Click Add Transforms button.

2. Add Transform Name.

3. Select Transform Type as Row.

4. Provide Javascript code.

5. Attach Dependent Libraries (optional).

6. Click Save.

Row Transform Examples

Append text

Append a constant to the cell value.

csvbox.row["serial_number"] = csvbox.row["serial_number"] + '_' + csvbox.user["user_id"];
  
  return csvbox;

Normalize dates

Normalize date value into US format.

function normalizeToUSFormat(dateString) {
  // Create a new Date object by parsing the input date string
  const date = new Date(dateString);

  // Check if the Date object is valid
  if (isNaN(date.getTime())) {  
    console.log("Invalid date format.");
    return dateString;
  }

  // Get month, day, and year
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  const year = date.getFullYear();

  // Return the date in MM/DD/YYYY format
  return `${month}/${day}/${year}`;
}

// Example usage
  csvbox.row["date_of_birth"] = normalizeToUSFormat(csvbox.row["date_of_birth"]);
 
  return csvbox;

Variables in the Row Transform

You have access to data variables included in the csvbox object. The following data is available:

csvbox.row

It contains row data. Each cell in the row can be accessed by providing the column name. Examples:

csvbox.row["first_name"]
csvbox.row["order_id"]
csvbox.row["price"]

csvbox.user

It contains the custom user attributes defined while initializing the importer. Examples:

csvbox.user["user_id"]
csvbox.user["team_id"]
csvbox.user["isAuthenticated"]

csvbox.import

This refers to the current import-specific data. The following data is available:

csvbox.import["sheet_id"]
csvbox.import["sheet_name"]
csvbox.import["original_filename"]
csvbox.import["import_start_time"]
csvbox.import["destination_type"]
csvbox.import["total_rows"]
csvbox.import["row_number"] //current row number starting with 1

csvbox.columns

This object contains the column metadata (name, type).

//Example usage
let column = csvbox.columns['birthdate'];

if(column.type == 'date') {

   // code  
   
}

if(column.isDynamic) {
   
   // this is a dynamic column
   // code  
   
}

if(column.isUnmapped) {
   
   // this is a unmapped column
   // code  
   
}

csvbox.environment

It contains the environment variables that are passed during importer initialization.

// Example Usage
if(csvbox.environment["user_id"] && csvbox.environment["user_id"] == "abc123") {
 
 // code
 
}

console.log(csvbox);

With this statement, you can print all the available variables in the debugging console.

Column Transforms

Adding Column Data Transforms

1. Go to the edit sheet page > Data Transforms tab > Click Add Transforms button.

2. Add Transform Name.

3. Add the Columns you need for the transform.

4. Provide Javascript code.

5. Attach Dependent Libraries (optional).

6. Click Save.

Column Transform Examples

Example 1

Capitalizing Text Fields:

//loop and capitalize each value

for(let i=0; i < csvbox.column["first_name"].length; i++)
{
    csvbox.column["first_name"][i] = csvbox.column["first_name"][i].toUpperCase();
}

// return the updated data set
return csvbox;

Variables in the Column Transform

You have access to data variables included in the csvbox object. The following data is available:

csvbox.column

It contains the entire column data. Each cell in the column can be accessed by providing the column name and the row number. The row number starts with 1. Examples:

csvbox.column["first_name"][1]
csvbox.column["order_id"][12]
csvbox.column["price"][10001]

Only the selected columns will be available in the Transform javascript.

csvbox.user

It contains the custom user attributes defined while initializing the importer. Examples:

csvbox.user["user_id"]
csvbox.user["team_id"]
csvbox.user["isAuthenticated"]

csvbox.import

This refers to the current import-specific data. The following data is available:

csvbox.import["sheet_id"]
csvbox.import["sheet_name"]
csvbox.import["original_filename"]
csvbox.import["import_start_time"]
csvbox.import["destination_type"]
csvbox.import["total_rows"]

csvbox.environment

It contains the environment variables that are passed during importer initialization.

// Example Usage
if(csvbox.environment["user_id"] && csvbox.environment["user_id"] == "abc123") {
 
 // code
 
}

csvbox.columns

This object contains the column metadata (name, type).

//Example usage
let column = csvbox.columns['birthdate'];

if(column.type == 'date') {

   // code  
   
}

if(column.isDynamic) {
   
   // this is a dynamic column
   // code  
   
}

if(column.isUnmapped) {
   
   // this is a unmapped column
   // code  
   
}

console.log(csvbox);

With this statement, you can print all the available variables in the debugging console.

Dependencies

In the Transforms Javascript snippet, you can utilize an external library hosted via a CDN. You can also call any external API endpoint to fetch real-time data. Add the library script tag and/or any custom scripts to the 'Dependencies' section and use it in the main Javascript snippet.

Key Features

• JavaScript-Powered: Write custom JavaScript code to perform operations on your data. With access to native JavaScript methods, you can implement transformations ranging from basic modifications to complex logic.

• Real-Time Execution: Your transformations are applied as the file is uploaded, ensuring that the data is processed and adjusted before it enters your app.

• Versatile: Use Data Transforms to handle a wide range of operations, such as:

  • Modifying string data (e.g., capitalizing text, trimming spaces)

  • Formatting dates

  • Performing calculations, such as currency conversions

  • Validating and cleaning up data

  • Applying conditional logic to data fields

• Error Handling: You can include custom error handling in your JavaScript to manage issues gracefully without disrupting the upload process.

Best Practices

• Data Validation: Use Data Transforms to validate incoming data and correct common issues, like missing or incorrectly formatted fields.

• Error Handling: Incorporate error-handling logic into your JavaScript code to avoid disruptions in the upload process due to malformed data.

• Optimize Performance: Keep your transformation code efficient, especially when working with large datasets, to ensure smooth uploads.

Conclusion

With Data Transforms, you have the flexibility to clean, format, and manipulate data in real-time as it's uploaded through CSVbox. Whether you need to perform simple formatting tasks or implement complex business logic, Data Transforms can help you streamline your data handling processes and ensure that your datasets are always consistent and accurate.