# Dataset export to Excel datetime conversion issue

**URL:** <https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617>\
**Category:** Ignition\
**Tags:** ignition80, scripting\
**Created:** [April 2, 2020, 6:43am UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617 "2020-04-02T06:43:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luca](https://avatars.discourse-cdn.com/v4/letter/l/51bf81/32.png) [@Luca](https://forum.inductiveautomation.com/u/Luca)\
**Post date:** [April 2, 2020, 6:43am UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/1 "2020-04-02T06:43:40Z")

</div>

Hello, I’m currently developing on Ignition version 8.0.9 and I am having issues exporting a chart dataset to excel by using both functions “system.dataset.exportExcel” and “system.dataset.toExcel”. The main problem is that the dataset has a column in Date format; Ignition exports such column transforming it in the following way:

01/02/2020 07:00:00 ===\> 43862,2916666667

This causes the excel spreadsheet to require dedicated cell formatting to re-transform such value into a readable date. The same functions in version 7.9 didn’t cause this transformation.  
How can I avoid this conversion from occurring?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [April 2, 2020, 2:49pm UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/2 "2020-04-02T14:49:20Z")

</div>

Upgrade to 8.0.10. Date cells should natively export as Excel dates.

---

<div class="post-metadata">

**Author:** ![guganathan.r](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@guganathan.r](https://forum.inductiveautomation.com/u/guganathan.r)\
**Post date:** [August 21, 2020, 5:35pm UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/3 "2020-08-21T17:35:08Z")

</div>

Is this error again happens in 8.0.15? I’m facing the same issue. It comes in unix milliseconds format.  
Is there any way to convert to DD-MM-YYYY while we export to excel?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [August 21, 2020, 6:09pm UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/4 "2020-08-21T18:09:34Z")

</div>

[https://docs.inductiveautomation.com/display/DOC80/system.dataset.formatDates](https://docs.inductiveautomation.com/display/DOC80/system.dataset.formatDates)

---

<div class="post-metadata">

**Author:** ![guganathan.r](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@guganathan.r](https://forum.inductiveautomation.com/u/guganathan.r)\
**Post date:** [August 22, 2020, 8:14am UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/5 "2020-08-22T08:14:41Z")

</div>

Sorry I couldnt get as required.

Table JSON is converted to dataset and I used system.dataset.formatDates as suggested. But no conversion. I still get in unix milliseconds timestamp.

```
data = self.parent.parent.getChild("TableConsumption").props.data

# TableEXPORTFIELDCHOOSER is a custom made table for users to choose the columns and order of column to export.
tablefieldds = self.getSibling("TableEXPORTFIELDCHOOSER").props.data
sorttablefieldds = system.dataset.sort(tablefieldds, 2)

#Get the chosen columns	
tablefieldheader = []
for row in range(sorttablefieldds.rowCount):
	if sorttablefieldds.getValueAt(row, "CHOOSE") == True:
		tablefieldheader.append(sorttablefieldds.getValueAt(row, "COLUMNS"))
	
system.perspective.print(tablefieldheader)
system.perspective.print(len(tablefieldheader))

tpc = self.parent.parent.getChild("TableConsumption").props.columns
newheader = []

#Get the field name for columns		
for title in range(len(tablefieldheader)):
	for row in range(len(tpc)):
		if tablefieldheader[title] == tpc[row]["header"]["title"] :
			tpcfield = tpc[row]["field"]
			newheader.append(tpcfield)
system.perspective.print(newheader)
system.perspective.print(len(newheader))

#convert JSON table output to dataset	
datasetData = []
for row in range(len(data)):	
	rowData = []
	for col in range(len(newheader)):
		cell = data[row][newheader[col]]
		if hasattr(cell, 'value'):
			rowData.append(cell.value)
		else:
			rowData.append(cell)
	#system.perspective.print(rowData)
	datasetData.append(rowData)

#export to excel	
finalDataset = system.dataset.toDataSet(tablefieldheader,datasetData)
formatdateds = system.dataset.formatDates(finalDataset, "dd-mm-yyyy")
XLSFormat = system.dataset.dataSetToExcel(True,[formatdateds], True)
timenow = system.date.format(system.date.now(), 'dd_MM_yyyy_HH_mm')
filename = 'T2_SHIFTPARAMS_' + timenow + '.xls'
system.perspective.download(filename,XLSFormat)

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/b/0bef94a31822313f838217e3748d258ddb4dfc20.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/4/14870bb2f6aaa6948c32300dfffdc8701ff06585.png)

---

<div class="post-metadata">

**Author:** ![francois.morin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/francois.morin/32/15064_2.png) [@francois.morin](https://forum.inductiveautomation.com/u/francois.morin)\
**Post date:** [August 12, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/dataset-export-to-excel-datetime-conversion-issue/33617/6 "2022-08-12T18:33:19Z")

</div>

Here is a method that can export a list of Tables in excel. I did fix the formatting of the date using the column render method and dateFormat  
This method only exports visible columns of Tables

```auto
'''
exportToExcel(fileName, dataTable, sheetName):
	Generate an excel file containing the exact representation of the data shown in the table.
	
input:
	fileName : Name of the file without date and extension. Date is added automatically to the filename.
	dataTables : List of Table Object to export
	sheetNames : List of sheets names
'''
def exportToExcel(fileName, dataTables, sheetNames):
	xlsxData=[]	
	for dataTable in dataTables:
		data = dataTable.props.data
		tableColumns = dataTable.props.columns
		headers = []
		rows = []
		for r, row in enumerate(data):
			newRow = []
			for c, col in enumerate(tableColumns):
				if col["visible"]:
					if r==0:
						headers.append(col["header"]["title"])
					#format date
					if col["render"]=="date":
						dateValue = system.date.fromMillis(row[col["field"]])
						dateFormat = col["dateFormat"]
						if dateFormat == "":
							dateFormat = 'yyyy-MM-dd HH:mm:ss'
						newRow.append(system.date.format(dateValue,dateFormat))
					else:
						newRow.append(row[col["field"]])
			rows.append(newRow)
	
		ds = system.dataset.toDataSet(headers, rows)
		xlsxData.append(ds)
	
	xlsxBinary = system.dataset.toExcel(True, xlsxData, True, sheetNames)
	filename = '%s_%s.xlsx' % (fileName, system.date.now())
	system.perspective.download(filename, xlsxBinary)

```