import hundreds of individual text files into one sheet and put each file as the first cell in each colum

Dpalkon

New Member
Joined
Dec 4, 2019
Messages
7
Office Version
2010
Platform
Windows
I have hundreds of individual text files that i need to review and edit. they contain machine recipes. The data in the text file is arranged vertically. The first two lines are string data and then 8 lines after are integers. all the files have the same number of lines. Each line has no more than 20 character. I can drag the data very easily into excel but it creates new files for each txt file i import. I just want it to create new a new colume for each text file so i can review all the recipes side by side. I can cut and paste and do all that but my goal would also be to go the other way and take each column and create text files that have a name that's in the first column and the data in all the columns below. Am I crazy for thinking this is possible? If anyone has any suggestions I would love to hear them.
thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,978
Office Version
2007
Platform
Windows
Try this, change "Sheet1" by the name of your sheet

VBA Code:
Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1
 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub
 

Dpalkon

New Member
Joined
Dec 4, 2019
Messages
7
Office Version
2010
Platform
Windows
Try this, change "Sheet1" by the name of your sheet

VBA Code:
Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub
Try this, change "Sheet1" by the name of your sheet

VBA Code:
Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub

This worked amazing. My text files actually ended in .DAT so I changed only that small portion of your code from .csv to .dat and BAM! This was really great. Is a lot of this same code the same for going back the other way? I mean Converting each Column back into many text files that would be named "row 1 string".DAT and the data on the rest of the rows would go into the text file right down the page one line at a time.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,978
Office Version
2007
Platform
Windows
This worked amazing. My text files actually ended in .DAT so I changed only that small portion of your code from .csv to .dat and BAM! This was really great. Is a lot of this same code the same for going back the other way? I mean Converting each Column back into many text files that would be named "row 1 string".DAT and the data on the rest of the rows would go into the text file right down the page one line at a time.
I'm glad to help you. Thanks for the feedback.

You need a new code, create a new thread and explain what you need.
 

Dpalkon

New Member
Joined
Dec 4, 2019
Messages
7
Office Version
2010
Platform
Windows
Dante,
Muchas gracias!!! I created a new thread with a picture for better understanding.
 

Forum statistics

Threads
1,078,532
Messages
5,341,016
Members
399,409
Latest member
Winfield Ullrich I

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top