Import CSV file

markco84 · June 2020

I am trying to use the import CSV button but I am getting a google Chrome Aw, Snap! error code: Out of Memory.

I have tried using the example below but when I import my sample CSV file it makes it to the Confirm import screen but says a system error has occurred.
https://editor.datatables.net/examples/extensions/import.html

I have used this example below with the same file and it works.
http://live.datatables.net/yixobehu/1/edit

Here is a link to my site.
https://epsilonfab.com/software/epsilon/ordersmanager.php

Can anyone point me in the right direction? Is there a way I can attach the CSV file to this post? It is only 162 lines (1KB)
If I change the file to 10 rows it works but how do I make it work with more?

This is what I get with the debugger code "Resource interpreted as Document but transferred with MIME type application/vnd.ms-excel:"

allan · June 2020

How odd. Could you just post the csv into a comment? Use triple backticks to put it in a code comment.

Thanks,
Allan

markco84 · June 2020

Hello Allan,
Thanks for the reply.

I can't seem to post the code or screenshots.

The csv is just two columns with 250 rows. The data I was using for testing is just 1-249 in each column.

Thanks for the help,
Mark

colin · June 2020

You should just be able to cut&paste the code here - what's the problem that prevents you?

Colin

markco84 · June 2020

I was getting an error when trying to comment on this site. It was preventing it, I think it has something to do with my pc.
Here is the CSV file

column1;column2
1;A1
2;A2
3;A3
4;A4
5;A5
6;A6
7;A7
8;A8
9;A9
10;A10
11;A11
12;A12
13;A13
14;A14
15;A15
16;A16
17;A17
18;A18
19;A19
20;A20
21;A21
22;A22
23;A23
24;A24
25;A25
26;A26
27;A27
28;A28
29;A29
30;A30
31;A31
32;A32
33;A33
34;A34
35;A35
36;A36
37;A37
38;A38
39;A39
40;A40
41;A41
42;A42
43;A43
44;A44
45;A45
46;A46
47;A47
48;A48
49;A49
50;A50
51;A51
52;A52
53;A53
54;A54
55;A55
56;A56
57;A57
58;A58
59;A59
60;A60
61;A61
62;A62
63;A63
64;A64
65;A65
66;A66
67;A67
68;A68
69;A69
70;A70
71;A71
72;A72
73;A73
74;A74
75;A75
76;A76
77;A77
78;A78
79;A79
80;A80
81;A81
82;A82
83;A83
84;A84
85;A85
86;A86
87;A87
88;A88
89;A89
90;A90
91;A91
92;A92
93;A93
94;A94
95;A95
96;A96
97;A97
98;A98
99;A99
100;A100
101;A101
102;A102
103;A103
104;A104
105;A105
106;A106
107;A107
108;A108
109;A109
110;A110
111;A111
112;A112
113;A113
114;A114
115;A115
116;A116
117;A117
118;A118
119;A119
120;A120
121;A121
122;A122
123;A123
124;A124
125;A125
126;A126
127;A127
128;A128
129;A129
130;A130
131;A131
132;A132
133;A133
134;A134
135;A135
136;A136
137;A137
138;A138
139;A139
140;A140
141;A141
142;A142
143;A143
144;A144
145;A145
146;A146
147;A147
148;A148
149;A149
150;A150
151;A151
152;A152
153;A153
154;A154
155;A155
156;A156
157;A157
158;A158
159;A159
160;A160
161;A161
162;A162
163;A163
164;A164
165;A165
166;A166
167;A167
168;A168
169;A169
170;A170
171;A171
172;A172
173;A173
174;A174
175;A175
176;A176
177;A177
178;A178
179;A179
180;A180
181;A181
182;A182
183;A183
184;A184
185;A185
186;A186
187;A187
188;A188
189;A189
190;A190
191;A191
192;A192
193;A193
194;A194
195;A195
196;A196
197;A197
198;A198
199;A199
200;A200
201;A201
202;A202
203;A203
204;A204
205;A205
206;A206
207;A207
208;A208
209;A209
210;A210
211;A211
212;A212
213;A213
214;A214
215;A215
216;A216
217;A217
218;A218
219;A219
220;A220
221;A221
222;A222
223;A223
224;A224
225;A225
226;A226
227;A227
228;A228
229;A229
230;A230
231;A231
232;A232
233;A233
234;A234
235;A235
236;A236
237;A237
238;A238
239;A239
240;A240
241;A241
242;A242
243;A243
244;A244
245;A245
246;A246
247;A247
248;A248
249;A249

