Meerdere txt bestanden met datumnaam importeren in excel

[Fheitkonig]

Hallo, ik heb een 1000 tal logfiles waarover ik moet rapporteren Elk bestand heeft een datum als naam en daarin staan veel regels met diverse gegevens, te starten met een kolom tijd waarin de tijd van een event staat.

Hoe kan ik in exel al deze bestanden importeren en tijdens het importeren 1 kolom toevoegen waarin iedere keer de datum van het bestand in die kolom wordt gezet voor zoveel regels als er in dat bestand zijn?

Daarnaast zou het mooi zijn als excel tijdens het importeren ook kan zien dat de legen ruimte tussen de gegevens een tab is die moet worden gebruikt om een nieuwe kolom te maken, nu ziet deze de code tab niet.

Als alles is ingevoerd kan ik gaan toveren en totalenen grafieken maken over de oproepen, per maand, weekdag overdag-nacht etc.

Wie weet hoe ik dit kan doen zodat het geautomatiseerd gaat en niet per bestand?
Ik heb een bestand bijgevoegd als voorbeeld.

Bekijk bijlage 20130912.txt

Alvast dank!

Groeten,
Ferry

 

[namliam]

Aangenomen dat dit bestand representatief is....
Het bijgesloten text bestand bevat geen tabs? Is een zgn "fixed width" setup met spaties

1000 files keer 1000 regels, 1.000.000 regels totaal, als je dat kwijt wil zal je toch naar een access database toe moeten tenzij je een "boel" van de rijen weg kan flikkeren.

Om de bestanden te importeren in access of in excel is wel heel goed mogelijk....

 

[Fheitkonig]

Dag Namliam, bedankt voor je antwoordt. Exel of Access, ik krijg de bestanden gewoon niet in excel op de wijze die ik beschrijf. Ipv dat de leverancier netjes in de log al een datum aangeeft moet ik deze nu vanuit de bestandsnaam overbrengen in een kolom van excel. Ik heb de formules bekeken, maar daar kom ik zo niet uit, is er een voorbeeld oid wat ik kan gebruiken?

 

[namliam]

Zoiets zou het importeren in excel, moet je alleen nog strLine gaan opsplitsen zodat je de juiste waardes in de losse kolommen krijgt

Sub import()
    Dim strFilename As String, strFolder As String
    strFolder = "C:\Folder\Subfolder\etc\"
    strFilename = Dir(strFolder & "2013*.txt")
    Range("A1").Select
    Do While strFilename <> ""
        Open strFolder & strFilename For Input As #1
        Line Input #1, strline
        Do While Not EOF(1)
            ' Set de datum van de filenaam in de huidige cell (kolom A)
            ActiveCell.Value = DateSerial(Left(strFilename, 4), Mid(strFilename, 5, 2), Mid(strFilename, 7, 2))
            ' zet de waarde van de regel in kolom B
            ActiveCell.Offset(0, 1).Value = strline

            Line Input #1, strline
            ActiveCell.Offset(1, 0).Select
        Loop
        Close #1
        strFilename = Dir
    Loop
End Sub

Een miljoen rijen in excel is wel veel, hoop dat je een nieuwe versie hebt.... anders moet je het in Access doen

 

[Fheitkonig]

Ik ben niet zo'n held met VB merk ik, te lang niet meer geprogrammeerd.... en niet in VB..

Het opsplitsen lukt mij niet, ik kan evt. wel nadat de import van alle bestanden heeft plaatsgevonden splitsen via tekst naar kolommen. Maar ik blijf nu hangen op een 400 error.
Pad netjes aangepast naar waar de bestanden staan, wat doe ik fout? Het deel waar ik, denk ik, de StrLine moet opsplitsen weggehaald.

Als het makkelijker gaat in Access mag dat ook, kan ik dan op dezelfde wijze importeren?

 

[namliam]

400 error? screenshots zijn hierbij meestal wel handig of de code zoals je die tot nu toe hebt.

Je kan de regels ongeveer met zoiets gaan opknippen, hoop dat je hiermee verder komt:

    ' zet de tijd in kolom 2
    ActiveCell.Offset(0, 2) = Mid(mijnString, 1, 8)
    'Zet 2 cijfers in kolom 3
    ActiveCell.Offset(0, 3) = Mid(mijnString, 9, 6)
    ' nog 2 cijfers
    ActiveCell.Offset(0, 4) = Mid(mijnString, 16, 4)
    ' Hoop tekst
    ActiveCell.Offset(0, 5) = Mid(mijnString, 20, 30)

Dit zet je neer voordat je de volgende regel leest: Line Input....

Of je dit in Access of Excel (moet) doen hangt ook heel erg af van wat je er mee wil doen, vergelijkbare (maar toch behoorlijk andere code) kan je in access schrijven om e.a. in een tabelletje te gieten. Ik kan dat (ook) wel voor je uitschrijven als je wil... Maar ik weet niet wat je data precies inhoudt en wat je er mee wil, etc... dus kan je het beter zelf doen, zo kan het ook beter onderhouden en later aanpassen naar je eigen wensen.

 

[Fheitkonig]

