Ура, WWC

я есть пакет служб SSIS, который загрузит файл Excel в базу данных. Я создал задачу Excel Source, чтобы сопоставить имя столбца Excel с именем столбца таблицы базы данных и его работоспособность.

В редких случаях мы получаем имя столбца файла Excel с пробелом(например: имя столбца «ABC», но мы получаем «ABC») и которые вызывают проблему с отображением и SSIS не удалось.

Есть ли возможность обрезать имя столбца, не открывая Excel.

Примечание. Имя страницы будет динамическим, и положение столбца может измениться (например, столбец «ABC может существовать в первом ряду или во втором ряду или ...»).

 Emilio Lucas Ceroleni28 нояб. 2017 г., 19:03
Когда ты сказалПримечание. Имя страницы будет динамическим, и положение столбца может измениться (например, столбец «ABC может существовать в первом ряду или во втором ряду или ...») ты имеешь в видуПримечание. Имя страницы будет динамическим, и положение столбца может измениться (например, столбец "ABC может существовать первымколонка или второйколонка или же ..")?
 Yahfoufi29 нояб. 2017 г., 16:40
@VigneshKumar вы можете объединить, если вы считаете,BHouse а такжеDrHouseofSQL в качестве первого шага, то вы идете сХади решение. Тогда у вас есть 100% рабочий раствор. Я думаю, что вы не получите лучшее решение, потому что вы просите сложную ситуацию ситуации

Ответы на вопрос(4)

выполняя шаги, аналогичные упомянутым @houseofsql

Шаг 1:

Исключить имена столбцов в первой строке в соединении Excel, используйте команду sql в качестве режима доступа к данным

Шаг 2: Имена столбцов псевдонимов в выходном столбце совпадают с адресом назначения,

Выбрать из[Sheet1$A2:I] выберут из второго ряда

Наконец добавьте пункт назначения как пункт назначения OLEDB

 Vignesh Kumar A23 нояб. 2017 г., 16:28
Да. И имя файла, и имя листа Excel будут динамическими
 Vignesh Kumar A23 нояб. 2017 г., 16:12
Привет, спасибо за идею, но мы используем динамическое имя страницы.
 Ven23 нояб. 2017 г., 16:20
Ты имеешь в виду, что имя меняется в Excel?
 Ven23 нояб. 2017 г., 17:58
Вы пробовали, следуя тем же шагам с переменной в качестве исходного пути
 Vignesh Kumar A24 нояб. 2017 г., 06:47
Нет ... есть ли способ открыть Excel и обновить столбец с помощью задачи скрипта?

поэтому, если вы думаете, что это глупо, возьмите его с небольшим количеством соли.

MS Access имеет почти те же функциональные возможности VBA, что и Excel, или вы можете написать новую рабочую книгу Excel, которая разбирает и форматирует перед импортом SQL, а затем импортирует ее (промежуточное ПО, если хотите).

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

myString = trim(msytring) «Это удалит все начальные и конечные пробелы, но не помешает пробелам между символами. Таким образом, при импорте вы можете запускать обрезку заголовков столбцов по мере их импорта.

Есть также LTrim и RTrim ', вы можете догадаться, что они делают слева и справа от строки

https://support.office.com/en-us/article/LTrim-RTrim-and-Trim-Functions-e340ced1-67df-435f-b078-1527a4eddea2

Для прописных букв вы можете использовать UCase

myString = UCase(Trim(myString))

И Replace всегда пригодится, если есть ситуация, когда я часто сталкиваюсь с тем, где иногда пользователь может использовать символ #, а иногда нет.

