• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel corruption of .csv file data

J

jacobsaunier

New Member
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:
    1. Select Data -> From Text
    2. Select the .csv file to be imported
    3. Choose the “Delimited” option
    4. Use comma as the delimiter character
    5. Select all columns (hold shift and click the right-most column header)
    6. Change the “Column data format” to Text
    7. Click Finish
    8. 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?
 
Last edited by a moderator:

Hi !

Jacob, it's the common case of not so good csv files ‼

What's a csv file ? It's just a text file that if well formatted
could be read in Excel without any loss !
In case of any issue, so the file is not so good …

When a csv is good, I just have to double click on it from Windows Explorer :
it is opened right on Excel …

Many threads of this subject all over the web …

Activate Macro recorder and do manually all the needed commands :
you will have a code base !

For development, just need to observe and a bit of logic …
Better to format cells before to import data !

So many ways to import a text file in Excel !
Read inner VBA help for Workbooks.Open,
Workbooks.OpenText and QueryTable methods …
​
 
Manual solution in 6 steps:
1. Rename the file and remove the .xlsx or .xls extention etc e.g. if my file is called test.xlsx I remove the '.xlsx' - you will get a warning message saying this could make the document unstable - accept it
2. The document icon should now have turned white (the document won't look like an excel document anymore)
3. Open a blank excel document by hitting start -> all programs -> Microsoft office -> excel (this may be different if you’re not on windows 7)
4. In your blank excel document hit file -> open
5. Navigate to the document you renamed above and select it, now click open
6. It should now open the document

I hope this helps someone, if not, visit some authoritative resources...
http://www.filerepairforum.com/foru...xcel/1439-xlsx-corruption-error-on-excel-2010 - any info about corrupted excel files
https://excel.recoverytoolbox.com/ Recovery Toolbox for Excel - paid, but fast and very effective method
http://www.excelforum.com/excel-general/1027831-problem-opening-a-xls-file.html - solutions from Excel IT professionals
 
Hi,

I know this is old but I wanted to post it here in case someone else came across this issue.

To repair a corrupt .csv file, first, import its data into Excel by any of the below possible solutions:
  1. Directly open CSV file in Excel: Open MS Excel and go to Open. Select the CSV file and open it. When you select the CSV file then click on the file type Text Files (*.prn, *.txt, *.csv). This selection will help to list the file of these formats.
  1. Open CSV file using Windows Explorer: This is the quickest way open the CSV file in Excel format. Right-click on the CSV file and select the Open with option. choose the default program from the context menu. Click on the Excel (Desktop) under the recommended program. check the box "Always use the selected program to open this kind of file". Click Ok.
  1. Import CSV to Excel: Open MS Excel, and go to the Data tab. Select From Text option. Now browse for the CSV file, select the file and click on the Import button.
And after importing the complete data repair the excel corruption of the .csv file by following the below thread:

https://social.technet.microsoft.co...how-to-recover-corrupted-excel-file-2010.aspx

I hope this works.

Best Regards,
Cheryl
 
You must log in or register to reply here.
Back
Top