How can i get an import package to display dates and times? right now they say object Object?
How can i get an import package to display dates and times? right now they say object Object?
bkuhn
Posts: 11Questions: 1Answers: 0
(```)
<
<table id="example" class="table table-bordered table-striped">
<thead >
<tr>
<th >Service Date</th>
<th >Route</th>
<th >Bus</th>
<th >Leave Yard</th>
<th >Leave OD</th>
<th >inservice</th>
<th> InServe OD</th>
<th >Out of Service</th>
<th >OutServe OD</th>
<th >Return to Yard</th>
<th >ReturnYard OD</th>
<th >leave for break</th>
<th >Return From Break</th>
<th >leave for break2</th>
<th >Return From Break2</th>
</tr>
</thead>
</div>
</div>
var editor; // use a global for the submit and return data rendering in the examples
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.',
onComplete: 'none'
});
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: '../php/dates.php?format=custom',
table: "#example",
ajax: {url :"addDRImport.php", type: "POST"},
fields: [ {
label: "service_date:",
name: "service_date"
}, {
label: "Route:",
name: "Route"
},{
label: "bus:",
name: "bus"
},{
label: "leave_yard:",
name: "leave_yard"
}, {
label: "Lvyd_odometer:",
name: "Lvyd_odometer"
}, {
label: "inservice:",
name: "inservice"
}, {
label: "inserv_odometer:",
name: "inserv_odometer"
}, {
label: "out_of_service:",
name: "out_of_service"
}, {
label: "outserv_odometer:",
name: "outserv_odometer"
}, {
label: "return_to_yard:",
name: "return_to_yard"
}, {
label: "rtnyd_odometer:",
name: "rtnyd_odometer"
}, {
label: "leave_for_break:",
name: "leave_for_break"
}, {
label: "return_from_break:",
name: "return_from_break"
}, {
label: "leave_for_break2:",
name: "leave_for_break2"
}, {
label: "return_from_break2:",
name: "return_from_break2"
}
]
} );
var uploadEditor = new $.fn.dataTable.Editor( {
fields: [ {
label: 'CSV file:',
name: 'csv',
type: 'upload',
ajax: function ( files, done ) {
// 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 {
selectColumns( editor, results.data, results.meta.fields );
}
// Tell Editor the upload is complete - the array is a list of file
// id's, which the value of doesn't matter in this case.
done([0]);
}
});
}
} ]
} );
var table = $('#example').DataTable( {
ajax: '../php/dates.php?format=custom',
ajax: 'data/objects_subarrays.txt',
dom: "Bfrtip",
ajax: {url :"viewDRImport.php", type: "POST"},
processing: true,
serverSide: true,
columns: [
{ data: "service_date" },
{ data: "Route" },
{ data: "bus" },
{ data: "leave_yard" },
{ data: "Lvyd_odometer" },
{ data: "inservice" },
{ data: "inserv_odometer" },
{ data: "out_of_service" },
{ data: "outserv_odometer" },
{ data: "return_to_yard" },
{ data: "rtnyd_odometer" },
{ data: "leave_for_break" },
{ data: "return_from_break" },
{ data: "leave_for_break2" },
{ data: "return_from_break2" }
],
select: {
style: 'os',
selector: 'td:first-child'
},
buttons: [
{
extend: 'csv',
text: 'Export CSV',
className: 'btn-space',
exportOptions: {
orthogonal: null
}
},
{
text: 'Import CSV',
action: function () {
uploadEditor.create( {
title: 'CSV file import'
} );
}
},
{
extend: 'print',
text: 'Print',
exportOptions: {
modifier: {
page: 'current'
}
}
}
]
} );
} );
</script>
(```)
This question has an accepted answers - jump to answer
Answers
Can you show me the data that is being loaded (i.e. the JSON) and also a screenshot of the result please?
Thanks,
Allan
Without seeing an example of your data its hard to offer suggestions. Providing a simple test case with an example of your data will help us to help you.
https://www.datatables.net/manual/tech-notes/10#How-to-provide-a-test-case
I see you have server side processing enabled. Use the browser's network inspector to get a page of data to use in the test case. Use the
dataoption to apply the row data to the Datatable. Similar to this example. You will only need to copy thecolumnsconfig into the test case and provide the HTML table.Kevin
I am not sure how to use the example page. I loaded the php file and the data into this page
https://live.datatables.net/bipadisi/1/
Here is a screen shot of the page
On my other datatables these items that say object would have a time or a date in them, but when using the import function, they say object OBJECT?
This is a City that receives Federal Funding , so i cannot link to their page and the data is coming from SQL so I cannot get the datatables page to load a sample
As Allan said, it would be helpful to see the data that gets loaded into the table. Could you post a couple of lines of the JSON here, so we can see what DataTables receives from the server, please.
Colin
In addition to the SSFRImport.php file, I have a ViewFRImport.php file.
https://live.datatables.net/muxoyeho/2/
SELECT top 10 [ID]
,[service_date]
,[Shift]
,[bus]
,[leave_yard]
,[Lvyd_odometer]
,[inservice]
,[inserv_odometer]
,[outserv_odometer]
,[return_to_yard]
,[rtnyd_odometer]
Returns
ID service_date Shift bus leave_yard Lvyd_odometer inservice inserv_odometer out_of_service outserv_odometer return_to_yard rtnyd_odometer
30463 2024-03-27 40MF 80 04:38:00 255967 05:02:00 255970 19:54:00 256229 20:15:00 256231
30464 2024-03-27 41MF 78 04:40:00 238517 05:03:00 238523 19:48:00 238765 20:02:00 238771
30465 2024-03-27 42MF 55 04:40:00 358144 05:00:00 358149 19:51:00 358365 20:02:00 358370
30466 2024-03-27 43MF 54 04:39:00 389658 05:03:00 389664 19:49:00 389843 20:01:00 389848
30467 2024-03-27 44MF 79 04:40:00 184165 04:58:00 184163 19:50:00 184409 20:00:00 184411
30454 2024-03-26 40MF 80 04:38:00 255704 05:05:00 255706 19:48:00 255965 19:56:00 255967
30455 2024-03-26 41MF 74 04:40:00 83616 05:03:00 83622 11:14:00 83731 11:14:00 83731
30456 2024-03-26 41MF 79 11:14:00 184010 11:14:00 184040 19:45:00 184151 19:59:00 184165
30457 2024-03-26 42MF 73 04:38:00 87782 05:00:00 87787 12:55:00 87902 13:25:00 87906
30458 2024-03-26 42MF 78 12:57:00 238412 13:00:00 238411 19:51:00 238512 20:11:00 238517
Sorry, I am not an IT guy
Use the browser's network inspector to see the JSON response. The steps are shown in this technote.
Kevin
Is this what you need?
{"draw":0,"recordsTotal":1,"recordsFiltered":1,"data":[{"service_date":{"date":"2024-03-27 00:00:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"Shift":"40MF","bus":80,"leave_yard":{"date":"2024-03-29 04:38:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"inservice":{"date":"2024-03-29 05:02:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"out_of_service":{"date":"2024-03-29 19:54:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"return_to_yard":{"date":"2024-03-29 20:15:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"Lvyd_odometer":255967,"inserv_odometer":255970,"outserv_odometer":256229,"rtnyd_odometer":256231}]}
Yes
The columns are defined like this:
The JSON response has nested objects:
This example shows how to display nested objects. The columns will need to be defined like this:
Kevin
Also note that the
drawvalue is0in your snippet. You may have edited when pasting but the value should never be0. It needs to match thedrawvalue in the request. I assume this is correct in your environment as the Datatable is displaying rows.Kevin
First Thank you soo much. I have been researching the datatable manuals for two eeks now. this is very close thanks to you and I really appreciate it.
That is bringing the date back now, but the times are in the database as 07:00 with no date. The datatable is trying to add todays date to last years entries. is there a way to show time only for the time fields?
The client side Datatable doesn't modify the supplied data. The server script is returning the data as
2024-03-27 00:00:0with the date and time. If this is not expected then it suggests that there is a disconnect between the server data query (how its handling datetime formats) and what is stored in the DB. The server script will need to be investigated.Kevin
What is the data type for those time fields in the database?
Kevin
We have dozens of datatables that properly bring back the time, but we only have this one import datatable and the times in the db do not have dates only times. But the datatable is displaying todays date and the time from the db on each entry. The service_date field is correctly bringing back the db date, but the time in the same row will show the current date with the time from the db. Is there a format function to add %H:%M:%S?
Maybe a renderer will do what you want. See these examples. Another option, if the datetime render doesn't fit your needs, is to use
columns.renderwith a datetime library, ie moment.js, to display in the format you want. See this example.Kevin
If
columns: [
{ data: "service_date.date" },
....
is used to format the value of date, what do i use for the time only?
columns: [
{ data: "leave_yard" },
I have looked at the examples and tried everything shown, but none of these are for time only?
Here is an example using moment.js.
https://live.datatables.net/tefotoha/1/edit
The example assumes the original datetime format looks like
2024-03-29 04:52:00. See the moment.js display format docs to learn how to format the date output.If you prefer you can use another datetime library like Luxon.
Kevin
This worked. Thank you so much for sticking with this...
data: "service_date.date",
render: DataTable.render.datetime('M/DD/YYYY')},
data: "leave_yard.date",
render: DataTable.render.datetime('HH:mm:ss')},