paste into datatable

montoyam · June 2020

I have an excel document that has a lot going on in it, so I can't figure out a way to use the csv importer without the user having to do a lot of manipulation. To make it easier on the end user, if I had a datatable/editor set up with the correct columns, is it possible to have them copy/paste the needed rows/cells into a datatable/editor?

colin · June 2020

They'd be able to cut&paste the cells individually, either with inline/bubble or on the form, but not a row all at once though.

Colin

kthorngren · June 2020

I would look for a Javascript library to allow for pasting data into an input on the page. Then you can take that input and use rows().add() to add to the Datatable. If you need to update a DB you can use an ajax request or use create() to insert the rows.

Kevin

montoyam · June 2020

awesome. thanks for the tip. I found this code that may work as far as getting it into a table.
https://stackoverflow.com/questions/2006468/copy-paste-from-excel-to-a-web-page

then I will just need to figure out the create() you mentioned.

montoyam · June 2020

I can't find any documentation on how to get a datatable saved using the editor.

here is what i have so far. this will take the excel copy/paste and put it into a dataTable:

<!DOCTYPE html>
<html>
<head>
    <script>
        function generateTable() {
            var data = $('textarea[name=excel_data]').val();
            console.log(data);
            var rows = data.split("\n");

            var table = $('<table cellpadding="0" cellspacing="0" border="0" class="display" id="LanguageLineData"/>');

            for (var y in rows) {
                var cells = rows[y].split("\t");
                
                var row = $('<tr />');
                for (var x in cells) {
                    
                    row.append('<td>' + cells[x] + '</td>');
                }
                table.append(row);
            }

            // Insert into DOM
            $('#excel_table').html(table);
            
            $('#LanguageLineData').DataTable({
                "columns": [
                    { "data": "Item", title: "Item" },
                    { "data": "ClientID", title: "Client ID" },
                    { "data": "ClientName", title: "Client Name" },
                    { "data": "Blank1", title: "Blank" },
                    { "data": "Date", title: "Call Date" },
                    { "data": "TIME", title: "Time" },
                    { "data": "Language", title: "Language" },
                    { "data": "IntNumber", title: "Internal Number" },
                    { "data": "Blank2", title: "Blank" },
                    { "data": "Department", title: "Department" },
                    { "data": "Blank3", title: "Blank" },
                    { "data": "Blank4", title: "Blank" },
                    { "data": "PhoneNumber", title: "Phone #" },
                    { "data": "Blank5", title: "Blank" },
                    { "data": "Blank6", title: "Blank" },
                    { "data": "PersonalCode", title: "Blank" },
                    { "data": "Blank7", title: "Blank" },
                    { "data": "RefNumber", title: "Ref #" },
                    { "data": "Duration", title: "Duration" },
                    { "data": "Charge", title: "Charge" },
                    { "data": "DialOut", title: "Dial Out" },
                    { "data": "ANI", title: "ANI" }
                ]
            });
            
        }
    </script>

</head>

<body>

    <p>Paste excel data here:</p>
    <textarea name="excel_data" style="width:250px;height:150px;"></textarea><br>
    <input type="button" onclick="javascript:generateTable()" value="Genereate Table" />
    <br><br>
    <div id="excel_table"></div>
</body>
</html>

kthorngren · June 2020

I can't find any documentation on how to get a datatable saved using the editor.

If you don't need to sue Editor for other functions you can use jQuery ajax() to send the data to your server for saving.

If using Editor the use the create() like I mentioned above to create new rows and the DB.

Kevin

allan · June 2020

Yes - if you are using Editor, don’t add it to the DataTable directly yourself. Let Editor do that, and as Kevin says (spot on as always!) use create() to instruct Editor to create a new row.

Allan

montoyam · June 2020

I have it working by instead of reading the excel data into a table, bringing it into an array. then I loop through each row doing an ajax call, row by row. But, I would love to learn how to use the Editor to do this. i am not able to find examples of how to add an entire recordset using .create.

Here is how I am currently doing it:

        function excelToObjects() {
            var stringData = $('textarea[name=excel_data]').val();
            var objects = [];
            //split into rows
            var rows = stringData.split('\n');

//            columns = rows[0].split('\t'); //use this if first row is column names
            columns = [ "Item" ,"ClientID" ,"ClientName" ,"Blank1" ,"CallDate" ,"CallTime" ,"Language" ,"IntNumber" ,"Blank2" ,
                            "Department" ,"Blank3" ,"Blank4" ,"PhoneNumber" ,"Blank5" ,"Blank6" ,"PersonalCode" ,"Blank7" ,
                            "RefNumber" ,"Duration" ,"Charge" ,"DialOut" ,"ANI" ];
            for (var rowNr = 0; rowNr < rows.length; rowNr++) {
                var o = {};
                var data = rows[rowNr].split('\t');

                //Loop through all the data (start with zero since first row is not column names)
                for (var cellNr = 0; cellNr < data.length; cellNr++) {
                    o[columns[cellNr]] = data[cellNr];
                }
                
                if ($.isNumeric(o.Item)) { //ignores lines that don't have numeric data (column headers, etc.)
                    $.ajax({
                        url: "api/LanguageLine/Import",
                        type: "POST",
                        data: o,
                        success: function (response) {
                            if (response !== 0) {

                            } else {
                                alert("There was a problem importing the language line data.");
                            }
                        }
                    });            
                }
                objects.push(o);
            }
            generateTable();
        }

        function generateTable() {

            var LanguageLineTable = $('#LanguageLine').DataTable({
                dom: 'Bfrtip',
                ajax: 'api/LanguageLine',
                columns: [
                    { data: "ClientID", title: "Client ID" },
                    { data: "ClientName", title: "Client Name" },
                    { data: "CallDate", title: "Call Date" },
                    { data: "CallTime", title: "Time" },
                    { data: "Language", title: "Language" },
                    { data: "IntNumber", title: "Internal Number" },
                    { data: "Department", title: "Department" },
                    { data: "PhoneNumber", title: "Phone #" },
                    { data: "RefNumber", title: "Ref #" },
                    { data: "Duration", title: "Duration" },
                    { data: "Charge", title: "Charge" },
                    { data: "DialOut", title: "Dial Out" },
                    { data: "ANI", title: "ANI" },
                    { data: "Questionable", title: "Questionable" },
                    { data: "ImportID", title: "Fund/Org" },
                    { data: "Taxes", title: "Taxes" }
                ],
                select: { style: 'single' },
                autoWidth: false,
                responsive: true
               
            });            
        }

kthorngren · June 2020

But, I would love to learn how to use the Editor to do this.

The create() docs show a couple examples. You will do a combination of the 3rd and 4th examples. Take a look at the field().multiSet() and submit() docs. I built this simple example that adds two rows on a button click:
http://live.datatables.net/zeliruja/1/edit

Kevin

montoyam · June 2020

Ok. I seem to have gotten the code working, however, with 118 rows, it is getting an error 'Out of Memory'.

kthorngren · June 2020

it is getting an error 'Out of Memory'.

In Javascript or your server script?

Maybe you need to do fewer rows at a time.

The other option is to not use create() and instead use what you had before and send the data to the server using a jQuery ajax() request.

Kevin

montoyam · June 2020

yeah, looks like I will stick with the ajax call. But I will definitely refer to this in the future.

paste into datatable

paste into datatable

Replies

Howdy, Stranger!

Categories

DataTables

paste into datatable

paste into datatable

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables