How to upload a CSV to get Records back?

Forum > Integration & Database > How to upload a CSV to get Records back?

Lovro

postedon May 27, 2013at 5:48 PM

Dear all!

I have a CSV I wanna upload in a process (as the Appway Studio can do). But I don't want the user to go into the Studio. How can I upload this?

I found the function "LOADFROMCSV('Record', $uploadFile.filePath, ',')", but it does not recorgnize 'Record' as dataclass.

Any workarounds, except creating an own dataclass and converting this dataclass to records later?

Thx for your help!!
Lovro

Lovro

repliedon May 28, 2013at 9:31 AM

The idea of the hole exercies is that the user can upload a new CSV without using the Studio. Also should it be possible to add new CSV to your Appway instance without Studio access. That's why we are making this complicated way.
I do see the point with the dynamic CSV source. But if I understand this correctly you would need a master PC where the CSV are stored, right?

For the columns, I realized the problem myself at another point, when updating the catalog with records. My workaround was to get the key set and run a ForEach over it. See Code below. I donno how LOADFROMCSV looks like, but maybe this can help.
But for this project in particular it would be enough, if the set of columns is always the same.

1
2Function CatalogueUpdateWithRecord(Record $rule, String $catalog) : Nothing Begin
3   // Compile List of Attributes
4   Indexed String $index := ToIndexed($rule.getValues().keySet(), String);
5   //
6   // Prepare NEW VALUES
7   //
8   String $id := TOSTRING($rule.getValue('id'));
9   Named String $newValues := new Named String;
10   // Copy entries
11   String $s := new String;
12   ForEach $s In $index Do
13      If $s != 'id' Then
14         $newValues[$s] := TOSTRING($rule.getValue($s));
15      End
16   End
17   //
18   // Write to Catalog
19   //
20   CatalogueUpdate($catalog, $id, $newValues);
21End

n/A

repliedon May 28, 2013at 10:55 AM

If something like this is needed I would rather implement this function in Java. The script function you wrote is not 100% safe as you do things like:

1Indexed String $index := ToIndexed($rule.getValues().keySet(), String);

Which can lead to issues if we update the Record class in the Appway core.
Also I'm not sure what are you using this update function for, but if you call CatalogueUpdate you will create a new uncommitted version for each update you do, so if you call this update in a loop where you update 100 entries, you end up with 100 version of that catalog object. What I normally would do for bulk updates is using a function which takes care of updating the whole BO and calling the "update" API only once (in Java).

In any case I'm not sure I understand what you are trying to achieve here. I mean you want to get back Records from a CSV to update a catalog? What is the high level goal of your development effort? There might be a better/easier solution for this.

Stephan

repliedon May 28, 2013at 11:28 AM

Lovro,

Lovro wrote:
> We would like to upload a CSV and save it as catalog.

Do you want to create a new catalog for every uploaded CSV file, or do you want to update the content of an existing catalog every time a CSV file is uploaded? And does the CSV file structure (columns, separator, quote character, encoding etc.) change from upload to upload?

Lovro wrote:
> This should be done in the UI.
> [...]
> But if I understand this correctly you would need
> a master PC where the CSV are stored, right?

No, you can implement file uploads in the workspace and store the uploaded files somewhere on the server. And the catalog's CSV File source could then point to this file. Do you know how to make file uploads in Appway screens?

Lovro

repliedon May 28, 2013at 1:41 PM

Do you want to create a new catalog for every uploaded CSV file, or do you want to update the content of an existing catalog every time a CSV file is uploaded? And does the CSV file structure (columns, separator, quote character, encoding etc.) change from upload to upload?

The structure stays the same. And for the Demo we only need to change an existing catalog.

No, you can implement file uploads in the workspace and store the uploaded files somewhere on the server. And the catalog's CSV File source could then point to this file. Do you know how to make file uploads in Appway screens?

This sounds like a good possibility. I saw the component in the Screen editor but I never really used the files later on. Do you think you can quickly give me a call or skype on that.

I add to Stephan's question: and can multiple users update the catalog at the same time, or is it more something like an admin flow where an admin updates the catalog using a CSV?

It is more like a admin feature you would execute few times a year.

n/A

repliedon May 28, 2013at 3:05 PM

Ok, so it is a demo. I think a good solution can be to use a catalog with a CSV file source and then just upload the file in the same path as selected in the catalog source.

From the screen documentation:

5.1.4 File Upload
A widget allowing the user to specify and upload a file from his computer.
Size

Horizontal size of the text field inside the File Upload widget
Mime-Type Filter

Filter specifying which file types are accepted (examples: "application/pdf", "text/*")
Table 31: File Upload properties
Additional Properties for "File Upload": Style, Converter, Validators.
If a File Upload Component is present on the Screen, the HTML form's encType attribute is automatically set to "multipart/form-data".
If a file is selected for upload, a new directory under $DATA_HOME/work/tmp is created. The content of the file is stored in this directory as "file.dat" and additional information like content type and original file name is stored in a Java Properties file called "file.dat.properties" in the same directory. The absolute path to "file.dat" is stored in the Data Property bound to this Component.
Files within the directory $DATA_HOME/work/tmp might get deleted automatically. By default, they exist for at least 60 minutes. Use a "Script Action" to exectue additional tasks like reading the file's content or copying it to a different location.
There is no restriction on the size of the file being uploaded.
here's an example for a File Upload Component bound to a variable named $uploadedFile. This Script moves the uploaded file to a new location within the file system.

1Script
2   If $uploadedFile != null Then
3      If FILEEXISTS($uploadedFile) Then
4         Local Named String $properties;
5         $properties := READPROPERTIESFILE(JOIN($uploadedFile, '.properties'));
6         Local String $imagepath;
7         $imagepath := JOIN('uploadedpictures/', UID(), '_', FILENAME($properties['fileName']));
8         COPYFILE($uploadedFile, $imagepath);
9      End
10   End
11End

Once your file is uploaded, the COPYFILE function can move it in a folder which is set to be your dynamic catalog source.

The only issue here is that, if for demo purposes then you want to show a "Static" catalog with the updated values, then you can't achieve you goal with this "trick". I mean what we are doing here is using a CSV as a source of information, while it seems to me that you really want to use a Static catalog as a source of information (so for example you can transfer the properties from environment to environment).

If that is the case, and if that is just a demo, I think you can easily use the CatalogUpdate feature, but I don't think you will ever need to translate the CSV into Records...I mean you are loading, translating to Records and then getting an Indexed of String out of the list of records to fill the catalog? Why would you do that, just load the CSV with READTEXTFILE and then SPLIT it in an Indexed String that can be used as an input in the CatalogUpdate function.

n/A

repliedon May 28, 2013at 3:51 PM

I meant Named String not Indexed.
Here is an example:

1// read the file
2String $file := READTEXTFILE('{DATA_HOME}/work/test.csv');
3// transform the string in a collection of String rowas
4Indexed String $rows := SPLIT($file, '\r\n');
5// set and remove header using the first row
6Indexed String $headers := SPLIT($rows[1], ';');
7$rows.removeElementAt(1);
8// looping variables
9Named String $record;
10Integer $index;
11// iterate over all the rows
12ForEach $row In $rows Do
13   $index := 1;
14   // create a record for each row
15   $record := new Named String;
16   // iterate over all the columns and fill the records
17   ForEach $cell In SPLIT($row, ';') Do
18      $record[$headers[$index]] := $cell;
19      $index := $index + 1;
20   End
21   PRINTLN($record);
22   // update each record
23   CatalogueUpdate('Test', $record['id'], $record);
24End
25// optionally commit the catalog to get rid of all the versions

And here is the sample CSV:

1id;description;sorter;test
21;individual;2;aaa
32;joint;1;bbb
43;company;3;ccc

Disclaimer: use it only for demo purposes as it will generate many versions!

Please sign in to add a reply