Dag namliam, bedankt voor de oplossing, het is inderdaad gelukt. Zie het resultaat hieronder. Ik moest nog wel na het runnen van het script met tekst naar kolommen kolom B splitsen, er was 1 datumkolom A en 1 kolom B met de rest na het script.
dat was niet zoveel moeite. Weet je misschien waar dat fout gaat in het script? Ik bedacht mij dat als 1 string de regel in kolom B zet er dan geen kolom 2 meer gebruikt kan worden voor de datum. Ga dat zo nog even proberen.
Dit zijn 126000 regels voor slechts 3 maanden, dus een overzicht per jaar moet misschien net lukken.

13-9-2013 00.00:54 PZI Server: pieper 3180 opgeroepen (zvpipc01)
13-9-2013 00.00:54 41 1 Oproep naar volgende pieper 180 2-01
13-9-2013 00.01:49 41 1 Hulpverlener aanwezig 2-01
13-9-2013 00.01:49 41 1 Oproep afgesteld 2-01
13-9-2013 00.01:49 PZI Server: pieper 3180 opgeroepen (zvpipc01)

 

[namliam]

Je kan het idd. met tekst naar kolommen oplossen, maar je kan het ook opsplitsen zoals ik je in de vorige post heb laten zien.

3 maanden, 126000, is -geextrapoleerd- 504.000 regels na een jaar, met een beetje mazzel is het dus 2 jaar mogelijk.

Als je datum en tijd in een cell wilt hebben dan kan dat ook (met gemak).
Uiteindelijk komt het allemaal neer op een antwoord op de vraag: Wat wil je met je data of wat kan je er mee.
Afhankelijk van dat antwoord zou het impact kunnen hebben op het ontleden of samenstellen van data uit je tekst bestand en misschien zelfs op je gekozen tool Excel vs Access of misschien nog iets anders.

 

[Fheitkonig]

Stom, net gezien dat je mijnString gebruikt i.p.v. strline in het script. Aangepast en werkt nu helemaal goed. Kolom 2 verwijderen en schone data over.

Harstikke bedankt, dit scheelt heeeeeel veel werk.

Groeten,
Ferry

 

[Fheitkonig]

Nog even op jou antwoord. Uiteindelijk wordt dit de basis om grafieken te maken per maand met overzichten aantallen alarmen, vzv mogelijk hoe snel die afgehandeld zijn en wie en wat voor soort melding voorkomt in een soort top 10.
Ik maak deze rapportages al vrij lang, alleen nog nooit met een leverancier die een datum bestand aanmaakt die je eerst allemaal moet koppelen, normaal staat de datum netjes in het bestand.

Ik ga hier nu eerst eens mee werken om te kijken hoe eea uitkomt. De code waardes ken ik nog niet, dier moeten dus ook nog uitgezocht worden...

Thx
Ferry

 

[snb]

Kan dat niet simpeler ?

Sub import()
    c00 = "C:\Folder\Subfolder\etc\"
    c01 = Dir(c00 & "2013*.txt")

    Do While c01 <> ""
        Open c00 & c01 For Input As #1
            c02 =c02 & vbcrLf & left(c01,8) & " " & replace(input(LOF(1),1),vbcrlf,vbcrlf & left(c01,8) & " ")
        close 
        c01 = Dir
    Loop

    Open c00 & "samen.txt" for output as #1
       print 1, mid(c02,3)
    close

    Workbooks.OpenText c00 & "samen.txt etc.
End Sub

 

[namliam]

Tja is inprincipe hetzelfde, SNB, al schrijf jij het eerst weg naar een nieuwe file en schrijf ik het direct naar excel...

 

[snb]

Inplaats van regel voor regel lees ik het hele bestand in 1 keer.
Ook het toevoegen van de datum is anders.
Hoe minder schrijfbewerkingen hoe sneller de code.

 

[Fheitkonig]

De laatste opmerkingen van snb had ik nog niet gezien, ik ga het ook op die manier eens proberen. De VB van NamLiam werkt goed, kunt alleen wel bij veel regels zien hoe het bestand opgebouwd wordt met gescrolde weergave.

 

[namliam]

Als je het scrollen wil voorkomen kan je dit "simpel" doen met

Application.ScreenUpdating = False
... Code here
Application.ScreenUpdating = True

En/of door ipv met activecell met een cells referentie te werken:

Sub import()
    Dim strFilename As String, strFolder As String, I as Double
    strFolder = "C:\Folder\Subfolder\etc\"
    strFilename = Dir(strFolder & "2013*.txt")
    Range("A1").Select
    I = 1
    Do While strFilename <> ""
        Open strFolder & strFilename For Input As #1
        Line Input #1, strline
        Do While Not EOF(1)
            ' Set de datum van de filenaam in de huidige cell (kolom A)
            Cells(I,1).Value = DateSerial(Left(strFilename, 4), Mid(strFilename, 5, 2), Mid(strFilename, 7, 2))
            ' zet de waarde van de regel in kolom B
            Cells(i,2).Value = strline

            Line Input #1, strline
            I = I + 1
        Loop
        Close #1
        strFilename = Dir
    Loop
End Sub

 

[snb]

Hoe minder vaak je naar een werkblad schrijft hoe sneller de code.