The Text Import window allows you to identify the data type for Calc. It is a good idea to always define the data type for dates. If the csv contains dates such as “09/01/2020” that you know is 1st September 2020 (as above) then it must be imported as column type Date (MDY). If the filter is set as another date type you will import only the original text, not dates
It can be subsequently formatted as YYYY-MM-DD but if you mis-identify the type during import it gets imported as just the original text.
Edit 2020-08-012
Open a new spreadsheet in Calc, in the menu select File > Open, in the dialog that opens select your csv file and click Open.
A new dialog window will open called Text Import, in this you need to select the delimiter (if it already shows the text in columns then you are probably OK), make sure “Detect special numbers” box is ticked so the money is imported as money. In the column for Date you need to tell LibreOffice what the format of the date is (it can’t tell if 1/1/19 is DMY or MDY) so click on the column and then you can change the Column type to Date (DMY), OK out.
Having successfully converted the text to dates and dollar amounts you can change the date column to your desired format, in your question you wanted YYYY-MM-DD. Select the date column, in the menu click Format > Cells..., in the dialog that opens select the Numbers tab, on the left hand side, Category, choose Date, in the next column, Format, scroll down and select 1999-12-31, OK out. Save As [SomeName].odt
As explained above, this doesn’t work. I thought it was explained. However, thanks for your reply.
I assume PayPal formats its dates in US format so the Text Import filter for the CSV must be set to Date (MDY) for all date columns.
I think that the whole point of this conversation was that it doesn’t work. It works in OpenOffice, but not in LibreOffice. Was that not explained above? But thanks for your reply.
Are you saying that the PayPal CSV date format VARIES between MDY and DMY in the same file?
Can you submit a CSV file that works in Office or OpenOffice but fails in LibreOffice?
CSV is a comma separated value text file and assumes that all values are text, with no special formatting applied to any entry. Dates are also in text format and if you want a consistent sortable date field, you need to have the format identified during import (is it MDY, DMY or YMD?). Then you can format the resultant column to your own format, such as YYYY-MM-DD.
Is 04/06/2020 = April 06 2020 or is it = 04 June 2020? If the format changes within the same file, how can a person, let alone LO determine which format is correct for each entry?
It’s not the csv file that’s the problem since Excel and OpenOffice can handle the import (Excel much more easily than OO). But LibreOffice is unable to process the import properly, leaving the dates broken or incomplete. To me it looks like a LibreOffice problem, not a csv problem. Here’s a better example:
Date Gross Fee QTY Ship Net
2019-01-01 $177.40 -$6.86 12 $10.00 $170.54
2019-11-01 $309.05 -$11.73 19 $32.00 $297.32
2019-12-01 $30.00 -$1.41 1 $0.00 $28.59
19/01/2019 $30.00 -$1.41 1 $0.00 $28.59
22/01/2019 $85.60 -$3.47 8 $6.00 $82.13
26/01/2019 $30.00 -$1.41 1 $0.00 $28.59
2019-02-02 $30.00 -$1.41 1 $0.00 $28.59
2019-08-02 $30.00 -$1.41 1 $0.00 $28.59
It may not show here clearly, but where the DAY is “12” or less, the date can be formatted, but the format is wrong (as YYYY-DD-MM), and it can’t be formatted any further. L-O moved the YEAR to the right place, but left the other characters in the wrong place. The dates are now unusable.
According to Paypal Forum the date codes since sometime in 2018 are in dd. mm. yyyy format. This format appears to match the unmodifed text dates in the text above, e.g. 22/01/2019. Therefore the date columns must be imported as Date (DMY) .
Ignore your current spreadsheet. Import the .csv into a new spreadsheet with the date column as type Date (DMY). When, and only when, that is imported into the spreadsheet you may change the format of the dates to anything you like.
I can import the file OK, but not sure what you mean by “date columns must be imported as Date (DMY)” ? I don’t see any choice for “importing as Date (DMY)”. Where do you see that? Once the file is imported, I can’t format the dates column. Here’s what I get:
Date Gross Fee Net
01/01/2019 $177.40 -$6.86 $170.54
03/01/2019 $22.95 -$1.15 $21.80
11/01/2019 $309.05 -$11.73 $297.32
12/01/2019 $30.00 -$1.41 $28.59
19/01/2019 $30.00 -$1.41 $28.59
22/01/2019 $85.60 -$3.47 $82.13
26/01/2019 $30.00 -$1.41 $28.59
02/02/2019 $30.00 -$1.41 $28.59
08/02/2019 $30.00 -$1.41 $28.59
09/02/2019 -$11.95 $0.00 -$11.95
09/02/2019 -$16.42 $0.00 -$16.42
I can format the numbers (into $ for example), but I can’t see how to format the dates into something useful.
"I can’t format the dates column"
Why not? Once the file is imported, have you tried to highlight the column of dates, then use menu Format → Cells…? Then under Category choose Date and an appropriate particular Format. Sometimes, I also have to choose Language = English(UK) to accomplish what I need, so you might have to choose something other than English(US).
Added – Also, when you perform the importation, be sure to check the Recognize Special Numbers box, in order to make sure the dates are recognized as something other than flat text.
@ve3oat There is a dangerous mix of date formats in the existing spreadsheet file. You can see that the 11th January 2019 has been imported as 1st November 2019 in the data given in the third comment above your comment. This is why it is easiest and fastest to start again from original data and import it correctly. Cheers, Al
@EarnestAl : Thanks, and I understand about the mix of date formats in the original data – A most unfortunate situation. The OP claimed that he couldn’t format the date column, which should not be the case. In case he does not want to go back and start over, my suggestion will work if he re-formats the column in sections according to the original format. (An unpleasant task, I know from experience.)
@ve3oat Presumably, he will want to import further PayPal data. It’s probably best to know how to import it correctly into a spreadsheet.
Solved! Finally! Thanks to EarnestAl, I’ve managed to wrangle this year’s PayPal summaries into a workable spreadsheet. I just couldn’t locate the solution suggested within the time limits to get the work done. But now, 6 years after fumbling with this data every January, I’ve got it figured out. Now I have to be able to remember it come next January!
Several years later and LibreOffice still hasn’t fixed this problem. Excel had it figured out 20 years ago, but LibreOffice is still fumbling around, not doing anything.
I don’t see any problem.
Import locale: English(Australia) because of the Dollars and D/M/Y dates.
Detect special numbers: Checked because of the Dollars and D/M/Y dates.
This dialog imports all numbers below the column labels.
Version: 24.2.7.2 (X86_64) / LibreOffice Community
Build ID: ee3885777aa7032db5a9b65deec9457448a91162
CPU threads: 4; OS: Linux 5.15; UI render: Skia/Raster; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded
LibreOffice programmers assume you/the user ist the intelligent part of the team wich gives instructions to follow, if possible.
.
What happens here is: You tell/don’t correct the settings to import perhaps US dates MDY (but we can see DMY would be right). LibreOffice will do this as told unless your month is bigger than 12. This lines are not converted (unless you find a way to tell LibreOffice the names for the 18 month of the year… So the are imported “untouched” as text. You should find an ’ before each value and text will not follow your formatting set for text.
.
On a general view the problem can never be solved, or can YOU tell what date is meant by 01-02-03 (or to make it easier for you 2003-01-02)
More interesting: Even you seemed to understand this two years earlier, we can read above:
Concerning your other idea you are completly wrong:
I used that version and just doing false imports was one reason to leave Excel in favor for OpenOffice. Ger mm an bank are partly using US defaults in csv-exports, others are using local dates an some standard yyyy-mm-dd. Excel never asked, while Open/LibreOffice did query me for csv-imports.
Nope, you find the leading semicolon if the text could be interpreted as a number. Importing the whole column explicitly as text, you would see the semicolon in front of the valid (but wrong) dates. The invalid dates are text anyway. They don’t need the apostrophe to mark the value as text.
Thanks - however, that’s not correct. You’re only showing the setup window. When it’s finished importing and displaying, it looks like this:
Not only is the date format confused, but the display changes, too, from hyphens to slashes, and back again.
Your import locale is “English(USA)”. I use “English(Australia)” and get all numbers right.
In your screenshot, A8 is interpreted as 1st of December 2024. 12th of January 2024 might be the actual date that was exported from the original database record.
The text in A9 indicates 18th of January 2024. However, in US Englissh context 18/1/2024 is not a valid date because of month number 18.
A8 should be 2024-01-12 in whatever date format
A9 should be 2024-01-19 in whatever date format
Alternatively, you may mark the date column in the import dialog as “Date(DMY)”