# Importing Txt file and defining some fields as txt



## Oberon70 (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

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 AMOUNTGST ON TRANSACTIONGROSS TRANSACTION AMOUNTCLAIM NUMBEREVENT NUMBEREXTERNAL SUPPLIER IDUsernameREPORT IDRUN DATE348.2234.82383.04ADU15807    Donald Duck                                                                                                                                             1000100ADU15332    Donald Duck                                                                                                                                                                              497.460497.46ADU15807    Donald Duck                                                                                                                                             20020ADU15822    Mickey Mouse                                                                                                                                                                             10010ADU16011    Clark Kent                                                                                                                                                                              20020ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              10010ADU16011    Clark Kent                                                                                                                                                                              


I would like if it could pasted into the table

Rejected Transactions - Master SheetREPORT IDRUN DATEAgency NameINVOICE IDINVOICE DATEBATCH KEYAMOUNTRECEIPT NOERROR MESSAGESUSPENSE 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 (Dec 16, 2022)

Oberon70 said:


> 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 (Dec 18, 2022)

thanks, I will look at doing that


----------

