MSSQL importeren csv problemen

[Stefke]

Ik moet csv files importeren in SQL server. Om de een of andere reden heb ik daar altijd ruzie mee. Ogenschijnlijk zijn de T-sql instructies daarvoor niet zo complex, maar bij mij wil het nooit zo maar werken.


Dit is de CSV file die ik moet importeren:

code:

ExportDate;AdminNo;RelNo;InvoiceNo;ItemType;InternalNmbr;InvoiceDate;InvoiceDueDate;DaysTerm;Description;CurrencyCode;OriginalAmount;PaidAmount;OpenAmount;VATAmount;VatPercentage;BaseCurrencyCode;BaseOriginalAmount;BaseOpenAmount;BaseVatAmount;Factor
"2023-12-08 23:30:54";"BLABLA";"477180186";"13723FACLI1205632";"VENTE";"";"2023-12-08";"2024-02-06";"60";"";"EUR";"66271.36";"0.0";"66271.36";"";"";"EUR";"66271.36";"66271.36";"0";"1"

17 kolommen (factuurdata)

code:

IF OBJECT_ID('TEMPDB..#StagingTableDebtors') IS NOT NULL DROP TABLE #StagingTableDebtors;
CREATE TABLE #StagingTableDebtors (
    ExportDate          nvarchar(100) NULL,     --1
    AdminNo             nvarchar(100) NULL,     --2
    RelNo               nvarchar(100) NULL,     --3
    CompanyName         nvarchar(100) NULL,     --4
    Street              nvarchar(300) NULL,     --5
    Street_2            nvarchar(300) NULL,     --6
    Street_3            nvarchar(300) NULL,     --7
    Zipcode             nvarchar(50) NULL,      --8
    City                nvarchar(50) NULL,      --9
    CountryCode         nvarchar(2) NULL,       --10
    PostalStreet        nvarchar(300) NULL,     --11
    PostalStreet_2      nvarchar(300) NULL,     --12
    PostalStreet_3      nvarchar(300) NULL,     --13
    PostalZipCode       nvarchar(50) NULL,      --14
    PostalCity          nvarchar(50) NULL,      --15
    PostalCountryCode   nvarchar(2) NULL,       --16
    CreditorAmount      nvarchar(100) NULL      --17 --decimal(15, 2) NOT NULL
);

INSERT INTO #StagingTableDebtors
SELECT *
FROM OPENROWSET(
   BULK 'D:\Data\TST\BLABLA\out_Customers_20231208.csv',
   FORMATFILE = 'D:\Data\DBasics\csv_file_import_set.fmt',
   FIRSTROW = 2
) AS Data;

DIt is de formatfile:

code:

14.0
17
1 SQLCHAR   0   100     ";"     1   ExportDate          
2 SQLCHAR   0   100     ";"     2   AdminNo         
3 SQLCHAR   0   100     ";"     3   RelNo           
4 SQLCHAR   0   100     ";"     4   CompanyName     
5 SQLCHAR   0   300     ";"     5   Street          
6 SQLCHAR   0   300     ";"     6   Street_2        
7 SQLCHAR   0   300     ";"     7   Street_3        
8 SQLCHAR   0   50      ";"     8   Zipcode             
9 SQLCHAR   0   50      ";"     9   City            
10 SQLCHAR  0   2       ";"     10  CountryCode     
11 SQLCHAR  0   300     ";"     11  PostalStreet    
12 SQLCHAR  0   300     ";"     12  PostalStreet_2
13 SQLCHAR  0   300     ";"     13  PostalStreet_3
14 SQLCHAR  0   50      ";"     14  PostalZipCode
15 SQLCHAR  0   50      ";"     15  PostalCity  
16 SQLCHAR  0   2       ";"     16  PostalCountryCode
17 SQLCHAR  0   100     "\n"   17  CreditorAmount

Helaas krijg ik deze foutmelding:
Msg 4823, Level 16, State 1, Line 87
Cannot bulk load. Invalid column number in the format file "D:\Data\csv_file_import_set.fmt"

Ik heb de kolommen in de code en de formatfile geknipt/geplakt om fouten te voorkomen, ik heb ze genummerd om telfouten te voorkomen, maar ik blijf deze fout krijgen.
Als ik de CSV file in Visual Studio Code open dan zie ik UTF-8 en LF aangegeven staan, dus volgens mij is "\n" correct als line terminator. "\r\n" werkt iig ook niet.
Ik heb het idee dat de fout iets anders is maar de melding verkeerd?

Al tientallen pagina's bekeken, videoinstructies op youtube, ChatGPT geraadpleegd. Ik kom er niet uit.

Vervolgens dit gemaakt (CSV, formatfile en TSQL) als testsetup

code:

ExportDate;Column2
"bla";"test"
"bla2";"test2"




