txt.bestand in xlsx-bestand zetten op nieuwe manier

[JJZijlstra]

Beste lezer,

Ik heb hier een testbestandje als txt-bestand en wil deze m.b.v. de powerquery omzetten naar een bruikbaar xlsx-bestand.
Op de oude manier is voor mij dat geen enkel probleem maar ik zou dit graag ook op de nieuwe manier willen doen, dus m.b.v. de Powerquery in excel.
Welke stappen volg ik dan? Bestandje is hierbij toegevoegd.

Groet,
Toby

 

[AlexCEL]

Ik zou geen PQ gebruiken voor de import. Open de txt-file gewoon via "normale" Excel. Druk dan op volgende, dan kun je de breedte van de kolommen instellen en vervolgens voltooien. Dan krijg je al een mooie import. Titelblokje even herstellen en opslaan als xlsx. Zie bijlage voor resultaat.

 

[JJZijlstra]

@AlexCEL,

Je bedoelt dus, "Gebruik de ouderwetse manier maar" om txt-bestanden te importeren in excel door gebruikmaking van de keuze-optie "Oude Wizards".
Zelf ben ik bang dat de deze werkwijze straks zal gaan verdwijnen, vandaar ook mijn vraag.

Zoals ik al eerder zei, op de oude manier heb ik geen probleem met het importeren van tekstbestanden in excel maar heb wel het idee dar we daar straks geen gebruik meer van kunnen maken omddat deze manier gaat verdwijnen. Heb ik hier gelijk in? Ik hoop van niet.

Groet,
Toby

 

[AlexCEL]

Ik heb de keuze-optie Oude Wizards niet gebruikt. Je kan gewoon txt-bestanden openen op de "normale" manier. Ik zie dat ook niet snel verdwijnen eerlijk gezegd.

Maar ok: ik heb ook een poging gedaan in PQ. Eerst: menu gegevens > Gegevens ophalen > uit tekstbestand. Dan krijg je alle data in 1 regel. Daarna moet je een paar truukjes uithalen om tot de tabel in bijlage te komen, waaronder alle dubbele spaties eruit. Daarna splitsen op spatie als delimiter. Wat ik verder gedaan heb kun je zien bij de query-instellingen. Bekijk maar eens.

 

[JJZijlstra]

@AlexCEL,

Welke trucjes pas je dan toe om in de bijlage te komen?

Grt. Toby

 

[AlexCEL]

Die kun je bekijken in de query-instellingen. Als je deze 1 voor 1 aanklikt zie je het effect.
Betreft dubbele spaties verwijderen in aantal stappen, maar niet die van een bepaalde kolomkop, splitsen e.d.

 

[JJZijlstra]

@AllexCEL,

Bedankt voor je antwoord.

Toch kom ik er niet uit ondanks alles uitgeprobeerd te hebben. Als je eens stap voor stap aangeeft wat ik moet doen zoals jij dat bedoelt, misschien lukt het mij dan wel.

De lijst die je mij stuurde ziet er prachtig uit. Zo bedoel ik het.

Groet,
Toby

 

[AlexCEL]

Een beknopte handleiding dan:

1. Menu gegevens > uit tekstbestand > selecteer je txt-bestand en importeer. Je ziet een scherm met 1 kolom met al je gegevens.
2. Druk op gegevens transformeren. Je komt in de Power Query editor nu.
3. Menu start > rijen verwijderen > bovenste rijen verwijderen > verwijder 5 rijen.
4. Vink in het filter (dropdown rechtsbovenaan de kolom) van column1 de rij streepjes uit, daar kan je niets mee.
5. Menu transformeren > waarden vervangen. Vervang “CIRAF ZONES” door “CIRAF_ZONES”.
6. Menu transformeren > waarden vervangen. Vervang “;” door “”.
7. Menu transformeren > waarden vervangen. Vervang “ ” door “ ” (dus twee spaties door 1 spatie).
8. Herhaal stap 7 tenminste 6x.
9. Selecteer de gehele kolom (klik op kolomtitel) > rechtermuisknop > Transformeren > Omloopspaties verwijderen.
10. Menu transformeren > kolom splitsen > op scheidingsteken > selecteer “spatie” en “elk exemplaar van het scheidingsteken”.
11. Selecteer gehele kolom “Column 1.4”. Via menu transformeren > waarden vervangen: vervang “CIRAF_ZONES” door “CIRAF ZONES”.
12. Menu transformeren > De eerste rij als veldnamen gebruiken.
13. Menu start > sluiten en laden.
14. Tabel klaar.

Succes ermee.

 

[snb]

Daar is bij de ontwikkelaars van PQ duidelijk over nagedacht.

 

[AlexCEL]

Ik vond het een mooi resultaat. Als je het uitschrijft lijkt het veel stappen, maar het instellen in PQ gaat wel vlot waarna je het ook voor nieuwe bestanden kunt gebruiken.

Een groot nadeel vind ik wel dat PQ alleen met "absolute" verwijzingen kan werken naar externe bestanden en niet met relatieve. Daarom werkt het bestand in #4 niet zonder de verwijzing aan te passen.

 

[snb]

Immer te gebruiken voor nieuwe bestanden:

Sub M_snb()
  With Application.FileDialog(1)
     If .Show Then Workbooks.OpenText .SelectedItems(1), 65000, 6, 2, FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(11, 1), Array(16, 1), Array(47, 1), Array(50, 1), Array(55, 1), Array(63, 1), Array(67, 1), Array(71, 1), Array(79, 1), Array(86, 1), Array(93, 1), Array(96, 1), Array(101, 1), Array(113, 1), Array(116, 1), Array(120, 1), Array(124, 1), Array(130, 1), Array(134, 1), Array(139, 1), Array(145, 1), Array(151, 1))
   End With
End Sub

of

Sub M_snb()
   c00 = "J:\download\Test_009.txt"
    
   sn = Split(CreateObject("scripting.filesystemobject").opentextfile(c00).readall, vbCrLf)
   st = Split(sn(6), "+")
   ReDim sp(UBound(sn), UBound(st))
   
   For j = 7 To UBound(sn)
      y = 1
      For jj = 0 To UBound(st)
        If j = 7 Then sp(0, jj) = Trim(Mid(sn(5), y, Len(st(jj))))
        sp(j - 6, jj) = Trim(Mid(sn(j), y, Len(st(jj)) + 1))
        y = y + Len(st(jj)) + 1
      Next
   Next
   
   Sheet3.Cells(1).Resize(UBound(sp), UBound(sp, 2) + 1) = sp
End Sub

 

[JJZijlstra]

@AlexCEL,

Super, super en nog eens super bedankt voor de uitgebreide uitleg v.w.b. mijn vraag. Dit is precies wat ik bedoelde, nu ben ik een heel stuk verder gekomen met dit bestand
En wat misschien nog belangrijker is namelijk dat ik zaken geleerd heb over het gebruik van PQ, al zal ik me nog verder erin moeten verdiepen want hier ben ik nog een beginneling.
Mijn bestand had nogal wat bewerkingen nodig en dat had ik van tevoren niet gezien.
Nogmaals heel veel dank.

Ook snb wil ik bedanken voor zijn reactie en bijdrage. Zo zijn er dus meerdere mogelijkheden en daar leer ik weer van.

Met vriendelijke groet,
Toby