CSV Quotation Mark Help

[Frazz89]

Hi,

I need to export a CSV file from my CRM to be imported into SAGE 200. The problem is that I need string fields to have quotation marks around them. I cannot find a way to have the quotation marks show only, for example, on columns A, C and D.

When I try and add them manually instead of showing "Test" it will show ""Test"" (when i open in notepad) or with even more extras. I was sent an example CSV and the excel spreadsheet itself doesn't show them but when opening in notepad it does.

Can anyone help?

Thank you!

Fraser

 

[BrianMH]

Excel won't show them but if you format the column as text it should save the file with quotations.

 

[Frazz89]

I tried that initially, as I believed any text field would 'update' to have quotation marks in a CSV format. However when I test this by opening the CSV in notepad, they are not showing.

 

[BrianMH]

You could add the quotation marks in excel and then open it in notepad and find and replace """ with ". Too bad excel doesn't seem to have any options for csv file saving.

 

[Frazz89]

Looks like it may be the only option, which is going to be a problem with so many reports exported/imported weekly!

 

[BrianMH]

You could use this macro I found on another thread. I've modified it so it only puts quotes around cells formatted as text. It looks like if you have a selection it saves only that but if you only select 1 cell it will save the usedrange of the spreadsheet.

Sub SaveCSVFile()


    Dim SrcRg As Range
    Dim CurrRow As Range
    Dim CurrCell As Range
    Dim CurrTextStr As String
    Dim ListSep As String
    Dim FName As Variant
    FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")


    If FName <> False Then
        ListSep = Application.International(xlListSeparator)
        If Selection.Cells.Count > 1 Then
            Set SrcRg = Selection
        Else
            Set SrcRg = ActiveSheet.UsedRange
        End If
        Open FName For Output As #1
        For Each CurrRow In SrcRg.Rows
            CurrTextStr = ""
            For Each CurrCell In CurrRow.Cells
                If CurrCell.NumberFormat = "@" Then
                    CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
                Else
                    CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
                End If
            Next
            While Right(CurrTextStr, 1) = ListSep
                CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
            Wend
            Print #1, CurrTextStr
        Next
        Close #1
    End If
End Sub