< 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:  1 2 3 4   další »
#051377
elninoslov
Takže naraz zo všetkých súborov v nejakom adresári? V tom prípade PowerQuery (súčasť Office 2016/209/2021/365, do 2010/2013 je ofiko doplnok), kde sa dajú ošetriť prebytočné medzery, filtre iba na požadované riadky textu, rozdelenie na stĺpce, aj správny prevod EN/US hodnôt. Priložte ešte nejaké 2-3 súbory, nech si to nemusíme simulovať my.

EIDT: NO a keď si to tak pozerám, nachádzam ďalšie otázky:
-To, že dohľadávate parametre Pxxx znamená, že v tieto parametre nemusia byť v každom súbore obsiahnuté, alebo sú na inom riadku?
-Ten názov "Batch" nemá byť náhodou iba to za posledným podtržítkom? Lebo 9 znakov majú v summary všetky (pozerám na podmienku v data!S1).
-"Initial vacuum duration" rátate iba hodiny {MOD(x;1)}? Nemôže nastať, že bude niečo trvať dlhšie ako 24 hod?
-Tie značky 2,1 / 2,9 / 3,9 ... atď, ktoré hľadáte na získanie hodnôt, tam nemusia existovať?
-Ako ste prišli na to, že stĺpec "Pressure at the end of conditioning" inak "PR03(kPa)" musí byť vždy záporné číslo?

Ďalšie otázky ešte dopíšem ako ma budú postupne napádať...citovat
#051378
avatar
díky za tvůj čas, každý nápad mi ušetří hodiny až dny práce, protož ve finále takových souborů je téměř 10tis. V příloze posílám seznam dalších souborů jako příklad.

a teď k otázkám:
- Parametry Pxxxx budou v každém souboru, pravděpodobně i na stejném řádku
- název batch je to za tím podržítkem, jednou ten batch má 9 znaků a jednou 8, rozlišuje se pouze písmenem A obsazeném/neobsazeném v batchi
-jakýkoliv čas by neměl být delší jak 24hodin
- Značky 2,1 / 2,9 atd. jsou konce jednotlivých fází procesu, ze kterých se odečítají data, jako třeba vlhkosti na konci fáze bla bla bla, tlak na konci fáze atd. atd.
- Pressusre at the end of conditioning bude vždy záporný, jen jak mi to konvertuje "import textu, tak to vypadá jako kladné číslo, proto jsem před to hodil záporné znaménko.

Díky za tvůj čas
Dalibor
Příloha: zip51378_aut32.zip (76kB, staženo 14x)
citovat
#051379
elninoslov
Už to nestíham dokončiť. Navyše sa mi nepozdávajú vždy tie Vaše odkazy na stĺpce vo vzorcoch. Ako bonus opäť podotýkam, že v "PR03(kPa)" ani omylom nie sú vždy záporné hodnoty. A časy idú cez polnoc, tak bude potrebné to prekopať.

Zatiaľ iba pracovná polyfunkčná verzia.

Zajtra mám na pláne veľa vecí, tak neviem ako dopadne toto...
Příloha: zip51379_data.zip (88kB, staženo 9x)
citovat
#051380
elninoslov
Úprava. Ale dnes už asi viac času mať nebudem...
Skontrolujte si to.
Příloha: xlsx51380_data-pq.xlsx (30kB, staženo 11x)
citovat
#051381
avatar
nevypadá to vůbec špatně, jestli dobře chápu, tak jenom nastavím cestu a on by si měl natáhnout všechna požadovaná data. Jak se to pak spouští? tomu zatím nerozumím. Já budu doma až večír, v práci na to taky nemám moc času. S dovolením se když tak ozvu, když budu mít nějaké dotazy.

Zatím moc děkuji,
D.citovat
#051382
avatar
fííííííha, ono to vypadá, že to funguje, zatím neznám pozadí jak jsi to vytvořil, ale klobouk dolů. Teď zkouším naimportovat všech 2500 souborů, tak uvidíme výsledek, dám vědět. 1

