Разделение данных SQL в 3 колонки, используя разделитель

Я использую Excel VBA и подключаюсь к PGSQL с помощью OleDB. К сожалению, oledb не позволяет ссылаться на столбцы вне агрегатной функции, где к таблице применена group by, поэтому мне приходится объединять данные, но у меня возникли проблемы с разделением данных (latestBMI) обратно на 3 отдельных столбца (eventdate|weight|bmi), потому что значения веса и bmi различаются по длине, но разделяются разделителем канала.

Вот как выглядит таблица через извлечение: 

master_id :   latestBMI
251       :   2008-05-08|84|26.8
2848      :   1992-11-23|86.71|27.3
2026      :   2002-04-16|105|31.6
22316     :   2014-02-28|107.955|35.1
16633     :   2005-07-04|70|25
9545      :   1997-04-08|82.73|24.9
Я играл с различными методами, пытаясь SUBSTRING, CHARINDEX, LEN, LEFT / RIGHT, но потерпел неудачу. Вот мой текущий код, но элементы подстроки для веса и ИМТ - это моя проблема: 
Const sqlconnection = "Provider=oledb;"

Dim conn As New Connection
conn.ConnectionString = sqlconnection
conn.Open
Dim rs As Recordset

Sheets("Sheet1").Select
Range("A1").Select

Dim DATA As String
DATA = "SELECT latest.master_id, " _
& "SUBSTRING(latestBMI,1,10) eventdate, " _
& "SUBSTRING(latestBMI,12,CHARINDEX('|',latestBMI,RIGHT(latestBMI,7)) weight, " _
& "SUBSTRING(latestBMI,20,4) BMI " _
& "FROM ( " _
& "SELECT master_id, " _
& "MAX(CAST(eventdate AS VARCHAR(10)) + '|' + RIGHT(weightkg,7)+ '|' + RIGHT(bmi,4)) AS latestBMI " _
& "FROM weight " _
& "GROUP BY master_id) as latest " _
& "LEFT JOIN person p on latest.master_id = p.entity_id "

Set rs = conn.Execute(DATA)
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
    .Refresh
End With

Как разделить их на 3 отдельных столбца с помощью разделителя труб?

4 2
excel vba excel-vba mysql sql-server

4 ответа:

Если вы в порядке с выборкой данных сначала, а затем разбить его один раз в таблице, попробуйте это (если вы знаете, что ваши latestBMI данные будут в диапазоне B2:B7)

Выбор.Texttocolumns Destination:=Range ("B2:B7"), _
   Тип данных:=xlDelimited, _
   TextQualifier:=xlDoubleQuote, _
   ConsecutiveDelimiter: = False, _
   Tab:=False, _
   Точка С Запятой: = False, Запятая:=False, Пробел:=False, Другое: = True, _
   OtherChar:="|", _
   FieldInfo:=Array (Array(1, 1), Array (2, 1), Array(3, 1)), _
   TrailingMinusNumbers: = True

0

Я предполагаю, что ваша таблица имеет 4 столбца

Master_id, BMI1, BMI2, BMI3 и BMI4

Вы можете генерировать инструкции INSERT, используя запрос ниже, а затем запускать их в своей базе данных

select 'insert into newTable (master_id, BMI1, BMI2, BMI3) values( ' +
   cast(master_id as varchar) + ',''' + replace(latestBMI, '|', ''',''') + ''')'
from #t

Он генерирует следующие операторы (используя ваши примерные данные) 

insert into newTable (master_id, BMI1, BMI2, BMI3) values( 251,'2008-05-08','84','26.8')
insert into newTable (master_id, BMI1, BMI2, BMI3) values( 2848,'1992-11-23','86.71','27.3')
insert into newTable (master_id, BMI1, BMI2, BMI3) values( 2026,'2002-04-16','105','31.6')
insert into newTable (master_id, BMI1, BMI2, BMI3) values( 22316,'2014-02-28','107.955','35.1')
insert into newTable (master_id, BMI1, BMI2, BMI3) values( 16633,'2005-07-04','70','25')
insert into newTable (master_id, BMI1, BMI2, BMI3) values( 9545,'1997-04-08','82.73','24.9')

Если столбцы не являются целыми, вы можете изменить инструкцию SELECT, чтобы получить то, что вам нужно. Но все равно сгенерированная вставка будет работать, даже если ваши столбцы числовые.

1

Существует множество сложных SQL/database-based решений (см. Split Strings the Right Way), это будет гораздо проще иметь дело с вашими данными в VBA после завершения инструкции SQL.

Ваш код выглядит так, что он выдаст результат, который выглядит следующим образом:

Введите описание изображения здесь

Таким образом, вы должны перебрать результаты в столбце B и отделить их.

Option Explicit

Sub test()
    SeparateFields ActiveSheet
End Sub

Sub SeparateFields(ws As Worksheet)
    '--- assumes your concatenated field (after your SQL
    '    statement) ends up in column B. The separated
    '    fields go in columns B, C, D.
    Dim concatFields As String
    Dim fields() As String
    Dim numRows As Long
    Dim i As Long
    numRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To numRows
        concatFields = ws.Cells(i, 2).value
        fields = Split(concatFields, "|", , vbTextCompare)
        ws.Cells(i, 2).value = fields(0)
        ws.Cells(i, 3).value = fields(1)
        ws.Cells(i, 4).value = fields(2)
    Next i
End Sub
0

После кода для импорта данных просто используйте текст в Столбцах, чтобы разделить его.

    Sub SepDelCol()
       Columns("B:B").Select
       Selection.TextToColumns _
       Destination:=Range("B:B"), _
       DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
       Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
       Other:=True, OtherChar:="|"
     End Sub
0