Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Export or Save Each Row as Text File in Excel
Excel is a powerful spreadsheet program developed by Microsoft. It is widely used for organizing, analysing, and manipulating data in various industries and professions. To export multiple columns into individual text files in Excel, you can use VBA (Visual Basic for Applications) macros.
Steps to Export or Save Each Row as Text File in Excel
Here's an example of how you can do it:
Step 1 :Open the Excel file in which you want to export the rows as text files. Press Alt + F11 to open the Visual Basic Editor in Excel.
Insert a new module by clicking on "Insert" and selecting "Module." In the module window, paste the following code:
Example
Sub ExportRowsAsTextFiles()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowNum As Long
Dim rowRange As Range
Dim cellValue As String
Dim filePath As String
Dim cell As Range
Set ws = ThisWorkbook.ActiveSheet ' Change to the appropriate sheet if needed
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in column A
' Loop through each row
For rowNum = 1 To lastRow
' Set the range for the current row
Set rowRange = ws.Range("A" & rowNum & ":" & ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Address)
' Initialize cellValue as an empty string
cellValue = ""
' Loop through each cell in the row
For Each cell In rowRange
' Check the data type of the cell value
Select Case True
Case IsNumeric(cell.Value) ' Numeric value
cellValue = cellValue & CStr(cell.Value) & ","
Case IsDate(cell.Value) ' Date value
cellValue = cellValue & Format(cell.Value, "dd-mm-yyyy") & ","
Case Else ' Text value or other types
cellValue = cellValue & CStr(cell.Value) & ","
End Select
Next cell
' Remove the trailing comma
cellValue = Left(cellValue, Len(cellValue) - 1)
' Define the file path for the text file (change as needed)
filePath = "E:\Assignments\3rd Assignments\How to export or save each row as text file in Excel\Output\file_" & rowNum & ".txt"
' Export the row as a text file
Open filePath For Output As #1
Print #1, cellValue
Close #1
Next rowNum
MsgBox "Rows exported to individual text files."
End Sub
Step 2 : Modify the code if needed:
Set the "ws" variable to the worksheet you want to export the rows from. By default, it exports from the active sheet.
Adjust the column reference in ws.Range("A" & rowNum & ":" & ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Address) to match the column range you want to export.
Update the filePath variable to the desired path where you want to save the text files. The example code saves them with a file name of file_1.txt, file_2.txt, etc., in my folder. Make sure to change the path to a valid directory.
Step 3 : Run the macro by pressing Alt + F8, selecting " ExportRowsAsTextFiles," and clicking "Run."
The code will iterate through each row in the specified worksheet, concatenate the values in that row into a comma−separated string, and save it as a text file with the specified file path. Each row will have its own text file.
Conclusion
To export each row in an Excel file as a separate text file, you can utilize VBA (Visual Basic for Applications) code. The code iterates through each row of the specified worksheet, concatenating the values in that row into a comma−separated string. The string is then saved as a text file with a unique file path for each row.
The code includes error handling to handle different data types within the row. It checks if a value is numeric, a date, or falls into the "else" category, which covers text and other types. The appropriate conversion is applied, and the values are appended to the cellValue variable. The resulting string is saved as a text file using the specified file path. This process ensures that each row in the Excel file is exported as an individual text file, allowing for further analysis or manipulation outside of Excel.
2K+ Views
