Shulou(Shulou.com)06/02 Report--

How to use python to extract specific information from txt files and write it into Excel. In view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Problem description:

I have such a data set called test_result_test.txt, probably hundreds of thousands of rows, separated by a blank line between the two rows of data.

N:505904X:0.969wsecY:0.694wsec

N:506038X:4.246wsecY:0.884wsec

N:450997X:8.472wsecY:0.615wsec

...

Now I want to be able to extract the numbers following each line of X: and Y:, and then save them into Excel for further data processing and analysis.

Take the first line, for example, I only need 0.969 and 0.694. The specific location of three numbers in each row is uncertain, so we can't deal with it with a fixed number of columns. It just happens that the split function can slice the text, so here we use this function to extract the digital information we need.

The split function syntax is as follows:

1. Split () function

Syntax: str.split (str= "", num=string.count (str)) [n]

Parameter description:

Str: represents as a delimiter, defaults to a space, but cannot be empty (''). If there is no delimiter in the string, the entire string is taken as an element of the list

Num: indicates the number of splits. If there is a parameter num, it is only separated into num+1 substrings, and each substring can be assigned to a new variable

[n]: indicates that the nth slice is selected

Note: when using spaces as delimiters, items that are empty in the middle are automatically ignored

So for our text here, we can first slice it with ":" and divide the text into three parts, for example, for the first line.

Slice with ":" to get

Take the third slice and slice it into "w" slices.

The first slice here is the X coordinate we want.

Finally, let's analyze the ideas:

First, locate the file location to read the contents of the txt file, remove the blank lines to save the Excel preparation, create a new Excel table, and edit the title to write data in place. for each line of data, first slice with':', then slice with'w' to get the desired number, and then write to Excel to save

Tools:

Visual studio 2017 with python module installed

Package: os,xlwt

Action:

First import the bags we need.

Import osimport xlwt

1. Find the file we want to process, so go to the specified location and locate the file

A = os.getcwd () # get the current directory, print (a) # print the current directory, os.chdir ('Dos.chdir planner') # navigate to the new directory, please modify a = os.getcwd () # to get the directory after location print (a) # print the directory after location

two。 Open our txt file to check the contents (this step is optional)

# read the contents of the target txt file and print it to show with open ('test_result1.txt','r') as raw: for line in raw: print (line)

3. Remove blank lines and save

# remove the blank lines from txt and save them to the new file with open ('test_result1.txt','r',encoding =' utf-8') as fr, open ('output.txt','w',encoding=' utf-8') as fd: for text in fr.readlines (): if text.split (): fd.write (text) print ('success')

After execution, there is an extra txt file in the same location.

4. Create an Excel file

# creating a workbook object is equivalent to creating an Excel file book = xlwt.Workbook (encoding='utf-8',style_compression=0)''Workbook class initialization with encoding and style_compression parameters encoding: set character encoding, generally set: W = Workbook (encoding='utf-8'), you can output Chinese in excel. The default is ascii. Style_compression: indicates whether it is compressed or not, which is not commonly used.''

5. Create a sheet object

# create a sheet object, and a sheet object corresponds to a table in the Excel file. Sheet = book.add_sheet ('Output', cell_overwrite_ok=True) # where Output is the name of the table, cell_overwrite_ok, indicating whether cells can be overridden, which is actually a parameter instantiated by Worksheet. The default value is False.

6. Add the basic data title to the table. Here are the X and Y coordinates.

# add the data header sheet.write (0,0,'X') # where'0-row, 0-column 'specifies the cell in the table, and' X' is the content written to the cell sheet.write (0,1,'Y')

7. Cut the data many times and locate the required parts and save them into Excel

# slice the text content multiple times to get the desired part of n=1with open ('output.txt' 'ringing') as fd: for text in fd.readlines (): x=text.split (':') [2] y=text.split (':') [3] print (x.split ('w')) print (y.split ('w')) sheet.write (npen0 X.split ('w') [0]) # write the X coordinate sheet.write to the table (nMagne1magnetic.split ('w') [0]) # write the Y coordinate n = n to the table finally Save the above operations to the specified Excel file book.save ('Output.xls')

Now locate to the previously defined file location, found that there is another Excel table, open Excel, want the data neatly arranged to lie in it, comfortable ~

At this time, the data is in text format. For further processing, please use Excel to convert it to digital format.

On how to use python to extract specific information in the txt file and write the answer to the Excel question here, I hope the above content can be of some help to you, if you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.