Importing Txt file and defining some fields as txt

[Oberon70]

Hi,

I have the below code that I used to import a txt file into excel. The problem is that some of the imported data columns need to be defined as txt so that I do not lose the 0 in front of the number.

The example number is 0412870, but when I import it I get 412870.

Sub ImportTextFileToExcel()

Application.ScreenUpdating = False


fileFilterPattern = "Text Files (*.txt;*.csv), *.txt; *.csv"

fileToOpen = Application.GetOpenFilename(fileFilterPattern)

'"G:\QIA\Short_Tail_Claims\Claims Risk Management\Recoveries\CART Exception Reports\test"

If fileToOpen = False Then
    'Input Canceled
    MsgBox "no file selected"

Else

Workbooks.OpenText _
            Filename:=fileToOpen, _
            StartRow:=2, _
            DataType:=xlDelimited, _
            Comma:=True
            

Set wbTextImport = ActiveWorkbook

Set wsMaster = ThisWorkbook.Worksheets.Add
Set wsMaster = ThisWorkbook.Worksheets("Data")

wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A3")

wbTextImport.Close False

End If

Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

How do I affect only specific data (columns) in the Data that is imported?    The delimited in the data file is a comma.

End Sub

 

[rlv01]

The trick is to open the file using the QueryTables add method.

Sub TestOpen()
    Dim fileFilterPattern, fileToOpen
   
    fileFilterPattern = "Text Files (*.txt;*.csv), *.txt; *.csv"
    
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)
    
    If fileToOpen = False Then
        'Input Canceled
        MsgBox "no file selected"
    Else
        CSV_Open_Text CStr(fileToOpen)
    End If
End Sub

'''
''' Open a CSV as text file to avoid formatting the data
'''
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
    
    Set QT = WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
    
    With WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A1"))
        .Name = QueryName
        .FillAdjacentFormulas = False
        .RefreshPeriod = 0
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .SaveData = True
        .SavePassword = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .TextFileCommaDelimiter = True
        .TextFileConsecutiveDelimiter = False
        .TextFileOtherDelimiter = ""                  ' Turns out this is sticky per the excel UI so it's important to explicitly set this to null.
        .TextFileParseType = xlDelimited
        .TextFilePlatform = xlWindows
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        '.AdjustColumnWidth = True
        '.FieldNames = True
        '.TextFilePromptOnRefresh = False
        '.TextFileSemicolonDelimiter = False
        '.TextFileSpaceDelimiter = False
        '.TextFileStartRow = 1
        '.TextFileTabDelimiter = False
        '.PreserveFormatting = True
        '.RefreshOnFileOpen = False
        .Refresh BackgroundQuery:=False
    End With
    
    ColCnt = ActiveSheet.UsedRange.Columns.Count
    If ColCnt > 20 Then
        ReDim FormatArray(ColCnt - 1)
        For I = 0 To ColCnt - 1
            FormatArray(I) = 2
            DoEvents
        Next I
        
        QT.TextFileColumnDataTypes = FormatArray
        QT.Refresh
    End If
End Sub

 

[Oberon70]

Thank you that code works great. I have modified it so that the Data file is imported into the workbook and worksheet. The only issue I have is that it doesn't paste into the table, but moves the table to the right.

NET TRANSACTION AMOUNT	GST ON TRANSACTION	GROSS TRANSACTION AMOUNT	CLAIM NUMBER	EVENT NUMBER	EXTERNAL SUPPLIER ID	Username	REPORT ID	RUN DATE
348.22	34.82	383.04	ADU15807			Donald Duck
100	0	100	ADU15332			Donald Duck
497.46	0	497.46	ADU15807			Donald Duck
20	0	20	ADU15822			Mickey Mouse
10	0	10	ADU16011			Clark Kent
20	0	20	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent
10	0	10	ADU16011			Clark Kent

I would like if it could pasted into the table

		Rejected Transactions - Master Sheet
REPORT ID	RUN DATE	Agency Name	INVOICE ID	INVOICE DATE	BATCH KEY	AMOUNT	RECEIPT NO	ERROR MESSAGE	SUSPENSE ACCOUNT NUMBER

Also, is there a way to skip the 1st line of the text file?

the code was modified to:

Sub TestOpen()
    Dim fileFilterPattern, fileToOpen
  
    fileFilterPattern = "Text Files (*.txt;*.csv), *.txt; *.csv"
    
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)
    
    If fileToOpen = False Then
        'Input Canceled
        MsgBox "no file selected"
    Else
        CSV_Open_Text CStr(fileToOpen)
    End If
End Sub


'''
''' Open a CSV as text file to avoid formatting the data
'''
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 = ThisWorkbook.Worksheets.Add
    Set WB = ThisWorkbook
    WB.Activate
    Set WS = WB.Worksheets("Data")
        
    Set QT = WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A3"))
    
    With WS.QueryTables.Add(Connection:=QueryConnection, Destination:=Range("A3"))
        .Name = QueryName
        .FillAdjacentFormulas = False
        .RefreshPeriod = 0
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .SaveData = True
        .SavePassword = False
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
        .TextFileCommaDelimiter = True
        .TextFileConsecutiveDelimiter = False
        .TextFileOtherDelimiter = ""                  ' Turns out this is sticky per the excel UI so it's important to explicitly set this to null.
        .TextFileParseType = xlDelimited
        .TextFilePlatform = xlWindows
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        '.AdjustColumnWidth = True
        '.FieldNames = True
        '.TextFilePromptOnRefresh = False
        '.TextFileSemicolonDelimiter = False
        '.TextFileSpaceDelimiter = False
        '.TextFileStartRow = 1
        '.TextFileTabDelimiter = False
        '.PreserveFormatting = True
        '.RefreshOnFileOpen = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[rlv01]

Also, is there a way to skip the 1st line of the text file?

Probably by uncommenting and changing this line

    '.TextFileStartRow = 1

to this

    .TextFileStartRow = 2

You'll have to experiment.

But I think you would be better off importing the query table into a new worksheet, then writing more code to move or copy the parts of your imported data you want into your table on sheet Data.

 

[Oberon70]

thanks, I will look at doing that