Импорт файла .csv с использованием sql bulkload или openrowset для отсутствующих данных столбца

.У меня есть файл csv. Данные приведены ниже,

___________________________________________________________
StateName       |  City                 |  Score1 |  Score2 |
________________|_______________________|_________|_________|
                |                       |         |         |
New South Wales |  Albury (C)           |  979    |   967   |  
                |  Armidale Dumaresq (A)|  987    |   985   |  
                |  Ashfield (A)         |  1015   |   1031  |
________________|_______________________|_________|_________| 
Victoria        |  Alpine (S)           |   987   |   970   |
                |  Ararat (RC)          |   951   |   938   |
________________|_______________________|_________|_________| 

Теперь я хочу загрузить его в SQL через BCP or OPENROWSET. Но загвоздка в записях, в которых отсутствует имя состояния, я хочу скопировать начальное объявленное имя состояния.

Например, в row# 2 выше. Я хочу, чтобы StateName был "New South Wales" для города "Armidale Dumaresq (A)". Как это сделать с помощью sql bulk load? Я действительно не хочу создавать новое приложение только для этой мелочи. Любое решение будет оценено по достоинству.

Выложите, пожалуйста, фрагмент вашего CSV файла.

Paweł Dyl 10.09.2018 08:53

Это не то, что вы сделали бы с OpenRowset как таковым, вы должны загрузить данные с отсутствующими данными с помощью OpenRowset в таблицу загрузки, а затем скопировать самое последнее имя состояния в любые строки, где оно отсутствует.

TomC 10.09.2018 09:18

Штат, Город, Оценка 1, Оценка 2 Новый Южный Уэльс, Олбери (C), 979 967, Армидейл Думареск (A), 987 985, Эшфилд (A), 1015 1031 Виктория, Альпайн (S), 987 970, Арарат (RC), 951 938

Ashish Bagade 10.09.2018 10:28
1
3
749
1

Ответы 1

Предполагая, что ваш файл csv сохранен как E:\Temp\Test.csv:

StateName,City,Score1,Score2
New South Wales,Albury (C),979,967
,Armidale Dumaresq (A),987,985
,Ashfield (A),1015,1031
Victoria,Alpine (S),987,970
,Ararat (RC),951,938

Вы можете записать Файл формата BCP в E:\Temp\Test.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="200"/> 
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/> 
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="8"/> 
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8"/> 
 </RECORD> 
 <ROW> 
  <COLUMN SOURCE="1" NAME="StateName" xsi:type="SQLNVARCHAR"/> 
  <COLUMN SOURCE="2" NAME="City" xsi:type="SQLNVARCHAR"/> 
  <COLUMN SOURCE="3" NAME="Score1" xsi:type="SQLINT"/> 
  <COLUMN SOURCE="4" NAME="Score2" xsi:type="SQLINT"/> 
 </ROW> 
</BCPFORMAT>

И используйте запрос:

WITH Src AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) N, * 
    FROM OPENROWSET(BULK N'E:\Temp\Test.csv', FORMATFILE = 'E:\Temp\Test.xml', FIRSTROW = 2) As Blk
), NameGroup AS
(
    SELECT *, COUNT(StateName) OVER(ORDER BY N ROWS UNBOUNDED PRECEDING) GroupName FROM Src
)
SELECT FIRST_VALUE(StateName) OVER (PARTITION BY GroupName ORDER BY N ROWS UNBOUNDED PRECEDING) StateName, City, Score1, Score2
FROM NameGroup

Результат

StateName         City                    Score1   Score2
----------------- ----------------------- -------- ------
New South Wales   Albury (C)              979      967
New South Wales   Armidale Dumaresq (A)   987      985
New South Wales   Ashfield (A)            1015     1031
Victoria          Alpine (S)              987      970
Victoria          Ararat (RC)             951      938

Вау, это был просто потрясающий Павел. Спасибо большое за вашу помощь. Действительно классный скрипт :)

Ashish Bagade 10.09.2018 14:45

Другие вопросы по теме