locked
SSIS-Excel to Sql table column value greater than 255 not able to change it. RRS feed

  • Question

  • i have a dataflow task which is in a for loop container to loop through the excel sheets of a particular folder based on Dealer ids.problem is one of the column value comments hav a datatype of varchar with length 2000 .

    i am not able to change the column value to dt_NText as its says error output colums are associated with output columns.the present datatype for column comments is Unicode string [DT_WSTR]

    The data flow task contains:

    excel source

    data conversion and

    oledb destination

    excel souce:

    i am using a excel connection manager .im browing for the path. and my excel version is 2007.

    properties of connection manger:

    connection string:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\FTP_Files\BayCityChevrolet\Bay City export_2012-04-10_12-58.xlsx;Extended Properties="EXCEL 12.0;HDR=YES"; ( i hav given iMEX=1) but dint make any difference

    i changed the type guess rows_>Decimal to 0...dint work

                                                    changed it to 16 --dint work..

    can anyone please tell me what i shoud be doing to increase the column value.i have looked at all the forums and tried the solutions..nothing is working..

    please let me know what could be the possible solution for this

    cuurent errors:

    [Excel Source 1 [1441]] Error: There was an error with output column "comments" (1554) on output "Excel Source Output" (1449). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

    [Excel Source 1 [1441]] Error: The "output column "comments" (1554)" failed because truncation occurred, and the truncation row disposition on "output column "comments" (1554)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Excel Source 1" (1441) returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    PS:i am able to send data to sql tabledata from a differnt sheets where column length for comments is less than 255.


    Friday, April 13, 2012 4:41 PM

Answers

  • Are those rows are in first 8 rows? As SSISJoost said , Excel reads first eight lines to define the datatype. 

    Check this link

    http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php

    To change in Registry check this

    http://www.sqlservercentral.com/Forums/Topic860660-147-1.aspx

    Thanks

    Aamir


    http://sqlage.blogspot.com/

    • Marked as answer by sql learner12 Friday, June 29, 2012 7:10 PM
    Friday, April 13, 2012 5:54 PM
  • Public Sub Main()
            '"User::FilePath" = c:\testfiles\NSBY.xlsx
            '"User::FolderPath" = c:\testfiles\
            'User::WorkSheetName = Sheet1
    
            Dim sourcefile As String = Dts.Variables.Item("User::FilePath").Value.ToString
            Dim attributes As System.IO.FileAttributes = System.IO.File.GetAttributes(sourcefile)
            If (attributes And IO.FileAttributes.ReadOnly) = IO.FileAttributes.ReadOnly Then
                attributes = attributes And (Not IO.FileAttributes.ReadOnly)
                System.IO.File.SetAttributes(sourcefile, attributes)
            End If
            Dim dest As String = Dts.Variables("User::FolderPath").Value.ToString() & "CsvFiles"
            Dim worksheetName As String = Dts.Variables("User::WorkSheetName").Value.ToString
    
            Dim targetfile As String = dest & "\" & worksheetName & ".csv"
            convertExcelToCSV(sourcefile, worksheetName, targetfile)
            Dts.TaskResult = ScriptResults.Success
    
        End Sub
        Public Sub convertExcelToCSV(ByVal sourcefile As String, ByVal worksheetname As String, ByVal targetfile As String)
    
            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"
    
            Dim Conn As OleDbConnection = Nothing
            Dim wrtr As StreamWriter = Nothing
            Dim cmd As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim x As Integer
    
            Dim y As Integer
            Try
                Conn = New OleDbConnection(strConn)
                Conn.Open()
                cmd = New OleDbCommand("SELECT * FROM [" + worksheetname + "$]", Conn)
                cmd.CommandType = CommandType.Text
                wrtr = New StreamWriter(TargetFile)
    
                da = New OleDbDataAdapter(cmd)
                dt = New DataTable()
                da.Fill(dt)
                Dim cnt1 As Integer = dt.Columns.Count - 1
                Dim cnt As Integer = dt.Rows.Count - 1
                For x = 0 To cnt
                    Dim rowstring As String = ""
                    For y = 0 To cnt1
                        Dim rs As String = dt.Rows(x)(y).ToString
                        If (y > 0) Then
                            rowstring = rowstring + ","
                        End If
                        rowstring = rowstring + """" & rs & """"
                    Next
    
                    wrtr.WriteLine(rowstring)
    
                Next
    
                wrtr.Close()
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
    
            End Try
    
        End Sub

    Here you go.  This worked fine for me

    Chuck


    Monday, April 16, 2012 1:01 PM
  • thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

    can you pls tell me if there is any  way to solve this


    akhila

    There isn't a 100% solution other then stop using excel within SSIS, otherwise you are stuck with the automatic guessing mechanism of the excel driver

    1) manually move large text to top

    2) dummy row with large text and filtering it out later on in the process

    MSDN: Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see http://support.microsoft.com/kb/281517


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Friday, April 13, 2012 6:25 PM
  • Thanks a looot for the code..

    i am still getting this error

    The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

    the sheet in the destination folder is getting created but the data is not going into the sheet.


    akhila

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:11 PM
    Monday, April 16, 2012 2:50 PM
  • Thanks a loot SSIS jhoost..im still trying :)

    akhila

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:11 PM
    Monday, April 16, 2012 2:51 PM
  • Since I do not have your excel file I've really got no way to figure it out - sorry.


    Chuck

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:10 PM
    Monday, April 16, 2012 3:21 PM
  • Did just notice a bug in the code that wouldn't cause the problem you were seeing - fixed it above

    it used to say if (x> 0) Then

    Should be

    If (y > 0) Then
                           


    Chuck

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:10 PM
    Monday, April 16, 2012 3:25 PM

All replies

  • The excel driver determines the datatype on the first 8 columns... so you need a large text string within the first 8 rows. With type guess rows you can change that number 8, but you still need a big text within the first couple of records.

    Imex=1 is for mixed dataypes only (string-values and int-values in a column).


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Friday, April 13, 2012 5:20 PM
  • the data has got many rows that have column value more than 255..could you please tell me if there is anyother way


    akhila

    Friday, April 13, 2012 5:46 PM
  • Are those rows are in first 8 rows? As SSISJoost said , Excel reads first eight lines to define the datatype. 

    Check this link

    http://www.justintubbs.com/code-samples/ssis-excel-source-failed-to-retrieve-long-data.php

    To change in Registry check this

    http://www.sqlservercentral.com/Forums/Topic860660-147-1.aspx

    Thanks

    Aamir


    http://sqlage.blogspot.com/

    • Marked as answer by sql learner12 Friday, June 29, 2012 7:10 PM
    Friday, April 13, 2012 5:54 PM
  • Thanks aamir but its still not working...no the col length  with 258 is the 23 rd row.

    i hav changed the registry values to 0 ..but its not working


    akhila

    Friday, April 13, 2012 6:01 PM
  • Can you just copy the large row to the first row in the file or is this a recurring process?

    Chuck


    Friday, April 13, 2012 6:03 PM
  • Setting type guess rows to 0 will result in checking a couple of 1000 records (I think 65000), but if there is one file with no large text it will fail...

    I often use a dummy row in this case and filter it out with a where clause or a conditional split.

    And even better... stop using Excel for SSIS :-)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, April 13, 2012 6:10 PM
  • thanks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

    can you pls tell me if there is any  way to solve this


    akhila


    Friday, April 13, 2012 6:13 PM
  • thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

    can you pls tell me if there is any  way to solve this


    akhila

    There isn't a 100% solution other then stop using excel within SSIS, otherwise you are stuck with the automatic guessing mechanism of the excel driver

    1) manually move large text to top

    2) dummy row with large text and filtering it out later on in the process

    MSDN: Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see http://support.microsoft.com/kb/281517


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Friday, April 13, 2012 6:25 PM
  • thnaks chuck...but this is a recurring process.we get the data  from dealers ..comments is a column with  size more than 2000 but excel source is accepting just 255.

    can you pls tell me if there is any  way to solve this


    akhila


    The way I would solve it is to convert the excel file into a delimited file.

    Chuck

    Friday, April 13, 2012 6:26 PM
  • @SSisJoost

    i inserted a large row in the first row of excel sheet.. but im still getting same error :(


    akhila

    Friday, April 13, 2012 6:38 PM
  • chuck

    will i need to save the excel file as space delimited or change list seperator to |

    how will i change my current excel sheet to delimited..

    can u pls tel me steps how to make the excel sheet to delimted and send it to sqltable


    akhila


    akhila

    Friday, April 13, 2012 6:42 PM
  • You could either do it the manual way - open excel and do a save as.

    Or in a script do something like this:

    http://www.c-sharpcorner.com/uploadfile/yuanwang200409/how-to-convert-xls-file-into-csv-file-in-C-Sharp/

    or this:

    http://stackoverflow.com/questions/2422212/simple-c-sharp-csv-excel-export-class


    Chuck


    Friday, April 13, 2012 7:04 PM
  • thanks chuck  ill try converting to csv and load the data

    akhila

    Friday, April 13, 2012 7:23 PM
  • @SSisJoost

    i inserted a large row in the first row of excel sheet.. but im still getting same error :(


    akhila

    Hi akhila,

    Did you re-opened the connection manager and Excel source after you added the dummy row? Otherwise the metadata will still be on dt_wstr 255...
    But if it's possible to get CSV files instead... even better.

    Joost


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, April 13, 2012 8:20 PM
  • thanks ssisjoost..

    ya i reopened the connection manger and excel source after changing it.but i was still getting the same error

    i am converting the excelfiles to csv. :)


    akhila


    Friday, April 13, 2012 9:45 PM
  • can any1 tell me where am i goin wrong..

    i am getiing an error saying System.Data.OleDb.OleDbException = {"The Microsoft Office Access database engine could not find the object 'Bay City export_2012-04-10_12-5'.  Make sure the object exists and that you spell its name and the path name correctly."}

    but the object exists in foldr

    D:/Ftp_files/baycityChevrolet/'Bay City export_2012-04-10_12-55(1).csv

    and sheetname is Bay City export_2012-04-10_12-5

    when i add a watch i see they are taking correct values but im still getting an error

           

    Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"


           

    'Dim strConn As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & sourcefile & ";Extended Properties=""EXCEL.0;HDR=YES;IMEX=1;"


           

    Dim Conn As OleDbConnection = Nothing


           

    Dim wrtr As StreamWriter = Nothing


           

    Dim cmd As OleDbCommand = Nothing


           

    Dim da As OleDbDataAdapter = Nothing


           

    Dim ds As New DataSet

           

    Dim dt As New DataTable

           

    Dim y As Integer


           

    Try


                Conn =

    New OleDbConnection(strConn)

                Conn.Open()

                cmd =

    New OleDbCommand("SELECT * FROM [" + sheetname + "]", Conn)

                cmd.CommandType = CommandType.Text

                wrtr =

    New StreamWriter(TargetFile)

                da =

    New OleDbDataAdapter(cmd)

                dt =

    New DataTable()

                da.Fill(dt)

               

    Dim cnt1 As Integer = dt.Columns.Count

               

    Dim cnt As Integer = dt.Rows.Count

               

    For x = 0 To cnt

                   

    Dim rowstring As String = " "


                   

    For y = 0 To cnt1

                       

    Dim rs As String = dt.Rows(x)(y).ToString

                        rowstring =

    "\" & rs & " \"","



                   

    Next


                    wrtr.WriteLine(rowstring)

               

    Next



                Console.WriteLine()

                Console.WriteLine(

    "Done! Your " + sourcefile + " has been converted into " + TargetFile + ".")

                Console.WriteLine()

           

    Catch ex As Exception

                Console.WriteLine(ex.ToString())

                Console.ReadLine()

           

    End Try


       

    End Sub

    End

    Class

    reffered source:http://www.c-sharpcorner.com/uploadfile/yuanwang200409/how-to-convert-xls-file-into-csv-file-in-C-Sharp/


    akhila

    Friday, April 13, 2012 11:55 PM
  • In excel the sheet names always have a $ at the end

    Try this:

    New OleDbCommand("SELECT * FROM [" + sheetname + "$]", Conn)


    Chuck

    Saturday, April 14, 2012 1:44 AM
  • Do NOT use a script with the same OLEDB provider for Excel!!! You wil get the same result. If you want to use scripting then you need the excel interop dll.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, April 14, 2012 5:27 AM
  • ssisjoost..thanks a lot for taking time...i am sorry but can you pls tell me what is excel interop dll..and how to use it


    akhila

    Saturday, April 14, 2012 5:32 AM
  • ssisjoost..thanks a lot for taking time...i am sorry but can you pls tell me what is excel interop dll..and how to use it


    akhila

    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.11).aspx

    but it's not recommended/common to use this in Excel, because it requires an office installation and that's a big no no on a production server :-)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, April 14, 2012 6:11 AM
  • Do NOT use a script with the same OLEDB provider for Excel!!! You wil get the same result. If you want to use scripting then you need the excel interop dll.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Well, I'd try it and find out if it works.  The issue with using the driver is that it guesses at field sizes and passes on that info.  The driver actually sends the full field into the calling app and that is where the issue happens when the variable being populated from the field is not large enough.  Since the code he posted is just walking the rows and columns in the result set I think it'll actually work.

    And he's already done 90% of the work - might as well try it.


    Chuck

    Saturday, April 14, 2012 11:22 AM
  • thanks chuck.i dont have the access to my office computer.i tried converting excel to csv from my laptop and i get an error saying System.UnauthorizedAccessException = {"Access to the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\Bay City export_2012-04-10_12-58' is denied."}

    can you please give me the code to convert xlx to csv.


    akhila

    Sunday, April 15, 2012 7:43 PM
  • Your error:  {"Access to the path 'c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\Bay City export_2012-04-10_12-58' is denied."}

    Indicates that you did not include the extension on your file name.  Provide your full code and it would be pretty easy to figure out what is wrong with it.


    Chuck

    Sunday, April 15, 2012 8:27 PM
  • 'my code for script that returns work sheetname

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.Data.SqlClient

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
        Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        Enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        End Enum


        ' The execution engine calls this method when the task executes.
        ' To access the object model, use the Dts property. Connections, variables, events,
        ' and logging features are available as members of the Dts property as shown in the following examples.
        '
        ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
        ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
        ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
        '
        ' To use the connections collection use something like the following:
        ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
        ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        '
        ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        '
        ' To open Help, press F1.
        Dim con As SqlClient.SqlConnection
        Public Sub Main()
            'con = New SqlClient.SqlConnection("Data Source=CarSqlServer;Initial Catalog=Zsample;Persist Security Info=True;User ID=sa;Password=whitehouse")
            'con.Open()
            'Dim cmd As New SqlCommand
            'cmd = New SqlCommand("select * from test_BCC", con)
            'Dim resut As Integer = cmd.ExecuteScalar
            Dts.Variables.Item("User::DealershipId").Value = 968
            Dts.Variables("User::FolderPath").Value = Dts.Variables("User::FolderPath").Value & "\" & Dts.Variables("User::DealershipId").Value.ToString & "\"
            Dim path As String = Dts.Variables.Item("User::FilePath").Value.ToString
            Dim fnPeices() As String = path.Split("\")
            Dim fileName As String = fnPeices(2).Split(".")(0)
            Dts.Variables.Item("User::WorkSheetName").Value = fileName
            Dts.TaskResult = ScriptResults.Success
        End Sub

    End Class

    -------------------------------------------------

    'code for converting xlsx to csv

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.Data.OleDb
    Imports System.Collections.Generic
    Imports System.IO


    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
        Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        Enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        End Enum


        ' The execution engine calls this method when the task executes.
        ' To access the object model, use the Dts property. Connections, variables, events,
        ' and logging features are available as members of the Dts property as shown in the following examples.
        '
        ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
        ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
        ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
        '
        ' To use the connections collection use something like the following:
        ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
        ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
        '
        ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        '
        ' To open Help, press F1.

        Public Sub Main()
            Dim sourcefile As String = Dts.Variables.Item("User::FilePath").Value.ToString
            Dim attributes As System.IO.FileAttributes = System.IO.File.GetAttributes(sourcefile)
            If (attributes And IO.FileAttributes.ReadOnly) = IO.FileAttributes.ReadOnly Then
                attributes = attributes And (Not IO.FileAttributes.ReadOnly)
                System.IO.File.SetAttributes(sourcefile, attributes)
            End If
            Dim dest As String = Dts.Variables("User::FolderPath").Value & "CsvFiles"
            Dim worksheetName As String = Dts.Variables("User::WorkSheetName").Value.ToString & ".csv"
            Dim targetfile As String = dest
            convertExcelToCSV(sourcefile, worksheetName, targetfile)
            Dts.TaskResult = ScriptResults.Success

        End Sub
        Public Sub convertExcelToCSV(ByVal sourcefile As String, ByVal worksheetname As String, ByVal targetfile As String)

            'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"""

            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"


            'Dim strConn As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & sourcefile & ";Extended Properties=""EXCEL.0;HDR=YES;IMEX=1;"
            Dim Conn As OleDbConnection = Nothing
            Dim wrtr As StreamWriter = Nothing
            Dim cmd As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim ds As New DataSet
            Dim dt As New DataTable

            Dim y As Integer
            Try
                Conn = New OleDbConnection(strConn)
                Conn.Open()
                cmd = New OleDbCommand("SELECT * FROM [" + worksheetname + "$]", Conn)
                cmd.CommandType = CommandType.Text
                wrtr = New StreamWriter(TargetFile)

                da = New OleDbDataAdapter(cmd)
                dt = New DataTable()
                da.Fill(dt)
                Dim cnt1 As Integer = dt.Columns.Count
                Dim cnt As Integer = dt.Rows.Count
                For x = 0 To cnt
                    Dim rowstring As String = " "
                    For y = 0 To cnt1
                        Dim rs As String = dt.Rows(x)(y).ToString
                        rowstring = "\" & rs & " \"","

                    Next
                    wrtr.WriteLine(rowstring)
                Next

                Console.WriteLine()
                Console.WriteLine("Done! Your " + sourcefile + " has been converted into " + TargetFile + ".")
                Console.WriteLine()

            Catch ex As Exception
                MessageBox.Show(ex.ToString())

            End Try

        End Sub
    End Class


    akhila

    Sunday, April 15, 2012 8:53 PM
  • Public Sub Main()
            '"User::FilePath" = c:\testfiles\NSBY.xlsx
            '"User::FolderPath" = c:\testfiles\
            'User::WorkSheetName = Sheet1
    
            Dim sourcefile As String = Dts.Variables.Item("User::FilePath").Value.ToString
            Dim attributes As System.IO.FileAttributes = System.IO.File.GetAttributes(sourcefile)
            If (attributes And IO.FileAttributes.ReadOnly) = IO.FileAttributes.ReadOnly Then
                attributes = attributes And (Not IO.FileAttributes.ReadOnly)
                System.IO.File.SetAttributes(sourcefile, attributes)
            End If
            Dim dest As String = Dts.Variables("User::FolderPath").Value.ToString() & "CsvFiles"
            Dim worksheetName As String = Dts.Variables("User::WorkSheetName").Value.ToString
    
            Dim targetfile As String = dest & "\" & worksheetName & ".csv"
            convertExcelToCSV(sourcefile, worksheetName, targetfile)
            Dts.TaskResult = ScriptResults.Success
    
        End Sub
        Public Sub convertExcelToCSV(ByVal sourcefile As String, ByVal worksheetname As String, ByVal targetfile As String)
    
            Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcefile & ";Extended Properties=Excel 12.0"
    
            Dim Conn As OleDbConnection = Nothing
            Dim wrtr As StreamWriter = Nothing
            Dim cmd As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim x As Integer
    
            Dim y As Integer
            Try
                Conn = New OleDbConnection(strConn)
                Conn.Open()
                cmd = New OleDbCommand("SELECT * FROM [" + worksheetname + "$]", Conn)
                cmd.CommandType = CommandType.Text
                wrtr = New StreamWriter(TargetFile)
    
                da = New OleDbDataAdapter(cmd)
                dt = New DataTable()
                da.Fill(dt)
                Dim cnt1 As Integer = dt.Columns.Count - 1
                Dim cnt As Integer = dt.Rows.Count - 1
                For x = 0 To cnt
                    Dim rowstring As String = ""
                    For y = 0 To cnt1
                        Dim rs As String = dt.Rows(x)(y).ToString
                        If (y > 0) Then
                            rowstring = rowstring + ","
                        End If
                        rowstring = rowstring + """" & rs & """"
                    Next
    
                    wrtr.WriteLine(rowstring)
    
                Next
    
                wrtr.Close()
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
    
            End Try
    
        End Sub

    Here you go.  This worked fine for me

    Chuck


    Monday, April 16, 2012 1:01 PM
  • Thanks a looot for the code..

    i am still getting this error

    The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

    the sheet in the destination folder is getting created but the data is not going into the sheet.


    akhila

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:11 PM
    Monday, April 16, 2012 2:50 PM
  • Thanks a loot SSIS jhoost..im still trying :)

    akhila

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:11 PM
    Monday, April 16, 2012 2:51 PM
  • Thanks a looot for the code..

    i am still getting this error

    The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

    the sheet in the destination folder is getting created but the data is not going into the sheet.


    akhila


    Well you need to look at your particular .xlsx file and see what the name is at the bottom of the sheet.   Sheet1$ is the default - but could have been changed

    Chuck

    Monday, April 16, 2012 2:51 PM
  • i renamed the sheet as sheet1 before execution.My folder properties are readonly.will that be a problem.itried unchecking the readonly option .but its not getting applied


    akhila



    Monday, April 16, 2012 3:04 PM
  • Readonly should be fine.

    Chuck

    Monday, April 16, 2012 3:09 PM
  • i still have the same error

    The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.


    akhila

    Monday, April 16, 2012 3:19 PM
  • Since I do not have your excel file I've really got no way to figure it out - sorry.


    Chuck

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:10 PM
    Monday, April 16, 2012 3:21 PM
  • Did just notice a bug in the code that wouldn't cause the problem you were seeing - fixed it above

    it used to say if (x> 0) Then

    Should be

    If (y > 0) Then
                           


    Chuck

    • Marked as answer by sql learner12 Monday, April 16, 2012 7:10 PM
    Monday, April 16, 2012 3:25 PM
  • thats ok..thank you :)

    akhila

    Monday, April 16, 2012 3:27 PM
  • chuck..thanks a ton..i created a dummy xlsx file..i think prob is with the data ... and this code runs perfect..thanks a looot for all the help  :)


    akhila


    Monday, April 16, 2012 7:10 PM
  • Liam.

    i converted excel file to ccv.then i gave DT_Text as datatype for strings.i think it accepts upto 4000 charecters


    akhila


    Thursday, April 19, 2012 1:58 PM
  • for me the problem was caused by excel sheet .

    it contained data with more than 255 characters

    and excel input colums accepted only ST_STR which had a limit of only 255 characters.

    i dint know handling it.

    so i converted excel sheet into csv file and then imported the data the converison is done using code given by chuck

    check the above post for code


    akhila


    Thursday, April 19, 2012 2:09 PM