omzetten van text naar tabel

Allen,

Ik zit met een probleempje waar ik niet uit kom. Voor mijn werk heb ik informatie uit servers wat op de volgende manier (geanonimiseerd) in een tekst bestand wordt weggeschreven:

Apparaatnaam 1
{
"SerialNumber": "waarde hier",
"Model": "waarde hier",
"AssetTag": "waarde hier",
"HostName": "waarde hier",
"PowerState": "waarde hier"
}
{
"SystemBIOS": "waarde hier",
"ILOFirmware": "waarde hier",
"ILOBaseFirmware": "waarde hier",
"ILOFirmwareRunning": "waarde hier",
"SDRR": "waarde hier"
}
Apparaatnaam 2
{
"SerialNumber": "waarde hier",
"Model": "waarde hier",
"AssetTag": "waarde hier",
"HostName": "waarde hier",
"PowerState": "waarde hier"
}
{
"SystemBIOS": "waarde hier",
"ILOFirmware": "waarde hier",
"ILOBaseFirmware": "waarde hier",
"ILOFirmwareRunning": "waarde hier",
"SDRR": "waarde hier"
}
etc

Dit is een klein deel van het totaal want ook alle overige componenten staan in dit bestand. De informatie van de volgende server staat direct eronder en begint weer met de naam van de server. Nu importeer ik dit bestand in excel door gegevens uit text bestand te kiezen en als scheidingsteken de " (dubbele aanhalingstekens) te kiezen. Overigens werkt dit dan weer niet in excel 2019 maar wel in de oudere versies. Voor excel 2019 heb ik de " vervangen door ;.

Ik krijg dan een tabel met 5 kolommen. Echter waar ik naar op zoek ben is een tabel (het gaat om een groot aantal items) waar de informatie die ik zoek achter elkaar op één rij staan. v.b.:

apparaatnaam 1 in A1, Serienummer van het apparaat 1 in B1, model van apparaat 1 in C1, etc.

Wanneer ik vertikaal zoeken gebruik dan krijg ik bijna wat ik wil maar de waarden die ik in b.v. B1 wil, staat één of een paar regels lager in de B kolom (afhankelijk van wat ik wil zien). Hoe krijg ik dat allemaal netjes achter elkaar op één regel? Transponeren werkt overigs niet omdat ergens in het bestand informatie van een ander apparaat begint en transponeren zet alles achter elkaar waardoor ik nog steeds niets met de informatie kan. Het is curciaal dat de informatie op één regel gaat over één apparaat en niet dat de informatie van apparaat 2 ergens tussen apparaat 1 staat.

Alvast bedankt voor de input en hulp.

Groetjes

Arthur

Heb je een voorbeeldbestand?

Dat is een standaard JSON bestand.
Hoe dat in Excel te importeren staat hier beschreven:
https://codingislove.com/excel-json/

edmoor zei:

Dat is een standaard JSON bestand.
Hoe dat in Excel te importeren staat hier beschreven:
https://codingislove.com/excel-json/

Klik om te vergroten...

Dat is het inderdaad.

Dank voor de link. Kan ik op m'n werk helaas niets mee (mag niets installeren) maar thuis wel. Ik ga er gelijk mee aan de slag

json niet zo standaard

Ik heb getracht te doorgronden hoe ik dat VBA gedeelte moet gebruiken. Helaas gaat mijn kennis niet ver genoeg. Had graag nu een antwoord gegeven dat het werkt.

excel 2019 bied de optie om json files te lezen maar blijkbaar is mijn file niet een echt json bestand. Ik krijg dan een melding dat er extra tekens zijn ingevoerd aan het einde van de json invoer.

