Batch CSV bestanden - regels conditioneel verwijderen

[Apolopo]

Ik heb als hobby het maken van trading algoritmes voor aandelen. Ik gebruik hiervoor een specifiek simulatie programma dat automatisch historische koersen van Amerikaanse aandelen kan downloaden, maar niet van Europese aandelen. Ik kan wel via Yahoo koersen downloaden in een CSV bestand (1 bestand per ticker) en die importeren. Ik heb zo 100+ CSV bestanden. De CSV bestanden bevatten echter ook regels zonder koersdata (feestdagen,etc), en die moet ik verwijderen om ze te kunnen importeren in mijn simulatie software.

De regels die verwijderd moeten worden bevatten de text 'null", bijvoorbeeld:
1997-01-01,null,null,null,null,null,null

Ik kan ze stuk voor stuk in Excel laden, filteren op 'null', deze regels verwijderen en opnieuw opslaan als CSV bestand, maar voor 100+ bestanden is dat erg veel werk, zeker om dagelijks te doen.

Weten jullie een manier om deze regels automatisch te verwijderen voor een groep CSV bestanden? Bij voorkeur in Excel, maar als er andere programma's zijn die beter geschikt zijn is dat natuurlijk ook goed.

Ik heb Office versie 2016

[F_J_K]

Je kunt een eenvoudige VBA macro maken in excel. Alle gebruikte regels langslopen, regel verwijderen als wordt voldaan aan je vereisten. (En rekening houden met dat dan de regelnummers verandert).

Kan op een vergelijkbare manier ook met bijv powershell.

Het lijkt me niet de bedoeling dat iemand een af-script voor je maakt, dus: waar loop je vast bij maken van een script?

[tdn135]

Ik zou het via Powerquery in excel (gegevens tab, dan helemaal links) of PowerBi doen. Je kunt het beste een map/folder met daarin alle csv inlezen en dan eenmalig de bewerkingen voordoen. Vervolgens voegt ie alle csv samen. Op YouTube staan genoeg tutorials.

Je kunt op de kolom een filter zetten is niet leeg of null.

[ Voor 10% gewijzigd door tdn135 op 08-07-2019 21:25 ]

[DukeBox]

Als je het toch in batch wil doen, gebruik dan gewoon een reverse find of grep. Daarmee filter je die regels er makkelijk uit.

[The Eagle]

Heb je linux subsystem draaien op je windows 10? Zo ja, verdiep je even in grep. Dit kan letterlijk in 1 regel code per bestand

[LeVortex]

Gebruik PowerQuery in excel.
Dan laad je alle csv files in een folder en laad je de bewerking los op alle bestanden tegelijk (vanuitgaande dat de ticker ook in de CSV zit). Makkelijker dan een macro maken, tenzij je al VBA kan.

Eventueel kun je powerquery ook de data van Yahoo laten ophalen (met de from Web onder de data tab). Echter werkt dat niet op alle websites even goed.

[DukeBox]

The Eagle schreef op maandag 8 juli 2019 @ 21:29:
Heb je linux subsystem draaien op je windows 10? Zo ja, verdiep je even in grep. Dit kan letterlijk in 1 regel code per bestand

find /v doet dat ook maar grep is wel iets sneller.

[burne]

DukeBox schreef op maandag 8 juli 2019 @ 22:01:
[...]

find /v doet dat ook maar grep is wel iets sneller.

Find zoekt naar bestandsnamen, dat zal in het linux-subsystem van windows niet anders zijn.

FIND.EXE blijkt het zwakzinnige CMD.EXE-broertje van grep te zijn.

Wat @The Eagle zegt:

code:

grep -v null bestandsnaam-in.csv > bestandsnaam-uit.csv

'null' is wat je zoekt, -v zorgt ervoor dat je enkel de regels ziet die geen 'null' bevatten.

[Apolopo]

Bedankt voor Ik heb weinig ervaring met scripts en geen Linux, maar ben inmiddels al wat aan het spelen met PowerQuery en kan alle CSV files vanuit een folder combineren en zo makkelijk alle regels zonder data verwijderen. De CSV bestanden bevatten echter geen kolom met de ticker naam; zou het ook mogelijk zijn een kolom toe te voegen met de filename van de individuele CSV files?

[basdej]

Je kunt Pentaho gebruiken.

https://www.hitachivantara.com/go/pentaho.html

[DukeBox]

burne schreef op maandag 8 juli 2019 @ 22:08:
FIND.EXE blijkt het zwakzinnige CMD.EXE-broertje van grep te zijn.

Het is andersom... find bestaat al sinds de eerste gangbare besturingssystemen (os360, bsd, dos, unix) in de huidige hoedanigheid.

[LeVortex]

Apolopo schreef op maandag 8 juli 2019 @ 22:09:
Bedankt voor Ik heb weinig ervaring met scripts en geen Linux, maar ben inmiddels al wat aan het spelen met PowerQuery en kan alle CSV files vanuit een folder combineren en zo makkelijk alle regels zonder data verwijderen. De CSV bestanden bevatten echter geen kolom met de ticker naam; zou het ook mogelijk zijn een kolom toe te voegen met de filename van de individuele CSV files?

