HOFIA | Consultancy & Photography

Hermann-Otto F. Israël

Contact us now
+31(6)54257954

Extra regels door kopiëren van Excel > SQL Server en vice versa

Een van mijn klanten liep tegen het volgende probleem aan: Bij het kopieren van data van Excel naar SQL-Server of juist andersom werden records afgebroken en werden er 2 of meer regels aangemaakt, terwijl het originele gegeven uit maar 1 regel bestond.

Dit probleem heeft te maken met de (harde) return/CRLF die in de cel is opgenomen. In de meeste gevallen wordt deze (harde) return/CRLF gebruikt om op een nieuwe regel verder te gaan met de tekst en een en ander netjes onder elkaar te plaatsen. Het probleem met deze werkwijze is dat het bij een kopieer/plakactie (copy/paste) tot meerdere regels (records) leidt.

De oplossing:

  • Kopieëren in Excel en plakken in SQL-server tabel:
    • Ga naar het desbetreffende Excel werkblad
    • Druk op de toetsen (in combinatie) CNTRL-H
    • Ga naar het [Find what]-veld en druk op de toetsencombinatie CNTRL-J. De waarde in het [Find what]-veld is nu een knipperende cursor.
    • Ga naar het [Replace with]-veld en geef daarin het scheidingsteken aan dat de (harde) return/CRLF moet vervangen. Zelf gebruik ik meestal het karakter “|”.
    • Click op [Replace all] en alle (harde) returns/CRLF’s worden vervangen door het “|” karakter.
  • In omgekeerde volgorde, van SQL-Server naar Excel, heb ik het opgelost in het select-statement:
    • In SQL Server creeer de query die voor de output moet zorgen.
    • Voor het afbeelden van het veld dat mogelijk een (harde) return/CRLF bevat gebruik ik de volgende SELECT regel, waarbij [Purpose] het variabele veld is:
      • ISNULL(REPLACE(REPLACE(Enriched.[Purpose],CHAR(10),’ | ‘),CHAR(13),”),”) as Purpose

Voorbeeld (voor beide oplossingen):

Toegepast op beide voorbeelden:

12345[harde return]
67890

wordt

12345|67890

Dit bespaart mij een hele boel corrigeerwerk en daarmee veel tijd…

Succes!