AnsweredAssumed Answered

Excel corruption of .csv file data

Question asked by reubenmurray reubenmurray on May 24, 2015
Latest reply on May 28, 2015 by alemao dosantos

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?

Outcomes