1

I have a folder that contains about 50 small txt files. What I need is to import them into a SINGLE Excel sheet one under another. I should start with:

textfile1.txt

line1
line2
line3
line4

textfile2.txt

line1
line2
line3
line4

textfile3.txt

line1
line2
line3
line4

textfile4.txt

line1
line2
line3
line4

and so on...

What I would like to end up with is:

It should basically look like a directory with a file name in column A and THE ENTIRE TEXT of a corresponding file in the same row of a column B. The text of an entire file needs to be pasted into ONE cell.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
Annabanana
  • 91
  • 1
  • 3
  • 13
  • You want each text file imported, line by line, in to Excel? "The text of an entire file needs to be pasted into ONE cell." Then what's "line 1 / line 2 / ..."? What have you tried so far? – BruceWayne Sep 20 '18 at 18:22
  • You could probably achieve this with Power Query (using M functions like `Folder.Files`, `File.Contents`, `Lines.FromBinary`, etc). Regardless of how you bring the data on to the worksheet itself, cells can only contain a limited number of characters, I think. So do you think any of your 50 text files exceed this limit -- and how do you want to handle instances where the limit is encountered? – chillin Sep 20 '18 at 18:36
  • I have added multiple lines to the text description to emphasize that the resulting import should not be delimited. Separate lines of the text that is in the same file should be pasted into the same cell. Each file's text should be pasted into a unique cell. – Annabanana Sep 20 '18 at 18:47
  • @chillin, none of them exceed the cell limit. They are actually short SQL scripts saved as text files. Speaking of SQL. Is there a way to do the same as I am asking but with SQL files? I have opened and saved my SQL files as .txt first. But if I can skip the step, it could be good. Say: open different SQL files and copy the script text into separate Excel cells. Can that be done? – Annabanana Sep 20 '18 at 18:53
  • @Annabanana, if you can open SQL files in a plain text editor (e.g. Notepad on Windows), then you can just use JosephC's solution. Plain text files are just plain text, regardless of their file extension - I believe. – chillin Sep 20 '18 at 19:01
  • @chillin, yes, but I do not want to open 50 files one-by-one if I don't have to. I wonder if I can open my .sql files without converting them to text and copy the script as a text? – Annabanana Sep 20 '18 at 19:03
  • @Annabanana, maybe I did not make myself clear in my previous comment. 'plain text files' = .txt, .csv, etc (.sql too if you can open the .sql file directly in any plain text editor). My point was that if you can view your sql file in Notepad, you should be able to use JosephC's solution directly on the 50 sql files -- without needing to convert them all to .txt files. If your point was that you want to copy-paste the SQL files from somewhere else (i.e. not the disk/file system), then you will need to share details of where/how they are stored. – chillin Sep 20 '18 at 19:33
  • 1
    Also, if you want the solution's code to be a bit more specific/explicit, try replacing the line `Call ScanDir("C:\temp\test2\")` with `Call ScanDir("C:\temp\test2\*.sql")`. Also, the `Call` keyword is unnecessary/deprecated, but does no harm either way (think you would need to get remove brackets if you got rid of `Call`). – chillin Sep 20 '18 at 19:36
  • 2
    Great Idea @chillin, unfortunately the posted version won't work with *.sql, only because I use the path given to build the full filename when opening the file `Open DirPath & oCurFile For Input As #1`. Not that it couldn't be changed to work that way. – JosephC Sep 20 '18 at 19:53
  • You are correct, JosephC. I did not read your code in enough detail. – chillin Sep 20 '18 at 20:10

1 Answers1

2

This will read all files in the directory specified, and read them as text files outputting the file name in column A and the contents in column B on sheet 1 (starting on row 2). Note: No error checking included.

Sub Test()
    Call ScanDir("C:\temp\test2\")
End Sub


Private Sub ScanDir(ByVal DirPath As String)
    Dim oCurFile As String
    oCurFile = Dir(DirPath)

    Dim oCurRow As Long
    oCurRow = 2
    Dim oFile As String
    Do While oCurFile <> ""
        Open DirPath & oCurFile For Input As #1
        oFile = Input(LOF(1), 1)
        Close #1

        Sheet1.Cells(oCurRow, 1).Value = oCurFile
        Sheet1.Cells(oCurRow, 2).Value = oFile

        oCurFile = Dir()
        oCurRow = oCurRow + 1
        oFile = ""
    Loop
End Sub
JosephC
  • 917
  • 4
  • 12
  • This worked. Now, can you open .sql files in the same manner and copy the script text into an excel cell? – Annabanana Sep 20 '18 at 18:54
  • There is no error checking & no filtering of files based on extensions. It will open any file in the directory and assume it's contents are text and read it. So just make sure the files in the directory contain text or it probably won't be happy. :) – JosephC Sep 20 '18 at 19:00
  • Got it. Thank you. Currently, I am converting .sql files into .txt files first. Can I open my .sql directly and copy the text in the same manner thus avoiding the conversion step? – Annabanana Sep 20 '18 at 19:05
  • 1
    You shouldn't need to convert anything. .sql are plain text files. Test it if you want.. just copy a couple of your .sql files in your target directory and run the code. – JosephC Sep 20 '18 at 19:30