• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Excel splitsen met contactgegevens en adresgegevens

Status
Niet open voor verdere reacties.
ariecade

ariecade

Gebruiker
Lid geworden
5 jun 2019
Berichten
146
Hallo,

Ik heb een Excel bestand met daarin o.a. deze kolommen:

Voorletters | Voorvoegsel | Achternaam | Straat | Huisnummer | Postcode | Woonplaats

Nu wil ik deze tabel importeren in MySQL en wil ik deze Excel sheet exporteren als CSV.

Op zich geen probleem maar ik wil 2 aparte tabellen in MySQL maken, Contacten en Adressen omdat er verschillende contacten zijn die op hetzelfde adres wonen en wil daar een 1:N koppeling van maken zodat ik elk adres maar 1 keer hebt in de tabel en dat deze dus meerdere contacten kunnen hebben.

Dus ik wil graag van 1 Excel bestand 2 Excel bestanden maken met de contacten en de adressen waarbij het adressnummer het koppelingsnummer is.

Contacten.xlsx
Adresnummer | Voorletters | Voorvoegsel | Achternaam

Adressen.xlsx
Adresnummer | Straat | Huisnummer | Postcode | Woonplaats

Iemand enig idee of dit mogelijk is en hoe dit aan te vliegen?

Met vriendelijke groeten,

Arie
 
Begin eens met een bestandje te posten. Tenzij je liever hebt dat wij eerst zelf een half uurtje data gaan inkloppen?
 
Dat begrijp ik dat daar niet de voorkeur naar uit gaat ............... :cool:

Maar wil je dan alleen de kolomkoppen of gevuld met data? Want ik denk dat dat AVG-technisch gezien ook niet zo handig is.
 
@ariecade,

Ik denk niet dat Accesguru een half bezig is om alleen kolomkoppen in bestandjes te zetten. Dus een AVGproof voorbeeldbestand bedient de voorkeur
 
Duidelijk, heb nu even snel een bestand met 10 records in elkaar geflanst waarbij een aantal records familie zijn en dus het hetzelfde adres wonen.

Hopelijk is dit wat je bedoelt, anders hoor ik het wel :)
 

Bijlagen

  • voorbeeld.xlsx
    11,4 KB · Weergaven: 23
Dit achter een knopje in dat document:
Code: 
Sub Export2CSV()
    Open ThisWorkbook.Path & "\Contact.csv" For Output As #1
    Open ThisWorkbook.Path & "\Adres.csv" For Output As #2
    
    For i = 2 To Range("A:A").End(xlDown).Row
        Print #1, Cells(i, 1) & ";" & Cells(i, 3) & ";" & Cells(i, 4) & ";" & Cells(i, 5)
        Print #2, Cells(i, 1) & ";" & Cells(i, 9) & ";" & Cells(i, 10) & ";" & Cells(i, 13)
    Next i
    
    Close #1
    Close #2
End Sub
 
Laatst bewerkt:
Voer het splitsen uit in je MySQL database.

Importeer daarom je hele Excel bestand in een (tijdelijke) tabel in je MySQL database.
Maak een insertquery met een select distinct op de adresgegevens (straat t/m land) waarmee je de adrestabel vult en zorg ervoor dat die tabel ook een unieke IDadres kolom krijgt.
N.B. Je ziet dan meteen dat je een tikfout hebt gemaakt in de postcode van clientnummer 2.
Voeg ook een kolom IDadres toe aan je tijdelijke tabel.
Schrijf een updatequery die aan de hand van postcode+huisnummer+toevoeging IDadres uit je adrestabel toevoegt aan je tijdelijke tabel.
Maak een insertquery voor je contactgegevenstabel die alles behalve de adresgegevens meeneemt uit je tijdelijke tabel.
Drop de tijdelijke tabel.

Als je eerst de typefout verbetert eindig je dan met een adrestabel met 6 records en een contactgegevenstabel met 10 records.

Ik heb e.e.a. uitgevoerd met een SQL Server Express edition database, maar met MySQL kan het natuurlijk ook.
Succes.
 
in VBA:
Code: 
Sub M_snb()
  Sheet1.Copy

  With ActiveWorkbook
    .Sheets(1).Columns(6).Resize(, 10).Clear
    .SaveAs "G:\OF\kontakt.csv", 23, local:=True
    .Close 0
  End With

  With ActiveWorkbook
    .Sheets(1).Range("B1:HL1,L1").EntireColumn.Delete
    .SaveAs "G:\OF\adres.csv", 23, local:=True
    .Close 0
  End With
End Sub
 
Beste allen,

Enorm bedankt voor jullie reactie, ik ga inderdaad het proberen op de manier met een tijdelijke SQL tabel zoals AHulpje voorstelde en kijken of ik daarmee alle data in 2 gekoppelde tabellen ga krijgen.
 
