Server file access from function on cloud

[Carson (Carson Ripple)]

I’m uploading a excel file to be processed by a function, but can’t find the correct server location on the cloud. Thanks to Hannah, the upload was no problem and I can see the file on the server using the server file download.
When I try to access the file using Erp.Internal.Lib.ExcelReader I get a file not found error. Guess and check hasn’t gotten me anywhere except I figured out the server folder is on the f drive and \ServerName\Epicordata gives a permission error.

Both the company path and user path come back empty when using Ice.Contracts.ServerPathSvcContract.

				CallService<Ice.Contracts.ServerPathSvcContract>(serverPathSvc =>
				{
					
					var userPath = ((Ice.Contracts.ServerPathSvcContract)serverPathSvc).GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, "", false);
					var companyPath = ((Ice.Contracts.ServerPathSvcContract)serverPathSvc).GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData, "", false);
				});

How To: Kinetic - Upload a Client File, Parse, and Display in a Grid

Another How To for how to use the File Picker Client control and file-erp-transfer widget. This tutorial will go over how to wire up the controls, pass the file to a function, parse the data, return the dataset to App Studio, and display the rows in a grid. Create a DataView holder [image] Create a grid and bind it to the new DataView from Step 1. [image] Add the File Picker Client (or server) control to your form and bind it to something. I used TransView because it’s runtime and I d…

[jkane (John Kane)]

You are not entering a sub-folder. There are probably no files in the User and Company directory.

[Carson (Carson Ripple)]

The files are in sub folders based on an ECO Group under both the company and user. (I’ve uploaded them both places and confirmed the files are there by downloading them)
I’ve tried:
F:\EpicorData\Companies{CompanyID}{ECOGroupID}{FIleName}
F:\EpicorData\Users{UserID}{ECOGroupID}{FIleName}

If I don’t put a drive letter in it defaults to the F drive and if I don’t put a path in at all it defaults to the inetpub server folder on the F drive.

[jkane (John Kane)]

You are on prem?

[Carson (Carson Ripple)]

No, in the cloud.

[klincecum (Kevin Lincecum)]

userPath[0].FullName

[jkane (John Kane)]

When I drop a file to the server using REST I denote the folder as an integer. I believe the Company folder is 5 and the user folder is 4. If I enter this using Ice.LIB.FileTransferSvc FileExists, it returns true.

{
  "folder": 4,
  "serverPath": "Import\\Testing.csv"
}

[Carson (Carson Ripple)]

I’ve kicked out the results of ServerPathSvc with include files set to true. I can see my files in both the user and company folder, but the FullName doesn’t include the full path.
It returns
“FullName”: “CWR\BOMGEN022404025MHS_Model.xls”
for both the User and Company folders. (The file is in both locations)

        CallService<Ice.Contracts.ServerPathSvcContract>(serverPathSvc =>
        {
          

          var userPath = ((Ice.Contracts.ServerPathSvcContract)serverPathSvc).GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, "", true);
          debug += Newtonsoft.Json.JsonConvert.SerializeObject(userPath, Newtonsoft.Json.Formatting.Indented) + System.Environment.NewLine;
          var companyPath = ((Ice.Contracts.ServerPathSvcContract)serverPathSvc).GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData, "", true);

          debug += Newtonsoft.Json.JsonConvert.SerializeObject(companyPath, Newtonsoft.Json.Formatting.Indented) + System.Environment.NewLine;

        });

[klincecum (Kevin Lincecum)]

@Carson

FullName doesn’t include the full path.

It’s not meant to, you are missing part of the process.

More like this:

CallService<FileTransferSvcContract>(fileTransfer =>
{
    byte[] dataBytes = fileTransfer.DownloadFile(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, $"{UserID}\\{ECOGroupID}\\{FileName}");
});

[Carson (Carson Ripple)]

I found the file it’s located at
F:\EpicorData\{TenantID}\{TenantID}\Companies\{CurrentComany}\{Folder}\{File}

(Yes two TenantID folders)

I had my mind set on finding the file since I was planning on using the ExcelReader.

				using (var excelReader = new Erp.Internal.Lib.ExcelReader(fullPath))
				{
					var workSheets = excelReader.WorksheetNames;
				}

