< návrat zpět

MS Excel


Téma: VBA - průvodce importem textu rss

Zaslal/a 23.10.2021 21:22

ahojte, dovolím se na vás obrátit, protože už si nevím rady a vždy jste mi ochotně a suprově pomohli. Mám asi 3 tisíce textových souborů, ze kterých potřebuju vytáhnout nějaký data. Mám k tomu vytvořenej excel, kde první list je sumarizace dat z těch TXT souborů, do druhé listu vždycky přes import textu nakopíruju textovej soubor. Nevím ale jak zautomatizovat import textu (ze schránky), tak aby oddělovač byla mezera, a oddělovač destinných míst je ".". Mám představu, že po spuštění makra se vymažou sloupce A až P, pak se zaktivní buňka A1 a provede se import textu. Když se pak přepnu do listu Summary, tak tam vidím data. Problém je v tom, že když to zkouším záznamem makra, tak oddělovač není mezera, ale vše je v jedné buňce. Příklady viz. příloha.

Budu vděčný za jakékoliv nápady.
Díky moc, Dalibor

Příloha: zip51376_test.zip (73kB, staženo 16x)
Zaslat odpověď >

Strana:  « předchozí  1 2 3 4   další »
#051387
avatar
tak, trochu jsem si s tabulkou pohrál, trochu tam neseděli data, podívej do přílohy. Teď se musím jen naučit, jak přidat další sloupece, první by byl Rh at the start of conditioning (Rh je vlhkost), druhý sloupec by byl Delta pressure, což je rozdíl tlaku na konci Conditioning a na konci EO inject (rozdíl fází 4,9 - 3,9). Co se týče té min, max a průměrné teploty, tak by to byly další tři sloupce, kde pro každý soubor by byla vytažena ta min, max a průměrná teplota z fáze 6,9 (sloupec PT02).

Tak sloupec Delta pressure se mi už podařilo vložit. Teď zkusím Rh at the start of conditioning.
Příloha: xlsx51387_51380_data-pq.xlsx (30kB, staženo 11x)
citovat
#051388
elninoslov
Kontrolovať 2 kódy, či je v jednom nejaká zmena??? To je na mašľu.
Ten pridaný stĺpec vo Vašom súbore nikde nevidím. Našiel som opravu D5 a D6.
Vašou úpravou D7 nie som si istý. Pretože ste zmenil kontrolu indexov 3.9 a 2.9 na indexy 3.9 a 4.9, no výpočet ste ponechal tak ako bol 3.9-2.9.
Prečo sa musí počet 10.9 deliť /2?

Pridal som tam spomenutých 5 stĺpcov, aj keď neviem či rátajú to čo chcete, lebo hovoríte mne neznámou rečou 1 . Nerozumiem, čo máte na mysli. Riadok 6.9 je tam vždy 1, preto Average tak ako som ho tam zatiaľ načrtol nemá zmysel.

Pridávať výsledné stĺpce sa dá, ale musí sa v kóde funkcie fncGetDataFromFile pridať riadok do záznamu
CalculatedData = Table.FromRecords(...)
Ďalej treba na to myslieť v poslednom riadku #"Zmenený typ"
a potom v kóde GetDataFromFiles na 2 miestach (pracovný aj reálny názov stĺpca)
#"Rozbalené fncGetDataFromFile" = Table.ExpandTableColumn({...}, {...})
a rovnako ako aj vo funkcii na konci tiež na riadku
#"Zmenený typ"