Misschien heb je hier dan wat aan:

Importeer je huidige Excelbestand in je MySQL database, dat levert tabel Blad1 op.

Vul en creëer on the fly de tabel adres met alle unieke (DISTINCT) adressen uit tabel Blad1:
SELECT DISTINCT [Straat],[Postcode],[Huisnummer],[Toevoeging],[Plaats],[Land]
INTO adres
FROM Blad1

Voeg key IDadres toe aan die tabel:
ALTER TABLE adres
ADD IDadres INT IDENTITY(1, 1) PRIMARY KEY

Voeg kolom IDadres toe aan tabel Blad1:
ALTER TABLE [Test].[dbo].[Blad1]
ADD IDadres INT


Vul kolom IDadres in tabel Blad1 met IDadres uit tabel Adres:
UPDATE b
SET b.IDadres = a.IDadres
FROM [Test].[dbo].[Blad1] AS b
JOIN adres a
ON a.[Huisnummer] = b.[Huisnummer]
AND a.[Toevoeging] = b.[Toevoeging]
AND a.[Postcode] = b.[Postcode]

Vul en creëer on the fly de tabel contact met alle clienten uit tabel Blad1:
SELECT [Clientnummer]
,[Geslacht]
,[Voorletters]
,[Tussenvoegsels]
,[Achternaam]
,[E-mail]
,[Mobiel]
,[Telefoon]
,[Geboortedatum]
,[IDadres]
INTO contact
FROM [Test].[dbo].[Blad1]

Verwijder tabel Blad1:
DROP TABLE Blad1
 
Wederom bedankt vor deze query's, ga daar zeker even naar kijken.

Had persoonlijk gedacht om een PHP script te schtijven waarbij ik door elk record van de import tabel zou gaan lopen om vervolgens de contact en adres tabel te vullen maar misschien is dit wel handiger / sneller.

Heb de eerste test van het CSV bestand naar de import tabel gereed, zag alleen wel dat alle datum velden blijkbaar in de CSV als text staan want er worden geen datum velden geimporteerd.

Nu doe ik een datumveld meestal met Gegevens --> Tekst naar kolommen omzetten maar die gaat niet als er een tijd bij staat.

Heb jij nog een idee hoe ik snel de notatie kan wijzigen van een dd-mm-jjjj uu:mm:ss naar jjjj-mm-dd uu:mm:ss notatie als de oorspronkelijke inhoud dus blijkbaar een tekstveld is waardoor het niet via Celeigenschappen werkt?
 
Kun je een Excel bestandje uploaden met desnoods alleen de betreffende datumkolom?
 
Hierbij een voorbeeldbestand met 3 kolommen waarvan 2 kolommen dus ook een tijd notatie hebben.

Al deze kolommen moeten dus in de SQL tabel komen met de Amerikaanse notatie, jjjj-mm-dd (hh-mm-ss)
 

Bijlagen

  • Contactpersoon-100.xlsx
    12,4 KB · Weergaven: 13
Converteer de datum naar een hulpkolom (in A2 staat bijvoorbeeld 18-6-2017 10:39:37):
=DATUMWAARDE(A2)+RECHTS(A2;8)
en geef de geconverteerde datum de celopmaak "jjjj-mm-dd uu:mm:ss"
Dit geeft 2017-06-18 10:39:37 als resultaat.
Kopiëren en plakken als waarden over de oude waarde heen en hulpkolom(men) weer verwijderen.

Maar kijk ook eens hier: https://stackoverflow.com/questions/18838000/mysql-csv-import-datetime-value
 
Tekst naar kolommen ook.

Aanklikken en op voltooien drukken, daarna de opmaak aanpassen.
 
Klopt, alleen is dit niet voldoende als je ook tijd in de kolom hebt staan, dit werkt volgens mij alleen met datum velden.
 
Optie met Power Query
 

Bijlagen

  • Contactpersoon-100 (AC).xlsx
    26,2 KB · Weergaven: 10
Bedankt voor die laatste optie met Power Query.

Kan je misschien uitleggen hoe dit werkt? Is het gewoon een formule of een apart tooltje?
 
Power Query is een onderdeel van Excel. Druk maar eens op gegevens > gegevens ophalen > power query editor starten (of met rechtermuisknop op tabel klikken > tabel > query bewerken).

Je kan gegevens inladen en er dan stappen op los laten om tot een gewenst eindresultaat te komen. Die vind je rechts, als je van boven naar beneden aanklikt zie je wat ik ingesteld heb als stappen (eventueel op tandwieltje drukken naast de stap).

Bekijk maar eens... (op Youtube ook veel filmpjes te vinden over PQ).
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan