Text string opening as date

[JWDegas]

Hi, we have a database extract with user ids created by concatenating the first 3 letters of their first name with their id from a separate table. This works fine (Pag520, Cho168 etc) until we get combinations (Jan13, Mar92) which Excel wants to display as a date (Jan-13, Mar-92). Is there a way we can make Excel display these strings in text form as intended rather than reformat them as dates? I haven't seen how (so far). Thanks, Jeremy

 

[Joe4]

Welcome to the Board!

What format is this database extract in?
How are you opening it in Excel?

 

[JWDegas]

Thanks! Have tried extracting to both xlsx and csv, then clicking on these in Windows Explorer to open

 

[Joe4]

Do you have any other Text file options you can export to?

The issue is that with Excel and CSV files, Excel tries to "guess" the data type of each field, and sometimes it guesses wrong (and it doesn't make it easy for you to override it).
It thinks "Jan13" and "Mar92" are dates, so it is bringing them in like that.

With other text file options (i.e. tab-delimited, fixed-width, etc), you usually have more power to control the data type of each column.

Another alternative would be to create/run a quick little Excel Macro to fix the file after opening it.

 

[rlv01]

You can use a QueryTable to open a CSV file as text so it won't try to 'guess' at the format.

'''
''' Use a QueryTable object to open a CSV file as text to avoid having Excel guess about cell format based on the contents.
'''
Sub CSV_Open_Text(FilePath As String)
    Dim ColCnt As Long, FormatArray() As Long, I As Long
    Dim QT As QueryTable
    Dim QueryConnection As String, QueryName As String
    Dim WB As Workbook
    Dim WS As Worksheet

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

    Set WB = Application.Workbooks.Add
    Set WS = WB.Worksheets(1)
    For I = 1 To WB.Worksheets.Count
        If I >= 2 Then
            Application.DisplayAlerts = False
            WB.Worksheets(I).Delete
            Application.DisplayAlerts = True
        End If
    Next I

    'Create a QueryTable
    Set QT = WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))

    'Set QueryTable properties
    With QT
        .Name = QueryName
        .BackgroundQuery = False
        .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
    End With

    QT.Refresh                                        'intial file load, general format

    ColCnt = ActiveSheet.UsedRange.Columns.Count      'count imported columns

    'Create query format array
    ReDim FormatArray(ColCnt - 1)
    For I = 0 To ColCnt - 1
        FormatArray(I) = xlTextFormat
    Next I

    QT.TextFileColumnDataTypes = FormatArray          'Set query format array
    QT.Refresh                                        'reload as text format

    WS.Name = Split(FilePath, "\")(UBound(Split(FilePath, "\")))
End Sub

 

[JWDegas]

Thank you both. Not sure if we'll use VBA but great to know it's here if we do, much appreciated.

 

[Joe4]

Thank you both. Not sure if we'll use VBA but great to know it's here if we do, much appreciated.

If you answer the question I asked in my reply, we may be able to offer other alternatives.

 

[JWDegas]

We've now decided to simply change the user id format so it won't clash with Excel. And sorry for missing Joe, tried XML which did correct the issue but otherwise messed up the layout.

 

[Alex Blakenburg]

Just an extension of @Joe4's line of thinking, you don't even need the file to be in a different format just a different file extension.
If you can get the system to not give it the extension csv but say txt, you will be able to use the import wizard when you open it and force it to be text.