Mursalin Ibne Salehin
In this article, we will learn to convert an Excel file to a text file with comma delimited. Text files are easily readable and consume less memory. Also, it is easy to export or import datasets using a text file. Today, we will demonstrate 3 methods. After learning the methods, you can convert both single and multiple excel files to text files. So, without any delay, let’s start the discussion.
Download Practice Book
Download the practice book here.
3 Methods to Convert Excel File to Text File with Comma Delimited
To explain the methods, we will use a dataset that contains information about the Sales Amount of some sellers in an excel file. We will convert the Excel file to a text file with comma delimited.
1. Convert Excel File to Text File with Comma Delimited Using the File Tab
In the first example, we will convert a single sheet of an excel file to a text file using the File tab. We can convert a single Excel file using the following method.
Let’s follow the steps below to learn more.
STEPS:
- In the first place, go to the File tab in the ribbon.
- Secondly, select Save As.
- Thirdly, in the save as type field, select CSV (Comma delimited) (*.csv).
- After that, click Save to proceed.
- After clicking Save, a warning message will pop up. Click OK to proceed.
- The message says you cannot convert multiple sheets at a time. To save multiple sheets, you need to convert them individually.
- Finally, go to the directory where you have saved the text file.
- Then, open it with a text editor app to see results like the picture below.
Read More: Macro to Convert Excel to Pipe Delimited Text File (3 Methods)
2. Transform Excel File to Text File Keeping Special Characters with Comma Delimited
Sometimes, our excel sheet contains special characters. You can also transform the excel file into a text file keeping special characters with comma delimited. The process is slightly different from the previous one. You can see some special characters in the dataset below.
Let’s pay attention to the steps below to know the method.
STEPS:
- First of all, select the File tab in the ribbon.
- In the following step, select Save As.
- After that, in the save as type field, select CSV UTF-8 (Comma delimited) (*.csv).
- Next, click Save to proceed.
- A message will pop up. Click OK to convert the excel file into a text file.
- Finally, go to the directory where you have saved the converted file and open it to see results like the image below.
Read More: How to Apply Macro to Convert Multiple Excel Files to CSV Files
Similar Readings
- How to Convert Multiple Excel Files to CSV (3 Suitable Ways)
- [Fixed!] Excel Not Saving CSV with Commas (7 Possible Solutions)
- How to Convert Excel to CSV without Opening (4 Easy Methods)
- Set Comma Instead of Semicolon as Delimiter in Excel CSV
3. Apply VBA to Change Excel File to Text File with Comma Delimited
We have already stated that you can’t convert multiple excel sheets using Method-1 and Method-2. But, you can easily convert multiple excel sheets using simple VBA code. This is really easy and helpful. Let’s observe the steps below to know the technique.
STEPS:
- Firstly, go to the Developer tab and select Visual Basic.
- In the second step, select Insert and then, select Module. This will open the Module window.
- After that, type the code in the Module window:
Sub Convert_Multiple_Excel_File_to_Text()
Dim xWks As Worksheet
Dim xPath As String
Application.ScreenUpdating = False
xPath = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, _
InStr(ActiveWorkbook.Name, ".") - 1)
For Each xWks In Worksheets
   xWks.Copy
   ActiveWorkbook.SaveAs Filename:=xPath & "_" & xWks.Name & ".csv", _
   FileFormat:=xlCSV, CreateBackup:=False
   ActiveWorkbook.Close False
Next
Application.ScreenUpdating = True
End Sub
- After typing the code, press Ctrl + S to save the code.
- Then, press the F5 key on the keyboard to run the code.
- After running the code, you will see multiple converted files in the directory where the excel file is saved.
- Finally, open them with a text editor app to see results like the picture below. You may lose some information in the process like the special characters.
Read More: Convert Excel to Comma Delimited CSV File (2 Easy Ways)
Things to Remember
There are certain things you need to remember when you are converting an excel file into a text file with comma delimited.
- You can convert only a single sheet using Method-1 & Method-2.
- To convert multiple sheets, convert them individually or use Method-3.
Conclusion
In this article, we have demonstrated 3 easy methods to Convert an Excel File to a Text File with Comma Delimited. We have used different cases to explain them. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
