Excel 2016 - Removing quote marks in CSV

[Tanquen]

I wish Excel had defaults to open CSV file as a CSV and I wish it would not make and changes or add random commas and so on...

I've opened a CSV and did a search and replace for some 4 digit numbers. After saving the CSV some strings have an added space at the end or so I thought. It's removing the quote marks.

This:
,"Configuration of Severity for On ",

Becomes this:
,Configuration of Severity for On ,

 

[Alex Blakenburg]

The fact that Windows chooses Excel as the default program to open CSV files is one of my biggest pet peeves (that and "merged cells"!).

You know that this is just a file type association in windows that you change right ?!

 

[Joe4]

You know that this is just a file type association in windows that you change right ?!

Absolutely. As I mentioned back in post 6:

(as matter as fact, one of the first things I do when I get a new computer is change the Windows default program for opening CSV file from Excel to a text editor like NotePad or NotePad++).

My beef is that Microsoft decided that by default, Excel is the best program to use to open CSV files, when it clearly should not be, for the reasons mentioned!

 

[Alex Blakenburg]

Absolutely. As I mentioned back in post 6:

My beef is that Microsoft decided that by default, Excel is the best program to use to open CSV files, when it clearly should not be, for the reasons mentioned!

My Bad.

 

[Alex Blakenburg]

@Tanquen
Do you have Power Query on your machine ? This would overcome quite a few of your issues.

Not sure if removing the double-quotes will affect the application importing the CSV back in. The application only added it to some so I'm guessing it needs them to be there when you import them back in.

It won't overcome the double quotes issue but this seems to be a bit odd. Are you saying you have some phrases with spaces and containing "no commas" have double quotes and some don't in the "same field / column" ? I would be testing loading it back into the application without the quotes. It seems likely that they are not required.

PS: In the case of ℃, opening the file with Excel distorted it as it did for you but going into Notepad then Ctrl+A, copy then pasting it into Excel and there doing a Text to Columns using comma delimiter did not.

 

[rlv01]

Absolutely. As I mentioned back in post 6:

My beef is that Microsoft decided that by default, Excel is the best program to use to open CSV files, when it clearly should not be, for the reasons mentioned!

To be fair to Microsoft, they are in a no-win position. For every person who is upset that Excel is the default association, there is someone else like me who is glad it is. For many people in the various STEM professions, the CSV format is the common format for data exchange from various applications and instrumentation, and Excel the preferred tool to analyze that data. I can open dozens of csv files in a given day, so I very much prefer that Excel be the default association. YMMV.

 

[Joe4]

To be fair to Microsoft, they are in a no-win position. For every person who is upset that Excel is the default association, there is someone else like me who is glad it is. For many people in the various STEM professions, the CSV format is the common format for data exchange from various applications and instrumentation, and Excel the preferred tool to analyze that data. I can open dozens of csv files in a given day, so I very much prefer that Excel be the default association. YMMV.

Do you ever have problems with the automated data conversions?
In my profession, in which we get a lot of ID numbers (Social Security Numbers) and Zip Codes, it wreaks havoc by converting to numbers and dropping leading zeroes.

I wouldn't mind it so much if it allowed you to set the data type/format of each field, like it does with other Text files (or when you use Text to Columns).
The fact that it chooses that as the default, but then gives you no easy way to define the data types, like with other text files, is what I find maddening.

 

[rlv01]

Do you ever have problems with the automated data conversions?
In my profession, in which we get a lot of ID numbers (Social Security Numbers) and Zip Codes, it wreaks havoc by converting to numbers and dropping leading zeroes.

I wouldn't mind it so much if it allowed you to set the data type/format of each field, like it does with other Text files (or when you use Text to Columns).
The fact that it chooses that as the default, but then gives you no easy way to define the data types, like with other text files, is what I find maddening.

Once in awhile, sure. There are some apps that for whatever reason try to do things in a non-standard way (like the OP's app that wants to surround data fields with double quotes when there is no need for it). Or I have one app that includes hyphenated strings, and so "3-4" can be misinterpreted by Excel as a date. In some cases when I know there are going to be issues like that, I will use a bit of code to open the .csv file using a query which in the previous case avoids that particular data conversion. Here's an example:

'''
''' Open a CSV as text file to avoid formatting the data
'''
Sub CSV_Open_Text(fname As String)
'
    Dim QueryConnection As String
    Dim QueryName As String
    Dim ColCnt As Long, I As Long
    Dim QT As QueryTable
    Dim WS As Worksheet
    Dim FormatArray() As Long
    Dim WB As Workbook

    QueryConnection = "TEXT;" & fname
    QueryName = "CSV_Import"

    Set WB = Application.Workbooks.Add(xlWBATWorksheet)
    WB.Activate
    Set WS = ActiveSheet

    With WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
        .Name = QueryName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .Refresh BackgroundQuery:=False
    End With

    Set QT = WS.QueryTables(QueryName)

    ColCnt = ActiveSheet.UsedRange.Columns.count

    If ColCnt > 20 Then
        ReDim FormatArray(ColCnt - 1)
        For I = 0 To ColCnt - 1
            FormatArray(I) = 2
        Next I

        QT.TextFileColumnDataTypes = FormatArray
        QT.Refresh
    End If
End Sub

 

[Joe4]

Once in awhile, sure. There are some apps that for whatever reason try to do things in a non-standard way (like the OP's app that wants to surround data fields with double quotes when there is no need for it). Or I have one app that includes hyphenated strings, and so "3-4" can be misinterpreted by Excel as a date. In some cases when I know there are going to be issues like that, I will use a bit of code to open the .csv file using a query which in the previous case avoids that particular data conversion. Here's an example:

'''
''' Open a CSV as text file to avoid formatting the data
'''
Sub CSV_Open_Text(fname As String)
'
    Dim QueryConnection As String
    Dim QueryName As String
    Dim ColCnt As Long, I As Long
    Dim QT As QueryTable
    Dim WS As Worksheet
    Dim FormatArray() As Long
    Dim WB As Workbook

    QueryConnection = "TEXT;" & fname
    QueryName = "CSV_Import"

    Set WB = Application.Workbooks.Add(xlWBATWorksheet)
    WB.Activate
    Set WS = ActiveSheet

    With WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
        .Name = QueryName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .Refresh BackgroundQuery:=False
    End With

    Set QT = WS.QueryTables(QueryName)

    ColCnt = ActiveSheet.UsedRange.Columns.count

    If ColCnt > 20 Then
        ReDim FormatArray(ColCnt - 1)
        For I = 0 To ColCnt - 1
            FormatArray(I) = 2
        Next I

        QT.TextFileColumnDataTypes = FormatArray
        QT.Refresh
    End If
End Sub

Yeah, that is what I mean. There are various different ways around it, but they usually involve VBA code or other "tricks" - things many novice users may not be aware of or comfortable doing.

 

[rlv01]

Yeah, that is what I mean. There are various different ways around it, but they usually involve VBA code or other "tricks" - things many novice users may not be aware of or comfortable doing.

Very true.