Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012

    Unanswered: Date Format Issue in CSV


    I have a CSV file (no choice in how I receive it) that has a column of CUSIPS (numeric identifiers that identify stocks) and a list of dates in MM-DD-YYYY format. I need to change the date format to YYYY/MM/DD. I can do this easily if I open in excel but then excel removes leading zeros and converts all the CUSIPs to scientific notation which then makes any output unreadable by the application that I upload into.

    Anybody have a work around for how I can change the date format while maintaining the integrity of the CUSIPs?

    Thanks very much for any assistance!

  2. #2
    Join Date
    Sep 2008
    London, UK
    Try this -

    • Change the file extension from csv to txt

    • Then open Excel and go to File > Open
    • Navigate to the folder where the file is saved
    • At the bottom make sure Files of Type: "All Files" is selected
    • Open the txt file
    • The Text Import wizard window will appear
    • Choose file type "Delimited" > Next
    • Choose Comma delimiter > Next
    • In the data preview pane select the column with the YYYY-MM-DD dates and then in the Column data format pane choose Date MDY
    • In the data preview pane select the column containing the cusips and then in the column data format pane choose Text
    • Finish

  3. #3
    Join Date
    Mar 2012

    Bringing in the CUSIP column as text like that keeps those in their original form. The date column did not change format but I was able to adjust that in excel and maintain the text format of the file. Thanks again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts