Datenübernahme von SAP Business One

Um Daten von SAP Business One zu übernehmen, erstellen Sie zuerst ein Verzeichnis, um die Daten dort zwischenzulagern, beispielsweise:

C:\X-IMPORT-SAP-TRANSFER

1. Legen Sie in dieses Verzeichnis die benötigten Übernahme-Scripte und die dazugehörigen Tabellenvorlagen.
2. Starten Sie das erste Script, beispielsweise Partner.SQL, um die Partneradressen aus SAP Business One auszulesen.
3. Kopieren Sie das Abfrageergebnis in die Tabellenvorlage Partner.XLSX.
4. Überprüfen Sie die Daten auf Richtigkeit.
5. Löschen Sie bei Bedarf „NULL“-Wörter.
6. Überprüfen Sie Pflichtfelder (z. B. Matchcode).
7. Ändern Sie doppelte Werte in eindeutigen Spalten (z. B. Matchcode).
8. Importieren Sie die Daten per Bulk-Import, beispielsweise:

   BULK INSERT dbo.Partner
   FROM 'C:\X-IMPORT-SAP-TRANSFER\Partner.CSV'
   WITH (
       FORMAT='CSV',
       CODEPAGE = '65001',
       FIELDTERMINATOR = ';',
       FIRSTROW=7
   );
           

9. Achten Sie auf Fehlermeldungen, korrigieren Sie fehlerhafte Daten und passen Sie das Script nach Bedarf an.
10. Überprüfen Sie das Ergebnis.

Mit diesem Script können Sie Geschäftspartnerdaten aus SAP Business One auslesen. Weitere Scripte erhalten Sie auf Anfrage.

SQL-Statement: Geschäftspartner

SELECT
    OCRD.CardCode AS Id,
    OCRD.CardName AS Name1,
    '' AS Name2,
    OCRD.Address AS Street,
    OCRD.ZipCode AS Zip,
    OCRD.City AS City,
    OCRD.Country AS CountryId,
    CONCAT(OCRD.CardCode, ' - ', OCRD.CardName) AS Matchcode,
    OCRD.LicTradNum AS VatNumber,
    OCRD.GroupCode AS PartnerGroupId,
    OCRD.Phone1 AS Tel1,
    OCRD.Phone2 AS Tel2,
    OCRD.Cellular AS Mobile,
    OCRD.Fax AS Fax,
    OCRD.E_Mail AS Email,
    '' AS NormalizedEmail,
    '' AS EmailConfirmed,
    '' AS PasswordHash,
    '' AS SecurityStamp,
    '' AS ConcurrencyStamp,
    '' AS TwoFactorEnabled,
    '' AS LockoutEnd,
    '' AS LockoutEnabled,
    '' AS AccessFailedCount,
    '' AS Homepage,
    '' AS PrivatePerson,
    '' AS LanguageId,
    '' AS LocationId,
    '' AS Name3,
    '' AS Name4,
    '' AS Name5,
    '' AS PostboxCity,
    '' AS PostboxZip,
    '' AS PostboxCountryId,
    '' AS Guest,
    '' AS Editor,
    '' AS Author,
    '' AS Active,
    '' AS Favorite,
    '' AS DeliveryBlock,
    '' AS CallBlock,
    '' AS EmailBlock,
    '' AS Gln,
    '' AS Eori,
    '' AS TaxNumber,
    OCRD.CardCode AS Keyword, -- Tipp: Speichern Sie die alt ID, um den Datensatz später noch zuordnen zu können.
    '' AS Webshop,
    '' AS Marketplace,
    '' AS Helpdesk,
    '' AS News,
    '' AS Wiki,
    '' AS Crm,
    '' AS DateCreated,
    '' AS CreatedBy,
    '' AS RowVersion
FROM OCRD;

SQL-Statement: Liefer- / Rechnungsadressen

