Exporting date to csv formating problem

steverob

New Member
Joined
May 13, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I need to export a list of dates from Excel of the form dd-mm-yy hh:mm to a .csv file where they have to be wrapped in double quotes. Like this
"31-12-2020 14:23"

This is turning out to be quite tricky. I tried the following:

a) Export as they are, and they appear in the csv file unwrapped.
b) Change them to text format - same thing. (Even though if you do the same thing with a list of words they do get wrapped in quotes)
c) I tried this:

=CONCATENATE(CHAR(34),TEXT(A1, "yyyy-mm-dd hh:mm"),CHAR(34)) - which wraps the date in cell A1 with double quotes.
Exporting this to a csv file ends up with the date wrapped in three sets of double quotes.

As a quick fix I then used the search and replace function but there must be a better way!
Can anyone help please?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Via VBA there is nothing to change, quite easy but you forgot to attach a source workbook sample and accordingly the expected result text file as well …​
As the CSV file format does not require any double quote until there is a comma within a cell …​
 
Upvote 0
I found the following code from microsoft, via googling, that may steer towards a solution. I have not tested it, I only did some formatting and stuff to the original code.

VBA Code:
Sub QuoteCommaExport()
'
'   From: https://docs.microsoft.com/en-US/office/troubleshoot/excel/export-text-file-with-comma-quote
'
'   Microsoft Excel does not have a menu command to automatically export data to a text file so that the text file is exported
'   with both quotation marks and commas as delimiters.
'   However, you can create this functionality in Excel by using a Microsoft Visual Basic for Applications procedure.
'
'   Before you run the macro, select the data that you want to export, and then run the QuoteCommaExport subroutine.
'
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Long
    Dim RowCount As Long
'
    DestFile = InputBox("Enter the destination filename" & Chr(10) & "(with complete path):", "Quote-Comma Exporter")   ' Prompt user for destination file name
'
    FileNum = FreeFile()                                ' Obtain next free file handle number
'
    On Error Resume Next                                ' Turn error checking off
'
    Open DestFile For Output As #FileNum                ' Attempt to open destination file for output
'
    If Err <> 0 Then                                    ' If an error occurs report it and end
        MsgBox "Cannot open filename " & DestFile
        End
    End If
'
    On Error GoTo 0                                     ' Turn error checking on
'
    For RowCount = 1 To Selection.Rows.Count            ' Loop for each row in selection
        For ColumnCount = 1 To Selection.Columns.Count  ' Loop for each column in selection
'                                                                                           ' \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/
            Print #FileNum, """" & Selection.Cells(RowCount, ColumnCount).Text & """";      ' Write current cell's text to file with quotation marks
'
            If ColumnCount = Selection.Columns.Count Then   ' Check if cell is in last column
                Print #FileNum,                             '   If so, then write a blank line
            Else
                Print #FileNum, ",";                        ' Otherwise, write a comma
            End If
        Next ColumnCount                                    ' Start next iteration of ColumnCount loop
    Next RowCount                                       ' Start next iteration of RowCount loop
'
    Close #FileNum                                      ' Close destination file
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top