Ik dacht dat het vanzelf ging, maar hier staat wel een duidelijke uitleg die hopelijk werkt!

Mocht de .txt blijven staan kun je die er altijd weer dit gebruiken: Text.Split(filename,".")

https://community.powerbi...rce-Power/m-p/22865#M7248

[tdn135]

Apolopo schreef op maandag 8 juli 2019 @ 22:09:
Bedankt voor Ik heb weinig ervaring met scripts en geen Linux, maar ben inmiddels al wat aan het spelen met PowerQuery en kan alle CSV files vanuit een folder combineren en zo makkelijk alle regels zonder data verwijderen. De CSV bestanden bevatten echter geen kolom met de ticker naam; zou het ook mogelijk zijn een kolom toe te voegen met de filename van de individuele CSV files?

Standaard wordt in PowerBi en ik vermoed dus ook powerquery in de resultaatquery de kolom source.name verwijderd. Verwijder daarom de werkstap die betrekking heeft op het verwijderen van de kolom (source.)name.

[burne]

DukeBox schreef op maandag 8 juli 2019 @ 22:18:
[...]

Het is andersom... find bestaat al sinds de eerste gangbare besturingssystemen (os360, bsd, dos, unix) in de huidige hoedanigheid.

HISTORY
A find command appeared in Version 1 AT&T UNIX.

Maar: die find zoekt bestanden, en kan niet in bestanden zoeken. (En OS/360 is een batch operating system en heeft van zichzelf geen interactieve commandline utilities..)

Maar de FIND.EXE die met DOS en CMD.EXE meekomt is een zeer matig aftreksel van grep. Het kan een string zoeken in een bestand. Het ondersteunt geen reguliere expressies en het kan niet in meerdere bestanden zoeken. Totaal ander beestje, dus. Laten we @Apolopo's topic niet verder kapen.

[Killah_Priest]

Dit is iets wat erg simpel uit te voeren is met Powershell (dus zonder 3rd party software oid) met enkele regels aan code.
Helaas zit ik momenteel op mijn telefoon dus voorbeeld code uitschrijven is even lastig.
Het komt in het kort neer op CSV importeren met Import-CSV, vervolgens dmv Where-object de data te filteren en vervolgens met Export-CSV de boel weer exporteren.

[Apolopo]

bedankt voor de hulp allen. Met Powerquery in Excel is het me gelukt, maar ga zeker ook kijken naar Powershell. Ik ben nog niet zo thuis in dergelijke scripts, maar ga me er in verdiepen.

[GNID]

Aanname: je ticker files zien er als volgt uit en staan samen in 1 fodler:

TICKER1.csv
1997-01-01,null,null,null,null,null,null
1997-01-02,1,2,3,4,5,6
1997-01-03,2,3,4,5,6,7

TICKER2.csv
1997-01-01,null,null,null,null,null,null
1997-01-02,1,2,3,4,5,6
1997-01-03,2,3,4,5,6,7


Plaats het volgende CMD script in een folder op je systeem:

code:

@echo off

   2> nul del "%~f1\Totaal.csv"

   (for  %%x in ("%~f1\*.csv") DO for /f "usebackq delims=" %%y in ("%%x") DO echo "%%~nx",%%y) > "%~f1\totaal.deleteme"
   findstr /v ",null," "%~f1\totaal.deleteme" > "%~f1\Totaal.csv"

   del "%~f1\totaal.deleteme"

   echo.
   echo.    Resultaat in "%~f1\Totaal.csv"
pause

Sleep de folder met CSV bestanden naar dit script (drag 'n drop)


Resultaat: Totaal.csv

code:

"TICKER1",1997-01-02,1,2,3,4,5,6
"TICKER1",1997-01-01,2,3,4,5,6,7
"TICKER2",1997-01-02,1,2,3,4,5,6
"TICKER2",1997-01-01,2,3,4,5,6,7

Ik had dit net zo makkelijk / makkelijker in PowerShell kunnen schrijven, maar dan had ik een heel verhaal over exection policies moeten schrijven ... geen zin in

Maar de FIND.EXE die met DOS en CMD.EXE meekomt is een zeer matig aftreksel van grep. Het kan een string zoeken in een bestand. Het ondersteunt geen reguliere expressies en het kan niet in meerdere bestanden zoeken. Totaal ander beestje, dus.

Sinds jaar en dag wordt FINDSTR.exe meegeleverd met Windows. Die ondersteunt meerdere bestandsnamen en (basale) regex functies (verwacht geen lookaround, etc.)

Het komt in het kort neer op CSV importeren met Import-CSV, vervolgens dmv Where-object de data te filteren en vervolgens met Export-CSV de boel weer exporteren.

Dat is overkill.
(gc .\ticker.csv) -notmatch ',null' geeft hetzelfde resultaat (en sneller).

[Apolopo]

@gnid, dat werkt perfect, dank je! Dat het met zo weinig regels kon had ik niet verwacht.

Ik ben trouwens mijn data flow stappen aan het evalueren, aangezien de Excel template/macro die ik gebruik om de data van Yahoo te downloaden en weg te schrijven naar CSV bestanden vaak vastloopt met een 'subscript out of range' error, maar dat is een ander verhaal en denk ik niet geschikt voor dit topic.