microsoft excel - What software can I use to edit CSV files?
2014-07
Einsteins Grandson
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....
dav
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.
Alf
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.
tinman
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.
Holgerwa
When I double-click on a .csv file, it is opened in Excel. The csv-files have columns delimited with semicolons (not commas, but also a valid format).
Using a German Windows/Excel setup, the opened file is displayed correctly, the columns are separated where the semicolons existed in the csv-file.
But when I do the same on an (US-) English Windows/Excel setup, only one column is imported, showing the whole data including the semicolons in the first column.
(I don't have an English setup available for tests, users have reported the behavior)
I tried to change the list separator value in Windows regional settings, but that didn't change anything.
What can I do to be able to double-click-open those CSV-files on an English setup?
EDIT:
It seems to be the best solution not to rely on CSV-files in this case. I was hoping that there is some formatting for CSV-files that makes it possible to use them internationally.
The best solution seems that I'll switch to creating XLS-files.
laurent
Those files are distributed? downloaded? Locally generated?
I think the unique way to solve your issue without access to the user's PC and without him to have to do something different of "double-click" on the file is to convert the files to .xls before distibuting or placing to download or to generate a .xls (or another format that works) instead of a .csv file. You can do that with a script or manually with a working Excel, depending on the number of files and the way they are generated.
slhck
Add this on the first line of the CSV file:
sep=;
or
sep=,
This will override system setting ("list separator character") and Excel will open the file correctly.
DMA57361
You can use the Text Import Wizard. This does not work on a double-click-open, but is the best I can come up with that doesn't involved messing with lanugage settings.
In Excel 2003 goto Data -> Import External Data -> Import Data on the menu bar (can't say where it is on 2007, I don't have that to hand at the moment). Find and select your CSV file.
When it opens you get the Text Import Wizard, which allows you to specify the delimiter(s) to use.
harrymc
See this article:
Trouble with Opening CSV Files with Excel? The Comma and Semicolon Issue in Excel due to Regional Settings for Europe
Which offers 3 solutions:
- Change the CSV file extension to TXT (when you open a TXT file with Excel it will start the text file import wizard where you can choose the delimiter)
- Replace all “,” with “;” in your CSV file(s)
- Change your regional and language settings
As you have only changed the list separator value in Windows regional settings, I note that the article says to enter “.” for Decimal Symbol and “,” for List Separator.
If you feel comfortable with VBA, then see:
Opening semicolon delimited CSV file with VBA in Excel 2000
Exporting And Import Text With Excel
Josephine
Had this problem last week.
The person who is trying to open the .csv file needs to change some settings within their control panel.
Control Panel > Regional and Language Options
Within the Regional Options tab select the customize button. Where it states "list separator" you'll find it has a comma in it. This needs to be changed to a semicolon (;)
Then select apply and then ok.
This should resolve the issue for the user.
Regards, Josie
Alex
The best way I have tried is to set your computer regional settings as below:
- in the "Standard and Formats" choose "English (United States)";
- in the "Locations" choose "United States".
It works for me, just give it a try.
DHayes
If there are no other commas in the file, you can simply do a find/replace in a text editor on the semicolons to turn them into commas. Treat as normal csv file after that.