Of ik mis is. Wanneer ik het bestand in notepad++ open dan herkend deze het als gewone tekst. Wanneer ik een rondje internet doe hoe een json file eruit moet zijn dan wordt er gesproken over header en footer (heeft mijn bestand niet) en zie ik in voorbeelden dat het bestand begint met een [ (heeft mijn bestand ook niet).
Nu kan ik die [ toevoegen aan het begin maar dan krijg ik ook niet het gewenste resultaat.

Kom ik toch weer terug bij mijn originele vraag, is er een andere manier om de informatie op 1 regel te krijgen?

JimVeerman zei:

Heb je een voorbeeldbestand?

Klik om te vergroten...

Het lijkt een gewoon tekst bestand waaruit ik een klein gedeelte gekopierd en geananomiseerd heb. Excel is nog helemaal leeg dus daar heb ik geen voorbeeld van. Wanneer ik in notepad de " vervang voor ; en ik doe ik excel een tekst import dan krijg ik 5 kolommen met tekst.Dit gebeurt volgens het format als in het tekst bestand.

Regel 1 = apparaatnaam (cel A1)
Regel 2 = {
Regel 3 = "SerialNumber": "waarde hier", waarbij de tekst tussen quotes in een cel komt de dubbele punt in de cel rechts etc.
Regel 4 = "Model": "waarde hier",
etc

Het is toch echt een JSON bestand. En ja, dat is gewoon tekst, maar wel volgens een standaard layout en het object dat ik leit zien leest dan zonder problemen in.

Je vraagt het op 1 regel te krijgen en vervolgens toon je als voorbeeld Regel 1, Regel 2 enz.
Dat maakt het er niet duidelijker op.
Plaats een Excel document met hoe je dit stukje er in wilt hebben.

Heb je een voorbeeldbestand?

Klik om te vergroten...

hier mee wordt bedoeld: plaats hier een voorbeeld bestand.

Dit lijkt me voldoende:

Is geen valide JSON, knip en plak de JSON in bericht#1, in https://jsonlint.com/

voorbeeld

Op blad in staat hoe ik het geimporteerd krijg waarbij excel het niet herkend als json bestand. Op blad 2 wat ik er van wil maken.
Hopelijk maakt dit e.e.a. duidelijk.

wederom bedankt voor jullie hulp

snb zei:

Dit lijkt me voldoende:

Code:

Sub M_snb()
  sn=split(Createobject("scripting.filesystemobject").opentextfile("G:\OF\voorbeeld.json").readall,"Serialnumber")

  for j=0 to ubound(sn)
    sp=filter(split(sn(j),vblf),":")
    sn(j)=join(filter(split(sn(j),vblf),":"),"|")
  next

  sheet1.cells(1).resize(ubound(sn)+1)=application.transpose(sn)
  sheet1.columns(1),texttocolumns ,,,,0,0,0,0,-1,"|"
End Sub

Klik om te vergroten...

Ben niet zo thuis in vb maar als ik dit zou willen gebruiken moet ik dan alle velden die ik wil gebruiken uit het export bestand op de eerste regel toevoegen?

Wijzig alleen de naam van het te importeren bestand in de macro (via de VBEditor alt-F11 )

Sla dan op F5 om hem uit te voeren.

moet je nog wel even aanpassen naar

Lijkt me een gezond idee.

snb zei:

Wijzig alleen de naam van het te importeren bestand in de macro (via de VBEditor alt-F11 )

Sla dan op F5 om hem uit te voeren.

Klik om te vergroten...

Bestand ontvangen en geopend. Ik ga er vanuit dat dit gedeelte"

opentextfile("G:\OF\voorbeeld.json"

een path verwijzing is want wanneer ik de macro run dan krijg ik een path not found error. Nadat ik dit gedeelte heb aangepast naar de lokatie waar mijn bestand staat. (op de desktop) dan krijg ik een 400 fout. Dat is nadat ik de , voor texttcoloms heb aangepast naar een . zoals elders door VenA opgemerkt.

Je zult de volledige naam van het in te lezen bestand er moeten zetten.
Dan zul je verder nog even moeten debuggen.
De tekst staat in ieder geval in kolom A.

klein stukje verder

Na een rondje internet kwam ik tegen dat om fout 400 op te lossen je dit kan doen door een module toe te voegen en de macro daarin te zetten.

Gedaan en fout 400 is weg. Maar nu krijg ik fout 1004
Daarvan kan ik niet echt vinden wat de oplossing is tenzij ik alle 122000 resultaten ga doorspitten.

Het enige dat ik heb aangepast is de lokatie en de naam van het bestand.

PiSang99 zei:

Na een rondje internet kwam ik tegen dat om fout 400 op te lossen je dit kan doen door een module toe te voegen en de macro daarin te zetten.

Gedaan en fout 400 is weg. Maar nu krijg ik fout 1004

Code:

 Sheet1.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)

Daarvan kan ik niet echt vinden wat de oplossing is tenzij ik alle 122000 resultaten ga doorspitten.

Het enige dat ik heb aangepast is de lokatie en de naam van het bestand.

Code:

Sub M_snb()
  sn = Split(CreateObject("scripting.filesystemobject").opentextfile("C:\Test\test.json").readall, "Serialnumber")

  For j = 0 To UBound(sn)
    sp = Filter(Split(sn(j), vbLf), ":")
    sn(j) = Join(Filter(Split(sn(j), vbLf), ":"), "|")
  Next

  Sheet1.Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
  Sheet1.Columns(1).TextToColumns , , , , 0, 0, 0, 0, -1, "|"
End Sub

Klik om te vergroten...

In de For Next loop staat sp=filter wanneer ik de sp verander in sn dan loopt het script door, echter wordt alles in kolom A onder elkaar gezet. Zoals mijn voorbeeld wil ik graag alle serienummers onder elkaar in kolom A, alle types onder elkaar in kolom B etc waarbij per regel geldt dat die waarden bij elkaar horen.

Ander iets is dat niet alle regels worden gelezen (het uiteindelijke document heeft meer dan 100000 regels maar ergens op regel 10000 krijg ik een #N/B in de cel.

Begrijp jezelf niet wat je geschreven hebt? Of heb je een andere reden om een volledig bericht te quoten? Jouw voorbeeldtekst in de OP wijkt af van wat je in het excelbestand laat zien. Dus hoe ziet de export er wekelijk uit? Aan application.transpose zit een beperking en waarschijnlijk werkt het daardoor niet. De reactie van @alphamax heb je blijkbaar gemist?