.csv

[skyladraco]

One of the users I support is copying and pasting data into an Excel 2003 or 2007 file, she has both, and needs to keep the formatting in 2 columns. One for the date to be shown as 09/03/08 and the other to show the numbers with two numbers after the decimal point (352.20). I know that when you save the file as a .csv it will not save the formatting, but she swears that were able to do this before in Excel 2003 and in 2007. Any ideas?

 

[SydneyGeek]

Hi, and welcome to the Board!

Change the extension to .txt, which will launch the import wizard when you open the file.
Define the delimiter as Comma.
Go through the fields and you should be able to set the formats. Make sure that the first column is a Date in MDY format.
You should also be able to set the decimals in the second column.

Denis

 

[Joe4]

Alternatively, instead of changing the file extension to a .txt (which works well), you could also open the file in Excel by going into Excel first, then going to the Data drop down menu, and selecting the "Import External Data" option and browsing to and opening the .csv file. This will also invoke the Import Wizard, where you follow the same steps that Denis laid out (in regards to formatting).

 

[skyladraco]

Thank you for the warm welcome and the helpful information. I don't know if I'm doing something wrong or not, but no matter how I've gone about it when I import the .txt the .csv file still only shows one number after the decimal point and the date goes back to m/d/yyyy format instead of mm/dd/yy.

I will talk with the user today and see if I can get ahold of the original document that she has been coping the information from and see if I can figure it out.

Thanks again,
Skyla

 

[Joe4]

I don't know if I'm doing something wrong or not, but no matter how I've gone about it when I import the .txt the .csv file still only shows one number after the decimal point and the date goes back to m/d/yyyy format instead of mm/dd/yy.

The methods we proposed just ensures that dates are imported as dates and numbers are imported as numbers. However, there are numerous different date/numeric/text formats, and importing any sort of text will always resulted in the "default" formats being applied. So you will need to change the format of the columns to show the appropriate date or numeric format (if you want two decimals or a mm/dd/yyyy date format). This can be done easily using the Format menu (Cells -> Number).

If you want the information imported to appear exactly as it does in the text file you are importing from, you can do this by importing every field as Text. However, then all dates and numbers are imported as text, which can make calculations involving these fields a bit more compelx than they need to be (you might need to use type conversion functions on them).

 

[skyladraco]

I have made the changes to get the formatting the way I want it, however when I save it, it does not save the formatting. That is my dilemma.

 

[Joe4]

Are you saving the file as an Excel file or a CSV file?
Then, how are you reviewing your saved changes? If you are opening up the CSV file in Excel, it may give you a accurate representation. Take a look at the file in a text editor like NotePad to see what is truly there.

 

[Richard Schollar]

If you are saving into a csv file, you will need to confirm whether it is saving it or not by opening up that file in a text editor (such as Notepad). Every time you open up a csv file within Excel (ie from File>Open) you will get Excel interpreting the data within each field according to the General number format, which means the data you see may not necessarily reflect what you want to see (ie you may have 1/1 in your file which Excel will interpret as a date).

 

[skyladraco]

Well, it has become obvious that the .csv file will not view with the correct formatting in Excel, but is correct when viewed in a text document (.txt). I showed this to the user and she is going to try uploading it in the .csv format after she has saved the changes and let me know if it is accepted by the bank. She will not be able to test it until Tuesday next week, but I will let you know how it goes.

Thank you all for your assistance. You ROCK!!!
Skyla