Late Night PC

Nope. All of this does not deal with the fact that Excel cannot remember formatting in a CSV file. Once you save it off as CSV, close the file and reopen it, it's back to interpretting that cell/column as a number and lopping off trailing or leading zeros. I've opened the file in Textpad and sure enough, those columns have 17.0. When I open in Excel it automatically shows it as 17 (right-justified to indicate a number). If I reformat that column as text it still only shows as 17 (now left-justified to indicate text, but still no .0). Reformatting at any point once the document has been opened does not affect the fact that the .0 has been lost by Excel when the file is in memory. If I then try to save the file as csv again, the .0 has been permanently lost in the file. The ONLY way I could get this to work the way I want is to save the file as a .txt file. In this way, when I try to open the file in Excel it will send me into the text file import wizard and I can specify that those columns should be considered as text (if I don't then the same thing happens when opened as csv). However there is an additional problem with that! The contents of a particular column contains multi-line information (same as when you press alt-enter in a cell's contents). This means that the contents of hte cell have multiple lines of information broken up by line feeds (but not carriage returns). I've opened the CSV file and each full line is broken up by CR LF (0x0D 0x0A) and each multi-line value in the cell is broken up by LF (0x0A). When opened as a CSV file in Excel, the file is properly parsed, but when it's opened as a text file in Excel, each LF is interpreted as a new line indication. In my opinion it's totally messed up. Try it...start with a Blank workbook in Excel and fill in the following cell contents: A1: 17.0 (formatted as Text) A2: Put some text in there that contains multiple lines A3: Blah B1: 16.3 B2: Blah B3: Blah Now save this off as a CSV file (carefully answer their confusing dialog boxes when doing this). Then close the file (do not save it again - even though Excel prompts you to do this and you've made zero changes). Now look at it in a [url=http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm]hex editor[/url] and you'll see that A1 shows as 17.0 properly, the 0x0A for the contents of A2 is there, etc. Everything looks as it should. Now open it in Excel. See how A1 shows as 17? Now close it (don't save!), rename the file as TXT and open it in Excel. See how the contents of A2 show up as multiple lines during the import (regardless of playing with "File origin"). If you're able to figure out a way to import this file properly into Excel, let me know. Regards, Jeff

n/a

• parent
• reply