I have a batch script which downloads some information to a csv file, then opens an Excel spreadsheet, which uses this csv as the source for a Power Query which refreshes whenever the spreadsheet is opened. After it has loaded information from the csv, I want the batch script to delete the csv.
To check if a file is locked for writing, I can use this:
:deletecsv
2>nul (
>>info.csv(call )
) && del info.csv || (goto :deletecsv)
However, surprisingly Excel does not seem to lock files for writing while a power query is searching them. At any rate my script just deletes the csv straight away, leaving an empty file for Excel to look at.
Can I detect what applications are reading the csv? Another route would be for the Power Query to delete the original file at the end, but I'm not aware that's possible. I could just timeout for an arbitrary duration, but in future there may be a lot more to download so not sure how long query will take. VBA is not an option.
The Power Query:
let
Source = Csv.Document(File.Contents("C:\Users\user\api\info.csv"),[Delimiter=",", Columns=31, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"activityid", Int64.Type}, {"activityenddate", type datetime}, {"activityendtime", type time}, {"activityfrequency", Int64.Type}, {"activitystartdate", type datetime}, {"activitystarttime", type time}, {"activitysubtype", type text}, {"activitytype", type text}, {"ambassadorenddate", type datetime}, {"ambassadorstartdate", type datetime}, {"attendedstudentscount", Int64.Type}, {"contacthours", type number}, {"customdata", type text}, {"deliveredbytype", Int64.Type}, {"displaysummary", type text}, {"eventtitle", type text}, {"feyear", Int64.Type}, {"heatlevelofintervention", Int64.Type}, {"heidescriptor", type text}, {"interaction", Int64.Type}, {"locationid", Int64.Type}, {"notes", type text}, {"projectidentifier", type text}, {"recordcreateddate", type datetime}, {"recordupdateddate", type datetime}, {"systemnotes", type text}, {"totalparents", Int64.Type}, {"totalstaff", Int64.Type}, {"yeargroups", type text}, {"activitybeneficiaryinstitutions", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "yeargroups", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"yeargroups.1", "yeargroups.2", "yeargroups.3", "yeargroups.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"yeargroups.1", type text}, {"yeargroups.2", type text}, {"yeargroups.3", type text}, {"yeargroups.4", type text}, {"activityenddate", type date}, {"activitystartdate", type date}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "activitysubtype", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"activitysubtype.1", "activitysubtype.2", "activitysubtype.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"activitysubtype.1", type text}, {"activitysubtype.2", type text}, {"activitysubtype.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"activitysubtype.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","name=","",Replacer.ReplaceText,{"activitysubtype.2"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"activitysubtype.3"}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns1", "activitytype", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"activitytype.1", "activitytype.2", "activitytype.3"}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Split Column by Delimiter2", {{"customdata", each Text.BetweenDelimiters(_, "}", "Learner Outcome=", {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd}), type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text Between Delimiters", {{"customdata", each Text.BeforeDelimiter(_, "event?="), type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Extracted Text Before Delimiter","@{Is this event a large scale carousel ","",Replacer.ReplaceText,{"customdata"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Replaced Value1", "customdata", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"customdata.1", "customdata.2", "customdata.3", "customdata.4", "customdata.5", "customdata.6", "customdata.7"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"activitytype.1", type text}, {"activitytype.2", type text}, {"activitytype.3", type text}, {"customdata.1", type text}, {"customdata.2", type text}, {"customdata.3", type text}, {"customdata.4", type text}, {"customdata.5", type text}, {"customdata.6", type text}, {"customdata.7", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"customdata.1", "Outcome"}, {"customdata.2", "Theme"}}),
#"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns"," Progression Programme Theme=","",Replacer.ReplaceText,{"Theme"}),
#"Removed Columns2" = Table.RemoveColumns(#"Replaced Value2",{"customdata.3"}),
#"Replaced Value3" = Table.ReplaceValue(#"Removed Columns2","Platform=","",Replacer.ReplaceText,{"customdata.4"}),
#"Removed Columns3" = Table.RemoveColumns(#"Replaced Value3",{"customdata.5", "customdata.6"}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns3", {{"customdata.7", each Text.AfterDelimiter(_, "="), type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"customdata.4", "Platform"}, {"customdata.7", "Phase"}}),
#"Extracted Text Between Delimiters1" = Table.TransformColumns(#"Renamed Columns1", {{"heidescriptor", each Text.BetweenDelimiters(_, "name=", ";"), type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Extracted Text Between Delimiters1",{"notes"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns4",{{"recordcreateddate", type date}, {"recordupdateddate", type date}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type4",{"systemnotes", "yeargroups.1", "yeargroups.2", "yeargroups.4"}),
#"Replaced Value4" = Table.ReplaceValue(#"Removed Columns5","name=","",Replacer.ReplaceText,{"yeargroups.3"}),
#"Extracted Text Between Delimiters2" = Table.TransformColumns(#"Replaced Value4", {{"activitybeneficiaryinstitutions", each Text.BetweenDelimiters(_, " name=", ";"), type text}}),
#"Removed Columns6" = Table.RemoveColumns(#"Extracted Text Between Delimiters2",{"activitytype.1", "activitytype.3"}),
#"Replaced Value5" = Table.ReplaceValue(#"Removed Columns6","name=","",Replacer.ReplaceText,{"activitytype.2"}),
#"Extracted Text Between Delimiters3" = Table.TransformColumns(#"Replaced Value5", {{"institutiongroup", each Text.BetweenDelimiters(_, " name=", ";"), type text}})
in #"Extracted Text Between Delimiters3"