Large data file uploads

wash b

Hello everyone,

I'm new to datatables and I have been using the 15 day trial to explore the tool's feature sets and so far, I must say I'm very impressed.
I know image files can be uploaded while creating new row data entires - but I have a particularly important use case that I'm working on that requires large data sets (tens of thousands of rows and several tens of columns) to be uploaded at a go through CSV files at the front end by the end users.

Can anyone advise me if datatables natively supports this feature?

Thank you

rf1234

"...that requires large data sets (tens of thousands of rows and several tens of columns) to be uploaded at a go through CSV files at the front end by the end users."

So the user is not exporting data from the front end data table to csv and subsequently loads it up? It is only about uploading a large file, right?

If the latter is the case you can upload very large files. It will depend on the limitations that you set for file size in Editor and, more importantly, the limitations your server might have. For example if you use PHP you will need to make adjustments to "post_max_size" and "upload_max_filesize". My settings for this are 90M and 50M. An individual file may not be > 50M and uploading several files at once they may not be > 90M in total.

In my PHP Editor instance I have the following validator to make sure the user doesn't upload files > 50M to avoid system error messages:

->validator( function ( $file ) {
    if ($file['size'] >= 52428800) {
        return'Files must be smaller than 50MB.';
    }
    return true;
} )

https://editor.datatables.net/manual/php/upload

wash b

Hi rf1234!

Firstly, thank you for the speedy response. I really appreciate it.
Secondly, yes, you're right - front end data uploads is what I wanted and yes, I am using pHp.

Your explanation is very useful.

But just to be clear - in your implementation that you've just described above, the data is added to existing SQL tables right? (I'm assuming you're using a mySQL server).

Thanks

rf1234

Yes it is added to existing SQL tables but it is MySQL. Shouldn't make any difference. I remember using SQLServer a while ago and didn't see much of a difference.

Here is an excerpt of my data model showing how files of all kind are being handled. The physical files are saved in the server's file system of couse. Only the paths are saved in the database. That's how Editor handles it and that's how I handle it too even if I write files purely at the back end. The advantage is that those files seamlessly display at the front end even if they weren't saved using Editor.

This is the excerpt:

The file table holds the paths etc to the files, and the link tables (I have about a dozen of them) hold the reference to the contract, offer and whatever it is that you want to save files for. Editor works with these tables and does all the CRUD operations for you.

This is a complete Mjoin (PHP) to get this done for my "contract" table linking to "file" via "contract_has_file". The users may upload multiple files at a time.

Mjoin::inst( 'file' )
    ->link( 'contract.id', 'contract_has_file.contract_id' )
    ->link( 'file.id', 'contract_has_file.file_id' )
    ->fields(
        Field::inst( 'id' )
        ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/lgfuploads/contracts/__ID__.__EXTN__' )
                ->db( 'file', 'id', array(
                    'about'   => 'C',  //contract
                    'name'    => Upload::DB_FILE_NAME,
                    'size'    => Upload::DB_FILE_SIZE,
                    'web_path'    => Upload::DB_WEB_PATH,
                    'system_path' => Upload::DB_SYSTEM_PATH
                ) )
                ->validator( function ( $file ) use ( $msg ) {
                    if ($file['size'] >= 52428800) {
                        return $msg[3];
                    } else {
                        return true;
                    }
                } )
                ->allowedExtensions( array  //php is not case sensitive here
                  ( 'pdf', 'xls', 'xlsx', 'csv', 'doc', 'docx', 'rtf', 'ppt',  
                    'pptx', 'odt', 'ods', 'odp' ), $msg[2] )
        ),
        Field::inst( 'web_path' )->set( false ),
        Field::inst( 'name' )->set( false )             
    )
)

And this is some pure server code doing just C and U by myself to save an XLSX file generated at the back end in the same data structure. In this case the link table is called "rfp_has_file". At the front end this file will show up just like any uploaded file automatically because this imitates what Editor does for Create and Update.

function saveRfpRecipientXLS($rfpId, &$spreadsheet, &$dbh) {
    
    $outputFileName = '__rpt__Empfänger_' . $rfpId . '.xlsx';
    //check whether a report file already exists, if it does it will be deleted
    // and a new file with the same path will be written; this is to avoid many
    //new paths for one report
    $dbh->query('SELECT `id`, `web_path`, `system_path`
                   FROM `file`
             INNER JOIN  rfp_has_file    ON  
                        `file`.`id`    = rfp_has_file.file_id  
                  WHERE `file`.`about` = "R"  
                    AND `file`.`name`  = :outputFileName  
                    AND  rfp_has_file.rfp_id = :id');
    $dbh->bind(':id', $rfpId);
    $dbh->bind(':outputFileName', $outputFileName);

    $row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
    if ( (bool)$dbh->rowCount() ) {
        $fileId = $row["id"];
        $webPath = $row["web_path"];
        $systemPath = $row["system_path"];
        if (file_exists($systemPath)) {
    //delete a reporting file with that file id if it exists
            unlink ($systemPath);                        
        }
    } else { //no record there yet => must be inserted
    //insert an empty record into the file table to obtain an id
        $dbh->query('INSERT INTO `file` (`about`) VALUES (:about)');
        $dbh->bind(':about', "R");
        $result = $dbh->execute();
        $fileId = $dbh->lastInsertId();     
    //insert link table between rfp and file
        $dbh->query('INSERT INTO rfp_has_file 
                    (rfp_id, file_id)   VALUES   
                    (:rfp_id, :fileId)');
        $dbh->bind(':rfp_id', $rfpId);
        $dbh->bind(':fileId', $fileId);
        $result = $dbh->execute();
    //set fields to update the file table and to write the file
        $webPath = '/lgfuploads/rfps/' . $fileId . '.xlsx';
        $systemPath = $_SERVER['DOCUMENT_ROOT'] . $webPath;
    }
    
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save($systemPath);

    //finally update the file record with the new data
    $dbh->query('UPDATE `file` 
                    SET `name`          = :name,  
                        `size`          = :size,  
                        `web_path`      = :webPath,  
                        `system_path`   = :systemPath  
                  WHERE `id`            = :fileId');
    $dbh->bind(':name', $outputFileName);
    $dbh->bind(':size', filesize($systemPath) );
    $dbh->bind(':webPath', $webPath);
    $dbh->bind(':systemPath', $systemPath);
    $dbh->bind(':fileId', $fileId);
    $result = $dbh->execute();
}

wash b

Dear rf1234,

Sorry for the delayed response.
Thank you so much for the detailed response.
It's quite detailed and worth a closer look on my end.
I'll definitely purchase a license going forward.

Once again, I really appreciate all your help.