# Import data from a local Excel file into Kinetic Grid

Kinetic ERP
1

I have seen and used excel import In the classic version, Microsoft Office Interop ,file-reading methods and SSIS. Regrettably, the Kinetic grids and screens do not offer similar functionality. Although there are options for paste insert/update, these do not support the efficient loading of a high volume of records. I have seen most of ERP applications and accounting software allows users to import data directly from Excel within the same login environment.
I have tried a method mentioned in the community, since we are on SAAS, file import is restricted.

 foreach (string file in csvFiles)
  {
    string fileName = System.IO.Path.GetFileName(file);
    // Do something with the file name or path
     
    System.IO.StreamReader newSR = new System.IO.StreamReader(Sourcepath2);
    {
      InfoMessage.Publish("2");
        string ALLline = newSR.ReadToEnd();
        string[] SFTPSORows = ALLline.Split('\n');
        
        SFTPSORows = SFTPSORows.Where(r=> !string.IsNullOrEmpty(r)).ToArray();

Error Details :

Message: System.UnauthorizedAccessException: Access to the path ‘C:\epi\storage\epicordata<sitecode>\Companies’ is denied.

Has anyone been able to effectively achieve this through Kinetic? I would appreciate any insights or experiences you might be able to share

1 Like
2

I’ve got some meetings today, but If I get a minute I’ll help you out.

Some of the other peeps may have a minute to help before then.

1 Like
3

Is this your normal EpicorData directory? It looks like a permissions issue, but if that’s your normal EpicorData directory, the Epicor Server User should definitely have access there. I’ve worked with bringing in Excel data into functions many times, and haven’t had any issues. Bringing the data directly into a grid is different, though.

One thing I’ve worked with before is using one of the UDXX tables as a temporary table for the grid: When the form is loaded, trigger a function to load the data into UDXX, then load the grid from that UDXX table. Then remove the rows from the UDXX table with another function when you’re ready.

4

This is server directory, where we download files in SAAS, Server File Download

5

What about using DMT.
It seems to have most of the tables that you would want to load.

6

DMT isn’t user-friendly and doesn’t seem like a viable option for end users. I often wonder why it’s not integrated into the Epicor UI

7

Hi,

I’ve submitted it as an idea in the Kinetic portal. Feel free to vote if you think it’s a good one.
https://epicor.ideas.aha.io/ideas/KIN-I-5481

8

I imagine that it’s because most business objects don’t require a large amount of a data import as part of daily operation. What is the business case that we’re trying to solve? Implementation tasks? Financial transactions?

1 Like
9

Our company specializes in manufacturing, with over 400 custom jobs generated monthly from tailored screens featuring distinct parameters derived from engineering drawings. Various departments utilize these custom screens to:

•⁠ Quick job creation
•⁠ ⁠Update raw material quantities
•⁠ ⁠Release and time and expenses in bulk

Similar bulk upload processes are applied to:

•⁠ ⁠Expense entries
•⁠ ⁠Accounts Payable (AP) invoices with multiple line items

10

It’s not for the Epicor Data Directory as stated by @kve, which is the same place you get to with Server File Download.

This is a relatively common request and has been solved a few times.
I will go ahead and link you to a couple threads to give you some ideas.

Now keep in mind, some of this will be changing soon, with some changes planned with System.IO, but other than a few minor changes when that happens, the process should be the same.

If you want to explain some more details on what exactly you would like done, I may can help further.

11

If they want to push the data in through the UI, definitely check out the links that Kevin posted above.

If they’re open to a different way than they always did it, and the company is heavily invested in Excel, you could also call REST services from Office Script and skip the intermediate file transfer altogether. Instead of batching things up, they might be able to post as they go.

3 Likes
12

DMT was not designed to be an end-user tool. Someone with access to DMT can upload any “SQL-valid” data into any field in the tables to which they have access, and end-users are not generally trained in how to handle bulk errors.

3 Likes
13
  1. I have already tried the first option and it doesnt works as we are on Epicor SAAS,I raised a ticket and Epicor support confirm that this path is inaccessible.
  2. This link well defines the uploading process in classic, but we already achieve this through Hannah’s above method.
    We are trying to upload an excel file to following path and load into grid *C:\epi\storage\epicordata\1000{sitecode}\Companies\1000{companycode}\Excel* ( this location is accessible through server file upload)

we are able to upload the file and failed to load in Kinetic grid

14

Yeah they don’t know what they are doing.

2 Likes
15

@iarunkumark perhaps it’s time to review how you are getting the data in, there are solutions that divide a conquer to negate the need for bulk importing, such as integrating with expensify, or using ECM for AP invoices… Or Epivor time and Expense mobile.

You know you business best, so I might be barking up the wrong tree there.

1 Like
16

This will work for now until they change it:

Code:

  var baseFolderType = Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData;
  var companyDataPath   = Ice.Lib.PathHelper.GetFolderPath(baseFolderType); 
  
  
  //Assuming you know the file names inside CompanyData
  List<string> csvFiles = new List<string>(){ "test1.csv", "test2.csv" };

  List<string> demoLines = new List<string>();

  foreach (string file in csvFiles)
  {
    string fullFilePath = System.IO.Path.Join(companyDataPath, file);
    
    List<string> allLines = System.IO.File.ReadAllLines(fullFilePath).ToList();
    
    //Put it back together for demo
    demoLines.AddRange(allLines.Take(4).Select(x => String.Join(" ", x.Split(",").ToList())));
  }

 //4 lines from each csv
 output = String.Join(Environment.NewLine, demoLines);

Output:

{
  "output": "\r\nTake me down to the Paradise City\r\nWhere the grass is green and the girls are pretty\r\nTake me home (I want you  please  take me home)\r\n\r\nCaptain America's been torn apart now\r\nHe's a court jester with a broken heart\r\nHe said  \"Turn me around and take me back to the start\""
}
2 Likes
17

The source of those two files:

string pc1 = @"
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home (I want you, please, take me home)
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home (I want you, please, take me home)
Just an urchin living under the street
I'm a hard case that's tough to beat
I'm your charity case so buy me somethin' to eat
I'll pay you at another time
Take it to the end of the line
Rags and riches, or so they say, you gotta
Keep pushing for the fortune and fame
You know it's, it's all a gamble when it's just a game
You treat it like a capital crime
Everybody's doing their time
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Oh, won't you please take me home? Yeah-yeah
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home
Strapped in the chair of the city's gas chamber
Why I'm here, I can't quite remember
The surgeon general says it's hazardous to breathe
I'd have another cigarette but I can't see
Tell me, who you're gonna believe?
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home, yeah-yeah
Take me down to the Paradise City
Where the grass is green and the girls are pretty
I want you, please, take me home
Yeah
So far away
So far away
So far away
So far away";

string pc2 = @"
Captain America's been torn apart now
He's a court jester with a broken heart
He said, ""Turn me around and take me back to the start""
I must be losin' my mind, ""Are you blind?""
""I've seen it all a million times""
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home, yeah-yeah
Take me down to the Paradise City
Where the grass is green and the girls are pretty
I want you, please, take me home
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home, yeah-yeah
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Oh, won't you please take me home?
Home
I wanna go, I wanna know
I want you, please, take me home
I wanna see how good it can be
I want you, please, take me home
Take me down to the Paradise City
Where the grass is green and the girls are pretty
Take me home (I want you, please, take me home)
Take me down to the Paradise City
Where the grass is green and the girls are pretty
I want you, please, take me home
Take me down, ooh yeah, spin me 'round
Oh, won't you please take me home?
I wanna see how good it can be
I want you, please, take me home
I wanna see how good it can be
Oh, oh, take me home
Take me down to the Paradise City
Where the grass is green and the girls are pretty
I want you, please, take me home (I want you, I want you take me home)
I wanna go (I wanna), I wanna know (I wanna)
I want you, please, take me home
Baby, yeah";
3 Likes
18

Also :dumpster_fire: …

@iarunkumark ← when I read that, I read @iamrunamok :rofl:

2 Likes
19

Hi, it is isn’t working, we are getting same error as before

image
image847×314 57.6 KB

20

Looks like something might be wrong with your environment.

Try userdata and see if you run into the same.