I was able to read the sheet names using the ExcelReader, but I think I will switch over to the byte array and use DocumentFormat.OpenXml. Using the service to read the file seems much less likely to break than
F:\EpicorData\{TenantID}\{TenantID}\Companies\{CurrentComany}\{Folder}\{File}

[Carson (Carson Ripple)]

Thanks for everyone’s help. I successfully created function that reads an excel file that was uploaded to the server and returns the contents in a data set. This works in the cloud.

			#region code
			error = "";
			debug = "";
			data = new DataSet();
			var currentCompany = callContextClient.CurrentCompany;
			var currentUser = callContextClient.CurrentUserId;
			try

			{


				debug += "Start LoadExcelFile" + System.Environment.NewLine;
				byte[] fileBytes = null;

				CallService<Ice.Contracts.FileTransferSvcContract>(fileTransfer =>
				{

					if (userData)
					{
						debug += "Trying UserData" + System.Environment.NewLine;

						fileBytes = ((Ice.Contracts.FileTransferSvcContract)fileTransfer).DownloadFile(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, $"{currentUser}\\{folder}\\{fileName}");
					}
					else
					{
						debug += "Trying CompanyData" + System.Environment.NewLine;

						fileBytes = ((Ice.Contracts.FileTransferSvcContract)fileTransfer).DownloadFile(Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData, $"{folder}\\{fileName}");
					}
					debug += "File Loaded" + System.Environment.NewLine;

				});

				using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream(fileBytes))
				{

					using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(memoryStream, false))
					{

						DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
						foreach (DocumentFormat.OpenXml.Spreadsheet.Sheet sheet in workbookPart.Workbook.Sheets)
						{
							DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = (DocumentFormat.OpenXml.Packaging.WorksheetPart)workbookPart.GetPartById(sheet.Id);
							DataTable dataTable = new DataTable(sheet.Name);
							debug += $"Adding table {sheet.Name}" + System.Environment.NewLine;

							DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();

							bool firstRow = true;
							foreach (DocumentFormat.OpenXml.Spreadsheet.Row row in sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>())
							{

								DataRow dataRow = dataTable.NewRow();
								int columnIndex = 0;

								foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
								{

									string cellValue = cell.InnerText;
									if (cell.DataType != null && cell.DataType.Value == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
									{
										cellValue = workbookPart.SharedStringTablePart.SharedStringTable.Elements<DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>().ElementAt(int.Parse(cellValue)).InnerText;
									}


									if (firstRow)
									{
										dataTable.Columns.Add(cellValue);
									}
									else
									{
										dataRow[columnIndex] = cellValue;
									}

									columnIndex++;
								}

								if (!firstRow)
								{
									dataTable.Rows.Add(dataRow);
								}
								firstRow = false;
							}

							data.Tables.Add(dataTable);
						}
					}
				}


			}
			catch (Exception ex)
			{
				error = ex.ToString();
				debug += Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented) + System.Environment.NewLine;
			}
			#endregion code

The code editor does throw an error, but if you ignore it everything works.

[josephmoeller]

@Carson

var userPath = ((Ice.Contracts.ServerPathSvcContract)serverPathSvc).GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, "", false);

@klincecum

userPath[0].FullName

For what it’s worth, at and prior to 2023.2.11, this was a viable method of obtaining the server absolute path to the user directory:

List<Ice.Lib.ServerPath.Types.PathInfo> pathInfoList;
string userFolder = String.Empty;
this.CallService<Ice.Contracts.ServerPathSvcContract>(hServerPath => {
  pathInfoList = hServerPath.GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.UserData, "", false);
  userFolder = pathInfoList[0].FullName;
});

This worked because passing false as the third method parameter said - I don’t want the file names, just the directory which returned a collection where the first element represented the parent directory.
However, this logic was changed somewhere between 2023.2.11 and 2024.2.7 to now return an empty string only. You can still obtain the same absolute path using this lib helper:

string userFolder = Ice.Lib.PathHelper.GetFolderPath(Epicor.ServiceModel.Utilities.SpecialFolder.UserData);

As @klincecum mentioned, you probably don’t need this pathway directly just for uploading / downloading files unless you are using a separate embedded program files that require it, such as Aspose PDF. I wanted to include this information for completeness when people search to this post and find a reference to ServerPathSvcContract for the purposes mentioned.