用于将文本分隔的 .txt 批量转换为 .xls 的 Applescript [英] 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 或小程序来放入 5 个文件.Google Sheets 脚本会更神奇.提前感谢您的任何指导.

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

-30c9a8329fa^910201:

推荐答案

您需要在变量 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.

或者，如您在问题中所述，您可以创建一个小程序，可以在其中放置和处理 CSV 文件.这将通过 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.

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