Пример: «Паттерсон № 288» или «Паттерсон 288»myString = UCase(Trim(Replace(myString,"#","") 'убирает знак # и удаляет начальные и конечные пробелы, а также заглавные буквы в случае, если пользователь также допустил ошибку

Это очень удобно для импорта и экспорта циклов.

Теперь, если имя файла меняется (это имя рабочей книги) или если имена рабочих листов меняются, вы также можете иметь свое «промежуточное программное обеспечение», которое всегда будет называть рабочую книгу тем же именем (с содержимым рабочей книги, которую вы собираетесь импортировать). ) то же самое с листами, или вы можете сосчитать количество листов и записать имена (снова шанс стандартизировать и переименовать их в «промежуточном программном обеспечении»)

Я полагаю, что это не ответ SQL, но поскольку я не очень хорош в SQL, я подготовлю данные, в данном случае сначала книгу Excel, и стандартизирую их для импорта, чтобы код не ломался на стороне БД (на стороне сервера). ).

Я использую Excel в качестве внешнего интерфейса для доступа с помощью сценариев запросов SQL, и он может быть связан непосредственно с SQL, но это гораздо сложнее. В этом отношении помогает дружественная к CSV БД, такая как PostGre SQL.

Надеюсь, это поможет. Если вам нужна помощь в форматировании рабочей книги перед импортом, сделайте копию и примените все ваши изменения (наименование, соглашение о названии поля // заголовок столбца), дайте мне знать. Я мог бы помочь с этим.

Это похоже на комментарий V о запуске сценария предварительной обработки в книге. Вот как я бы подошел к этому.

Ура, WWC

Решение Вопроса

мое решение основано на ответах @DrHouseofSQL и @Bhouse, поэтому сначала нужно прочитать ответ @DrHouseofSQL, а затем ответ @BHouse, а затем продолжить с этим ответом.

проблема

Примечание. Имя страницы будет динамическим, и положение столбца может измениться (например, «Столбец» ABC может существовать в первом ряду или во втором ряду или ...

Эта ситуация немного сложна и может быть решена с помощью следующего обходного пути:

Обзор решенияДобавьте задачу скрипта перед задачей потока данных, которая импортирует данныеВы должны использовать задачу скрипта, чтобы открыть файл Excel и получить имя листа и строку заголовка.Создайте запрос и сохраните его в переменнойво второй задаче потока данных вы должны использовать запрос, сохраненный выше, в качестве источника (Обратите внимание, что вы должны установитьDelay Validation свойство к истине)Детали решенияСначала создайте переменную SSIS типа string(т.е. @ [User :: strQuery])Добавьте другую переменную, которая содержит путь к файлу Excel(т.е. @ [User :: ExcelFilePath])Добавить задачу сценария и выберите@[User::strQuery] как переменная ReadWrite и@[User::ExcelFilePath] как переменная ReadOnly(в окне задач скрипта)Установите язык сценариев на VB.Net и в окне редактора сценариев напишите следующий сценарий:

Примечание: вы должны импортироватьSystem.Data.OleDb

В приведенном ниже коде мы ищем в первых 15 строках Excel, чтобы найти заголовок, вы можете увеличить число, если заголовок будет найден после 15 строк. Также я предположил, что диапазон столбцов отA вI

    m_strExcelPath = Dts.Variables.Item("ExcelFilePath").Value.ToString

    Dim strSheetname As String = String.Empty
    Dim intFirstRow As Integer = 0

    m_strExcelConnectionString = Me.BuildConnectionString()
    Try


        Using OleDBCon As New OleDbConnection(m_strExcelConnectionString)

            If OleDBCon.State <> ConnectionState.Open Then
                OleDBCon.Open()
            End If

            'Get all WorkSheets
            m_dtschemaTable = OleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                               New Object() {Nothing, Nothing, Nothing, "TABLE"})

            'Loop over work sheet to get the first one (the excel may contains temporary sheets or deleted ones

            For Each schRow As DataRow In m_dtschemaTable.Rows
                strSheetname = schRow("TABLE_NAME").ToString

                If Not strSheetname.EndsWith("_") AndAlso strSheetname.EndsWith("$") Then

                    Using cmd As New OleDbCommand("SELECT * FROM [" & strSheetname & "A1:I15]", OleDBCon)

                        Dim dtTable As New DataTable("Table1")


                        cmd.CommandType = CommandType.Text

                        Using daGetDataFromSheet As New OleDbDataAdapter(cmd)

                            daGetDataFromSheet.Fill(dtTable)

                            For intCount As Integer = 0 To 15

                                If Not String.IsNullOrEmpty(dtTable.Rows(intCount)(0).ToString) Then

                                    '+1 because datatable is zero based indexed, +1 because we want to start from the second row
                                    intFirstRow = intCount + 2

                                End If


                            Next



                        End Using

                        If intFirstRow = 0 Then Throw New Exception("header not found")

                    End Using

                    'when the first correct sheet is found there is no need to check others
                    Exit For

                End If
            Next

            OleDBCon.Close()

        End Using

    Catch ex As Exception
        Throw New Exception(ex.Message, ex)
    End Try


    Dts.Variables.Item("strQuery").Value = "SELECT * FROM [" & strSheetname & "A" & intFirstRow.ToString & ":I]"

    Dts.TaskResult = ScriptResults.Success
End Sub
Затем вам нужно добавить менеджер соединений Excel и выбрать файл Excel, который вы хотите импортировать(просто выберите образец, чтобы определить метаданные только в первый раз)Назначьте значение по умолчаниюSelect * from [Sheet1$A2:I] к переменной@[User::strQuery]В Задаче потока данных добавьте источник Excel, выберите команду SQL из переменной и выберите@[User::strQuery]Перейдите на вкладку столбцов и назовите столбцы так же, как предложила @BHouse

Изображение взято с @BHouse ответа

Установите задачу DataFlowDelay Validation собственность наTrueДобавить другие компоненты в задачу DataFlowОБНОВЛЕНИЕ 1:

Из комментариев ОП:sometimes excel with empty data will come.(i.e) we have only header row not not data... in that case it fails entire task

Решение:

Если ваш файл Excel не содержит данных (только заголовок), вам необходимо выполнить следующие действия:

Добавьте переменную SSIS типа boolean * (т.е.@[User::ImportFile])Добавлять@[User::ImportFile] к скрипту задачи ReadWrite переменныеВ Задаче сценария проверьте, содержит ли файл строкиЕсли да Установить@[User::ImportFile] = Правда, еще@[User::ImportFile] = ЛожьДважды щелкните стрелку (ограничение приоритета), которая соединяет задачу скрипта с DataFlowУстановите его тип в Ограничение и Выражение

Напишите следующее выражение

@[User::ImportFile] == True

Примечание. Новый код задачи скрипта:

    m_strExcelPath = Dts.Variables.Item("ExcelFilePath").Value.ToString

    Dim strSheetname As String = String.Empty
    Dim intFirstRow As Integer = 0

    m_strExcelConnectionString = Me.BuildConnectionString()
    Try


        Using OleDBCon As New OleDbConnection(m_strExcelConnectionString)

            If OleDBCon.State <> ConnectionState.Open Then
                OleDBCon.Open()
            End If

            'Get all WorkSheets
            m_dtschemaTable = OleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                               New Object() {Nothing, Nothing, Nothing, "TABLE"})

            'Loop over work sheet to get the first one (the excel may contains temporary sheets or deleted ones

            For Each schRow As DataRow In m_dtschemaTable.Rows
                strSheetname = schRow("TABLE_NAME").ToString

                If Not strSheetname.EndsWith("_") AndAlso strSheetname.EndsWith("$") Then

                    Using cmd As New OleDbCommand("SELECT * FROM [" & strSheetname & "A1:I15]", OleDBCon)

                        Dim dtTable As New DataTable("Table1")


                        cmd.CommandType = CommandType.Text

                        Using daGetDataFromSheet As New OleDbDataAdapter(cmd)

                            daGetDataFromSheet.Fill(dtTable)

                            For intCount As Integer = 0 To 15

                                If Not String.IsNullOrEmpty(dtTable.Rows(intCount)(0).ToString) Then

                                    '+1 because datatable is zero based indexed, +1 because we want to start from the second row
                                    intFirstRow = intCount + 2

                                End If


                            Next



                        End Using





                    End Using

                    'when the first correct sheet is found there is no need to check others
                    Exit For

                End If
            Next

            OleDBCon.Close()

        End Using

    Catch ex As Exception
        Throw New Exception(ex.Message, ex)
    End Try

                If intFirstRow = 0 OrElse _
                   intFirstRow > dtTable.Rows.Count Then

                    Dts.Variables.Item("ImportFile").Value = False

                Else

                    Dts.Variables.Item("ImportFile").Value = True

                End If                    

    Dts.Variables.Item("strQuery").Value = "SELECT * FROM [" & strSheetname & "A" & intFirstRow.ToString & ":I]"

    Dts.TaskResult = ScriptResults.Success
