代码之家 › 专栏 › 技术社区 › user7852656

如何读取文件夹中的文本文件并保存到excel文件中

xlwt operating-system python-3.x excel

user7852656 · 技术社区 · 6 年前

0.907831
0.992549

我想创建一个主excel文件,将文本文件中的所有值合并在一起(而不是手动输入它们)。

所需的输出如下所示。

'Filename' 0.907831 0.992549

到目前为止,我有以下代码。

import xlwt
import os
import fnmatch

path='Z:\Data\13-output'
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('data')
row = 0


for files in os.walk(path):
     for file in files:
         if fnmatch.fnmatch(file, '*.txt'):
             L = open(os.path.join( file), "r").read()
             sheet.write(row,5,L)
             row += 1

wbk.save('all_values_in_txt.xls')

all_values_in_txt.xls '. 但是,excel表是空白的。关于如何改进/修复代码有什么想法吗?

编辑1(通过将fnmatch更改为fnmatch.fnmatch文件):我意识到我有以下错误的一些问题, if fnmatch(file, '*.txt'): TypeError: 'module' object is not callable

编辑2:我现在遇到了新的错误

  File "<ipython-input-81-ddeb0284f378>", line 17, in <module>
    if fnmatch.fnmatch(file, '*.txt'):

  File "C:\Users\JohnDoe\Anaconda3\lib\fnmatch.py", line 34, in fnmatch
    name = os.path.normcase(name)

  File "C:\Users\JohnDoe\Anaconda3\lib\ntpath.py", line 48, in normcase
    s = os.fspath(s)

TypeError: expected str, bytes or os.PathLike object, not list

1 回复 | 直到 6 年前

ASH 6 年前

Sub ReadFilesIntoActiveSheet()

Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range

' Get a FileSystem object
Set fso = New FileSystemObject

' get the directory you want
Set folder = fso.GetFolder("C:\your_path_here\")

' set the starting point to write the data to
'Set cl = ActiveSheet.Cells(1, 1)
Dim sht As Worksheet
Dim LastRow As Long

Set sh = ActiveSheet

' Loop thru all files in the folder
For Each file In folder.Files
    ' Write file-name
    LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1
    Range("A" & LastRow).Select
    ActiveCell = file.Name

    ' open the file
    Set txtFile = fso.OpenTextFile(file)

    col = 2
    Do While Not txtFile.AtEndOfStream
        dat = Application.Transpose(Application.Index(Split(txtFile.ReadLine, ","), 1, 0))
        sh.Cells(LastRow, col).Resize(UBound(dat), 1) = dat
        col = col + 1
    Loop

    ' Clean up
    txtFile.Close
    'Range(cl.Address).Offset(1, 0).Select
Next file

Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing

End Sub

请注意,您需要从Excel中的模块运行此操作。