Applescript用于将文本分隔的.txt批量转换为.xls [英] Applescript for batch-converting text delimited .txt to .xls

问题描述

每天，我都会收到〜5个以"^"分隔的.txt数据文件.手动将每个步骤转换为电子表格的步骤: •在Excel中打开.txt文件 •文本到列 •通过定界向导

Each day, I'm delivered ~5 .txt data files delimited with "^". Manual steps taken to convert each to spreadsheet: • Open .txt file in Excel • Text-To-Columns • Run through the delimiting wizard

希望有一个applescript或applet可以将5个文件放入其中. Google表格脚本会更加神奇.在此先感谢您的指导.

Would love to have an applescript or applet to drop the 5 files into. A Google Sheets script would be even more magical. Thanks in advance for any guidance.

样本数据:

developer^project^lender^uuid^id^remarks^code^transfer_date
1500^1502^009^f1e97d20-b311-41cf-a40f-59db90b25ba8^73890^a10a46e8-bca8-4f0d-8938-8f2803a8bf90^9^2018-10-23 10:17:23.0
1500^1502^009^5dfc330d-0b9a-407d-a9e6-36895207b89e^74460^4a9c046a-a544-45b5-a627-f567b94f2b87^9^2018-10-23 10:17:25.0
1500^1502^009^d3295a4a-235d-4b9d-8775-5c079571193e^74901^de8f7b66-0c14-450f-8f29-c30c9a8329fa^9^2018-10-23 10:17:26.0

推荐答案

您需要在脚本开始时更改变量CSVFiles中提供的文件路径. Excel 文件与它从中获取数据的CSV文件保存在同一目录中，并使用相同的文件名，并在文件名后附加扩展名".xlsx".

You'll need to change the file paths supplied at the start of the script inside the variable CSVFiles. The Excel file is saved in the same directory as the CSV file from which it sources its data, and uses the same filename, appending the extension ".xlsx" to it.

use Excel : application "Microsoft Excel"
use scripting additions

# Used to split the CSV data into columns
property text item delimiters : {"/", "^"}

# These represent the 5 CSV files you are sent on a given day
set CSVFiles to {¬
    "/Users/CK/Desktop/sample.csv", ¬
    "/Users/CK/Desktop/sample2.csv", ¬
    "/Users/CK/Desktop/sample3.csv", ¬
    "/Users/CK/Desktop/sample4.csv", ¬
    "/Users/CK/Desktop/sample5.csv"}

repeat with fp in CSVFiles
    try
        # Make sure CSV file exists
        fp as POSIX file as alias
    on error
        false
    end try
    set f to the result

    if f ≠ false then
        # Obtain directory and filename to use for saving Excel document
        set [dirpath, filename] to [¬
            text items 1 thru -2 of POSIX path of f as text, ¬
            text item -1 of POSIX path of f]

        # Read the CSV data into an array (list)
        set CSVrows to paragraphs of (read f)
        if the last item of CSVrows = "" then ¬
            set CSVrows to items 1 thru -2 of CSVrows

        repeat with r in CSVrows
            set r's contents to text items of r
        end repeat

        set n to count CSVrows
        set cellrange to "A1:H" & n
        set colHrange to "H2:H" & n

        # Create the Excel sheet
        make Excel new document
        set S to active sheet of window 1 of Excel
        # Copy in CSV data
        set value of range cellrange to CSVrows
        # Format the last column to handle dates & times
        set number format of range colHrange to "dd/mm/yyyy hh:mm:ss"

        # Save & Close
        save S in (dirpath & "/" & filename & ".xlsx")
        Excel's (close front window)
    end if
end repeat

如果您有一个专用文件夹将新收到的CSV文件保存到其中，则此行:

If you had a dedicated folder into which you saved your newly received CSV files, then this line:

set CSVFiles to {¬
    "/Users/CK/Desktop/sample.csv", ¬
    "/Users/CK/Desktop/sample2.csv", ¬
    "/Users/CK/Desktop/sample3.csv", ¬
    "/Users/CK/Desktop/sample4.csv", ¬
    "/Users/CK/Desktop/sample5.csv"}

可以用以下行代替:

tell app "System Events" to set CSVFiles to the POSIX path of ¬
    every file in the folder named "/Path/To/Folder for CSV files" whose ¬
    name extension = "CSV"

然后，您所需要做的就是从该目录中删除昨天的CSV文件(您不希望第二次对其进行处理)；传输当今的CSV文件；然后执行脚本.

Then, all you'd need to do is remove yesterday's CSV files from that directory (you don't want them being processed a second time); transfer in today's CSV files; then execute the script.

或者，如您在问题中所述，您可以创建一个applet，可以将CSV文件拖放到该applet上并进行处理.这可以通过 Automator 完成.

Alternatively, as you stated in your question, you can create an applet onto which CSV files can be dropped and processed. This would be done via Automator.

这篇关于Applescript用于将文本分隔的.txt批量转换为.xls的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！