markco84 · June 2020

Here is the code.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Orders Manager</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs-3.3.7/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.6/b-html5-1.5.6/b-print-1.5.6/sl-1.3.0/datatables.min.css"/>
 
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/bs-3.3.7/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.6/b-html5-1.5.6/b-print-1.5.6/sl-1.3.0/datatables.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/4.6.3/papaparse.min.js"></script>

    <link href="css/editor.bootstrap.min.css" rel="stylesheet" type="text/css" />
<script src="js/dataTables.editor.min.js"></script>
<script src="js/editor.bootstrap.min.js"></script>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.2/moment.min.js"></script>
            <script src="https://cdn.datatables.net/plug-ins/1.10.19/dataRender/datetime.js"></script>

</head>
<body>
<div class="main" id="belowtopnav" style="margin-left:220px; padding-top:0px;">
</br>
    <div class="container mb-3 mt-3 col l10 m12" id="main">
</br>
<table id="example" class="display table table-striped table-bordered" cellspacing="0" style="width: 100%">
        <thead>
            <tr>
                <th style='width:100px;'>Initial User</th>
                <th>Comment</th>                
            </tr>
        </thead>
</table>
</div>
</div>
<script>
var editor; // use a global for the submit and return data rendering in the examples 

// Display an Editor form that allows the user to pick the CSV data to apply to each column
function selectColumns ( editor, csv, header ) {
    var selectEditor = new $.fn.dataTable.Editor();
    var fields = editor.order();
 
    for ( var i=0 ; i<fields.length ; i++ ) {
        var field = editor.field( fields[i] );
 
        selectEditor.add( {
            label: field.label(),
            name: field.name(),
            type: 'select',
            options: header,
            def: header[i]
        } );
    }
 
    selectEditor.create({
        title: 'Map CSV fields',
        buttons: 'Import '+csv.length+' records',
        message: 'Select the CSV column you want to use the data from for each field.'
    });
 
    selectEditor.on('submitComplete', function (e, json, data, action) {
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
 
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
            var mapped = data[ field.name() ];
 
            for ( var j=0 ; j<csv.length ; j++ ) {
                field.multiSet( j, csv[j][mapped] );
            }
        }
    } );
}



$(document).ready(function() {
    editor = new $.fn.DataTable.Editor( {
        ajax: {
            url: "PHPorders-manager.php",
            type: 'POST'
        },
        table: "#example",
        fields: [{
                label: "Tag:",
                name: "orderlist.tag"
            },{
                label: "Type:",
                name: "orderlist.type"
            }
        ]
    } );

// Upload Editor - triggered from the import button. Used only for uploading a file to the browser
    var uploadEditor = new $.fn.dataTable.Editor( {
        fields: [ {
            label: 'CSV file:',
            name: 'csv',
            type: 'upload',
            ajax: function ( files ) {
                // Ajax override of the upload so we can handle the file locally. Here we use Papa
                // to parse the CSV.
                Papa.parse(files[0], {
                    header: true,
                    skipEmptyLines: true,
                    complete: function (results) {
                        if ( results.errors.length ) {
                            console.log( results );
                            uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                        }
                        else {
                            uploadEditor.close();
                            selectColumns( editor, results.data, results.meta.fields );
                        }
                    }
                });
            }
        } ]
    } );

    var oTable;
    oTable = $('#example').DataTable( {
        dom: "Bfrtip",
        "responsive": true,
        "processing": true,
        "pageLength": 100,
        fixedColumns: true,
        ajax: {
            url: "PHPorders-manager.php",
            type: 'POST'
        },
        columns: [
            { data: 'orderlist.tag'},
            { data: 'orderlist.type'}
        ],
        select: {
            style:    'os',
            selector: 'td:first-child'
        },
        buttons: [
            { extend: "edit",   editor: editor },
            { extend: "remove",   editor: editor },
            {
                text: 'Import CSV',
                action: function () {
                    uploadEditor.create( {
                        title: 'CSV file import'
                    } );
                }
            },
        ]
    });
} );
</script>
</body>
</html>

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

