Excel Question

[Ben E Lou]

I need to help someone who has a csv file that has dates in it, formatted as MMDDYYYY (04262007). I need to be able to go in and edit the file, but it is critical that the dates retain the leading zeros. The files that will need to be edited will have hundreds of rows, and 190 columns, so opening it with Notepad as a text file is completely impractical. The best-case scenario here would be for a way to configure Excel so that the default field format is "Text", rather than "General."

Help?

[Coder]

Do you have any way of affecting how the csv-file is written? Having "" around the "datefield" would indicate to Excel that it's a text-field I would think.

Other than that, when you open the file, is it immediately put into columns or are you faced with all data inside one column and you have to do "Text to Columns"?

If it's the latter, you have the chance to indicate what datatype a particular column is.

[JonInMiddleGA]

Originally Posted by SkyDog

The best-case scenario here would be for a way to configure Excel so that the default field format is "Text", rather than "General."
Help?

Maybe I'm making this simpler than it actually is, but if this is the optimum scenario, the third step of the import wizard allows you to define a column as text rather than general.

(I'm assuming that this csv is consistent & is ready for an easy import into Excel, and that the field with the leading zeroes needed is consistent across the entire file).

[Ben E Lou]

Originally Posted by Coder

Do you have any way of affecting how the csv-file is written? Having "" around the "datefield" would indicate to Excel that it's a text-field I would think.

Other than that, when you open the file, is it immediately put into columns or are you faced with all data inside one column and you have to do "Text to Columns"?

If it's the latter, you have the chance to indicate what datatype a particular column is.

No. I have no control. It is written by a third party.

To answer JIMGA's question, I don't get the wizard. It just opens straight up in Excel. The best workaround so far is to change it to .txt, then use the wizard to import in Excel, and change the format of that field. However, my hope is that there's some global setting in Excel that can be changed to do this before the file has to be opened.

[JonInMiddleGA]

Originally Posted by SkyDog

However, my hope is that there's some global setting in Excel that can be changed to do this before the file has to be opened.

All I can say on that is that, if there is, I've never noticed it.

[Coder]

What happens if you Open the file through Excel's File->Open menu rather than double-click on the file? Don't you get the import-wizard then?

[Ben E Lou]

Originally Posted by Coder

What happens if you Open the file through Excel's File->Open menu rather than double-click on the file? Don't you get the import-wizard then?

No. It opens right up.

[Wolfpack]

Well, another way would be to open it up in Excel, then select the column with the date information, then go into the column's "Format Cell" properties and select "Custom" format for the "Number" and then type in the date/time format you like, e.g. "mm/dd/yy hh:mm:ss AM/PM" in the box available, then save. Excel tends to want to force such numbers into a default format of some kind, so this is a way to get around that. You'd probably need to save as an Excel file to retain the formatting, however. Otherwise, you'll need to re-apply the format change every time you open the CSV.

[Simms]

Open Excel.

Go to Data --> Import External Data --> Import Data.

Select your file.

You should get the wizard.

[Coder]

What about this:

1) Open Excel, empty spreadsheet before you
2) Go to Data->Import External Data->Import Data
3) Select Textfiles and CSV files and open your file

Any luck?

[Coder]

Simms stole my thunder!

[GabeRivers]

If the cells that are "formatted as MMDDYYYY (04262007)" are contained in the same column, you can do the following;

1. import into excel

2. In a temporary new column at far left of your spreadsheet, you will enter a formula that you will fill down:
=text([click on cell in same row with date reference],"00000000")

3. After filling down the formula, select the entire new column with the new formula entries (from top cell thru bottom); copy.

4. Now select the top cell of the column where the original date entries are and do a paste special/values.

5. Finally, delete the formula column and save your spreadsheet.

There is probably an easier way, but this will work.