14.0
2
1 SQLCHAR   0   100     ";"     1   ExportDate                      
2 SQLCHAR   0   100     "\n"     2   Column2    




IF OBJECT_ID('TEMPDB..#StagingTableDebtors') IS NOT NULL DROP TABLE #StagingTableDebtors;
CREATE TABLE #StagingTableDebtors (
    ExportDate          nvarchar(100) NULL,         --1
    Column2             nvarchar(100) NULL,
);

INSERT INTO #StagingTableDebtors
SELECT *
FROM OPENROWSET(
   BULK 'C:\temp\test.csv',
   FORMATFILE = 'C:\temp\test.fmt',
   FIRSTROW = 2
) AS Data;

maar dezelfde foutmelding. Iemand een idee wat ik verkeerd doe? Ik kijk vast over iets heel stoms heen.

[ Voor 3% gewijzigd door Stefke op 10-12-2023 13:34 ]

[Crazy D]

Heb je een enter gegeven na de laatste regel in je format file?

[Voutloos]

En mag je de column collation eigenlijk wel weglaten in de formatfile?

[Merethil]

Kan het zijn dat je csv row terminator "\r\n" is ipv "\n"? Als ie dan aan het einde van je regel komt en niet de juiste row terminator tegenkomt wil hij waarschijnlijk domweg kolom 18 gaan bepalen en die heb je (logischerwijs) niet.

[Stefke]

Crazy D schreef op zondag 10 december 2023 @ 13:55:
Heb je een enter gegeven na de laatste regel in je format file?

Die was er niet, net toegevoegd maar ik krijg dezelfde foutmelding

Voutloos schreef op zondag 10 december 2023 @ 13:58:
En mag je de column collation eigenlijk wel weglaten in de formatfile?

Ik heb collation toegevoegd, nu krijg ik deze fout

code:

Msg 213, Level 16, State 1, Line 52
Column name or number of supplied values does not match table definition.

code:

14.0
2
1 SQLCHAR   0   100     ";"     1   ExportDate  SQL_Latin1_General_CP1_CI_AI
2 SQLCHAR   0   100     "\n"  2   Column2     SQL_Latin1_General_CP1_CI_AI

Merethil schreef op zondag 10 december 2023 @ 15:03:
Kan het zijn dat je csv row terminator "\r\n" is ipv "\n"? Als ie dan aan het einde van je regel komt en niet de juiste row terminator tegenkomt wil hij waarschijnlijk domweg kolom 18 gaan bepalen en die heb je (logischerwijs) niet.

Beide geprobeerd, maar dan ook de melding m.b.t. het aantal kolommen. Ook in combinatie met de 2 bovenstaande aanpassingen (al krijg ik met collation dus een iets andere foutmelding)

[MatHack]

Moet de laatste niet "\r\n" zijn ipv "\n"? Ik neem aan dat dit vanaf een Windows machine gebeurd.

[ocwil]

Probeer eens SQLVARCHAR en een lengte van 0 inplaats van 100 in de Format file?

[Stefke]

MatHack schreef op maandag 11 december 2023 @ 11:52:
Moet de laatste niet "\r\n" zijn ipv "\n"? Ik neem aan dat dit vanaf een Windows machine gebeurd.

WIndows inderdaad, maar \r\n of \n maakt geen verschil

ocwil schreef op maandag 11 december 2023 @ 12:03:
Probeer eens SQLVARCHAR en een lengte van 0 inplaats van 100 in de Format file?

Dat geeft deze fout:
Msg 4824, Level 16, State 1, Line 52
Cannot bulk load. Invalid data type for column number 1 in the format file "C:\temp\test.fmt".

Als ik de 2e kolom SQLVARCHAR maak en de eerste SQLCHAR krijg ik de melding op de 2e kolom


https://learn.microsoft.c...ver?view=sql-server-ver16
Als ik hier kijk, dan maken ze een csv file zonder kolomheaders. Moet ik nog ergens aangeven dat de 1e rij kolomheaders zijn misschien? Ik zie dit niet terug in de instructies op deze pagina

[ Voor 19% gewijzigd door Stefke op 11-12-2023 13:01 ]

[LuukRuyter]

Wellicht dat je de "import and export Data" wizzard is kan draaien om het handmatig met 1 file te proberen.
Op die manier kan je ieder geval is testen of het inlezen goed werkt.
Dit zit praktische altijd standaard mee geïnstalleerd bij de SQL server installatie.

[Stefke]

Dat zal ik straks even doen (nu niet in de gelegenheid), maar het gaat nu om mijn testfile van 2 kolommen en 2 records, zie boven. Zelfs dat krijg ik niet geimporteerd

[LuukRuyter]

Voor je test file mis je volgens mij zowel de collation als de new row.
Text qualifiers via bulk insert is ook wel een uitdaging

code:

