Import data into R2 through file stored in SFTP

Content was last updated in 06.23.01-00

Revision History

A meticulously create nomenclature is what builds a robust and business-friendly database.

Unless the list of Categories and Sub-Categories of items is curated wisely, it might becomes a taxing issue to manage the inventory.

Since while adding a new inventory item or while searching an existing one, as a user you might not know where to look for them.

We have provided a seamless methodology to import Categories, Subcategories, Products, and Packages information in R2.

This can be done through a batch file. For each of the above type, there will be an individual batch file.

• The same file can be used to perform any of the — Create, Update, and/or Delete functions.

• In both cases, it reads from from a .CSV file stored at a specific path, which is mentioned in the batch file itself.

• This batch file read the .CSV file in the sorted order of created date from oldest to newest, which is determined by its file-name.

• Thus the .CSV files follow a specific file naming convention. And that is how the system determines, if the .CSV file is for Category or Sub-Category.

• The ARC_<Type>_yyyymmdd_HHMMSS.csv is used for Categories. See Sample format here.

• Additionally it has a new column (binary value) used to delete a Category. The default value of this field is NO.

• Additionally it has a new column (binary value) used to delete a Sub-Category. The default value of this field is NO.

• The yyyymmdd_HHMMSS refers to the Date & time stamp at which the file was created. This way, two files created one after the other, will not over-write each other, and be saved separately.

• This batch file will read files from the mentioned folder in the sorted order of created date from oldest to newest.

• If the .CSV file is processed without any errors/warnings:

• It is moved to the 'Success' folder under path mentioned in the batch file. For Example: /upload/<Type>Feed/

• The log file generated is moved to the 'Logs' folder in the same folder hierarchy.

• If the .CSV file  File encounters any errors/warnings:

• It is moved to the 'Failure' folder under path mentioned in the batch file. For Example: /upload/<Type>Feed/

• The log file generated is moved to the 'Logs' folder in the same folder hierarchy.

The batch files can be scheduled to be run every X hour in Windows based server that has access to the respective R2 Database and the .csv file path that it reads from. The folders where the output files would get saved, must be write-access enabled, so that system allows to save files and logs created during the above process.