How to convert text files to Excel (TXT to XLSX): 8 methods

If you happen to use textual content recordsdata to retailer and handle information, you have in all probability confronted the problem of changing these recordsdata into CSV, XLSX, or different Excel-compatible codecs.

Excel is a powerful tool for automation, information evaluation, modeling, reporting, and extra. It might probably even deal with giant quantities of information effectively.

Nevertheless, you could first carry your information into Excel to benefit from these capabilities. Copying and pasting the info manually from a TXT file to Excel isn’t possible.

The info would possibly find yourself pasted in a single column or the unsuitable cells, and it may be time-consuming, particularly with bigger recordsdata. Plus, you danger shedding or misplacing vital info.

This text will cowl tips on how to convert textual content to Excel utilizing on-line and offline instruments and strategies.

Additionally learn: How to convert Word to Excel?

Here is a fast overview of the totally different strategies:

1. Open the textual content file utilizing Excel

This can be a easy methodology to transform text to Excel. No extra software program or on-line instruments are required.

Simply observe the steps beneath:

1. Open Excel and choose the ‘File’ choice from the menu
2. Click on on ‘Open’ and navigate to the situation of your file
3. Choose ‘All Recordsdata (*.*)’ from the drop-down menu within the decrease proper nook
4. Find and choose your textual content file, then click on ‘Open’ (Word that Excel can deal with varied textual content file codecs, together with .txt, .csv (comma-separated values), and .tsv (tab-separated values))
5. Excel’s Textual content Import Wizard will seem — specify how Excel ought to interpret the info in your textual content file
6. Select the suitable Delimiter (comma, tab, or different) that separates your information
7. Preview your information and modify any settings as wanted, then click on ‘End’. (Use Excel’s formatting choices to appropriate information formatting errors with dates or numbers)
8. Your textual content information will now seem in an Excel spreadsheet

Whereas this methodology is quick and easy, it might battle with poorly formatted content material, information containing a mixture of numbers, textual content, and dates, or utilization of non-standard delimiters. Excel could not parse and interpret such recordsdata appropriately, leading to inconsistencies.

Finest suited to: Importing information from sources like exported reviews, log recordsdata, or easy information backups.

2. Copy-paste the tables from Notepad to Excel

One other easy solution to convert textual content to Excel is to repeat the info from a textual content editor like Notepad and paste it into an Excel worksheet. Excel’s Textual content Import Wizard allows you to simply copy and paste tabular information from textual content recordsdata whereas sustaining the construction.

To repeat a textual content desk from Notepad to Excel utilizing the Textual content Import Wizard:

1. Open the textual content file in Notepad or another textual content editor
2. Choose and replica the tabular information you need to convert
3. Maintain a notice of how the values are separated (comma, tab, house, and many others.) — the delimiter used
4. Open a brand new Excel workbook and choose the cell the place you need to paste the desk
5. Paste and Choose the Contents. As soon as chosen go to Information -> Textual content to Columns.
6. The Textual content to Columns dialog field will open — choose ‘Delimited‘ and click on ‘Subsequent’
7. Choose the delimiter utilized in your textual content file (comma, tab, house, and many others.) and click on ‘Subsequent’
8. Specify the info format for every column or use the default ‘Common’ format
9. Click on ‘End’, and the copied information will seem within the Excel worksheet, sustaining the unique tabular construction.

This methodology works properly for small tables, however the information have to be cleanly formatted, with correct delimiters and no lacking or additional values. The output Excel desk is probably not structured appropriately if it accommodates inconsistencies or irregular formatting. Additionally, it’s not perfect for big datasets as a result of you need to manually copy and paste information, which may be time-consuming and susceptible to errors.

Finest suited to: Transferring information from easy lists or tables with a restricted variety of rows and columns like stock data and buyer lists.

3. Use Excel’s ‘Get Information’ Function

Excel’s ‘Get Information’ characteristic is extra environment friendly for importing and changing text to Excel. This characteristic helps quite a lot of information sources, together with textual content recordsdata.

Listed below are the steps to observe:

1. Open Excel and choose the ‘Information’ tab from the menu bar
2. Click on on the ‘Get Information’ choice and navigate to ‘From Textual content/CSV’
3. Find and choose your textual content file, then click on ‘Import’
4. Excel will preview your information — select the suitable delimiter, file origin, and information sort detection setting, then click on ‘Load‘
5. Get the textual content information in an Excel window, prepared for additional evaluation and reporting

Nevertheless, Get Information could misread fields like string dates because of inconsistent codecs, zeros, or regional settings, resulting in incorrect information imports or conversion errors. So, the info could require some pre-processing earlier than conversion.

It is very important notice that the ‘Get Information’ characteristic is simply out there in newer variations of Excel (2016 and later). If you happen to’re utilizing an older model, chances are you’ll have to improve or strive a unique methodology.

Finest suited to: Importing information from gross sales reviews, transaction logs, financial institution statements or stock reviews, and log recordsdata from internet servers or functions.

4. Convert Textual content to Excel with on-line convertors

These on-line converters can present output in varied Excel-compatible codecs, resembling CSV, XLS, and XLSX, in a matter of clicks. No technical experience or software program set up is required.

A few of the well-liked on-line converters embody:

These instruments are easy to make use of. Usually, it’s essential:

1. Go to the converter’s web site
2. Add your textual content file
3. Choose the specified output format (e.g., CSV, XLS, or XLSX)
4. Click on ‘Convert’ or the same command
5. Obtain the transformed file and open it in Excel for additional use

Whereas on-line converters are useful, they is probably not your best option for delicate or confidential information. As these instruments require you to add and course of your recordsdata on third-party servers, there’s a danger that they could not adhere to information privateness and safety requirements. Plus, some instruments could have constraints on file sizes and lack customization choices.

Finest suited to: Changing small to medium-sized textual content recordsdata (up to a couple thousand rows) with easy, constant buildings containing contact lists, product catalogs, or survey responses.

5. Batch processing a number of textual content recordsdata utilizing VBA

If in case you have a number of textual content recordsdata that it’s essential convert to Excel format, manually opening and importing every file may be time-consuming. Happily, Excel affords a solution to automate this course of utilizing VBA (Visible Fundamental for Purposes) scripts.

By making a VBA macro, you’ll be able to loop by all of the textual content recordsdata in a specified folder and import the info from every file into a brand new worksheet inside your energetic Excel workbook. This automation can considerably velocity up your workflow when coping with a number of textual content recordsdata.

To create a VBA macro for batch processing textual content recordsdata, you will have to:

1. Open the Visible Fundamental Editor in Excel (press Alt+F11)
2. Create a brand new module and write a VBA script that does the next:

• Specifies the folder path the place your textual content recordsdata are situated
• Loops by every textual content file within the folder
• Creates a brand new worksheet for every file and units the worksheet identify primarily based on the file identify
• Imports the info from every textual content file into the corresponding worksheet

3. Run the macro (Press Alt + F8) to import all of the textual content recordsdata into your Excel workbook robotically

Sub ImportTextFileToExcel()
    Dim FilePath As String
    Dim FileNum As Integer
    Dim FileContent As String
    Dim LineItems() As String
    Dim RowNumber As Lengthy
    Dim ColNumber As Integer
    Dim Delimiter As String
    
    ' Set the file path
    FilePath = "C:pathtoyourfile.txt"  ' Replace this to the trail of your textual content file

    ' Set the delimiter (e.g., "," for CSV, "|" for pipe-separated, vbTab for tab-separated)
    Delimiter = "|"
    
    ' Open the file
    FileNum = FreeFile
    Open FilePath For Enter As FileNum
    
    ' Begin studying the file line by line
    RowNumber = 1
    Do Whereas Not EOF(FileNum)
        Line Enter #FileNum, FileContent  ' Learn a line from the file
        LineItems = Break up(FileContent, Delimiter)  ' Break up the road into objects primarily based on the delimiter
        
        ' Write the objects to the worksheet
        For ColNumber = LBound(LineItems) To UBound(LineItems)
            Cells(RowNumber, ColNumber + 1).Worth = Trim(LineItems(ColNumber))
        Subsequent ColNumber
        
        ' Transfer to the following row within the worksheet
        RowNumber = RowNumber + 1
    Loop
    
    ' Shut the file
    Shut FileNum
    
    ' Notify the consumer
    MsgBox "Textual content file has been efficiently imported to Excel!", vbInformation
Finish Sub

VBA helps you automate the conversion course of and lets you tailor the import course of to particular wants, resembling defining how information is parsed and formatted. Nevertheless, it has a steep studying curve for these unfamiliar with programming or its particular syntax, making it tough for novice customers to create and preserve macros. Chances are you’ll not need to spend effort and time debugging and troubleshooting complicated macros, particularly with their restricted error dealing with and reporting capabilities.

