We imported an Excel file into Libre Office and it changed all the dates. We had imported the LibreOffice data into another program before finally seeing that it had changed ALL of the dates. Is there a way to reset them back to what was originally in the Excel file?
How do we import this type of data in the future without it changing the dates?
There are many workarounds and more detail is necessary to diagnose exactly what happened in your situation. Rather than trying to work all of that out, consider this: transferring by Paste to the Tables tab of a spreadsheet with dates formatted MM/DD/YY works as designed in LO 5.1. If something goes wrong, presumably it is about converting the integer value that is the foundation for the date into a date. For some systems, 0 might by 1900-01-01, for others, something else. I am speculating.
A workaround that should work generally is to import the Date field as a Text column and then use SQL to have the database do the conversion directly without fussing over the integer conversion, if indeed that is the problem. I believe the MM/DD/YY LO display is a mask over the database engine format of YYYY-MM-DD, so what you will do is feed the text version of the date into an UPDATE query in Tools → SQL formatted in the indicated format.
Thus, the sample query is, where dttx is a Text-formatted column with dates formatted MM/DD/YY, and dt is a blank column defined to have DATE data:
UPDATE "tbl1"
SET "dt" = CONCAT(CONCAT(CONCAT('20', RIGHT("dttx", 2)), '-'),
CONCAT(CONCAT(LEFT("dttx", 2), '-'), SUBSTR("dttx", 4, 2)))
There are a lot of CONCAT statements over there because HSQLDB v1.8 only allows concatenating two strings at once. Needless to say, this assumes all dates are in the 21st century.
This solution tests ok on my system. The result will be the blank dt column will get the data from dttx, and now be a date field.
(if this answers your question, please accept it by clicking the check box (
) to the left)
@doug Can SQL be used with Calc? I thought it was only applicable to Base. While @TJNolander does not specify in the OP, based on the source being Excel, I assume the target is Calc.
@JKEngineer: user tagged the question [base] and thus I assumed import into that app. However, I concede the question text does not say specifically.
Have you tried exporting the Excel files to csv format and then importing the csv files into Base? Depending on your situation, that might be the simplest solution.