?
Solved

Excel corruption of .csv file data

Posted on 2010-08-24
16
Medium Priority
?
2,168 Views
Last Modified: 2013-12-04
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?
0
Comment
Question by:Terry Woods
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 33517089
No need to worry about VB.net corrupting a csv file.

Just use the text reader to open your csv file and iterate through the fields to make your changes then use the textwriter to save it.

PS. You will NOT be able to change a columns datatype as described when saving. Additionally, you willnot be able to change the book name, however, Excel will automatically rename the book name to the file name.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33517136
They were thinking that you wanted to use Excel 'xls', not 'csv'.  A plain text editor like PSPad will open and edit 'csv' files but only in a text format, not a column format.  I went looking for a 'csv' editor for another question but couldn't find one.  You'd think that after 30 or 40 years one would be available.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 33517146
I want to use Excel 2007 to edit the file, and Excel saves to .csv format ok without corrupting it but it corrupts the data when a .csv file is opened by just double clicking it (or probably with File->Open)
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 200 total points
ID: 33517172
Ok.  But I Googled 'csv editor' and found what I didn't find before.  Downloaded CSVed from http://csved.sjfrancke.nl/, opened a csv with it and saved it under another name and Winmerge says the files are identical.  So at least there are alternatives.  There were others listed.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 33517715
Thanks for the link. CSVed looks quite good, but I've got a lot of my neurons invested in using Excel quickly, so I want to pursue that avenue further if I can.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33517726
I understand.  Let us know how it works out.  A number of questions have come up where CSV is being used for something other than an Excel import and it is often a problem.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1800 total points
ID: 33522383
TerryAtOpus,This is ugly, but it works.  For some odd reason, even when using the OpenText method, I could not get Excel to stop being "helpful" by trying to convert numeric literals and entries that looked like date literals.  When I tried the same code (without the funky file conversion jiu jitsu) on txt files, I got exactly the behavior I wanted.To curb that behavior, I did a little behind the scenes conversion to txt and back to csv.If no one posts a better solution within a few hours, I suggest you hit that request attention link, because this is probably the best I will be able to do.Patrick
Sub OpenCSVAllText()
    
    Dim FilesToOpen As Variant
    Dim FileCounter As Long
    Dim wb As Workbook
    Dim FilePath As String
    Dim NumColumns As Long
    Dim ColumnInfo() As Variant
    Dim ColumnCounter As Long
    Dim fso As Object
    Dim FilePathTxt As String
    
    FilesToOpen = Application.GetOpenFilename("CSV files (*.csv), *.csv", , "Select files to open", , True)
    If Not IsArray(FilesToOpen) Then
        MsgBox "No files selected, aborting", vbCritical, "No soup for you!"
        Exit Sub
    End If
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    For FileCounter = LBound(FilesToOpen) To UBound(FilesToOpen)
        FilePath = FilesToOpen(FileCounter)
        Set wb = Workbooks.Open(FilePath)
        NumColumns = Cells(1, Columns.Count).End(xlToLeft).Column
        wb.Close False
        ReDim ColumnInfo(0 To NumColumns - 1) As Variant
        For ColumnCounter = 1 To NumColumns
            ColumnInfo(ColumnCounter - 1) = Array(ColumnCounter, 2)
        Next
        FilePathTxt = Left(FilePath, Len(FilePath) - 3) & "txt"
        If Dir(FilePathTxt) <> "" Then Kill FilePathTxt
        fso.MoveFile FilePath, FilePathTxt
        Workbooks.OpenText Filename:=FilePathTxt, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
            Comma:=True, Space:=False, Other:=False, FieldInfo:=ColumnInfo, TrailingMinusNumbers:=True
        ActiveWorkbook.SaveAs FilePath, xlCSV
        Kill FilePathTxt
    Next
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    Set fso = Nothing
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 0 total points
ID: 33555745
Thanks, patrick - I can now use a hotkey in Excel instead of ctrl-o to open csv files without corruption, so that is fantastic. I would still love to have a context-menu option in Windows Explorer to do it though, so I will use the request attention link to see if it brings any more ideas out of the woodwork.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33556305
Is it important that the data be saved back into the csv file?

I ask because it would be fairly simple to code a small app (in .net) that opens and reads the csv file then uses automation to fill a new excel table with the information from the csv file (so data should not get converted by excel) and as excel thinks this is a new document one can save it as an xls file.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33556310
excel thinks this is a new document one can save it as an xls file.

By that I mean if you hit the save button in excel it won't overwrite the csv file with 'corrupt' values
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 33562874
Andy, it is important to save data back to a csv file, though not necessarily the same one. I use Excel to find and fix errors prior to importing the data to a system which accepts csv files. It's a fairly repetitive task so the actions that I perform to open a file, edit it and save it need to be fairly streamlined - I will need a mechanism that avoids having to do a Save-As and select csv as the file type.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 33564542
What sort of 'errors' in the csv file - would it be possible to drop excel totally and check/correct the csv file completely in code ?

It would be possible to code the app as I said then have it rename the csv file (backup - safety) then perform a SaveAs to save the new excel sheet as a csv file with the same name and path as the original.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 33572020
Thanks for your comments Andy

The kind of errors that are fixed in the csv file differ for each file, but can be things like unusual dates and outlying data values, or values that may been recorded with an incorrect unit of measurement. Data filters and formulae etc are used to find and correct them - things that Excel excels at. I don't think it would be sensible to try to avoid using Excel.

Yes, it would be fine for an app to do as you describe, though I've added some extra detail to ensure efficiency and safety:
1. Open and read the csv file (from Windows Explorer context menu, or even just by associating the .csv file type with the new app) then use automation to fill a new excel table with the information from the csv file (so data should not get converted by excel)
2. Rename the csv file (as a backup for safety), adding a datetime to the filename to ensure it doesn't get overwritten when the same file is reopened
3. Perform a SaveAs to save the new excel sheet as a csv file with the same name and path as the original.
0

Featured Post

7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question