Import Delimited Text File

[enygma]

Hi all,

Want to import a text file in excel via macro, however I need it to do two things - first I want to select a text file (as the name changes every day) & secondly I want it to be imported with delimiters '|'.

Currently I have something as mentioned below, however the below code only provides me a dialog box and after selecting the right file, it opens it in excel sheet without any delimiters.

Kindly help.

Code:

Sub txtfile()
Dim myfile
myfile = Application.GetOpenFilename("Txt files (*.txt), *.txt", , "Please select your file")
If myfile = False Then Exit Sub
Dim aData As String
aData = ""
Dim x As Long
x = 1
Open myfile For Input As #1
    While Not EOF(1)
        Input #1, aData
        Range("A" & x).Value = aData
        x = x + 1
    Wend
Close #1
End Sub

 

[xenou]

Hi, I assume your text file is delimited (you don't import a file with delimiters, rather you import a delimited file). The help article on the Open statement says that it imports fields that are comma-delimited so I don't think you can use it with pipe-delimited fields. However, I am not a user of the VBA native I/O routines very often so I could be wrong.

This is how I would do it using the Scripting Library for file I/O:

[COLOR="Navy"]Sub[/COLOR] txtfile()

[COLOR="Navy"]Dim[/COLOR] myfile          [COLOR="SeaGreen"]'Path to file To be read (string)[/COLOR]
[COLOR="Navy"]Dim[/COLOR] aData           [COLOR="SeaGreen"]'Temp variable to hold each line of data in the file[/COLOR]
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]       [COLOR="SeaGreen"]'Row Counter[/COLOR]
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]   [COLOR="SeaGreen"]'As Scripting.FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]    [COLOR="SeaGreen"]'Scripting.TextStream[/COLOR]

[COLOR="SeaGreen"]'//Scripting Library Enums[/COLOR]
[COLOR="Navy"]Const[/COLOR] FOR_READING [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR] = 1
[COLOR="Navy"]Const[/COLOR] USE_DEFAULT_ENCODING [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR] = -2
[COLOR="Navy"]Const[/COLOR] USE_UNICODE_ENCODING [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR] = -1

    
    myfile = Application.GetOpenFilename("Txt files (*.txt), *.txt", , "Please select your file")
    [COLOR="Navy"]If[/COLOR] myfile = False [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]With[/COLOR] FSO
        [COLOR="Navy"]Set[/COLOR] ts = .OpenTextFile(myfile, FOR_READING, False, USE_DEFAULT_ENCODING)
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] ts.AtEndOfStream
            x = x + 1
            aData = Split(ts.ReadLine, "|")
            Range("A" & x).Resize(1, UBound(aData) + 1).Value = aData
        [COLOR="Navy"]Loop[/COLOR]
        ts.Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

For what it's worth, it may be easier to just record a macro while using the text import wizard in Excel. Then you can read the file in one go rather than doing this line by line thing.

ξ

 

[apo]

Hi..

This part got me a bit confused too..

secondly I want it to be imported with delimiters '|'.

It almost sounds like you want to split the text file by the pipe delimiter and then add it back to each cell in the Excel file.. surely that can't be what you want?

Anyway.. the following will split the text file BY the delimiter and place each value in its own cell..

Private Sub CommandButton1_Click()
    Dim i As Long, x, xx, s As String, strFile As String
    strFile = Application.GetOpenFilename("Text Files,*.txt")
    If strFile = "False" Then Exit Sub
    s = CreateObject("scripting.filesystemobject").getfile(strFile).openastextstream.readall
    x = Split(s, vbCrLf)
    For i = LBound(x) To UBound(x)
        xx = Split(x(i), "|")
            Sheets("Sheet1").Cells(i + 1, 1).Resize(1, UBound(xx) + 1).Value = xx
    Next i
    MsgBox "Data has been imported.", vbInformation
End Sub

 

[enygma]

Thanks a lot for the codes from both Apo & Xenou, works like a charm. Sorry for the confusion about importing with delimiters, my text file has pipe delimiter and wanted it to add it in individual cells in excel. Overall you guys understood what I wanted and gave me the perfect code.

Now I can continue with the rest of the macro.

Thanks a lot guys

 

[enygma]

Hi Guys,

One more query in case in the above given codes, I want the import to start from A2 then where do I need to mention that. I tried to change the range A2 in the code provided by Xenou, however the result starts from A21 with only 9-10 entries instead of the entire text content.

 

[apo]

Hi..

In my code..

Change this:

Sheets("Sheet1").Cells(i + 1, 1).Resize(1, UBound(xx) + 1).Value = xx

to this:

Sheets("Sheet1").Cells(i + [B][COLOR=#ff0000]2[/COLOR][/B], 1).Resize(1, UBound(xx) + 1).Value = xx

That will make it so the data gets imported into Row 2 down..

In xenou's ... you could just add:

x=1

just above the "Do While" line...

 

[xenou]

yes, that is correct for starting at A2. Thanks apo.

however the result starts from A21 with only 9-10 entries instead of the entire text content.

This sounds mysterious. If there is more code then they might be affecting the result. You might want to post the complete code and possibly the text file data as well (to the extent possible given size and/or confidentiality).