在Mac上将.xls和.xlsx转换为.txt（Tab分隔符） [英] Mass Convert .xls and .xlsx to .txt (Tab Delimited) on a Mac

问题描述

我有大约150个.xls和.xlsx文件，我需要转换为制表符分隔。我尝试使用自动机，但我只能一个接一个地做到这一点。这绝对比单独打开每个人快。我有很少的脚本知识，所以我会很乐意做到这一点，尽可能无痛。

解决方案

如果你会准备使用Python为此我写了一个脚本，将Excel电子表格转换为csv文件。该代码在 Pastebin 中可用。

您只需需要更改以下行：

  writer = csv.writer（fileout）
  

to：

  writer = csv.writer fileout，delimiter =\t）
  

使输出文件选项卡分隔而不是标准逗号分隔。

由于此脚本会一次提示一个文件（允许您从对话框中选择），但可以轻松调整给定目录树中的所有Excel文件或名称与给定模式匹配。

如果您首先尝试使用单个文件，并让我知道如何如果您愿意，我可以帮助您更改自动化。

更新

这是一个可以使用的包装脚本：

 ＃！/ usr / bin / python 
 
 import os，sys，traceback 
 sys.path.insert（0，os.getenv（'py'））
 import excel_to_csv 
 
 def main（）：
＃退出，如果没有arg for excel dir 
 if len（sys.argv）< 2：
 print'用法：Python xl_csv_wrapper< path_to_excel_files>'
 sys.exit（1）
 else：
 xl_path = sys.argv [1] 
 
 xl_files = os.listdir（xl_path）
 valid_ext = ['.xls'，'.xlsx'，'.xlsm'] 
 
＃循环路径$ b $中的文件b for f in xl_files：
 f_name，ext = os.path.splitext（f）
如果ext_lower（）in valid_ext：
 try：
 print'arg1：' ，os.path.join（xl_path，f）
 print'arg2：'，os.path.join（xl_path，f_name +'。csv'）
 excel_to_csv.xl_to_csv（os.path.join（xl_path ，f），
 os.path.join（xl_path，f_name +'。csv'））
除了：
 print'**无法转换文件：'，f，'**' 
 exc_type，exc_value，exc_traceback = sys.exc_info（）
 lines = traceback.format_exception（exc_type，exc_value，exc_t 
打印'!!'，行
其他：
打印'成功地收敛'，f，'到.csv'
 
 
如果__name__ =='__main__'：
 main（）
  

您将需要更换：

  sys.path.insert（0，os.getenv（'py'））
  

顶部有一个绝对路径到excel_to_csv脚本或系统上的环境变量。 >

I have about 150 .xls and .xlsx files that I need converting into tab-delimited. I tried using automator, but I was only able to do it one-by-one. It's definitely faster than opening up each one individually, though. I have very little scripting knowledge, so I would appreciate a way to do this as painlessly as possible.

解决方案

If you would be prepared to use Python for this I have written a script that converts Excel spreadsheets to csv files. The code is available in Pastebin.

You would just need to change the following line:

writer = csv.writer(fileout)

to:

writer = csv.writer(fileout, delimiter="\t")

to make the output file tab delimited rather than the standard comma delimited.

As it stands this script prompts you for files one at a time (allows you to select from a dialogue), but it could easily be adapted to pick up all of the Excel files in a given directory tree or where the names match a given pattern.

If you give this a try with an individual file first and let me know how you get on, I can help with the changes to automate the rest if you like.

UPDATE

Here is a wrapper script you could use:

#!/usr/bin/python

import os, sys, traceback
sys.path.insert(0,os.getenv('py'))
import excel_to_csv

def main():
    # drop out if no arg for excel dir
    if len(sys.argv) < 2:
        print 'Usage: Python xl_csv_wrapper <path_to_excel_files>'
        sys.exit(1)
    else:
        xl_path = sys.argv[1]

    xl_files = os.listdir(xl_path)
    valid_ext = ['.xls', '.xlsx', '.xlsm']

    # loop through files in path
    for f in xl_files:
        f_name, ext = os.path.splitext(f)
        if ext.lower() in valid_ext:
            try:
                print 'arg1:', os.path.join(xl_path,f)
                print 'arg2:', os.path.join(xl_path,f_name+'.csv')
                excel_to_csv.xl_to_csv(os.path.join(xl_path,f),
                                       os.path.join(xl_path,f_name+'.csv'))
            except:
                print '** Failed to convert file:', f, '**'
                exc_type, exc_value, exc_traceback = sys.exc_info()
                lines = traceback.format_exception(exc_type, exc_value, exc_traceback)
                for line in lines:
                    print '!!', line
            else:
                print 'Sucessfully conveted', f, 'to .csv'


if __name__ == '__main__':
    main()

You will need to replace the :

sys.path.insert(0,os.getenv('py'))

At the top with an absolute path to the excel_to_csv script or an environment variable on your system.

这篇关于在Mac上将.xls和.xlsx转换为.txt（Tab分隔符）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！