Snáď som na niečo nezabudol. Samozrejme, ak chcete používať na ladenie aj ten cvičný dotaz, tak aj v ňom.
Příloha: xlsx51388_data-pq.xlsx (33kB, staženo 10x)
citovat
#051389
avatar
na ty výpočty se ještě podívám, díky, včera večer jsem to už moc nedával. Včera se mi i podařilo přidat nějaké sloupce a aby to počítalo. Co se týká té hodnoty min, max a průměr, tak to je jenom bonus, ale je to počítáno z celé fáze 6.1, viz. příloha (je tam samozřejmě víc řádků v té fázi 6.1, jen jsem to pro přehlednost trochu ořezal), samozřejmě z jednoho bodu by to byl nesmysl (předtím jsem to špatně formuloval. Ale toto už myslím nepůjde.

Ale i tak neuvěřitelná práce,
Díky moc, D.

P.S. nedalo mi to a trochu jsem se narychlo tím kódem prohrabal, změnil fázi 6.9 na 6.1 a ono to opravdu počítá min, max a průměr z celé té fáze. Jak budu mít chvíli, tak si ještě zkusím nějaký příklady, abych to pochopil 1

Co jsem ještě neobjevil, kde případně změním cestu k těm souborům. Dá se použít i síťová adresa typu \\192.168.xxx.xxx ??
Příloha: png51389_min-max-a-prumerna-teplota.png (41kB, staženo 16x)
51389_min-max-a-prumerna-teplota.png
citovat
#051390
elninoslov
Áno, je možné mu zadať aj LAN cestu. Tu som si všimol letmým testom s druhým PC, že ak zadám meno PC nepýta si to prihlasovacie údaje (???), a ak zadám IP, pýta to nastavenie bezpečnosti (verejný, súkromný, ...) a meno a heslo do konta na danom PC.citovat
#051391
avatar

elninoslov napsal/a:

Áno, je možné mu zadať aj LAN cestu. Tu som si všimol letmým testom s druhým PC, že ak zadám meno PC nepýta si to prihlasovacie údaje (???), a ak zadám IP, pýta to nastavenie bezpečnosti (verejný, súkromný, ...) a meno a heslo do konta na danom PC.


a ta cesta se zadává kam? pořádně jsem to nenašel 1citovat
#051392
elninoslov
1. Do tej modrej Tabuľky na liste Settings. Teraz je tam vzorec, ktorý určuje aktuálne umiestnenie súboru XLSX + podadresár "Data". Predpokladal som distribúciu aj s dátami. V mojom prípade konkrétne:
\\DRUHE-PC\Zdieľané\Data
2. Ak je potrebné robiť ešte pokusy na tom cvičnom dotaze pre jeden súbor "Iba na ladenie", tak potom v ňom treba nastaviť presnú cestu na súbor. V prvom riadku M-code je premenná FileName.
V mojom prípade konkrétne:
FileName = "\\DRUHE-PC\Zdieľané\Data\AUT_0032_32211010.TXT",

Ak bude pýtať poverenie, zaškrtnite ignorovať. Záleží aj na tom ako máte zdieľanie nastavené. Či treba globálne poverenia nastaviť na Verejné, alebo vypýta meno+heslo,...citovat
#051393
avatar
zdravím pánové,

dovolil jsem si navrhnout některé úpravy, které mohou zjednodušit případné úpravy kódu.

Poznámky:
a{x}? pokud prvek seznamu neexistuje, zamezí chybě a vrátí null

a[x]? obdoba pro record

Otazníky jsou tedy důležité.

Některé dlouhé seznamy je vhodné udržovat v tabulce excelu a v kóde se na ně odvolávat. Je snažší pro údržbu.

Ze stejného důvodu jsem pro počítané proměnné deklaroval funkce.

V Table.Group jsou ukázky jak počítat průměry, minima,
ten první řádek je už pro pokročilejší. Je nutné vědět, že Table.Group do vnořených výpočtů posílá tabulku se všemi řádky skupiny.

Způsob načtení souborů je podle mých zkušeností o něco rychlejší, než ten automaticky generovaný (a i kód je kratší)

Načtení souborů může tedy vypadat
// GetDataFromFiles
let
Zdroj = Folder.Files(Excel.CurrentWorkbook(){[Name="tblDirPath"]}[Content]{0}[Directory path]),
#"Filtrované riadky" = Table.SelectRows(Zdroj, each [Extension]=".TXT"),
#"Odebrané ostatní sloupce" = Table.SelectColumns(#"Filtrované riadky",{"Content"}),
#"Transformovaný sloupec" = Table.TransformColumns(#"Odebrané ostatní sloupce",{},fncGetDataFromFile),
#"Rozbalené Content" = Table.ExpandTableColumn(#"Transformovaný sloupec", "Content", {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"}, {"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"}),
#"Přejmenované sloupce" = Table.RenameColumns(#"Rozbalené Content",List.Zip({
{"Batch", "P4021", "P4001", "P4004", "P4005", "P4002", "P4006", "P4007", "P4008", "P4009", "P4015", "P4011", "P4012", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "D10", "D11", "D12", "D13", "D14"},
{"Batch", "Recipe", "Temperature", "Initial vacuum", "Conditioning duration", "Humidity", "Gas inlet duration", "EO weight", "Sterilization pressure", "Dwell", "Final vacuum", "Number of washings", "Washing vacuum", "Initial vacuum 2", "Initial vacuum duration", "Rh at the end of conditioning", "Conditioning duration 2", "Pressure at the end of conditioning", "Temperature at the end of conditioning", "Pressure increment", "Pressure after EO inject", "EO inject duration", "Dwell duration", "Pressure at the end of DWELL", "Pressure leak", "Final vacuum 2", "Number of washes"}
})),
#"Zmenený typ" = Table.TransformColumnTypes(#"Přejmenované sloupce",{{"Batch", type text}, {"Recipe", type number}, {"Temperature", type number}, {"Initial vacuum", type number}, {"Conditioning duration", type number}, {"Humidity", type number}, {"Gas inlet duration", type number}, {"EO weight", type number}, {"Sterilization pressure", type number}, {"Dwell", type number}, {"Final vacuum", type number}, {"Number of washings", type number}, {"Washing vacuum", type number}, {"Initial vacuum 2", type number}, {"Rh at the end of conditioning", type number}, {"Pressure at the end of conditioning", type number}, {"Temperature at the end of conditioning", type number}, {"Pressure increment", type number}, {"Pressure after EO inject", type number}, {"Pressure at the end of DWELL", type number}, {"Pressure leak", type number}, {"Final vacuum 2", type number}, {"Number of washes", Int64.Type}, {"Initial vacuum duration", type time}, {"Conditioning duration 2", type time}, {"EO inject duration", type time}, {"Dwell duration", type time}})
in
#"Zmenený typ"


