Exporting Text Values to CSV file

[StewartS]

Hi

I have a file which contains a text value which is in the format ##-####. The problem I have is that when I copy the data into a new workbook using VBA and save it as a CSV file some of the values will show as dates in the CSV file.

For example if the text value is 02-2019 it can show as Feb 2019 whereas I need it to show as 02-2019.
This can be the same for and values where the first two digits are 01 - 12.

The Excel file where I hold the data has the cells formatted as Text so there isn't a problem in the base data. Is there a way I can ensure that data is kept as a text value when it is copied over.

For information I am using Excel 2016 and the file has 250k records in it.

Thanks

StewartS

 

[Joe4]

The problem I have is that when I copy the data into a new workbook using VBA and save it as a CSV file some of the values will show as dates in the CSV file.

The problem may be with how you are trying to view the file, and not the copy/save process.
How are you viewing the file that gets created? Are you opening in Excel?
If so, DON'T!!! That is probably the issue. The problem is that when you open CSV files in Excel, Excel "guesses" the format of each field, which may convert some of your data.

To see what the data in the CSV files REALLY looks like, view it in a Text Editor, like NotePad, WordPad, or some other third party text editor.
If you view it there, does the field look correct there?

If you wish to view it in Excel, do not open it directly in Excel. Go to the Data menu, then to the "Get External Data" ribbon, and select Text.
Browse to your file, and this should invoke the Import Wizard, where you get to designate the format of every incoming field. So you can set this format to Text, so it will not convert it to Date for you.

 

[StewartS]

Thanks Yes I had picked up on this, I think what I was looking for is if you can export the value with quote marks around or something similar. The data will eventually be read into an SQL database so I suspect it should be ok as it is. Just looking for belts and braces!

StewartS

 

[Misca]

Try formatting the target columns as text before pasteing the values.

Or you might want to let Power Query ( or "Get & Transform" as it's called nowadays) do the whole thing for you. This way building the query would take a few minutes but once you're done you could update your data in a few seconds (i.e. faster than you could ever copy paste the data manually).

 

[Joe4]

I think what I was looking for is if you can export the value with quote marks around or something similar.

Check in the Text Editor. If your field was set to Text before saving as a CSV, it may already have the double-quote marks.

Here is one of the more annoying things about how Excel handles CSV files. Even if you have double-quotes around the field, if you open it directly in Excel, Excel may ignore the double-quotes and STILL make it a date anyway.

I love Excel, but one of my biggest pet peeves with it is that Microsoft decided that Excel should be the default program to open CSV files. Since it does these "automatic" conversions, this can cause issues (like you are seeing). One of the first things I do when I get a new computer is to change the settings so that a Text Editor is the default program I use to open CSV files (and not Excel). Then you can easily see the native CSV file, as it really appears, and not view it after Excel may have done a bunch of conversions to the data.

 

[rlv01]

You can also use the QueryTable mechanism to code a file-open routine for those times when you need to open a .csv file while preserving data that excel will choose to interpret as a date via the normal file open process. One example:

Sub OpenExample()
   CSV_Open_Text "MyDataFile.csv"
End Sub

'''
''' Open a CSV file as text to avoid excel guessing about data format
'''
Sub CSV_Open_Text(fname As String)
    Dim QueryConnection As String
    Dim QueryName As String
    Dim ColCnt As Integer, I As Integer
    Dim QT As QueryTable
    Dim WS As Worksheet
    Dim FormatArray() As Integer
    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 = WS.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

 

[StewartS]

Thanks, I already have a workbook where I can import the CSV file into to ensure the values don't revert to dates.

Stewart