SELECT
    CRD1.CardCode AS PartnerId,
    OCRD.CardName AS Matchcode,
    OCRD.CardName AS Name1,
    '' AS Name2,
    '' AS Name3,
    '' AS Position,
    '' AS Title,
    '' AS Salutation,
    '' AS FirstName,
    '' AS LastName,
    CRD1.Street AS Street,
    CRD1.Building AS Extension,
    CRD1.ZipCode AS Zip,
    CRD1.City AS City,
    CRD1.Country AS CountryId,
    '' AS Tel1,
    '' AS Tel2,
    '' AS Mobile,
    '' AS Fax,
    '' AS Email,
    '' AS Homepage,
    '' AS Keyword,
    CRD1.LicTradNum AS VatNumber,
    CRD1.AltTaxId AS TaxNumber,
    1 AS Favorite,
    1 AS Active,
    '' AS DateCreated,
    '' AS CreatedBy,
    '' AS Latitude,
    '' AS Longitude,
    '' AS RowVersion
FROM CRD1
INNER JOIN OCRD ON CRD1.CardCode = OCRD.CardCode
WHERE CRD1.AdresType = 'S'; -- Lieferadresse
-- WHERE CRD1.AdresType = 'B'; -- Rechnungsadresse

SQL-Statement: Ansprechpartner

SELECT
    OCPR.CardCode AS PartnerId,
    OCPR.Name AS Matchcode,
    OCPR.Position AS Position,
    OCPR.Title AS Title,
    '' AS Salutation,
    OCPR.FirstName AS FirstName,
    OCPR.LastName AS LastName,
    OCPR.Address AS Street,
    '' AS Extension, -- Feld in OCPR nicht vorhanden
    '' AS Zip,
    '' AS City,
    '' AS CountryId,
    OCPR.Tel1 AS Tel1,
    OCPR.Tel2 AS Tel2,
    OCPR.Cellolar AS Mobile,
    OCPR.Fax AS Fax,
    OCPR.E_MailL AS Email,
    '' AS ContactPersonEmailCategoryId,
    '' AS Homepage, -- Feld in OCPR nicht vorhanden
    '' AS Keyword, -- Feld in OCPR nicht vorhanden
    1 AS Favorite,
    CASE WHEN OCPR.Active = 'Y' THEN 1 ELSE 0 END AS Active,
    OCPR.Notes1 AS Info,
    '' AS DateCreated,
    '' AS CreatedBy, -- Feld in OCPR nicht vorhanden
    OCPR.updateDate AS DateModified,
    '' AS ModifiedBy, -- Feld in OCPR nicht vorhanden
    OCPR.EncryptIV AS RowVersion
FROM OCPR;

SQL-Statement: Artikel

SELECT
ItemCode AS Id
, ItemName AS Name1
, “ AS Name2
, “ AS Matchcode
, “ AS ArticleTypeId
, ItmsGrpCod AS ArticleGroupId
, SUoMEntry AS UomId
, “ AS DecimalPlacesQuantitiesId
, InvntItem AS Warehousing
, “ AS Active
, “ AS Favorite
, “ AS PercentageMarkup1
, “ AS FixedMarkup1
, “ AS IntermediateCalculatedSalesPrice
, “ AS PercentageMarkup2
, “ AS FixedMarkup2
, “ AS CalculatedSalesPrice
, “ AS PriceUnitId
, FirmCode AS ManufacturerId
, SuppCatNum AS ManufacturerOrderNumber
, “ AS WarehouseInfoId
, “ AS Shop
, “ AS Printing
, “ AS Weight
, “ AS Length
, “ AS Width
, “ AS Height
, SVolume AS Volume
, “ AS Content
, “ AS ContentUomId
, CodeBars AS Gtin
, CreateDate AS DateCreated
, “ AS CreatedBy
, “ AS SalesFinanceGroupId
, “ AS LotSize
, MinLevel AS MinimumQuantity
, LeadTime AS DeliveryTime
, “ AS ProcurementFinanceGroupId
, PrcrmntMtd AS ProcurementTypeId
, “ AS ProcurementTime
, CardCode AS StandardSupplierId
, “ AS StandardWarehouseId
, “ AS NegativeInventoryAllowed
, “ AS IndividualizationTypeId
, “ AS ResourceGroupId
, “ AS RowVersion
FROM
OITM;