Finest suited to: Automating repetitive imports of numerous textual content recordsdata, resembling each day gross sales reviews, stock updates, or log recordsdata generated by inside programs.

6. Convert textual content to Excel utilizing Python libraries

If you happen to’re comfy with coding and like a extra programmatic strategy to changing textual content recordsdata to Excel, Python affords a number of highly effective libraries that may show you how to automate the method. One of the vital well-liked and versatile libraries for this activity is pandas.

Utilizing pandas, you’ll be able to simply learn textual content recordsdata right into a DataFrame, a two-dimensional information construction just like an Excel spreadsheet. The library helps varied textual content file codecs, resembling CSV (comma-separated values), TSV (tab-separated values), and fixed-width recordsdata. As soon as your information is in a DataFrame, you’ll be able to carry out a variety of information cleansing, manipulation, and evaluation duties utilizing pandas‘ built-in features and strategies.

pip set up pandas openpyxl

To transform a textual content file to Excel utilizing pandas, you could first set up the library and its dependencies (openpyxl for Excel assist). Then, you should utilize features like read_csv() or read_fwf() to load your textual content file right into a DataFrame, specifying the suitable delimiter or column widths. If wanted, you’ll be able to clear and rework your information inside the DataFrame, resembling renaming columns, filtering rows, or changing information sorts.

Lastly, you’ll be able to export the DataFrame to an Excel file utilizing the to_excel() operate. You possibly can specify the output file identify, sheet identify, and different choices like writing to a number of sheets or utilizing an ExcelWriter for extra management over the output.

import pandas as pd

# Load the textual content file right into a DataFrame
df = pd.read_csv('yourfile.txt', delimiter="|")

# Clear and manipulate the info (non-obligatory)
df.columns = ['Product Name', 'Quantity', 'Price', 'Total']
df['Quantity'] = df['Quantity'].astype(int)
df['Price'] = df['Price'].astype(float)
df['Total'] = df['Total'].astype(float)

# Export the DataFrame to Excel
df.to_excel('output.xlsx', index=False)
print("Information efficiently transformed and saved to output.xlsx")

Python affords better flexibility, customization, and automation than guide strategies. Nevertheless, the better computing necessities and steep studying curve of Python syntax and information manipulation libraries can decelerate non-programmers. You may additionally should take care of compatibility points between totally different variations of Python or dependencies, which may result in script failures or surprising conduct.

Finest suited to: Dealing with giant, complicated, or unstructured textual content recordsdata, resembling internet server logs, social media information exports, or scientific information with hundreds of thousands of data.

To keep away from writing scripts to automate the extraction course of, you’ll be able to leverage the ability of Massive Language Fashions (LLMs) like ChatGPT and Claude to transform your textual content recordsdata to Excel format. LLMs can perceive and course of pure language, making them able to dealing with structured and unstructured information.

For easy text-to-Excel conversions, you’ll be able to straight use an LLM like ChatGPT.

Here is how:

1. Add the textual content file(s) to ChatGPT.
2. Present ChatGPT with clear directions on changing the textual content information right into a structured format appropriate for Excel. For instance: “Please convert the next textual content information right into a comma-separated format appropriate for Excel”.
3. The LLM will generate a structured output primarily based in your immediate. Overview the output and make any essential changes to make sure it aligns together with your desired Excel format.
4. You possibly can copy-paste the structured output into Excel and specify the delimiter or request ChatGPT to generate a downloadable Excel file.

Now when you want to absolutely automate the workflow, you are able to do so by creating Zapier zaps that combine with ChatGPT and your most well-liked cloud storage service. Here is how:

1. Select a set off occasion in Zapier primarily based on the place your textual content recordsdata are saved (e.g., Google Drive, Dropbox, or e-mail).
2. Use the ChatGPT motion in Zapier to ship the textual content file content material to the LLM for processing. Present a transparent immediate instructing ChatGPT to transform the info right into a structured format.
3. ChatGPT will generate a structured output primarily based in your immediate. Zapier will robotically go this output to the following motion in your workflow.
4. Use a Zapier motion to ship the structured information to Excel. This may be finished by creating a brand new row in Google Sheets or saving the info as a CSV file in a cloud storage service like Dropbox.

