sql server - चर हेडर वाले एक्सेल फ़ाइलों को आयात करना




sql-server excel (3)

मेरे पास SSIS पैकेज है, जो एक्सेल फाइल को डेटाबेस में लोड करेगा। एक्सेल कॉलम नाम को डेटाबेस टेबल कॉलम नाम और इसके ठीक काम करने के लिए मैंने एक्सेल सोर्स टास्क बनाया है।

दुर्लभ स्थिति में, हम एक्सेल फाइल कॉलम नाम को कुछ स्थान के साथ प्राप्त कर रहे हैं (उदाहरण के लिए: कॉलम नाम "एबीसी" है लेकिन हम "एबीसी" प्राप्त कर रहे हैं) और जिसके कारण मैपिंग मुद्दा और एसएसआईएस विफल हो गया।

क्या एक्सेल को खोले बिना कॉलम नाम को ट्रिम करना संभव है।

नोट: पृष्ठ का नाम गतिशील होगा और कॉलम की स्थिति बदल सकती है (जैसे: कॉलम "ABC पहली पंक्ति या दूसरी पंक्ति में मौजूद हो सकता है या ..")।


फ़ाइल मैन्युअल रूप से या स्वचालित रूप से बनाई जा रही है? या तो मामले में आप पूरी तरह से एक्सेल फ़ाइल से हेडर पंक्ति (या तो प्रोग्रामेटिक रूप से या फ़ाइल को सहेजने से पहले हटाने के लिए लोगों को बता सकते हैं)। एक बार जब आप ऐसा कर लेते हैं, तो एक्सेल कनेक्शन मैनेजर में जाएं और उस बॉक्स को खोजें जो इंगित करता है कि 'पहली पंक्ति में कॉलम नाम हैं'। यदि आप उस बॉक्स को साफ कर सकते हैं तो कॉलम को फिर से उस गंतव्य पर मैप करें जो आपकी समस्या का समाधान करे। आपको स्तंभ नाम में गलत वर्तनी (या अतिरिक्त रिक्त स्थान) के बारे में चिंता करने की आवश्यकता नहीं होगी।

मुझे लगता है कि SSIS में पहली पंक्ति को पूरी तरह से छोड़ देने का विकल्प भी है, लेकिन मुझे याद नहीं है कि वह विकल्प कहां है। यदि आप पा सकते हैं तो एक्सेल फाइल की पहली पंक्ति को छोड़ दें। एक ही मैपिंग अभी भी बनी हुई है।

धन्यवाद


मैं मंच के लिए काफी नया हूं, इसलिए यदि आपको लगता है कि यह मूर्खतापूर्ण है, तो इसे नमक के दाने के साथ लें।

MS Access में Excel के समान VBA कार्यक्षमता है या आप अपने 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))

और बदलें हमेशा काम में आती हैं अगर कोई स्थिति होती है जैसा कि मैं अक्सर करता हूं जहां कभी-कभी उपयोगकर्ता एक # चार का उपयोग कर सकता है और कभी-कभी नहीं।

