Text import into Excel

Question

I have this code that helps with import of text files into excel. It intended to be a workaround for Excel not being able to deal with numbers longer than 15 characters.

I am trying to figure out a way to change this code .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1) into something that would go through a file an automatically check if a column contains numbers 16 characters or longer and automatically switch them to text values in order to preserve their formatting.

Sub Text_import()
Dim Ret

Ret = Application.GetOpenFilename("CSV Files (*.csv), *.csv")

If Ret <> False Then
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & Ret, Destination:=Range("$A$1"))

    .Name = "Sample"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    End With
End If

End Sub

Going by your description, your approach won't work. If long numbers are imported into Excel in a numerical format, then "switched back" to text, they will have already been corrupted. It won't be possible to recover the lost digits by switching the number "back." So you have to import the relevant columns strictly in text format. — BDra
– BDra, Commented Mar 28, 2021 at 0:56
Well that's the thing, if it was a standard file, I could specify which columns should be treated as text. For example, if a file had order id's which are 16 digits and a total of 5 columns, this would work .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1). However, I was wondering if it was possible for vba to cycle through columns before importing a file to check if any of them were numeric and had more than 15 characters. — Leo Veynberg
– Leo Veynberg, Commented Mar 29, 2021 at 5:39

MT1 · Accepted Answer · 2021-03-29 06:20:02Z

0

Read the .CSV file in using ADO.

    With CreateObject("ADODB.Stream")
    .Open
    .Charset = "UTF-8"
    .LoadFromFile ActiveWorkbook.Path & "\\tw_202103231726.txt"
    text = .ReadText(-1)
    .Close
    End With

then you have a text variable that you can process with VBA as you need.

Update:

maybe something like this -

    For ii = 1 To Len(text) Step 1

    Select Case Mid(text, ii, 2)
    Case Chr(34) & Chr(58) ' double quote 34 / colon 58

            dqcPos = InStr(ii + 3, text, Chr(34) & Chr(44)) ' double quote and comma

edited Mar 29, 2021 at 6:20

answered Mar 28, 2021 at 12:22

MT1

9532 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Text import into Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related