Dear Excel
-
Is there any other software that can manipulate csv files in columns without thinking it knows better about what the data is? Open a csv that has a column of numbers, 8 digits with leading zeros. Of course the leading zeros go away. Change format of data, too late, all leading zeros have gone away. Fiddle about, get things nice, first column is blank, save as csv with a new name; Not only has it decided to remove the first column (obviously it being blank meant I didn't really need it), but my original csv file is now empty. I accept Excel can do some wonderful things, and that I probably don't know how to use it properly. But it does seem to make it very difficult to do the easy things.
Every man can tell how many goats or sheep he possesses, but not how many friends.
Let me see if I can sort this out... You have a column in an excel file that has leading zeroes, and you want to retain them when you export to a csv file? Simple. Save the spreadsheet, then reformat the column to a 'Text' column and export to a csv file. That way you retain the lead zeroes. Simples
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^] Trolls[^]
-
Let me see if I can sort this out... You have a column in an excel file that has leading zeroes, and you want to retain them when you export to a csv file? Simple. Save the spreadsheet, then reformat the column to a 'Text' column and export to a csv file. That way you retain the lead zeroes. Simples
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^] Trolls[^]
No, I have a column in a csv file that has leading zeros, when I open it in excel they get dropped. Reformatting the column to text does not bring them back. I had to change the format to custom and then define it as 00000000 for them to come back. I still want to know where the first column went to when I saved it too.
Every man can tell how many goats or sheep he possesses, but not how many friends.
-
Is there any other software that can manipulate csv files in columns without thinking it knows better about what the data is? Open a csv that has a column of numbers, 8 digits with leading zeros. Of course the leading zeros go away. Change format of data, too late, all leading zeros have gone away. Fiddle about, get things nice, first column is blank, save as csv with a new name; Not only has it decided to remove the first column (obviously it being blank meant I didn't really need it), but my original csv file is now empty. I accept Excel can do some wonderful things, and that I probably don't know how to use it properly. But it does seem to make it very difficult to do the easy things.
Every man can tell how many goats or sheep he possesses, but not how many friends.
I know you really don't want to hear this but - never ever ever ever use excel to open a csv file then save in csv format. I learnt some time ago as you have about the terrible tendency of excel to think it knows better when it comes to csv files. This is a known issue with excel - well for those who know about it anyway ... :sigh: If you need to use an office program with csv files - use Access to read in the csv file and format it for excel...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
I know you really don't want to hear this but - never ever ever ever use excel to open a csv file then save in csv format. I learnt some time ago as you have about the terrible tendency of excel to think it knows better when it comes to csv files. This is a known issue with excel - well for those who know about it anyway ... :sigh: If you need to use an office program with csv files - use Access to read in the csv file and format it for excel...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
What I really want is a column based editor that doesn't attempt to format anything. What I was trying to achieve was this; A .csv with six hundred thousand rows and 40ish columns. Another .csv with a similar number of rows and 1 column. The one column needed to replace one of the columns in the original file. I could write something to do that, but I stupidly thought that I could open both, select and copy a column from one and then paste it over a column in the other before saving. Of course I can do that. It is just the content gets changed whilst I'm doing it.
Every man can tell how many goats or sheep he possesses, but not how many friends.
-
What I really want is a column based editor that doesn't attempt to format anything. What I was trying to achieve was this; A .csv with six hundred thousand rows and 40ish columns. Another .csv with a similar number of rows and 1 column. The one column needed to replace one of the columns in the original file. I could write something to do that, but I stupidly thought that I could open both, select and copy a column from one and then paste it over a column in the other before saving. Of course I can do that. It is just the content gets changed whilst I'm doing it.
Every man can tell how many goats or sheep he possesses, but not how many friends.
Not the most elegant way - but if you have Microsoft Access try using Access to do this. It comes with an import wizard which will let you specify the column delimitation and column type. Oracle PL/SQL Developer and SQL Server also both have wizards to import csv data... Excel just gets too clever sometimes...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
Not the most elegant way - but if you have Microsoft Access try using Access to do this. It comes with an import wizard which will let you specify the column delimitation and column type. Oracle PL/SQL Developer and SQL Server also both have wizards to import csv data... Excel just gets too clever sometimes...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
Just checked, I do have Access. It is nearly 11 years since I had anything to do with that. And that was only fleetingly. I spent far more time with Lotus Approach. Think I'll write a csv editor that acts dumb.
Every man can tell how many goats or sheep he possesses, but not how many friends.
-
What I really want is a column based editor that doesn't attempt to format anything. What I was trying to achieve was this; A .csv with six hundred thousand rows and 40ish columns. Another .csv with a similar number of rows and 1 column. The one column needed to replace one of the columns in the original file. I could write something to do that, but I stupidly thought that I could open both, select and copy a column from one and then paste it over a column in the other before saving. Of course I can do that. It is just the content gets changed whilst I'm doing it.
Every man can tell how many goats or sheep he possesses, but not how many friends.
You can use ultraedit[^]: - you can activate the column mode. - you can set some macros that would replace the "," for n tabs. - you can select the columns freely and paste them into excel. All done! :thumbsup: Not the best, but if you are not lucky with excel neither with access...
[www.tamelectromecanica.com] Robots, CNC and PLC machines for grinding and polishing.
-
What I really want is a column based editor that doesn't attempt to format anything. What I was trying to achieve was this; A .csv with six hundred thousand rows and 40ish columns. Another .csv with a similar number of rows and 1 column. The one column needed to replace one of the columns in the original file. I could write something to do that, but I stupidly thought that I could open both, select and copy a column from one and then paste it over a column in the other before saving. Of course I can do that. It is just the content gets changed whilst I'm doing it.
Every man can tell how many goats or sheep he possesses, but not how many friends.
If you reformat the column using custom format "00000000" and then save as CSV you will get your leading zeros back. Alternatively you could change the file extensions to
txtand then when you import them into Excel ensure that the column with the leading zeroes is treated as text.The best things in life are not things.
-
Leave my formatting alone you utter, utter cunt.
Every man can tell how many goats or sheep he possesses, but not how many friends.
Open the CSV in Notepad++, select all + copy, create new Excel document, paste, Data > Text to Columns, Comma Delimeter, select column with leading zero numbers, set column data format to "Text", press "Finish" to exit wizard and you are done (leading zeroes will be maintained).
Martin Fowler wrote:
Any fool can write code that a computer can understand. Good programmers write code that humans can understand.
-
Leave my formatting alone you utter, utter cunt.
Every man can tell how many goats or sheep he possesses, but not how many friends.
The easiset way to do this is to use the data import wizard. Open a blank workbook and select Data|Get External Data|From Text in the menu bar. In the last step, change the data type from "General" to "Text" for the columns you want to preserve the leading zeroes.
L'enfer, c'est les autres - Jean-Paul Sartre
Und wenn du lange in einen abgrund blickst, blickt der Abgrund auch in dich hinein - Friedrich Nietzsche -
Leave my formatting alone you utter, utter cunt.
Every man can tell how many goats or sheep he possesses, but not how many friends.
I can see a need for a csv editor tool. How often do we resort to excel to view, manipulate or otherwise open a csv file. Odds are if it contains numerics or even worst dates your screwed, you need to rat through that bloody ribbon thingy to try and find out how to format the column. I HATE EXCEL - let me emphasize that I HATE EXCEL
Never underestimate the power of human stupidity RAH
-
Leave my formatting alone you utter, utter cunt.
Every man can tell how many goats or sheep he possesses, but not how many friends.
I'd approach such a problem armed with UltraEdit[^] Its column mode is ideal for such slice'n'dice operations. I don't know if they still have a free version - I paid up about 6 years ago, and took a couple of the free upgrades, then let it sit at version 13. Still in daily use. And yes, I do drag CSVs into and out of Excel from time to time. The import/change column format trick Sahir Shar mentioned above works for me in these cases. Cheers, Peter
Software rusts. Simon Stephenson, ca 1994.
-
I can see a need for a csv editor tool. How often do we resort to excel to view, manipulate or otherwise open a csv file. Odds are if it contains numerics or even worst dates your screwed, you need to rat through that bloody ribbon thingy to try and find out how to format the column. I HATE EXCEL - let me emphasize that I HATE EXCEL
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
How often do we resort to excel to view, manipulate or otherwise open a csv file
I have learnt to not use csv files and to discourage others from sending them to me. I now use pipe delimited files which Excel can manage very easily - other than that tab delimited is a very good choice. I think that the csv file format, despite it being very popular is probably not the best choise of file delimiter - as anything containing an address will have a comma that may not be handled correctly by the machine handling the csv file. I use Excel daily and it is a brilliant piece of software for analysing and graphing data - the pivot tables/graphs are a godsend. However Excel is shite with csv files - part of the reason is that there are different rules for how to process csv files(there must be an ISO standard... which nobody keeps to) and Microsoft use a set of rules that tend to mess with csv files.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
Mycroft Holmes wrote:
How often do we resort to excel to view, manipulate or otherwise open a csv file
I have learnt to not use csv files and to discourage others from sending them to me. I now use pipe delimited files which Excel can manage very easily - other than that tab delimited is a very good choice. I think that the csv file format, despite it being very popular is probably not the best choise of file delimiter - as anything containing an address will have a comma that may not be handled correctly by the machine handling the csv file. I use Excel daily and it is a brilliant piece of software for analysing and graphing data - the pivot tables/graphs are a godsend. However Excel is shite with csv files - part of the reason is that there are different rules for how to process csv files(there must be an ISO standard... which nobody keeps to) and Microsoft use a set of rules that tend to mess with csv files.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
I don't have a problem with the delimiter, don't really care for tab being a non visual character. My problem is the changing of the format of the data. Try importing a file with 12 character numerics and immediately export it stupid program formats the numbers to engineering format. Just on of many issues I have with excel as a ETL medium. Granted it is an excellent spreadsheet - DD swears by it but we need a tool to manage CSV files ONLY.
Never underestimate the power of human stupidity RAH
-
Mycroft Holmes wrote:
How often do we resort to excel to view, manipulate or otherwise open a csv file
I have learnt to not use csv files and to discourage others from sending them to me. I now use pipe delimited files which Excel can manage very easily - other than that tab delimited is a very good choice. I think that the csv file format, despite it being very popular is probably not the best choise of file delimiter - as anything containing an address will have a comma that may not be handled correctly by the machine handling the csv file. I use Excel daily and it is a brilliant piece of software for analysing and graphing data - the pivot tables/graphs are a godsend. However Excel is shite with csv files - part of the reason is that there are different rules for how to process csv files(there must be an ISO standard... which nobody keeps to) and Microsoft use a set of rules that tend to mess with csv files.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
GuyThiebaut wrote:
I now use pipe delimited files
GuyThiebaut wrote:
as anything containing an address will have a comma that may not be handled correctly
Bloke at the last place I worked used pipe delimiter for everything he wrote. That was fine until someone used a pipe in an address. When I was creating files that were only ever going to be used by programs then I used CHR(3) as a delimiter.
Every man can tell how many goats or sheep he possesses, but not how many friends.
-
GuyThiebaut wrote:
I now use pipe delimited files
GuyThiebaut wrote:
as anything containing an address will have a comma that may not be handled correctly
Bloke at the last place I worked used pipe delimiter for everything he wrote. That was fine until someone used a pipe in an address. When I was creating files that were only ever going to be used by programs then I used CHR(3) as a delimiter.
Every man can tell how many goats or sheep he possesses, but not how many friends.
I was asked what the best delimiter was recently and I said "a database"... The room went silent - either because they thought I was a pretentious geek or because in the end databases are probably the best way to exchange data. Or xml files...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
The easiset way to do this is to use the data import wizard. Open a blank workbook and select Data|Get External Data|From Text in the menu bar. In the last step, change the data type from "General" to "Text" for the columns you want to preserve the leading zeroes.
L'enfer, c'est les autres - Jean-Paul Sartre
Und wenn du lange in einen abgrund blickst, blickt der Abgrund auch in dich hinein - Friedrich Nietzsche