End Sub
ОБНОВЛЕНИЕ 2:

Из комментариев ОП:is there any other work around available to process the data flow task without skipping all data flow task,Actually one of the task will log the filename and data count and all, which are missing here

Решение:

Просто добавьте еще одно задание DATA FLOWСоедините этот поток данных с задачей скрипта, используя другой соединитель и с выражением@[User::ImportFile] == False (те же шаги первого разъема)В Задаче DataFlow добавьте Компонент SCript как ИсточникСоздайте столбцы вывода, которые вы хотите импортировать в журналыСоздайте строку, содержащую информацию, необходимую для импортаДобавить журнал назначения

Или вместо добавления другогоData Flow TaskВы можете добавитьExecute SQL Task вставить строку в таблицу журнала

 Vignesh Kumar A27 нояб. 2017 г., 13:51
На самом деле одна из задач будет записывать имя файла и количество данных и все, что здесь ошибочно.
 Vignesh Kumar A27 нояб. 2017 г., 04:11
Спасибо за подробную информацию ... все работает нормально ... кроме одного условия ... иногда получаются отличные данные с пустыми данными. (То есть) у нас есть только строка заголовка, а не данные ... в этом случае она не справится со всей задачей.
 Hadi27 нояб. 2017 г., 14:43
@VigneshKumar просто добавьте еще одну задачу потока данных с компонентом сценария в качестве источника и создайте строку, содержащую значения журнала. Соедините этот поток данных с задачей скрипта, используя другой соединитель и с выражением@[User::ImportFile] == False
 Vignesh Kumar A27 нояб. 2017 г., 10:43
Да, это работает @hadi, но есть ли другой способ обойти задачу обработки потока данных, не пропуская все задачи потока данных.
 Hadi27 нояб. 2017 г., 04:59
@VigneshKumar, вы правы, есть простой обходной путь, добавьте выражение к соединителю между задачей сценария и задачей потока данных. Я обновил свой ответ, посмотрите

ностью удалить строку заголовка (либо программно, либо попросить людей удалить ее перед сохранением файла) из файла Excel. Как только вы это сделаете, зайдите в Диспетчер соединений Excel и найдите поле с надписью «Первая строка имеет имена столбцов». Если вы можете очистить это поле, сопоставьте столбцы с местом назначения, которое должно решить вашу проблему. Вам никогда не придется беспокоиться об ошибках (или лишних пробелах) в именах столбцов.

Я думаю, что в SSIS также есть возможность вообще пропустить первый ряд, но я не могу вспомнить, где находится этот параметр. Если вы можете найти это, то просто пропустите первую строку файла Excel. Те же сопоставления все еще остаются.

Спасибо

 Vignesh Kumar A22 нояб. 2017 г., 16:02
Файл будет создан автоматически.

Ваш ответ на вопрос