Microsoft SQL import quotes

Door Dennis WhoCares op 03-10-2019 07:41 gewijzigd op 03-10-2019 07:46

2.109 views

Goede morgen allen,

Ik weet niet goed waar ik hulp moet vragen mbt Windows SQL.
Voor PowerBI heb ik een SQL server opgezet. Ze wouden graag Microsoft SQL... Their wish is my command.

Daarnaast heb ik een SFTP server en een paar enkele powershell scripts op de SQL server geplanned dat kijkt voor nieuwe bestanden in de map, download -> verwijder van sftp -> import naar sql -> hernoem bestand lokaal.

Voorbeeld data:


"Activiteitnaam";"Activiteitnr";"Jaar";"Datum";"Uren";"Projectnaam";"Klantnaam";"Organisatienaam";"Info";"Maand"
"KGBOUT_pick";"A_BRE_0113";2019;14-01-2019;9,13333333333333;"";"";"Warehouse Out";"";1

Import 'query' :


 $query = @"
BULK INSERT $($DB_TABLE) FROM '$($fileTransfer.Destination)' 
WITH 
(FIRSTROW = 2, DATAFILETYPE='char', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
"@

Zoals je ziet is de 'field terminator' geen ";" maar gewoon ;
De database waar het vandaan komt (TimeTell)
De datumnotatie al niet goed, de 'float' is ook niet goed, maar PowerBI kan hier wel mee omgaan als ik de fieldtype op varchar zet.

Het importeren gaat verder ook niet helemaal lekker, het importeerd ook de " waar ik graag vanaf wil.

Ik heb ook al de vraag uitstaan bij TimeTell om de datum als jaar-maand-dag en decimale getallen met een . te exporteren, maar weet niet of ze dit wel kunnen aanpassen xD

Ook heb ik geprobeerd:


 Get-Content $($fileTransfer.Destination)  | Foreach {$_ -replace '"', ""}  | Set-Content $($fileTransfer.Destination)

Maar daarbij kon de import script niet z'n gang gaan, omdat ik het idee heb dat dit op de achtergrond gebeurt, en eigenlijk zijn het bestanden van 15-20mb wat de import wel langzaam maakt als ik eerst die file moet opschonen.

Hebben jullie enig idee?

Bart V B

03-10-2019 08:00 gewijzigd op 03-10-2019 08:03

Ik ken totaal niks van ms sql of de andere programma's die je benoemd.
Dus waarschijnlijk roep ik nu een hele hoop domme dingen.
Als je de (ruwe) data hebt geïmporteerd, kan je dan niets met een update doen?
Heb even zitten googlen en daar kwam ik dit tegen:
https://stackoverflow.com/questions/41148915/bulk-import-csv-file-into-sql-server-remove-double-quotes

en dan


UPDATE tbl
SET Activiteitnaam = REPLACE(Activiteitnaam, CHAR(34), '')

Dennis WhoCares

03-10-2019 08:45 gewijzigd op 03-10-2019 08:45

Dan zitten we in hetzelfde schuitje
*facepalm*
Ja dat kan uiteraard ook..

Ik begin daar wel mee :D

Bart V B

03-10-2019 09:21

Ben benieuwd of we doorgaan voor de volgende ronde, of is de magnetron al gewonnen? :)

Dennis WhoCares

03-10-2019 14:58

Hi Bart,

Jawel ik ga voorlopig ff door ;-)
Maar ben benieuwd of er een andere oplossing voor is om de " te laten vervallen vanaf mijn kant.
Ook de Datum in de csv heeft geen " " maar moet opgeslagen worden als varchar in de SQL omdat de notatie foutief is.

Ik gebruik nu zoals voorgesteld:


UPDATE
    table_uren
SET
    Activiteitnaam = REPLACE(Activiteitnaam, '"',''),
    Activiteitnr = REPLACE(Activiteitnr, '"',''),
    Projectnaam = REPLACE(Projectnaam, '"', ''),
    Klantnaam = REPLACE(Klantnaam, '"', ''),
    Organisatienaam = REPLACE(Organisatienaam, '"', ''),
    Info = REPLACE(Info, '"', '')
WHERE
    Datum = '<dag-maand-jaar*van gisteren>'

Thomas van den Heuvel

03-10-2019 15:15

Uhm, kun je niet gewoon een enclosure opgeven, op dezelfde manier als dat je een delimiter opgeeft? Dat lijkt mij redelijk standaard functionaliteit als je met CSV werkt?

Zou zoveel mogelijk native CSV-functionaliteit proberen te gebruiken in plaats van zelf proberen dingen recht te breien waar al voorzieningen voor zijn.

Oh who am I kidding, het is Microsoft... De paar artikelen die ik van de "Microsoft Scripting Guy" voorbij heb zien komen geven mij weinig hoop.

Dennis WhoCares

04-10-2019 11:23

Inderdaad microsoft he...

"Bulk Insert (BCP) will not honour quotes and will treat them like normal data."

Naderhand de quotes weghalen is het makkelijkste denk ik :)

Thomas van den Heuvel

04-10-2019 16:19

Dan hoop ik voor jou dat er geen delimiters in de data zelf voorkomen want het klinkt alsof het allemaal als plaintext behandeld wordt, en niet als CSV. Zelfs halfbakken CSV-verwerkingsfunctionaliteit zou dat op moeten kunnen pikken.

"Henk","lala","test,abc","jaja"
//                 ^ oops

Reageren

Inloggen om te reageren