markco84 · June 2020

Can someone delete the one I forgot the ``` in. Thanks again for your help.

kthorngren · June 2020

That is an interesting problem to track down.

Using the debugger I see this:

The field.name() API returns this:
field.name(): "orderlist.tag"

But its not the correct way to access a nested object which results in mapped being undefined. This screenshot explains what I mean:

In this section of code:

    selectEditor.on('submitComplete', function (e, json, data, action) {
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
 
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
            var mapped = data[ field.name() ];
 
            for ( var j=0 ; j<csv.length ; j++ ) {
                field.multiSet( j, csv[j][mapped] );
            }
        }
    } );

You will need to split out the field().name() so you can do something like this var mapped = data[ "orderlist" ][ "tag" ];. There are various ways to do this and how you choose would be based on your real solution of nested objects.

Kevin

markco84 · June 2020

Thanks for the reply I will look into that (I am new to this so I have to dig around a bit). Why would it work with only 10 rows though?
In this screenshot it has a large number for DT_RowId is this sequential or just a random number?

kthorngren · June 2020

Why would it work with only 10 rows though?

Its not really working with 10 rows. Take a look at the data inserted, its not the data from the CSV file.

In this screenshot it has a large number for DT_RowId is this sequential or just a random number?

@allan or @colin can answer that question. But its not part of the problem.

Kevin

markco84 · June 2020

Thanks a lot for showing me where the issue is and clearing up my questions, very helpful.
When I manually enter the field name it works for one field but I couldn't get it to work with multiple. Obviously I am missing something.
I played around a bit and changed the code to strip the decimal.

    selectEditor.on('submitComplete', function (e, json, data, action) {
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
 
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
            var mapped = data[ field.name().split(".")[0] ] [ field.name().split(".")[1] ]; 
            
            for ( var j=0 ; j<csv.length ; j++ ) {
                field.multiSet( j, csv[j][mapped] );
            }
        }
    } );

Is there any problems is proceeding this way, with initial testing it seems to work.

Thanks again,
Mark

kthorngren · June 2020

var mapped = data[ field.name().split(".")[0] ] [ field.name().split(".")[1] ];

Will work for your specific test case link. However if you have columns that are not nested objected then it will fail. In that case you will need to do some checks to handle the data object appropriately.

Kevin

markco84 · June 2020

Awesome thank you, I believe all of my columns are nested objects.
I tried a larger number of rows and it seems to max out at 249 rows. If I import 250 rows it doesn't work. I am using Netfirms and I know that when I manually import it does not like a lot of rows so maybe it is a limitation with their server. Is there a way to increase this limit? If not I will just limit the size of the import for now until we build a network server then we can allow what we want.
You have answered my initial question but I cannot mark as answered. If I need to start a new question please just let me know.

Thanks a million,
Mark.

allan · June 2020

DT_RowId is this sequential or just a random number?

If creating rows client-side and there is no ID given, then Editor uses a date stamp (Unix epoch in mS) concatenated with the row number of the set being created. So not random, but unique.

If I import 250 rows it doesn't work.

I suspect this is indeed a limitation with the server. What http server are you using? If Apache / PHP you might want to change the max_input_vars property to something much higher than it is by default. You can check what it is currently set to using php_info();.

Allan

p.s.: Kevin - cracking work tracking this one down!

markco84 · June 2020

I am using Apache, the max_input_vars is set to 1000. I'll bump it up when we switch to a network server.

Thanks,

Import CSV file

Import CSV file

Replies

Howdy, Stranger!

Categories

DataTables

Import CSV file

Import CSV file

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables