[Jiaz]

Originally Posted by StefanM

Guess, the 'json-module' in Excel is pretty old, as it is also extremely slow...

see limitation here, support.microsoft.com/en-us/office/power-query-specifications-and-limits-in-excel-5fb2807c-1b16-4257-aa5b-6793f051a9f4
One *workaround* woud be to split the exported config into max 1000 rows per file
then you can import them one by one. Issue might also happen when the json object changes and new columns show up, see
stackoverflow.com/questions/50230358/llimitation-while-converting-big-json-into-csv-in-power-query-editor

[mgpai]

Originally Posted by Jiaz

instead write out the whole json at once, loop/interate through the entries and write out
the information in CSV format, make sure to JSON.stringify the value

I only wish it were that simple. The db uses the following keys, but not all keys are used in all objects. The number of keys used in an object varies between 4 and 10.

[
 "abstractType",
 "defaultValue",
 "docs",
 "enumLabel",
 "enumOptions",
 "interfaceName",
 "key",
 "storage",
 "type",
 "value"
]

While it is possible, it will take more than a simple iteration. The 'tsv' file is ultimately intended for use in excel, which is better equipped to handle db structure like this one. When imported correctly, it will contain a table with above 10 keys as column headers, with rows empty or filled with data, depending on whether it is present in the original object.

Originally Posted by StefanM

Of course, just wanted to share this.
As this was completely new to me: limitations of only 1,000 rows, while on the other hand Excel can handle sheets with 1,048,576 rows and 16,384 columns...

Guess, the 'json-module' in Excel is pretty old, as it is also extremely slow...

Check the page linked by Jiaz. It is a navigation items limitation which will be applicable to all db types, not only JSON. From what I understand, by default it will be limited to the number of column headers (keys) in the database or 1000, whichever is less, with the option of manually adding more as required.

In this case it should be equal to 10, not 1000, which is the number of keys listed above.

[StefanM]

Originally Posted by Jiaz

see limitation here, ...

@Jiaz

Funny thing is, that I see all 1,178 rows. But I don't know, whether or not everything is complete.

Another things that puzzles me is, that every time I e.g. filter a column, and then reset all filters, Excel will only load (once again) only a small part of those 1,178 rows and I have to scroll down several times, until the last row has been reached once again.

And all of this is slow: I takes a couple of seconds.

And I also cannot adjust the column widths to any size I want. The last column, I can only set to some 30 or 50 % of the width needed.


I know, all this is kind of off topic, but maybe you are familiar with handling *.json files with Excel. And you can answer this anyway:

As I'm always curious and eager to learn new things (in this case handling *.json files and Excel)...

How do I copy/save those rows and columns from the attached screenshot to a 'normal' Excel sheet?
Any other opened Excel window is blocked, as long as the attached window is open.

What I did, was copy it to another Excel sheet on another PC, connected via Remote Desktop. And also copying it to a txt file and later back to Excel would work.

But I guess there must be another way.

EDIT

Got 'Close & Load' finally working, but the result is not correct. Because now I have 2,618 rows instead of 1,178. Maybe because of that 1,000 rows limitation...

[StefanM]

Originally Posted by mgpai

Check the page linked by Jiaz. It is a navigation items limitation which will be applicable to all db types, not only JSON. From what I understand, by default it will be limited to the number of column headers (keys) in the database or 1000, whichever is less, with the option of manually adding more as required.

In this case it should be equal to 10, not 1000, which is the number of keys listed above.

@mpgai:
Well, for me it's the first time that I got that warning. Never saw it before with other databases.

If you read my post addressed to Jiaz, you will see some other 'peculiar' behavior of Excel I experienced here.

Anyway, do you think you can find a way to create a tsv file? Or is it that complicated or even impossible, that you have to give up?

[mgpai]

Originally Posted by StefanM

Or is it that complicated or even impossible, that you have to give up?

Really? That is what you took from what I wrote?

[StefanM]

Originally Posted by mgpai

Really? That is what you took from what I wrote?

@mgpai:
Please accept my apologies!

I guess, this is what you are referring to?

Originally Posted by mgpai

While it is possible, it will take more than a simple iteration. The 'tsv' file is ultimately intended for use in excel, which is better equipped to handle db structure like this one. When imported correctly, it will contain a table with above 10 keys as column headers, with rows empty or filled with data, depending on whether it is present in the original object.

But I don't know how to interpret it:

• More than a simple iteration -> I understand it is more complicated
• 'tsv' file ultimately intended for use in Excel -> Yes, when I could use a tsv file, in particular there wouldn't be all those problems I mentioned here.

But I still don't know, whether or not you are prepared to create a tsv generating script.

And I have no idea, how much time that would take, and if it would be OK or if it even would be impudent or outrageous to ask for such a script.

Once again, I apologize, in case my reaction sounded kind of rude, which for sure wasn't my intention.

But if you decide to create that tsv script (with those 10 columns) I can only say once again thanks a lot for your efforts!

[mgpai]

Originally Posted by StefanM

Please accept my apologies!

No worries.

Originally Posted by StefanM

But I still don't know, whether or not you are prepared to create a tsv generating script.

The JSON file which the scirpt provides should have been enough for you to see things through in excel, especially since it is very well equipped to perform such tasks. So the question really is not "whether I am prepared to" or "whether I can", but "do I need to". Since I have provided a viable solution, the answer should have been obvious. That being said, here is the script:

/*
    Export settings
    Trigger : none
*/    

var rows = [];
var file = JD_HOME + "/settings.tsv";
var settings = callAPI("config", "list", ".+", true, true, true, true);
var keys = [
    "abstractType",
    "defaultValue",
    "docs",
    "enumLabel",
    "enumOptions",
    "interfaceName",
    "key",
    "storage",
    "type",
    "value"
];

rows.push(keys.join("\t"));

settings.forEach(function(item) {
    var row = [];

    keys.forEach(function(key) {
        row.push(JSON.stringify(item[key]));
    })

    rows.push(row.join("\t"));
})

writeFile(file, rows.join("\n"), false);

Let me know if you need any modifications.

[StefanM]

Originally Posted by mgpai

...That being said, here is the script:

Haven't started testing, yet. But nevertheless already now I have to say:

Thank you so much for your time and efforts. I really appreciate it.
Will get back to you with my report later.

Will also try to verify the result/columns by comparing the Excel-result (importing the *json file to Excel that is) with the result of the script you just sent me.

Once again: Thank you very much.

[StefanM]

Originally Posted by mgpai

Let me know if you need any modifications.

Now, here are my test results:

While the *.json export misses some information, the *.tsv export seems to be complete.

Examples (first line is from tsv file, second line is from json file)

["-i","%audio","-vn","-f","ogg","-acodec","libvorbis","-aq","4","%out","-y"]
[List]

{"mp4":"mp4"}
[Record]

If I may ask for a minor modification:
Would it be possible to change the name of the output (tsv) file to something like this?
2022-06-20-11-35-10.tsv
YYYY-MM-DD-hh-mm-ss.tsv

And a far more complex request would be the possibility to restore the settings from the saved tsv file, writing them back to JD that is.

But I'm afraid that this would probably really be too much work load.
But if I'm wrong and it's not that complicated, I thought I might as well ask...

And once more: Thank you very much for what you already did.
It's a great and very useful script!

[mgpai]

Originally Posted by StefanM

... change the name of the output

var file = JD_HOME + "/" + new Date().toISOString().substr(0, 19).replace(/[^\d]/g, "-") + ".tsv";

Originally Posted by StefanM

.. restore the settings from the saved tsv file, writing them back to JD that is.

We'll see. No ETA though.

Originally Posted by StefanM

Thank you ..

You're welcome.

[Jiaz]

Originally Posted by StefanM

While the *.json export misses some information, the *.tsv export seems to be complete.
Examples (first line is from tsv file, second line is from json file)

["-i","%audio","-vn","-f","ogg","-acodec","libvorbis","-aq","4","%out","-y"]
[List]

{"mp4":"mp4"}
[Record]

What makes you think that there is something missing here? Those are two different settings.

[StefanM]

Originally Posted by Jiaz

What makes you think that there is something missing here? Those are two different settings.

Some misunderstanding:

Example 1:

in tsv it reads: ["-i","%audio","-vn","-f","ogg","-acodec","libvorbis","-aq","4","%out","-y"]
In json the same reads only:[List]

Same applies to example 2

Here instead of: {"mp4":"mp4"}
in json it only reads: [Record]

Please have a look at the attached screenshot:
https://i.imgur.com/9oIEVFh.jpg

Black is tsv export and blue is json export.

[StefanM]

Originally Posted by mgpai

var file = JD_HOME + "/" + new Date().toISOString().substr(0, 19).replace(/[^\d]/g, "-") + ".tsv";

We'll see. No ETA though.

You're welcome.

Even without any ETA...
... thanks a lot for trying, maybe, some time later...

[TomNguyen]

Originally Posted by mgpai

/*
    Random delay before download
    Trigger : A Download Started
    IMPORTANT : Enable "Synchronous execution" checkbox in the top panel
*/

var seconds = Math.floor(Math.random() * 5) + 1 // 1 to 5 seconds

while (seconds && !link.aborting) {
    sleep(seconds * 1000);
    seconds--;
}

Dear mgpai,
Today I got this error when running this script

net.sourceforge.htmlunit.corejs.javascript.EcmaError: ReferenceError: "link" is not defined. (#9)

I am not sure why. I have run it for a few days and this is the first time I got this error. I have created a log incase Jdownloader Team can take a look at it.

20.06.22 03.34.21 <--> 20.06.22 17.19.40 jdlog://6301035302851/

Thank you!

[StefanM]

Originally Posted by mgpai

Let me know if you need any modifications.

@mpgai:
Sorry to bother you once again. There is one thing I forgot from my initial spec:

A column which indicates whether or not the default settings for a row have been changed. (e.g. DEFAULT / CUSTOM)

Could you add such a column, please?

I'm referring to the yellow arrow in Advanced Settings, indicating that default settings have been changed:



Thanks once again!

[Jiaz]

@TomNguyen: You've set wrong Trigger

Trigger=ON_DOWNLOADS_RUNNING

But must set to "A Download started"

[Jiaz]

Originally Posted by StefanM

A column which indicates whether or not the default settings for a row have been changed. (e.g. DEFAULT / CUSTOM)

@mgpai: you could JSON.stringify default and value and compare to check if value is customized or not

[TomNguyen]

Originally Posted by Jiaz

@TomNguyen: You've set wrong Trigger
But must set to "A Download started"

Dear Jiaz. The trigger was set as: "Download controller started". I just set it to "A Download started" as your post. I will update the result after a few days. Thank you!

Update: It works well for the last week. Thanks again!

[Jiaz]

Originally Posted by TomNguyen

Thank you!

Nothing to thank me for and you're welcome!

[Jiaz]

Originally Posted by StefanM

in tsv it reads: ["-i","%audio","-vn","-f","ogg","-acodec","libvorbis","-aq","4","%out","-y"]
In json the same reads only:[List]
Here instead of: {"mp4":"mp4"}
in json it only reads: [Record]

Excel informs you that JSON value is a List or Record, the type and you have to query which information of that value you're intersted in
see marcusrauhut.com/importing-json-data-into-excel-power-query/
A single value may contain multiple other values and that's why a single value to column representation doesn't work. In TSV the value is stored as JSON String and that's why value <-> column assignment works fine.