Importera textfil till sql-server med format file

När man skall importera en textfil (ofta kommaseparerad) till sql-server så kan man ibland få problem på olika sätt. Det kan exempelvis vara så att ett av fälten innehåller en kommaseparerad i sig själv.

Som exempel kan vi ta följande textfil.

1,Field2,”keyword1,keyword2,keyword3″,Field4
2,Field2,”keyword1,keyword2,keyword3″,Field4
3,Field2,”keyword1,keyword2,keyword3″,Field4
4,Field2,”keyword1,keyword2,keyword3″,Field4

Problemet i denna textfil är fält nummer tre som innehåller en kommaseparerad sträng, men att fältseparatorn i övrigt är komma.

Normalt när man importerar en textfil kan man göra på följande sätt:

BULK INSERT MyTable
FROM 'C:\mycsv.txt'
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)

Men i vårt fall kommer detta att resultera i följande:

Col1 	Col2 	Keywords		Col4
1 	Field2 	"keyword1 	keyword2,keyword3",Field4
2 	Field2 	"keyword1 	keyword2,keyword3",Field4
3 	Field2 	"keyword1 	keyword2,keyword3",Field4
4 	Field2 	"keyword1 	keyword2,keyword3",Field4

 
Det var inte riktigt så vi hade tänkt oss. Därför finns det en funktion som heter format file som vi kommer att använda. Det gör det möjligt att skapa en fil med instruktioner till hur filen ser ut och hur den skall importeras.
I vårt fall ser filen ut såhär

9.0
4
1   SQLCHAR  0   7      ","	 	1     Col1 	""
2   SQLCHAR  0   50     ",\""	2     Col2 	""
3   SQLCHAR  0   50     "\","	3     Keywords 	""
4   SQLCHAR  0   50     "\n" 	4     Col4 	""

Beskrivning av format file

Notera hur vi anger att separatorn för fält 2 skall vara ,” och för fält 3 skall vara ”,. På grund av detta så får vi allt som finns mellan dubbelfnuttarna i fält 3.

Här kan du läsa mer om hur man bygger en format file med rätt datatyper och annat.

Exempel på hur man använder BULK INSERT med format file.

BULK INSERT MyTable
   FROM 'C:\mycsv.txt'
   WITH (FORMATFILE = 'C:\formatfile.txt');

Resultat:

Col1	Col2	Keywords			Col4
1	Field2	keyword1,keyword2,keyword3	Field4
2	Field2	keyword1,keyword2,keyword3	Field4
3	Field2	keyword1,keyword2,keyword3	Field4
4	Field2	keyword1,keyword2,keyword3	Field4

Eller ett lite mer modernt exempel med OPENROWSET så man direkt kan använda WHERE-villkor för att exempelvis filtrera bort vissa rader. I vårt exempel tar vi bara med den post där Col1 har värdet 2.

INSERT INTO MyTable
	SELECT * FROM
	OPENROWSET(BULK 'C:\mycsv.txt',
FORMATFILE='C:\formatfile.txt') t
WHERE Col1 = 2

Vi får då precis det resultat som vi var ute efter:

Col1	Col2	Keywords			Col4
2	Field2	keyword1,keyword2,keyword3	Field4

Kommentarer inaktiverade.

%d bloggare gillar detta: