我正在嘗試將分號分隔的 csv 檔案中的資料匯入 SQL Server 資料庫。這是表結構
CREATE TABLE [dbo].[waste_facility]
(
[Id] INT IDENTITY (1, 1) NOT NULL,
[postcode] VARCHAR (50) NULL,
[name] VARCHAR (50) NULL,
[type] VARCHAR (255) NULL,
[street] VARCHAR (255) NULL,
[suburb] VARCHAR (255) NULL,
[municipality] VARCHAR (255) NULL,
[telephone] VARCHAR (255) NULL,
[website] VARCHAR (255) NULL,
[longtitude] DECIMAL (18, 8) NULL,
[latitude] DECIMAL (18, 8) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
csv 檔案如下所示：
Location Coordinate;Feature Extent;Projection;Postcode;Name Of Facility;Type Of Facility;Street;Suburb;Municipality;Telephone Number;Website;Easting Coordinate;Northing Coordinate;Longitude Coordinate;Latitude Coordinate;Google Maps Direction
-37.9421182892,145.3193857967;"{""coordinates"": [145.3193857967, -37.9421182892], ""type"": ""Point""}";MGA zone 55;3156;Cleanaway Lysterfield Resource Recovery Centre;Recovery Centre;840 Wellington Road;LYSTERFIELD;Yarra Ranges;9753 5411;https://www.cleanaway.com.au/location/lysterfield/;352325;5799275;145.31938579674124;-37.94211828921733;https://www.google.com.au/maps/dir//-37.94211828921733,145.31938579674124/@your location,17z/data=!4m2!4m1!3e0
-38.0529529215,145.2433557709;"{""coordinates"": [145.2433557709, -38.0529529215], ""type"": ""Point""}";MGA zone 55;3175;Smart Recycling (South Eastern Depot);Recycling Centre;185 Dandenong-Hastings Rd;LYNDHURST;Greater Dandenong;8787 3300;https://smartrecycling.com.au/;345876;5786853;145.24335577090602;-38.05295292152536;https://www.google.com.au/maps/dir//-38.05295292152536,145.24335577090602/@your location,17z/data=!4m2!4m1!3e0
-38.0533129717,145.267610135;"{""coordinates"": [145.267610135, -38.0533129717], ""type"": ""Point""}";MGA zone 55;3976;Hampton Park Transfer Station (Outlook Environmental);Transfer Station;274 Hallam Road;HAMPTON PARK;Casey;9554 4502;https://www.suez.com.au/en-au/who-we-are/suez-in-australia-and-new-zealand/our-locations/waste-management-hampton-park-transfer-station;348005;5786853;145.2676101350274;-38.053312971691255;https://www.google.com.au/maps/dir//-38.053312971691255,145.2676101350274/@your location,17z/data=!4m2!4m1!3e0
-38.1243050577,145.2183465487;"{""coordinates"": [145.2183465487, -38.1243050577], ""type"": ""Point""}";MGA zone 55;3977;Frankston Regional Recycling and Recovery Centre;Recycling Centre;20 Harold Road;SKYE;Frankston;1300 322 322;https://www.frankston.vic.gov.au/Environment-and-Waste/Waste-and-Recycling/Frankston-Regional-Recycling-and-Recovery-Centre-FRRRC/Accepted-Items-at-FRRRC;343833;5778893;145.21834654873447;-38.12430505770815;https://www.google.com.au/maps/dir//-38.12430505770815,145.21834654873447/@your location,17z/data=!4m2!4m1!3e0
-38.0973208774,145.4920399066;"{""coordinates"": [145.4920399066, -38.0973208774], ""type"": ""Point""}";MGA zone 55;3810;Pakenham Waste Transfer Station (Future Recycling);Transfer Station;30-32 Exchange Drive;PAKENHAM;Cardinia;13Recycling;https://www.futurerecycling.com.au/;367776;5782313;145.4920399066473;-38.09732087738631;https://www.google.com.au/maps/dir//-38.09732087738631,145.4920399066473/@your location,17z/data=!4m2!4m1!3e0
有些列我不需要，所以我創建了一個格式檔案來匯入資料。格式檔案如下圖所示
<?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="CharFixed" LENGTH="50"/>
<FIELD ID="12" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="13" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="14" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="15" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="41"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="41"/>
<FIELD ID="16" xsi:type="CharFixed" LENGTH="50"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="postcode" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="name" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="type" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="street" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="suburb" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="municipality" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="telephone" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="website" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="longtitude" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="8"/>
<COLUMN SOURCE="11" NAME="latitude" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="8"/>
</ROW>
</BCPFORMAT>
然后我嘗試了批量插入和 bcp in- 它們都不起作用。
這是批量插入命令
USE [waste-facility-locations];
BULK INSERT [dbo].[waste_facility]
FROM 'E:\onboardingIteration\waste-facility-locations.csv'
WITH (FORMATFILE = 'E:\onboardingIteration\waste_facility_formatter.xml',
FIRSTROW = 2,
LASTROW = 6,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
ERRORFILE = 'E:\onboardingIteration\myRubbishData.log');
但不幸的是，生成了一些錯誤檔案。這是myRubbishData.log錯誤的內容：
第 2 行檔案偏移量 1993 錯誤檔案偏移量 0 - HRESULT 0x80004005
實際行存盤在myRubbishData.txt：
;Pakenham Waste Transfer Station (Future Recycling);Transfer Station;30-32 Exchange Drive;PAKENHAM;Cardinia;13Recycling;https://www.futurerecycling.com.au/;367776;5782313;145.4920399066473;-38.09732087738631;https://www.google.com.au/maps/dir//-38.09732087738631,145.4920399066473/@your location,17z/data=!4m2!4m1!3e0;Pakenham Waste Transfer Station (Future Recycling);Transfer Station;30-32 Exchange Drive;PAKENHAM;Cardinia;13Recycling;https://www.futurerecycling.com.au/;367776;5782313;145.4920399066473;-38.09732087738631;https://www.google.com.au/maps/dir//-38.09
如您所見，行似乎沒有正確分隔。所以我嘗試將行分隔符更改為 "\n","\r","\n\r","\r\n"，它們都不起作用。
我試過了bcp。它也沒有作業。
Here is the bcp command I used:
bcp [waste-facility-locations].[dbo].[waste_facility] in "E:\onboardingIteration\waste-facility-locations.csv" -f "E:\onboardingIteration\waste_facility_formatter.xml" -T -S "(LocalDB)\MSSQLLocalDB" -F 2 -t ";" -r "\n"
Then I get an error said somehow the same thing
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
One interesting things is, if I create a new excel and choose "Get data" option to import the csv file, the file can be literally correctly parsed.
Basically I can't find what I did wrong here. Can someone help me on this one?
uj5u.com熱心網友回復：
SQL Server 匯入工具非常不容忍不良資料，甚至只是格式化變體或選項。在我的職業生涯中，我確實花費了數千個作業時間來為客戶開發和除錯匯入程式。我現在可以告訴你，嘗試單獨使用 SQL 來解決這個問題既困難又耗時。
當您遇到此問題（錯誤資料和/或格式不一致）時，幾乎總是更容易找到或開發更靈活的工具來將資料預處理為 SQL 期望的嚴格標準。所以我想說，如果 Excel 可以決議它，那么只需使用 Excel 自動化對其進行預處理，然后使用 SQL 匯入 Excel 輸出。如果這對您不實用，那么我建議您使用某種客戶端語言（C#、Vb、Java、Python 等）撰寫您自己的工具來預處理檔案。
你可以用 SQL 來做（我已經做過很多次了），但我向你保證，這是一個漫長而復雜的跋涉。
SSIS 對此類問題有更靈活的錯誤處理，但如果您還不熟悉和使用它，它的學習曲線非常陡峭，而且您的第一個 SSIS 專案也可能非常耗時。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/443357.html
標籤：sql sql-server csv tsql bulkinsert
上一篇：百分比細分