díky,
D.citovat
#051383
elninoslov
Dotazy PowerQuery (PQ) sa spúšťajú buď na karte Údaje - Obnoviť všetko, alebo pravý klik na Tabuľku dotazu - Obnoviť, alebo skratka Ctrl+Alt+F5.
Kód samotného dotazu (M-code) sa dá editovať. Karta Údaje - Dotazy a pripojenia - vpravo klik na hociktorý dotaz pravým a výber Upraviť.
Dotaz "Iba na ladenie" nie je potrebný, jeho účel je jasný z názvu. Samotnú Tabuľku vytvára dotaz "GetDataFromFiles" a použité kroky je vidno vpravo "PUŽITÉ KROKY". No to najdôležitejšie obstaráva vytvorená funkcia "fncGetDataFromFile". Funkcie nezobrazujú použité kroky vpravo, ale treba pozrieť M-code. Karta Domov - Rozšírený editor.
M-code funkcie fncGetDataFromFile:
(FileName as text) =>
let
//načítanie dát zo súboru ako text, kvôli ošetrovaniu medzier, oddeľovačov a mínusov
File = Csv.Document(File.Contents(FileName),[Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Nahradená hodnota" = Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(File," "," ",Replacer.ReplaceText,{"Column1"})," "," ",Replacer.ReplaceText,{"Column1"})," "," ",Replacer.ReplaceText,{"Column1"})," "," ",Replacer.ReplaceText,{"Column1"}),
TextFromFile = Table.ReplaceValue(#"Nahradená hodnota","- ","-",Replacer.ReplaceText,{"Column1"}),

//vytvorenie tabuľky dát
#"Filtrované riadky" = Table.SelectRows(TextFromFile, each Text.StartsWith([Column1], "Date") or Text.StartsWith([Column1], "> ") or Text.StartsWith([Column1], ". ")),
#"Nahradená hodnota4" = Table.ReplaceValue(Table.ReplaceValue(#"Filtrované riadky","> ","",Replacer.ReplaceText,{"Column1"}),". ","",Replacer.ReplaceText,{"Column1"}),
#"Rozdeliť stĺpec podľa oddeľovača" = Table.SplitColumn(#"Nahradená hodnota4", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13"}),
#"Odstránené ostatné stĺpce" = Table.SelectColumns(#"Rozdeliť stĺpec podľa oddeľovača",{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.7"}),
#"Hlavičky so zvýšenou úrovňou" = Table.PromoteHeaders(#"Odstránené ostatné stĺpce", [PromoteAllScalars=true]),
#"Zlúčené stĺpce" = Table.CombineColumns(#"Hlavičky so zvýšenou úrovňou",{"Date", "Hour"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DateHour"),
#"Zmenený typ4" = Table.TransformColumnTypes(#"Zlúčené stĺpce",{{"DateHour", type datetime}}),
#"Zmenený typ pomocou miestneho nastavenia" = Table.TransformColumnTypes(#"Zmenený typ4", {{"Phase", type text}, {"PR03(kPa)", type number}, {"PT02(C)", type number}, {"Humidity(%)", type number}}, "en-US"),
DataFromFile = Table.TransformColumnTypes(#"Zmenený typ pomocou miestneho nastavenia",{{"DateHour", type number}}),

//vytvorenie tabuľky parametrov
#"Filtrované riadky1" = Table.SelectRows(TextFromFile, each Text.StartsWith([Column1], "+ ")),
#"Nahradená hodnota5" = Table.ReplaceValue(#"Filtrované riadky1","+ ","",Replacer.ReplaceText,{"Column1"}),
#"Rozdeliť stĺpec podľa oddeľovača2" = Table.SplitColumn(#"Nahradená hodnota5", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Parameter", "Hodnota"}),
ParamList = Table.FromList({"P4021","P4001","P4004","P4005","P4002","P4006","P4007","P4008","P4009","P4015","P4011","P4012"}, null, {"Parameter"}),
ParamVal = Table.ReplaceErrorValues(Table.AddColumn(ParamList, "Value", each #"Rozdeliť stĺpec podľa oddeľovača2"[Hodnota]{List.PositionOf(#"Rozdeliť stĺpec podľa oddeľovača2"[Parameter], [Parameter])}), {{"Value", null}}),
ParamFromFile = Table.TransformColumnTypes(ParamVal, {{"Value", type number}}, "en-US"),

//extrakcia názvu batch
BatchVal = Table.FromRecords({[Parameter = "Batch", Value = Table.SelectRows(TextFromFile, each Text.StartsWith([Column1], "= "))[Column1]{0}]}),
Batch = Table.TransformColumns(BatchVal, {{"Value", each Text.AfterDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),

//indexácia hľadaných značiek v tabuľke dát
IndexPhaseList = Table.FromList({"2.1","2.9","3.9","4.9","6.9","8.9"}, null, {"Name"}),
IndexPhase = Record.FromTable(Table.ReplaceErrorValues(Table.AddColumn(IndexPhaseList, "Value", each List.PositionOf(DataFromFile[Phase], [Name]), Int64.Type), {{"Value", null}})),

//vypočítavané stĺpce
CalculatedData = Table.FromRecords({[Parameter = "D1", Value = if IndexPhase[2.9] = -1 then null else DataFromFile{IndexPhase[2.9]}[#"PR03(kPa)"]],
[Parameter = "D2", Value = if IndexPhase[2.1] = -1 or IndexPhase[2.9] = -1 then null else DataFromFile{IndexPhase[2.9]}[#"DateHour"]-DataFromFile{IndexPhase[2.1]}[#"DateHour"]],
[Parameter = "D3", Value = if IndexPhase[3.9] = -1 then null else DataFromFile{IndexPhase[3.9]}[#"Humidity(%)"]],
[Parameter = "D4", Value = if IndexPhase[3.9] = -1 or IndexPhase[2.9] = -1 then null else DataFromFile{IndexPhase[3.9]}[#"DateHour"]-DataFromFile{IndexPhase[2.9]}[#"DateHour"]],
[Parameter = "D5", Value = if IndexPhase[3.9] = -1 then null else DataFromFile{IndexPhase[2.9]}[#"PR03(kPa)"]],
[Parameter = "D6", Value = if IndexPhase[3.9] = -1 then null else DataFromFile{IndexPhase[2.9]}[#"PT02(C)"]],
[Parameter = "D7", Value = if IndexPhase[3.9] = -1 or IndexPhase[2.9] = -1 then null else DataFromFile{IndexPhase[3.9]}[#"PR03(kPa)"]-DataFromFile{IndexPhase[2.9]}[#"PR03(kPa)"]],
[Parameter = "D8", Value = if IndexPhase[4.9] = -1 then null else DataFromFile{IndexPhase[2.1]}[#"PR03(kPa)"]],
[Parameter = "D9", Value = if IndexPhase[4.9] = -1 or IndexPhase[3.9] = -1 then null else DataFromFile{IndexPhase[4.9]}[#"DateHour"]-DataFromFile{IndexPhase[3.9]}[#"DateHour"]],
[Parameter = "D10", Value = if IndexPhase[6.9] = -1 or IndexPhase[4.9] = -1 then null else DataFromFile{IndexPhase[6.9]}[#"DateHour"]-DataFromFile{IndexPhase[4.9]}[#"DateHour"]],
[Parameter = "D11", Value = if IndexPhase[6.9] = -1 then null else DataFromFile{IndexPhase[6.9]}[#"PR03(kPa)"]],
[Parameter = "D12", Value = if IndexPhase[6.9] = -1 or IndexPhase[4.9] = -1 then null else DataFromFile{IndexPhase[6.9]}[#"PR03(kPa)"]-DataFromFile{IndexPhase[4.9]}[#"PR03(kPa)"]],
[Parameter = "D13", Value = if IndexPhase[8.9] = -1 then null else DataFromFile{IndexPhase[8.9]}[#"PR03(kPa)"]],
[Parameter = "D14", Value = Table.RowCount(Table.SelectRows(DataFromFile, each ([Phase] = "10.9")))]}),

//predfinálne spojenie tabuliek
CombineTables = Table.Combine({Batch, ParamFromFile, CalculatedData}),

//Finalizácia výstupu pre daný súbor
#"Transponovaná tabuľka" = Table.Transpose(CombineTables),
#"Hlavičky so zvýšenou úrovňou1" = Table.PromoteHeaders(#"Transponovaná tabuľka", [PromoteAllScalars=true]),
#"Zmenený typ" = Table.TransformColumnTypes(#"Hlavičky so zvýšenou úrovňou1",{{"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"

Ide ešte o to, či som to urobil tak, aby to vracalo správne výsledky. Najmä tie časy som riešil, ale hlavne či treba tie záporné čísla riešiť...

PS: A čo robí s dátami ten M-code funkcie, sa dá odkrokovať práve v tom dotaze "Iba na ladenie", kde si iba treba na začiatku kódu zmeniť cestu k skúšobnému súboru.

Ešte som zabudol, postaviť sa na Tabuľku dotazu a na karte Údaje - Dotazy a pripojenia - Vlastnosti, zrušiť zaškrtnutie "Prispôsobiť šírku stĺpca".citovat
#051384
avatar
Kdysi jsem něco psal i ve VBA kódu, ale todlencto vidím prvně. Zatím to vypadá, že to funguje dobře. Chtěl bych tam přidat jeden sloupec, a to je RH na konci initial vacuum, snad se mi to nějak podaří. Jestli ještě můžu mít dotaz, myslíš, že by šlo z několika řádků (je to mezi fázemi 4.9 a 6.9 vytáhnout minimální, maximální a průměrnou teplotu? nebo je to už moc složité?

Jinak díky moc, opravdu klobouk dolů, jsi frajer 10citovat
#051385
elninoslov
Čo je to RH?
Čo znamená na konci "initial vacuum"? Navyše "initial vacuum" sú dve, stĺpec 4 a stĺpec 14.
"minimální, maximální a průměrnou teplotu" počítanú zo všetkých súborov? Lebo každý má predsa iba jednu teplotu (P4001).citovat
#051386
avatar
tak zatím se s tím trápím, vůbec nemůžu najít a otevřít ten zdrojový kód, abych něco upravil. Sloupec Pressure after EO inject je špatně, má tam být hodnota z řádku, kde je fáze 4,9, sloupec hned za číslem té fáze (4,9). Potřebuju ještě přidat sloupec RH at the start of conditioning (což je na konci fáze 2,9. Jinak je to úžasný nástroj :)citovat

Strana:  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