As part of a spreadsheet I have a column with cells containing the text:
1
1a
2
2a
3
3a
I export the spreadsheet to a CSV file for manipulation by another program then reimport it into Excel. Those cells now appear as:
1
1a
2
2a
3
3a
because the numerals have been converted from alphanumerics to values. This sorts into:
1
2
3
1a
2a
3a
which is obviously Not What I Want. How can I convert values into text?
Highlight the column, right click, Choose Format Cells, and choose “Text”
Zev Steinhardt
It worked. Thanks!
When you open the .csv in Excel, you can also specify that columns should be formatted as text, thus eliminating that step after the file is imported.
My copy of Excel 2002 does not allow me to specify column formatting when importing a CSV file, but that turns out to be irrelevant because of how “helpful” Excel insists on being.
New problem! I created a flatfile database in Excel. A typical tuple is made up of seven fields:
name,qty,speaker,zone,amp,keypad,wireless
Since fields “zone” and “amp” are formatted as text when I export it as a CSV file a record should look like:
FAMILY DINING ROOM 101,2,Boston VRi580,“1-1”,“1-1”,Yes,No
but instead becomes:
FAMILY DINING ROOM 101,2,Boston VRi580,1-1,1-1,Yes,No
which could mean “zone” and “amp” have become calculated fields, a feature the CSV format lacks, or should just be treated as text, which is what I want. However, Excel, in its infinite wisdom, interprets them as dates (another feature not found in CSV files) when the file is imported. In fact, in this example 1-1 is read specifically as January 1, 2005, where the current year has been assumed because stupid ol’ me apparently left it off.
I edit the CSV file, thinking I’m forcing those two fields to be treated as straight text because I put them in double quotes, but Excel doesn’t care. No, sirree, that’s still a date. Reformat it as text? Okay, now the date is 38353 since January 1, 2005 is 38353 days after January 1, 1900.
However, if I rename the file from FILENAME.CSV to FILENAME.TXT everything works fine. I don’t even have to add in the quote marks because the wizard UncleBeer mentioned pops up and I can tell Excel what to do. Better yet, I tell it what to do AND IT DOES IT.
How can I force Excel to handle CSV files correctly?
Is there any reason that you have to do that in Excel? Access would be a lot easier to configure to handle this correctly.
Um, because it was SUPPOSED to be a quick and dirty way to make a single use flatfile database from text extracted from Autocad. Nothing is, or should be, quick and dirty in Access; a well-designed relational database should be thoroughly thought out.
If it’s any comfort, and it shouldn’t be, I also manipulate the files in QBASIC and the Norton Editor (subject of another question, maybe later today). By the time I’m done with them these poor files don’t know what hit them.
Change the file extension from .csv to .txt. Start Excel and open the .txt file. You’ll find the option I spoke of earlier for formatting cells in the .txt conversion utility. Opening a .csv, just takes you directly to Excel and imports the file without allowing you to control what it’s doing.
Like I said in my second-to-last paragraph, Unc. 
Not that everything is fixed–it still thinks those values are dates and gives me an input error on zone 1-32 because everybody knows there wasn’t a January 32, 2005. :rolleyes: And being errors they don’t sort right. At least everything LOOKS okay.
I’m bringing in an old laptop with DOS and Quattro 1.0. That program knew how to do this without thinking it was smarter than me.
The correct solution here is to upgrade from Excel to OpenOffice.org. The OOo spreadsheet program has a much nicer wizard for handling text imports than Excel, and it brings it up when you open a CSV file. (It will, however, open a .txt file in the word processor.)
However, if you perversely cling to the old ways, the text import wizard that Excel brings up should still hold a solution to the problem. I’m basing this on Excel 2000, but I don’t think the wizard has changed that much.
The OOo spreadsheet does all of that in one dialog, by the way.
Oh. Now I see it. I’m not too observant sometimes, eh?
Anyway, you can control the import of a .csv. Try this:
After starting Excel, from the menu bar, select:
Data ==» Import External Data ==» Import Data
and go from there. Importing a .csv through this method will present you with same options (and some additional ones) that you’ll see when you’re trying to open a .txt directly.
Yeah. Just like I said in post #8. And dropzone pointed out to me that he already know that and had mentioned it in post #5.
Ya know, lotsa people don’t have admin rights on their office machines. Additionally, most organizations frown on people using non-standard software. Makes desktop support from the IT department much more troublsome and costly. Which makes your “correct” solution anything but in most work environments.
Yes, and just above my post, drop indicated that Excel was still treating the imported values as dates. I thought it would be helpful to offer step-by-step directions to correct that specific problem.
As to the rest–that’s just my tounge-in-cheek way of endorsing the open source alternative. I really do prefer the OpenOffice spreadsheet for most things (although I still use Excel when I need to do complex macro stuff), and it does handle this particular case more neatly. I’m well aware of the “Microsoft shop” nature of many corporate IT groups–I can’t even get the web people at my office to stop putting IE-only traps in their pages. Still, if he was ready to resort to Quattro, things had grown even more desperate than calling the Helpless Desk. 
Now, THAT worked! The problematic cells are all formatted as text and not a single pretend date anywhere. Thanks!
I’ve used Open Office/Star Office here and at home and, just like MS Office, nothing is perfect. Our specification document, for instance, is so heavily linked and formatted that when tou open it in OO it is barely recognizable.
Don’t diss the first generation of Quattro! It’s surprisingly capable for its age and creates the cleanest DBF files I’ve seen.