Applescript 用于将分隔.txt文本批量转换为.xls

Question

每天，我都会收到~5个用"^"分隔的.txt数据文件。将每个电子表格转换为电子表格的手动步骤： • 在 Excel 中打开.txt文件 • 文本到列 • 运行分隔向导

很想有一个苹果脚本或小程序将 5 个文件放入其中。谷歌表格脚本会更加神奇。提前感谢您的任何指导。

示例数据：

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

Answer 1

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

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 文件保存到其中，则此行：

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文件;然后执行脚本。

或者，正如您在问题中所述，您可以创建一个小程序，可以在其上删除和处理CSV文件。 这将通过Automator完成。