Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Import a whole text file into a single cell

This is a discussion on Import a whole text file into a single cell within the Excel Questions forums, part of the Question Forums category; How can I import a whole text field into a single cell in Excel, without the line breaks making Excel ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Smile Import a whole text file into a single cell

    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...

  2. #2
    Board Regular somnath_it2006's Avatar
    Join Date
    Apr 2009
    Location
    Pune, India
    Posts
    574

    Default Re: Import a whole text file into a single cell

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


    e.g.

    Code:
    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
        Range("A1").Value = strString
         
        ' Close the file
        Close lFile
        
        
    End Sub
    Regards,
    Som



    The more we come out and do good to others, the more our hearts will be purified, and God will be in them. - Swami Vivekananda

  3. #3
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Default Re: Import a whole text file into a single cell

    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...

  4. #4
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Default Re: Import a whole text file into a single cell

    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

  5. #5
    Board Regular somnath_it2006's Avatar
    Join Date
    Apr 2009
    Location
    Pune, India
    Posts
    574

    Default Re: Import a whole text file into a single cell

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


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

    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
    Regards,
    Som



    The more we come out and do good to others, the more our hearts will be purified, and God will be in them. - Swami Vivekananda

  6. #6
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,696

    Default Re: Import a whole text file into a single cell

    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:

    Code:
    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:

    Code:
    sFil = Dir(sPath & "*.txt") 'change or add formats
    in which case you don't need the ChDir statement at all.
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Default Re: Import a whole text file into a single cell

    Brilliant. That works a dream now.

    Richard and somnath - You deserve major thanks!

  8. #8
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Default Re: Import a whole text file into a single cell

    I've noticed one very odd thing that I can't seem to resolve, despite much googling.

    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.


    Code:
     
    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
    Last edited by mv5869; Apr 21st, 2010 at 09:04 AM. Reason: typo

  9. #9
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,696

    Default Re: Import a whole text file into a single cell

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

    Code:
    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
    Richard Schollar

    Using xl2013

  10. #10
    New Member
    Join Date
    Apr 2010
    Posts
    6

    Default Re: Import a whole text file into a single cell

    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.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com