a procedura


// fncGetDataFromFile
(FileContent as any) =>
let
// Načteme po řádcich a rozdělíme na části
File = Lines.FromBinary(FileContent, QuoteStyle.None, false, 65001 ),
// Začátky a konce jednotlivých částí
Oddíl = List.Generate(
() => [L={"=", "Sterilisation", "Date", "End of cycle"}, i=0, r = -1],
each <= List.Count([L]),
each [
L = [L],
r = List.PositionOf(File, List.FindText(File,[L]{}){0}?),
i = + 1
],
each [r]
),
//
// první část - batch
Batch = [Batch = Text.AfterDelimiter( File{Oddíl{1}}, "_", {0, RelativePosition.FromEnd})],

//
// druhá část - parametry
ParamList = {"P4021","P4001","P4004","P4005","P4002","P4006","P4007","P4008","P4009","P4015","P4011","P4012"},
ParametryData = Table.FromList(List.FirstN(List.Skip(File, #"Oddíl"{2}+1), #"Oddíl"{3} - #"Oddíl"{2}-1), each {_}, {"Param"}),
#"Rozdělit parametry" = Table.SplitColumn(ParametryData, "Param", Splitter.SplitTextByWhitespace(QuoteStyle.Csv), {"Param.1", "Param", "Value"}),
#"Odebrané sloupce" = Table.RemoveColumns(#"Rozdělit parametry",{"Param.1"}),
#"Změněný typ s národním prostředím" = Table.TransformColumnTypes(#"Odebrané sloupce", {{"Value", type number}}, "en-150"),
#"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(
// tabulku měníme na záznam
Table.Transpose(#"Změněný typ s národním prostředím"), [PromoteAllScalars=true]),
Parametry = Table.First(Table.SelectColumns(#"Záhlaví se zvýšenou úrovní",ParamList)),
//
// třetí část
// data mají pevnou pozici
FileData = Csv.Document(Lines.ToText(List.FirstN(List.Skip(File, #"Oddíl"{3}+1), #"Oddíl"{4} - #"Oddíl"{3}-1)),13,{2,12,21,26,33,39,45,51,57,62,69,75,81},ExtraValues.Ignore,65001),
#"Přejmenované sloupce" = Table.RenameColumns(FileData,
List.Zip(
{
Table.ColumnNames(FileData),
List.RemoveNulls(Text.Split(File{#"Oddíl"{3}}, " "))
})),
#"Odebrané sloupce1" = Table.RemoveColumns(#"Přejmenované sloupce",{"PT04(C)", "PT05(C)", "Operator", "PR01(kPa)", "PT01(C)", "PT03(C)", "Circulator(ON/OFF)"}),
OdebranéPrázdnéŘádky = Table.SelectRows(#"Odebrané sloupce1", each ([Date] <> "")),
//
// Některé příkazy mění pořadí a z dat původní pořadí nejde určit
#"Přidané: Index" = Table.AddIndexColumn(OdebranéPrázdnéŘádky, "Index", 0, 1, Int64.Type),
DateHour = Table.CombineColumns(#"Přidané: Index",{"Date", "Hour"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateHour"),
#"Vyčištěný text" = Table.TransformColumns(DateHour,{
{"Phase", Text.Trim, type text},
{"PR03(kPa)", each Text.Combine(List.RemoveNulls(Text.Split(_, " ")),"")}}),
#"Změna typu - DateHour" = Table.TransformColumnTypes(#"Vyčištěný text",{{"DateHour", type datetime}}),
Data = Table.TransformColumnTypes(#"Změna typu - DateHour", {{"Phase", type text}, {"PR03(kPa)", type number}, {"PT02(C)", type number}, {"Humidity(%)", type number}}, "en-US"),
//
// Data seskupíme a vyberemo potřebná
//
#"Seskupené řádky" = Table.Group(Data, {"Phase"}, {
{"Tab",
each Table.SelectRows(_, (r) => r[Index] = List.Min([Index])),
type table [DateHour=nullable datetime, Phase=nullable text, #"PR03(kPa)"=nullable number, #"PT02(C)"=nullable number, #"Humidity(%)"=nullable number, Index=number]
},
{"Počet řádků",
each Table.RowCount(_),
Int64.Type},
{"PT02 (Max)",
each List.Max([#"PT02(C)"]),
type number},
{"PT02 (Min)",
each List.Min([#"PT02(C)"]),
type number},
{"PT02 (Prům)",
each List.Average([#"PT02(C)"]),
type number}
}),
#"Rozbalené Tab" = Table.ExpandTableColumn(#"Seskupené řádky", "Tab", {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}, {"DateHour", "PR03(kPa)", "PT02(C)", "Humidity(%)"}),
GroupData = Table.Buffer(Table.AddKey(#"Rozbalené Tab", {"Phase"}, true)),
//
// Máme připravené souhrny
// teď výpočty - připravíme funkce
D1 = (_) => _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D5 = (_) => _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D8 = (_) => _{[Phase="2.1"]}?[#"PR03(kPa)"]?,
D11 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]?,
D13 = (_) => _{[Phase="8.9"]}?[#"PR03(kPa)"]?,

D3 = (_) => _{[Phase="3.9"]}?[#"Humidity(%)"]?,
D6 = (_) => _{[Phase="2.9"]}?[#"PT02(C)"]?,

D7 = (_) => _{[Phase="3.9"]}?[#"PR03(kPa)"]? - _{[Phase="2.9"]}?[#"PR03(kPa)"]?,
D12 = (_) => _{[Phase="6.9"]}?[#"PR03(kPa)"]? - _{[Phase="4.9"]}?[#"PR03(kPa)"]?,

D2 = (_) => _{[Phase="2.9"]}?[#"DateHour"]? - _{[Phase="2.1"]}?[#"DateHour"]?,
D4 = (_) => _{[Phase="3.9"]}?[#"DateHour"]? - _{[Phase="2.9"]}?[#"DateHour"]?,
D9 = (_) => _{[Phase="4.9"]}?[#"DateHour"]? - _{[Phase="3.9"]}?[#"DateHour"]?,
D10 = (_) => _{[Phase="6.9"]}?[#"DateHour"]? - _{[Phase="4.9"]}?[#"DateHour"]?,

D14 = (_) => _{[Phase="10.9"]}?[#"Počet řádků"]?,

CalculatedData =
[D1 = D1(GroupData)] &
[D2 = D2(GroupData)] &
[D3 = D3(GroupData)] &
[D4 = D4(GroupData)] &
[D5 = D5(GroupData)] &
[D6 = D6(GroupData)] &
[D7 = D7(GroupData)] &
[D8 = D8(GroupData)] &
[D9 = D9(GroupData)] &
[D10 = D10(GroupData)] &
[D11 = D11(GroupData)] &
[D12 = D12(GroupData)] &
[D13 = D13(GroupData)] &
[D14 = D14(GroupData)]
,

// spojíme záznamy
CombineTables = Table.FromRecords({Batch & Parametry & CalculatedData}),
#"Duration na time" = Table.TransformColumns(CombineTables,{{"D2", each #time(0,0,0) + _, type time}, {"D4", each #time(0,0,0) + _, type time}, {"D9", each #time(0,0,0) + _, type time}, {"D10", each #time(0,0,0) + _, type time}}),

#"Zmenený typ" = Table.TransformColumnTypes(#"Duration na time",{{"Batch", type text}, {"P4021", type number}, {"P4001", type number}, {"P4004", type number}, {"P4005", type number}, {"P4002", type number}, {"P4006", type number}, {"P4007", type number}, {"P4008", type number}, {"P4009", type number}, {"P4015", type number}, {"P4011", type number}, {"P4012", type number}, {"D1", type number}, {"D2", type time}, {"D3", type number}, {"D4", type time}, {"D5", type number}, {"D6", type number}, {"D7", type number}, {"D8", type number}, {"D9", type time}, {"D10", type time}, {"D11", type number}, {"D12", type number}, {"D13", type number}, {"D14", type number}})
in
#"Zmenený typ"[/code]
#051394
avatar
ahoj Lubo,

můžeš to prosím zkusit hodit do přiložených souborů, abychom viděli, jak to funguje? Já v těchto kódech nejsem moc kovanej, moje maximum bylo psaní něco málo ve VBA, ale toto je už na mě moc, vůbec nechápu, jak se v tom můžete vyznat 1citovat
#051399
avatar
Přidal jsem to do souboru 51387_51380_data-pq.xlsx.

Doplnil jsem pár drobností, vč. přidání průměru, maxima, ...
Snad to je pochopitelné.

Jinak, jak zmínil elninoslov, máš v definici počítaných hodnot docela zmatek.

Původní dotazy jsem nechal s verzí (2).

Asi všechno hned nepochopíš, ale podle vzoru by snad úpravy počítaných hodnot nemusely být problém.
Příloha: xlsx51399_51387_51380_data-pq-2.xlsx (41kB, staženo 16x)
citovat
#051406
avatar
chlapi, to mi řekněte, to ty kódy píšete ručně? to fakt takto nemůžu dát, vůbec nevím, jak jste to vytvořili. Nebo si to prvně přes nějakýho průvodce rozdělíte na ty oddíly a sloupce, a pak to jen v kódu doladíte?citovat

Strana:  « předchozí  1 2 3 4   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

odpocet a storno tl.

PavDD • 28.3. 8:53

odpocet a storno tl.

Začátečník • 26.3. 14:39

odpocet a storno tl.

PavDD • 26.3. 10:22

odpocet a storno tl.

elninoslov • 26.3. 7:50

odpocet a storno tl.

PavDD • 26.3. 7:26

odpocet a storno tl.

elninoslov • 25.3. 22:34

odpocet a storno tl.

Začátečník • 25.3. 15:09