Search code examples
pythonexcelreadfile

How to read a file and copy certain numbers from the use of keywords into excel or another txt file?

I have a file that I am currently just manually transferring the numbers from certain sections into excel and was hoping to instead write them into a txt file or directly into excel.

The original file is a sum file (although essentially is just a txt file)

And part way through the file it has a number of CP's I want to obtain the v= value and G= value. And then have them in array on a txt file.For example in the file it has this printed half way down:

enter image description here

  import os
  from glob import glob

  list_of_files = glob('*.sum')  # get list of all .sum files

  for file in list_of_files:
      with open(file, 'r') as f: 
          content = f.read()

          # not sure how to find the keyword 'i.e. G=' and print what is 
          # behind 
          g = content        

          f = open("excelsheet.txt", "w+")   
          f.write(content)
Solution

  • You can use the csv and re packages to find the lines you need.

    Read in a file as you have shown. I'm testing my scipt with the following "text file"

    text = '''
CP# 1
V = -3.02132E+02
G = -5.15550E+12
K = -9.84693E+06
L =  1.16878E+06

CP# 2
V =  5.53209E+08
G =  6.06581E+08
K = -7.41506E+18
L = -9.87995E+02

CP# 3
V =  5.47807E+08
G = -5.03863E+00
K = -1.09701E+12
L = -5.30051E+09
'''

    Now that you have the file loaded, use the re package to find the matches you are looking for. You can use the link above to learn more about the syntax.

    h_matches = re.findall(r'CP#\s\d', text)
v_matches = re.findall(r'(?<=V\s=)\s+[0-9+-E]+', text)
g_matches = re.findall(r'(?<=G\s=)\s+[0-9+-E]+', text)

headers  = [h for h in h_matches]
v_values = [m for m in v_matches]
g_values = [m for m in g_matches]

    Once you have all the matches, use csv to write out the data to a csv file.

    with open('example.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow(['header', 'V', 'G'])

    for h, v, g in zip(headers, v_values, g_values):
        writer.writerow([h, v, g])

    Below is the output in example.csv

    header,V,G
CP# 1, -3.02132E+02, -5.15550E+12
CP# 2,  5.53209E+08,  6.06581E+08
CP# 3,  5.47807E+08, -5.03863E+00

    If you are looking to write into an existing excel sheet, you can checkout xlsxwriter