Question
Accenture
IN
Last activity: 6 Jan 2025 13:16 EST
Upload Excel file and parse only 5000 rows and shows validation
we have a requirement Upload Excel file and parse only 5000 rows .If the excel sheet has more than 5000 rows need to show validation. Could anyone help on this
Accepted Solution
Updated: 6 Jan 2025 13:16 EST
Vibrantech
US
Assuming the file is being uploaded from a section, insert a 'FilePath' control with .pyFilename property for abutton to upload the file. Now, you can run an activity (below) to parse it
On an upload button with a click action set - refresh this section, Create an activity that first runs OOTB activity pxParseExcelFile on a step page. The parameters for this take FSFileName and TemplateRFB. For FSFileName if you can likely use pxRequestor.pyFileName and for TemplateRFB use the template you created for the excel file.
If the step page is 'ExcelData', then loop through each embedded page in ExcelData.pxResults
in the loop, check if Param.pyForEachCount >5000 (this checks which iteration of the loop you are in, i.e. which row).
if so, you can exit the iteration and go to a validation step.
Otherwise, continue through iteration and property-set each property you need to an existing pagelist property, for example you have a pagelist property "MappedData"
Primary.MappedData(Param.pyForEachCount).PropertyName to ExcelData.pxResults(<CURRENT>).PropertyName
HCA Healthcare
US
@AjayKumarK7664 You can upload an Excel file and check its row count using Python. First, load the file with the pandas library using pd.read_excel. Count the total rows using len(df). If the row count exceeds 5000, display an error message saying the file exceeds the limit. If it has 5000 rows or fewer, you can proceed to parse the data using df.head(5000). Return a success message indicating how many rows were processed. If any error occurs during the upload or parsing, catch the exception and display the error message. This approach ensures only up to 5000 rows are processed, preventing system overload with larger files.
Maantic
US
@AjayKumarK7664 You can use pxParseExcel activity to parse the excel placed in the respective server path. You have to pass two mandatory parameters i.e. fileName with complete path of the file and TemplateRFB i.e. the binary file key to the above said activity. The output that you are going to get will be a pagelist of all the records present in the excel. Then you can use function LengthOfPageList(<Pagelist Name>) to get the total number of rows present and then apply the validation using Obj-Validate and display error message.