Results 1 to 2 of 2
  1. #1
    Join Date
    May 2015
    Posts
    1

    Answered: Excel corruption of .csv file data

    I've become aware of the limitations of opening .csv data files into

    Excel 2007 (earlier versions have the same issue). Various changes are

    made to the data as the file is opened, including at least the

    following:
    * Fields that appear to be numeric and have leading or trailing zeroes

    have the leading/trailing zeroes removed
    * Values that Excel thinks match a date such as 5-10 get assigned a

    data type of date. Reformatting the field's data type to text gives a

    crazy value such as 40308 (probably the number of days from 1/1/1970 to

    the date 5/10 in the current year)

    Saving changes to the original .csv file causes permanent data loss as

    the above changes are saved back to the file (What were they

    thinking?!).

    This issue can be worked around by using the Data -> Get External Data

    From Text tool, but I deal with .csv files so frequently that I want an

    easier way of getting my data opened without corruption. I came up with

    the below set of actions:

    When right clicking on a .csv file in Windows Explorer, a context menu

    appears with various options. It would be useful to have an option

    “Open without corruption using Excel” or similar, which does the

    following:

    1. Opens Excel
    2. Creates a blank spreadsheet
    3. Imports the select .csv file similar to the following commands:
    a. Select Data -> From Text
    b. Select the .csv file to be imported
    c. Choose the “Delimited” option
    d. Use comma as the delimiter character
    e. Select all columns (hold shift and click the right-most

    column header)
    f. Change the “Column data format” to Text
    g. Click Finish
    h. Put the data into cell A1
    4. And possibly even alter the currently open Excel file name from

    “Book1” (or whatever it is) to the name of the .csv file that was

    opened.

    I have a colleague trying to learn to program with .NET that can

    attempt this, but the key questions are:

    1. Will it be possible to perform the above actions with .NET?
    2. What is a general programming plan (perhaps which modules to use,

    etc?) that I can pass on to my colleague for development?

  2. Best Answer
    Posted by aidenburke

    "You can use the following methods to troubleshoot problems with opening or using Excel files:
    • Use a recovered copy of the document.
    • Use the Open and Repair command.
    • Save the file in HTML format.
    • Save the file in XML Spreadsheet format.
    • If a chart is linked to the corrupted file, use a macro to extract the data.
    • In case nothing helps make use https://social.msdn.microsoft.com/Fo...forum=exceldev"


  3. #2
    Join Date
    Jun 2015
    Posts
    1
    Provided Answers: 1
    You can use the following methods to troubleshoot problems with opening or using Excel files:
    • Use a recovered copy of the document.
    • Use the Open and Repair command.
    • Save the file in HTML format.
    • Save the file in XML Spreadsheet format.
    • If a chart is linked to the corrupted file, use a macro to extract the data.
    • In case nothing helps make use https://social.msdn.microsoft.com/Fo...forum=exceldev

Posting Permissions

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