@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)”