Utilizing LLMs like ChatGPT or Claude can considerably scale back the effort and time required for text-to-Excel conversions. These AI fashions can perceive the context and construction of your information, making the method extra environment friendly and correct in comparison with conventional strategies.

Nevertheless, these AI fashions could sometimes generate inaccurate or inconsistent output. Hallucination is a standard difficulty with generative fashions, the place the AI could confidently current false info as reality. Moreover, the price of utilizing LLMs can add up rapidly for bigger volumes of information.

Finest suited to: Extract particular information factors from small to medium-sized unstructured or semi-structured textual content information datasets, resembling buyer critiques or social media posts. They are perfect for exploratory evaluation, prototyping, or conditions the place non-technical customers have to carry out ad-hoc conversions.

Whereas LLMs and Zapier present a robust mixture for automating text-to-Excel conversion, typically you want extra superior capabilities to extract structured information from complicated paperwork. As a result of LLMs haven’t got a built-in understanding of your particular information extraction necessities, their accuracy relies on the type of prompts you give.

This isn’t perfect when you need to course of a big quantity of paperwork with various codecs and buildings. Crafting the proper immediate for every doc sort may be difficult. That is the place specialised Clever Doc Processing platforms like Nanonets come in useful.

These instruments use AI fashions which are skilled to know the construction and structure of various kinds of paperwork, to allow them to precisely find and extract the info you want with out counting on complicated prompts. This protects you effort and time and ensures extra constant and dependable outcomes, even for difficult paperwork.

For the aim of this text, let’s think about log recordsdata from a big e-commerce web site for example. These log recordsdata may be huge, containing hundreds of thousands of strains of textual content information, with every line representing a consumer motion or system occasion.

Now think about you’ve gotten hundreds of thousands of strains like this, and it’s essential extract particular information factors (like User_ID, Motion, Product_ID, and Worth) and convert them right into a structured Excel format for additional evaluation.

Here is how you should utilize a instrument like Nanonets to effectively course of these giant, complicated log recordsdata:

1. Add your log recordsdata to Nanonets or join them to your cloud storage service the place the recordsdata are saved.
2. Prepare the AI mannequin by annotating a number of pattern strains, highlighting the info factors you need to extract (User_ID, Motion, Product_ID, Worth).
3. Configure the workflow to robotically course of incoming log recordsdata, specifying the file format, information fields to extract, and any validation or transformation guidelines wanted.
4. Let Nanonets extract the desired information fields out of your log recordsdata, changing the semi-structured textual content information right into a structured format prepared for export.
5. Validate the extracted information towards predefined guidelines or ship it for human assessment if wanted.
6. Export the extracted and validated information to an Excel file, utilizing Nanonets’ varied export choices and integrations.

This workflow ought to show you how to map fields from the log recordsdata to the suitable columns in your Excel sheet, making certain a clear and structured output. Additionally, you’d have the ability to auto-populate the Excel sheet with new information as extra log recordsdata are processed, retaining your evaluation up-to-date.

IDP instruments can deal with a variety of file codecs, together with textual content recordsdata, pictures, PDFs, and Excel sheets, and precisely extract structured information from each structured and unstructured paperwork.

These options allow companies to automate doc processing with out worrying about readability of prompts or workflow errors. In addition they provide options like information validation, error dealing with, and integration with different enterprise programs, making your complete course of extra sturdy and scalable.

Finest suited to: Extracting structured information from giant volumes of complicated, unstructured paperwork like invoices, receipts, or log recordsdata. They guarantee excessive accuracy and consistency in information extraction, making them perfect for automating essential enterprise processes and gaining invaluable insights from textual content information.

Ultimate ideas

Keep in mind, getting your information into Excel is simply step one. With instruments like Nanonets, you’ll be able to automate the following steps in your information processing workflow, saving time, bettering accuracy, and enhancing your total productiveness.

Whether or not you are an IT admin, a enterprise proprietor, or a knowledge analyst, having the proper instruments and strategies at your disposal could make a major distinction. Greater than getting your palms on the info, the main focus ought to be on managing, processing, and utilizing that information to drive insights and selections. 

Scale your doc information extraction now!

Learn the way Nanonets may be custom-made to your particular doc sorts and information necessities. Schedule a demo to see how one can practice customized OCR fashions and arrange workflows that match what you are promoting completely.

📅 E-book a Demo