What software can be used for editing CSV files? Notepad is gonna work smoothly? I have been using MS Excel but now I have this issue. When I put date like this: 090913 whcih represents 9th of Sept. 2013... I don't see the first zero in front of the date... Excel hides it? and the zero is still there? So I don't have to not worrying? I will use CSV file later when I will create Java application that will read from the CSV file....
asked Oct 10, 2013 at 12:31
-
first I would recommend you not use MMDDYY format, and use a 4digit year just to avoid ambiguity. that said, a text editor is the safest bet, but awkward. Excel is the better option, but since it does try to treat the data as numeric, you will have to tell it that the column is text, or a date for it to display correctly.– Frank ThomasCommented Oct 10, 2013 at 12:37
Add a comment
|
3 Answers
Any text editor (Notepad, Notepad++, Wordpad, Word, etc...) will edit a .csv file. They're simply text documents with a specific format.
Your bigger issue with Excel is likely based on how Excel deals with dates. Excel stores dates as serial numbers, and then formats those numbers based upon your format settings. So, to Excel 2013-09-09 (you should try and use ISO compliant dates) is actually 41526 (number of days since 1900-01-01).
If you need to bring a specifically formatted date for your Java app, then I would suggest using a text formula like: =Text(A1,"DD/MM/YY") where A1 is your date location. This should return a "properly" formatted date for your export/import.
-
1Just want to add that there is now a CSV Lint plug-in for Notepad++ github.com/BdR76/CSVLint which can reformat datetime and decimal formats– BdRCommented Mar 18, 2022 at 22:08
You can use Excel: You have to set the correct format for the cells, tell Excel it is a number (then it will not be interpreted as a date). Which version of excel are you using?
Besides: notepad is ok, I would rather use (assuming you are running Windows) notepad++ which is a much more powerful editor. And it's for free.
You could try using CSVed. The benefit of this (or any other CSV editor) over plain text editors is that if your column contents are different widths on each row then this will display in a grid therefore aligning your data and making it easier to see.
CSVed displays your text as is, and does not remove leading zeros.
A word of warning: there is a facility to modify the date time formats but that is for changing the contents of the file, not for changing the way it is displayed.
