[Solved] Undesired sort order after import of CSV file

[zmix]

I found that I cannot sort by date because the data didn't include leading zeros, so when I do sort by date (M/D/Y) it looks like


1/5/2023
1/25/2023
10/20/2023
10/25/2023
11/12/2023
12/12/2023
3/5/2023
4/6/2023

It's a mess..


Is there a way to correct the lack of leading zeros?

Screen Shot 2024-03-23 at 6.44.06 PM.jpg (119.91 KiB) Viewed 794 times

Edit: Changed subject, was Cannot sort by Date because the data didn't include leading zeros.. help?  Make your post understandable by others  -- MrProgrammer, forum moderator

[zmix]

I posted here about sorting by date when there wasn't a leading zero on the month, but sorting an amount is just as confusing.

I tried to sort by the amount in the 'net' column, but it's bizarrely inaccurate...

Screen Shot 2024-03-23 at 6.39.12 PM.jpg (71.67 KiB) Viewed 796 times

[FJCC]

Your dates and your numbers have entered the cells as text and are being sorted alphabetically. How is the data being entered into Calc?

[zmix]

Your dates and your numbers have entered the cells as text and are being sorted alphabetically. How is the data being entered into Calc?

It was a .csv file provided by Paypal that I imported.

[robleyd]

I've merged your questions as they are both the same problem; your "dates" are in fact stored as text and so are the "numbers" in your second post. You caa use Ctrl+F8 or View | Value Highlighting to confirm this. Text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.

See [Tutorial] Ten concepts that every Calc user should know for more information on types of data in Calc - or any other spreadsheet.

[Tutorial] Text to Columns may help you with a fix. If the data was imported from e.g. a CSV file, make sure in future you check the option Detect Special Numbers.

[FJCC]

In the Text Import dialog that you should get as part of importing a csv file, there is a table at the bottom showing a few rows of data and the data type under which they will be imported. The default type is Standard. Right click on the word standard and pick something appropriate for the column. Your date column should work with the mdy setting and your numbers should work with US English.
And, as robleyd said, click Detect Special Numbers as a matter of course.

[zmix]

In the Text Import dialog that you should get as part of importing a csv file, there is a table at the bottom showing a few rows of data and the data type under which they will be imported. The default type is Standard. Right click on the word standard and pick something appropriate for the column. Your date column should work with the mdy setting and your numbers should work with US English.
And, as robleyd said, click Detect Special Numbers as a matter of course.

Thank you for the help..

I ended up using Microsoft Excel and when I imported the csv data it immediately recognized that there was numerical data entered as text (thanks Paypal geniuses!) and asked if I wanted them converted before importing the data.

It seems that Open office could use some help screens like that..!