Import a whole text file into a single cell

[mv5869]

How can I import a whole text field into a single cell in Excel, without the line breaks making Excel start a new row? I'd like the whole text document in a single cell.

I know I could copy and paste but I want to automate it.

Eventually, I would like to write a script to go through a whole directory and import each text file into a different cell of my spreadsheet. But small steps...

 

[somnath_it2006]

I think, you an simply concatenate all the lines of file to a string variable and assign to the cell.


e.g.

Sub test()
        
    Dim szThisPath As String
    Dim strString As String
    
    ' File path
    szThisPath = ThisWorkbook.Path
    
    ' File name
    szFileName = "abc.txt"
    
    
    Dim szPathSep As String
    
    ' Path seprator
    szPathSep = Application.PathSeparator
     
    Dim szValidPath As String
    
    ' Filename created
    szValidPath = szThisPath & szPathSep & szFileName
    
    On Error GoTo ErrHandler
     
    Dim lFile As Long
    Dim szLine As String
     
    lFile = FreeFile()
     
    Open szValidPath For Input As lFile
     
    While Not EOF(lFile)
         
        Line Input #lFile, szLine
        
        ' Concatenete lines from text file
        strString = strString & szLine
        
    Wend
        
    ' Add to cell
    [COLOR="Red"]Range("A1").Value = strString[/COLOR]
     
    ' Close the file
    Close lFile
    
    
End Sub

 

[mv5869]

Brilliant. Thanks for being so helpful. That works

Now I can start work on the next step. Automating it to do this for a set of files in a directory...

 

[mv5869]

So now I have managed to create a file importer that loops through a directory and pulls in all txt files located there.

It almost works (I think!) but when it get to the Do While sFil <> "" part it just jumps to the end. The directory definitely exists, and contains some text files.

Is there an error in my code? I cant find it.

Sub MikeMaster()
Dim x As Integer
Dim temp
Dim i As Integer
Dim Drive As String
Dim ChFiles() As String
Dim FFiles As Integer
Dim WB As Integer
'Option Explicit
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:\ImportTest\" 'location of files
'sPath = ThisWorkbook.Path 'location of files (ie it runs from wherever the Excel itself is)
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
iRow = 2 ' Row to start inserting data
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

Dim szValidPath As String
' Filename created
szValidPath = sPath & "\" & sFil

Dim lFile As Long
Dim szLine As String

lFile = FreeFile()

Open szValidPath For Input As lFile
strString = ""
While Not EOF(lFile)

Line Input #lFile, szLine

' Concatenete lines from text file
strString = strString & szLine & vbCrLf
Wend

' Add to cell
Cells(iRow, 1).Value = strString
iRow = iRow + 1

' Close the file
Close lFile

Application.ScreenUpdating = True

'oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
MsgBox "Completed!"
End Sub

 

[somnath_it2006]

Its working on my system... (But I have experience - sometime dir give problems)


Add "Microsoft Scripting Runtime" from references and try below code:

Option Explicit
Sub MikeMaster()
    
    Dim sPath As String
    Dim iRow As Long
    Dim strString  As String
    
    Dim fso As FileSystemObject
    Dim xFile As File
    Dim xFolder As Folder
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set xFolder = fso.GetFolder("C:\ImportTest\")
    
    iRow = 2 ' Row to start inserting data
    
    For Each xFile In xFolder.Files
        
        If InStr(1, xFile.Name, ".txt") <> 0 Then
        
            Dim lFile As Long
            Dim szLine As String
            
            lFile = FreeFile()
            
            Open xFile.Path For Input As lFile
            
            strString = ""
            While Not EOF(lFile)
            
                Line Input #lFile, szLine
            
                ' Concatenete lines from text file
                strString = strString & szLine & vbCrLf
                
            Wend
            
            ' Add to cell
            Cells(iRow, 1).Value = strString
            
            iRow = iRow + 1
            
            ' Close the file
            Close lFile
            
            Application.ScreenUpdating = True
                        
        End If
        
    Next ' End of LOOP
    
    MsgBox "Completed!"
End Sub

 

[Richard Schollar]

When you change the current directory using ChDir it only changes the directory on the current drive (it doesn't alter the current drive at all) so you need to ensure you use ChDrive first to change the drive:

ChDrive = sPath
ChDir = sPath
sFil = Dir("*.txt") 'change or add formats

Of course, it's actually easier just to include the file path within the Dir statement on the first call:

sFil = Dir(sPath & "*.txt") 'change or add formats

in which case you don't need the ChDir statement at all.

 

[mv5869]

Brilliant. That works a dream now.

Richard and somnath - You deserve major thanks!

 

[mv5869]

I've noticed one very odd thing that I can't seem to resolve, despite much googling.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The code below opens the text file and imports it nicely into my Excel cell. All the line breaks are there too, so perfect! However, the strange (and annoying) part is that when I import it brings in a space after every line break. I cant see why. I want it to replicate the line breaks but without adding any extra space.<o></o>
<o></o>

Dim szValidPath As String
' Filename created
szValidPath = sPath & "\" & sFil
 
Dim lFile As Long
Dim szLine As String
 
lFile = FreeFile()
 
Open szValidPath For Input As lFile
strString = ""
While Not EOF(lFile)
 
Line Input #lFile, szLine
 
' Concatenete lines from text file
strString = strString & szLine & vbCrLf
Wend
 
' Add to cell
Cells(iRow, ICol).Value = strString
iRow = iRow + 1
 
' Close the file
Close lFile

 

[Richard Schollar]

Personally I would change the way you read the file in to this:

Dim szValidPath As String
' Filename created
szValidPath = sPath & "\" & sFil
 
Dim lFile As Long
Dim strString As String
 
lFile = FreeFile()
strString = Space(FileLen(szValidPath))

Open szValidPath For Binary Access Read As #lFile

 
Get #lFile, ,strString

Close #lFile
 
' Add to cell
Cells(iRow, ICol).Value = strString

 

[mv5869]

Thanks. I tried that code and it works as before. ie. whenever there is a line break in the text file, it seems to bring in an extra space after the line break in Excel. Its not the end of the world (after all I cant actually see the space) but it does cause a couple of other issues.