Microsoft SQL import quotes
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:
Code (php)
1
2
2
"Activiteitnaam";"Activiteitnr";"Jaar";"Datum";"Uren";"Projectnaam";"Klantnaam";"Organisatienaam";"Info";"Maand"
"KGBOUT_pick";"A_BRE_0113";2019;14-01-2019;9,13333333333333;"";"";"Warehouse Out";"";1
"KGBOUT_pick";"A_BRE_0113";2019;14-01-2019;9,13333333333333;"";"";"Warehouse Out";"";1
Import 'query' :
Code (php)
1
2
3
4
5
2
3
4
5
$query = @"
BULK INSERT $($DB_TABLE) FROM '$($fileTransfer.Destination)'
WITH
(FIRSTROW = 2, DATAFILETYPE='char', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
"@
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:
Code (php)
1
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?
Gewijzigd op 03/10/2019 07:46:23 door Dennis WhoCares
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
Code (php)
1
2
2
UPDATE tbl
SET Activiteitnaam = REPLACE(Activiteitnaam, CHAR(34), '')
SET Activiteitnaam = REPLACE(Activiteitnaam, CHAR(34), '')
Gewijzigd op 03/10/2019 08:03:37 door Bart V B
*facepalm*
Ja dat kan uiteraard ook..
Ik begin daar wel mee :D
Gewijzigd op 03/10/2019 08:45:57 door Dennis WhoCares
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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
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>'
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>'
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.
"Bulk Insert (BCP) will not honour quotes and will treat them like normal data."
Naderhand de quotes weghalen is het makkelijkste denk ik :)
Code (php)
1
2
2
"Henk","lala","test,abc","jaja"
// ^ oops
// ^ oops