14.0
2
1 SQLCHAR   0   100     ";"        1   ExportDate    SQL_Latin1_General_CP1_CI_AS                   
2 SQLCHAR   0   100     "\r\n"     2   Column2       SQL_Latin1_General_CP1_CI_AS

[Stefke]

Met jouw versie krijg ik een andere maar soortgelijke foutmelding:

code:

Msg 213, Level 16, State 1, Line 52
Column name or number of supplied values does not match table definition.

Wat zou "number of supplied values" kunnen betekenen? Klinkt alsof het gaat over de data, mijn 2 records in de test-csv?

Je laatste opmerking snap ik niet helemaal?

[ Voor 20% gewijzigd door Stefke op 11-12-2023 16:27 ]

[LuukRuyter]

Hij wil nog verplicht een enter na lijn 4:



Edit: De code viewer van Tweakers haalt automatisch lege regels weg.

Flatfiles zijn de grootste rotzakken die er zijn.
Volgens mij is het niet mogelijk om via Bulk Insert ook velden met tekst als tekst te zien.
Je kan geen text qualifiers aangeven zoals ".
Hij zal "bla" dus ook letterlijk als "bla" inlezen en niet als bla.

[ Voor 41% gewijzigd door LuukRuyter op 11-12-2023 16:41 ]

[Stefke]

Ik ben er uit, maar wat het probleem nou was weet ik niet.

Ik had een nieuwe test-csv gemaakt met Excel. Die kwam met een bestand zonder " en , als scheidingsteken. Daar de test-formatfile op aangepast en die werd ingelezen.
Toen weer omgezet naar ; en met ", die werd ook ingelezen

Vervolgens de oude test-csv aangepast, en die wordt nu ook ingelezen, terwijl er volgens mij niets anders is... Op één parameter na
format='CSV'
Die zag ik nog staan in een youtubeinstructie

Nu de klantfile nog eens bekeken; volgens notepad++ toch echt een CRLF als rowterminator. Nog eens alles nagelopen en nu wordt ook de klantfile geimporteerd.
Blijkbaar was de format='CSV' nodig, zonder doet ie het niet

code:

IF OBJECT_ID('TEMPDB..#StagingTableDebtors') IS NOT NULL DROP TABLE #StagingTableDebtors;

CREATE TABLE #StagingTableDebtors (
    ExportDate          datetime NULL,          --1
    AdminNo             nvarchar(100) NULL,     --2
    RelNo               nvarchar(100) NULL,     --3
    CompanyName         nvarchar(100) NULL,     --4
    Street              nvarchar(300) NULL,     --5
    Street_2            nvarchar(300) NULL,     --6
    Street_3            nvarchar(300) NULL,     --7
    Zipcode             nvarchar(50) NULL,      --8
    City                nvarchar(50) NULL,      --9
    CountryCode         nvarchar(2) NULL,       --10
    PostalStreet        nvarchar(300) NULL,     --11
    PostalStreet_2      nvarchar(300) NULL,     --12
    PostalStreet_3      nvarchar(300) NULL,     --13
    PostalZipCode       nvarchar(50) NULL,      --14
    PostalCity          nvarchar(50) NULL,      --15
    PostalCountryCode   nvarchar(2) NULL,       --16
    CreditorAmount      nvarchar(100) NULL      --17--decimal(15, 2) NOT NULL
);

INSERT INTO #StagingTableDebtors
SELECT *
FROM OPENROWSET(
   BULK 'D:\Data\DBasics\TST\COFEL\COFEL_137\out_Customers_20231208.csv'
   ,FORMATFILE = 'C:\temp\csv_file_import_set.fmt'
   ,FIRSTROW = 2
   ,FORMAT='CSV'
) AS Data;

Laat ik die formatparameter weg dan krijg ik deze meldingen:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ExportDate).

Mogelijk op een andere manier op te lossen, maar het werkt zo iig eindelijk...


Verder was de functie in Notepad++ die de tekens weergeeft nieuw voor mij achteraf logisch dat dit kan met Notepad.
Ben er nu wel achter dat de klant niet alle files met dezelfde rowterminator aanlevert... handig (wisselend LF en CRLF)


edit: ik weet het wel :

Column name or number of supplied values does not match table definition.

Deze fout slaat op de tijdelijke SQL-tabel: ondanks dat ik die elke keer verwijder voor het aanmaken raakt SQL server toch op de een of andere manier in de war. De import zal waarschijnlijk wel gelukt zijn al een paar keer, maar ergens ging dus wat fout met de SQL-tabel.

@LuukRuyter En je had gelijk over de ", die wordt geimporteerd als tekst. Maar als ik de parameter format='CSV' gebruik dan wordt de " niet geimporteerd

Het werkt nu, bedankt voor t meedenken

[ Voor 10% gewijzigd door Stefke op 11-12-2023 17:36 ]