Файлы к уроку:
Описание
В этом уроке мы воспользуемся функцией для подключения к файлам в ZIP архиве, которую написал KenR.
Мы подключимся к файлу TXT, находящемуся в ZIP архиве на вашей локальной машине, а потом сделаем то же самое, но файл будет находится в сети интернет.
Решение
Для подключения к ZIP файлу нам понадобится функция DecompressFiles, которую написал KenR.
После того как мы применим эту функцию останется только выполнить нужные вам преобразования
Примененные функции
- BinaryFormat.Record
- BinaryFormat.Binary
- BinaryFormat.ByteOrder
- BinaryFormat.UnsignedInteger32
- ByteOrder.LittleEndian
- BinaryFormat.UnsignedInteger16
- Binary.Decompress
- Compression.Deflate
- Table.FromRecords
- Table.Combine
- Excel.CurrentWorkbook
- File.Contents
- Csv.Document
- Table.PromoteHeaders
- Table.SelectRows
- Table.SelectColumns
- Table.TransformColumnTypes
- Table.RenameColumns
- Table.Sort
- Order.Ascending
- Web.Contents
Код
Код функции Кена Р для разархивирования zip файла:
(ZIPFile, Position, FileToExtract, DataSoFar) =>
let
MyBinaryFormat = try
BinaryFormat.Record(
[
DataToSkip = BinaryFormat.Binary(Position),
MiscHeader = BinaryFormat.Binary(18),
FileSize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
UnCompressedFileSize = BinaryFormat.Binary(4),
FileNameLen = BinaryFormat.ByteOrder(
BinaryFormat.UnsignedInteger16,
ByteOrder.LittleEndian
),
ExtrasLen = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
TheRest = BinaryFormat.Binary()
]
)
otherwise
null,
MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize] + 1 otherwise null,
MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null,
MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
MyBinaryFormat2 = try
BinaryFormat.Record(
[
DataToSkip = BinaryFormat.Binary(Position),
Header = BinaryFormat.Binary(30),
Filename = BinaryFormat.Text(MyFileNameLen),
Extras = BinaryFormat.Binary(MyExtrasLen),
Data = BinaryFormat.Binary(MyCompressedFileSize),
TheRest = BinaryFormat.Binary()
]
)
otherwise
null,
MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null,
DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null,
NewPosition = try
Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1
otherwise
null,
AsATable = Table.FromRecords({[Filename = MyFileName, Content = DecompressData]}),
#"Appended Query" =
if DecompressData = null then
DataSoFar
else if (MyFileName = FileToExtract) then
AsATable
else if (FileToExtract = "") and Position <> 0 then
Table.Combine({DataSoFar, AsATable})
else
AsATable
in
if (MyFileName = FileToExtract) or (#"Appended Query" = DataSoFar) then
#"Appended Query"
else
@fn_DecompressFiles(ZIPFile, NewPosition, FileToExtract, #"Appended Query")
Подключаемся к zip файлу, применяем функцию DecompressFiles и выполняем оставшиеся преобразования:
let
path = Excel.CurrentWorkbook(){[Name = "path_zip"]}[Content]{0}[Column1],
source = File.Contents(path & "\fut_fin_txt_2020.zip"),
decompress = fn_DecompressFiles(source, 0, "", null),
content = decompress{0}[Content],
import_data = Csv.Document(
content,
[Delimiter = ",", Columns = 191, Encoding = 1251, QuoteStyle = QuoteStyle.None]
),
headers = Table.PromoteHeaders(import_data, [PromoteAllScalars = true]),
rows_select = Table.SelectRows(
headers,
each ([Market_and_Exchange_Names] = "RUSSIAN RUBLE - CHICAGO MERCANTILE EXCHANGE")
),
cols_select = Table.SelectColumns(
rows_select,
{
"Market_and_Exchange_Names",
"Report_Date_as_YYYY-MM-DD",
"Dealer_Positions_Long_All",
"Dealer_Positions_Short_All",
"Asset_Mgr_Positions_Long_All",
"Asset_Mgr_Positions_Short_All"
}
),
col_types = Table.TransformColumnTypes(
cols_select,
{
{"Report_Date_as_YYYY-MM-DD", type date},
{"Dealer_Positions_Long_All", Int64.Type},
{"Dealer_Positions_Short_All", Int64.Type},
{"Asset_Mgr_Positions_Long_All", Int64.Type},
{"Asset_Mgr_Positions_Short_All", Int64.Type}
}
),
cols_rename = Table.RenameColumns(
col_types,
{{"Market_and_Exchange_Names", "Product"}, {"Report_Date_as_YYYY-MM-DD", "Date"}}
),
rows_sort = Table.Sort(cols_rename, {{"Date", Order.Ascending}})
in
rows_sort
Как подключиться к zip архиву, который находится в интернете:
let
path = Excel.CurrentWorkbook(){[Name = "path_url"]}[Content]{0}[Column1],
source = Web.Contents(path),
decompress = fn_DecompressFiles(source, 0, "", null),
content = decompress{0}[Content],
import_data = Csv.Document(
content,
[Delimiter = ",", Columns = 191, Encoding = 1251, QuoteStyle = QuoteStyle.None]
),
headers = Table.PromoteHeaders(import_data, [PromoteAllScalars = true]),
rows_select = Table.SelectRows(
headers,
each ([Market_and_Exchange_Names] = "RUSSIAN RUBLE - CHICAGO MERCANTILE EXCHANGE")
),
cols_select = Table.SelectColumns(
rows_select,
{
"Market_and_Exchange_Names",
"Report_Date_as_YYYY-MM-DD",
"Dealer_Positions_Long_All",
"Dealer_Positions_Short_All",
"Asset_Mgr_Positions_Long_All",
"Asset_Mgr_Positions_Short_All"
}
),
col_types = Table.TransformColumnTypes(
cols_select,
{
{"Report_Date_as_YYYY-MM-DD", type date},
{"Dealer_Positions_Long_All", Int64.Type},
{"Dealer_Positions_Short_All", Int64.Type},
{"Asset_Mgr_Positions_Long_All", Int64.Type},
{"Asset_Mgr_Positions_Short_All", Int64.Type}
}
),
cols_rename = Table.RenameColumns(
col_types,
{{"Market_and_Exchange_Names", "Product"}, {"Report_Date_as_YYYY-MM-DD", "Date"}}
),
rows_sort = Table.Sort(cols_rename, {{"Date", Order.Ascending}})
in
rows_sort