उदाहरण: "पैटरसन # 288" या "पैटट्रसन 288" myString = UCase(Trim(Replace(myString,"#","") # चिह्न को समाप्त करता है और अग्रणी और अनुगामी रिक्त स्थान से छुटकारा पाता है और मामले में अक्षरों को ऊपर भी लिखता है। उपयोगकर्ता ने एक गलती भी की

यह चलाने के लिए बहुत आसान है आयात और निर्यात लूप है।

अब यदि फ़ाइल का नाम बदल रहा है (यह वर्कबुक नाम है) या यदि वर्कशीट के नाम बदल रहे हैं तो आप अपना "मिडलवेयर" भी रख सकते हैं। हमेशा वर्कबुक को उसी नाम पर रखें (वर्कबुक की सामग्री जिसके साथ आप आयात करने जा रहे हैं ) शीट्स के साथ ही, या आप शीट्स की # गिनती कर सकते हैं और नाम रिकॉर्ड कर सकते हैं (फिर से अपने "मिडिल वेयर" में उन्हें मानकीकृत और नाम बदलने का मौका)

मुझे लगता है कि यह एक एसक्यूएल जवाब नहीं है, लेकिन क्योंकि मैं एसक्यूएल के साथ अच्छा नहीं हूं कि मैं डेटा को प्रस्तुत करूंगा, इस मामले में पहले एक एक्सेल वर्कबुक और आयात के लिए इसे मानकीकृत करें ताकि कोड डीबी साइड (सर्वर साइड) पर टूट न जाए )।

मैं SQL क्वेरी स्क्रिप्ट के साथ एक्सेस करने के लिए फ्रंट एंड के रूप में एक्सेल का उपयोग करता हूं और इसे सीधे SQL से जोड़ा जा सकता है लेकिन यह बहुत अधिक कठिन है। PostGre SQL जैसे ACSV अनुकूल DB उस संबंध में मदद करता है।

आशा है कि ये आपकी मदद करेगा। यदि आपको कॉपी बनाने और अपने सभी परिवर्तनों (नामकरण, फ़ील्ड नाम सम्मेलन // कॉलम हेडर) को लागू करने से पहले आयात करने से पहले कार्यपुस्तिका को प्रारूपित करने में सहायता की आवश्यकता है तो मुझे बताएं। मैं शायद उसके साथ मदद कर सकता था।

यह वर्कबुक पर प्री-प्रोसेसिंग स्क्रिप्ट चलाने के लिए वी की टिप्पणी के समान है। इस तरह मैं इसे अप्रोच करूंगा।

चीयर्स, डब्ल्यूडब्ल्यूसी


सबसे पहले, मेरा समाधान @DrHouseofSQL और @Bhouse उत्तरों पर आधारित है, इसलिए आपको पहले @DrouseofSQL उत्तर को पढ़ना होगा, उसके बाद @ उत्तर लिखें और फिर इस उत्तर के साथ जारी रखें

संकट

नोट: पृष्ठ का नाम गतिशील होगा और कॉलम की स्थिति बदल सकती है (उदाहरण के लिए: कॉलम "ABC पहली पंक्ति या दूसरी पंक्ति में मौजूद हो सकती है या ...

यह स्थिति थोड़ी जटिल है और इसे निम्नलिखित वर्कअराउंड का उपयोग करके हल किया जा सकता है:

समाधान अवलोकन

  1. डेटा आयात करने वाले डेटा प्रवाह कार्य से पहले एक स्क्रिप्ट कार्य जोड़ें
  2. एक्सेल फ़ाइल खोलने और वर्कशीट नाम और हेडर पंक्ति प्राप्त करने के लिए आपको स्क्रिप्ट कार्य का उपयोग करना होगा
  3. क्वेरी बनाएँ और इसे एक चर में संग्रहीत करें
  4. दूसरे डेटा फ्लो कार्य में आपको स्रोत के रूप में ऊपर संग्रहीत क्वेरी का उपयोग करना होगा ( ध्यान दें कि आपको सही करने के लिए Delay Validation संपत्ति सेट करनी है )

समाधान का विवरण

  1. सबसे पहले टाइप स्ट्रिंग का एक SSIS वैरिएबल बनाएं (यानी @ [उपयोगकर्ता :: strQuery])
  2. Excel फ़ाइल पथ (अर्थात @ [उपयोगकर्ता :: ExcelFilePath]) में एक और चर जोड़ें
  3. एक स्क्रिप्ट कार्य जोड़ें, और ReadWrite चर के रूप में @[User::strQuery] चयन करें, और @[User::ExcelFilePath] ReadOnly चर के रूप में (स्क्रिप्ट कार्य विंडो में)
  4. स्क्रिप्ट भाषा को VB.Net पर सेट करें और स्क्रिप्ट संपादक विंडो में निम्न स्क्रिप्ट लिखें:

नोट: आपको System.Data.OleDb आयात करना होगा

नीचे दिए गए कोड में, हम शीर्ष लेख को खोजने के लिए पहले 15 पंक्तियों को खोजते हैं, यदि आप 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
  1. फिर आपको एक एक्सेल कनेक्शन प्रबंधक जोड़ना होगा, और एक्सेल फाइल को चुनना होगा जिसे आप आयात करना चाहते हैं (केवल पहली बार मेटाडेटा को परिभाषित करने के लिए एक नमूना चुनें)
  2. Select * from [Sheet1$A2:I] से चर का Select * from [Sheet1$A2:I] का डिफ़ॉल्ट मान असाइन Select * from [Sheet1$A2:I] @[User::strQuery]
  3. डेटा फ़्लो टास्क में एक एक्सेल सोर्स जोड़ें, चर से SQL कमांड चुनें, और @[User::strQuery]
  4. कॉलम टैब पर जाएं और कॉलम को उसी तरह नाम दें, जैसा @Bouse ने सुझाया था

@Bouse जवाब से ली गई छवि

  1. DataFlow टास्क Delay Validation प्रॉपर्टी को True सेट करें
  2. DataFlow Task में अन्य घटक जोड़ें

अद्यतन 1:

ओपी टिप्पणियों से: sometimes excel with empty data will come.(ie) we have only header row not not data... in that case it fails entire task

उपाय:

यदि आपकी एक्सेल फ़ाइल में कोई डेटा नहीं है (केवल हेडर) तो आपको ये चरण करने होंगे:

  1. प्रकार बूलियन का एक SSIS वैरिएबल जोड़ें * (अर्थात @[User::ImportFile] )
  2. स्क्रिप्ट कार्य ReadWrite चर में @[User::ImportFile] जोड़ें
  3. स्क्रिप्ट टास्क में जाँच करें कि क्या फाइल में पंक्तियाँ हैं
  4. यदि हां सेट करें @[User::ImportFile] = सत्य, अन्य @[User::ImportFile] = गलत
  5. एरो (पूर्ववर्ती बाधा) पर डबल क्लिक करें जो स्क्रिप्ट कार्य को डेटाफ़्लो से जोड़ता है
  6. इसके प्रकार को बाधा और अभिव्यक्ति पर सेट करें
  7. निम्नलिखित अभिव्यक्ति लिखें

    @[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

उपाय:

  1. बस एक और डेटा फ्लो कार्य जोड़ें
  2. इस डेटाफ़्लो को स्क्रिप्ट कनेक्टर के साथ किसी अन्य कनेक्टर का उपयोग करके और अभिव्यक्ति के साथ कनेक्ट करें @[User::ImportFile] == False (पहले कनेक्टर के समान चरण)
  3. DataFlow टास्क में एक स्रोत के रूप में एक SCrip घटक जोड़ें
  4. उन आउटपुट कॉलम को बनाएं जिन्हें आप लॉग में आयात करना चाहते हैं
  5. एक पंक्ति बनाएँ जिसमें वह जानकारी हो जिसे आपको आयात करने की आवश्यकता है
  6. लॉग डेस्टिनेशन जोड़ें

या किसी अन्य Data Flow Task को जोड़ने के बजाय, आप लॉग टेबल में एक पंक्ति सम्मिलित करने के लिए एक Execute SQL Task जोड़ सकते हैं





sql-server-data-tools