Format-Datei für Bulk insert erstellen

Question

Hallo,

ich möchte mit Bulk Insert Daten aus einer Textdatei importieren.

Wie erstelle ich die Formatdatei? Als Separator habe ich ein Semikolon. Welches Format muss die Datei haben?

Gruß Andreas

---

http://www.AccessBlog.de

Answer 1

Hallo Andreas,

unter http://msdn.microsoft.com/de-de/library/ms178129.aspx ist sehr gut beschrieben wie eine solche Datei auszusehen hat. Als Format kannst du wählen zwischen NoXML und XML Datei, wobei ich letztere bevorzugen würde. Je nachdem wie Deine Auswahl auswählt findest Du dann nähere Informationen unter:

für NoXML: http://msdn.microsoft.com/de-de/library/ms191479.aspx

bzw. für XML: http://msdn.microsoft.com/de-de/library/ms187833.aspx

Auf Deine Anforderung angepasst, sieht die da als Beispiel verwendete Format-Datei so aus:

NoXML:

10.0
4
1 SQLCHAR 0 7 ";" 1 Col1 ""
2 SQLCHAR 0 100 ";" 2 Col2 ""
3 SQLCHAR 0 100 ";" 3 Col3 ""
4 SQLCHAR 0 100 "\r\n" 4 Col4 ""

bzw. für XML:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Also einfach das Trennzeichen an das in der Textdatei verwendete anpassen.

 

---

Gruß Falk
XING

Answer 2

Hallo,

Meine SP sieht nun so aus:

USE [Database]
GO
/****** Object: StoredProcedure [dbo].[ImportG] Script Date: 06/27/2011 10:12:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ImportG]
@data_file nvarchar(500),
@format_file nvarchar(500),
@error_file nvarchar(500)

AS

SET NOCOUNT ON;

declare @sql nvarchar(max)
declare @anzahl int

set @sql = '
INSERT INTO [Database].[dbo].[tbl_importG]
(Feld1
,Feld2
,Feld3
,Nummer
,Status
,Erfassungsdatum
,Erfassungszeit)

SELECT CONVERT(decimal(18,0), [Feld1]) AS Feld1
,CONVERT(float, [Feld2]) AS Feld2
,Feld3
,CONVERT(int, [Nummer]) As Nummer
,CONVERT(int, [Status]) As Status
,Erfassungsdatum
,Erfassungszeit

FROM OPENROWSET(BULK N''' + @data_file + '''
,FORMATFILE = N''' + @format_file + '''
,CODEPAGE = ''OEM''
,ERRORFILE = N''' + @error_file + '''
) AS GImport'

EXEC sp_executesql @sql

Select @anzahl = @@ROWCOUNT
Return @anzahl

GO

 

Meine Formatdatei so:
8.0
9
1 SQLCHAR 0 255 ";" 4 Nummer ""
2 SQLCHAR 0 255 ";" 3 Feld3 ""
3 SQLCHAR 0 255 ";" 1 Feld1 ""
4 SQLCHAR 0 255 ";" 0 Skip1 ""
5 SQLCHAR 0 255 ";" 0 Skip2 ""
6 SQLCHAR 0 255 ";" 2 Feld2 ""
7 SQLCHAR 0 255 ";" 5 Status ""
8 SQLCHAR 0 255 ";" 6 Erfassungsdatum ""
9 SQLCHAR 0 255 ";" 7 Erfassungszeit ""

Alle Text-Dateien liegen unter C:\Users\Andreas Vogt\Kunde\SQL Server\

Beim Aufrufen aus Access bekomm ich folgende Fehlermeldung:

"Das Massenladen ist nicht möglich, da die Datei .....FehlerDatei.txt nicht geöffnet werden konnte. BetriebssystemfehlerCode 80 (Die Datei ist vorhanden)"

Wo liegt mein Fehler? Gruß Andreas

---

http://www.AccessBlog.de

Answer 3

Hallo,

na die Spezifikationen sollte man halt kennen, gefunden hab ich sie hier:

http://msdn.microsoft.com/de-de/library/ms190312.aspx

Vorallem dieser Satz: Falls die Datei bereits vorhanden ist, wird ein Fehler ausgelöst.

Dies bedeutet aber wenn die SP das 2. mal ausgeführt wird, ist die Datei ja erstellt worden und es wird wieder Fehler ausgelöst?

Wie schlau ist das denn?

Gruß Andreas

---

http://www.AccessBlog.de