connection needed for upload text over 250 characters in excel

hi, im trying to export a excel file to db with a module, all works find until the text is so long in the case of the fiel desciption that im atached it is
im ussing this conection
 
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.jet.OLEDB.4.0; " & _
                               "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")

im attaching the problem file as well
tsm
LM-Valvulas-Rev1.xls
ErnestoAsked:
Who is Participating?
 
ScriptAddictCommented:
Looks like this might be solved by moving a copy of the file to your c:\Temp or something like that, and running it from there.

That should dramatically reduce the size of the database connection if you are suffering from a long path.  

Or just move it to somewhere with a much shorter path.  Could you map a drive to the location of file?  Would that also work to reduce the file path size?

If you break up the connection string does that fix it?  

 mycn.connection = mycn.connection & blah blah more?
0
 
ErnestoAuthor Commented:
tsm
thats not the problem,
the problem is if you see the file  the "DESCIPTION" column,
do not do the process if the cell continues in an other line like the most,
ej
"VALVULA DE COMPUERTA INSERTO SOLDABLE CLASE 800 API, CUERPO ASTM A105, INTERIORES DE ACERO INOXIDABLE 316, DISCO Y ASIENTO RECUBIERTO DE ESTELITA, CUERDA EXTERIOR Y YUGO, BONETE ATORNILLADO, CUÑA SOLIDA, ASIENTOS RECAMBIABLES, OPERADA CON VOLANTE. NACE MR-01-75"
from
" OPERADA CON VOLANTE. NACE MR-01-75" is in the row bellow that  cause fail to the process"
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
CodeCruiserCommented:
>im trying to export a excel file to db with a module

What DB is it and what is the length of description field in DB?
0
 
ErnestoAuthor Commented:
is 512,
the problem isnt there, the problem is when pass the excell sheet to the datagreed.

from the datagrid i have a migration proccess that do it well, the problem is show up the sheet in the datagrid.
tsm
0
 
ScriptAddictCommented:
So, just to clarify.  

The issue here is that when your automated import process has a line that is longer then excel will allow in a single cell, and splits it in two cells, the second cell crashes the import.

?
0
 
CodeCruiserCommented:
No. He is importing from excel not exporting to it.

>when pass the excell sheet to the datagreed.


Can you show the code which is used to import excel to your grid?
0
 
ErnestoAuthor Commented:
ok, the procces is when import to the grid
the code to puts the excell file to the grid is the next
event click
 Private Sub Btn_Import_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Import.Click
        Try
            Dim _Obj As New ExcelConnection1
            _Obj.ImportAttendence(Txt_Path.Text, DataGrid1)
            '_Obj.ImportAttendence(Txt_Path.TextLength, DataGrid1)

        Catch ex As Exception

        End Try
    End Sub

**** then


 Public Class ExcelConnection1


#Region "Public Function(s)"

        Public Function ImportAttendence(ByVal PrmPathExcelFile As String, ByVal DataGrid1 As DataGrid)

            Dim MyConnection As System.Data.OleDb.OleDbConnection

            Try

                ''''''' Fetch Data from Excel

                Dim DtSet As System.Data.DataSet

                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter


                '  MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
                '                 "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")


                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.jet.OLEDB.4.0; " & _
                               "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")



                ' Select the data from Sheet1 of the workbook.

                MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where desciption<>'' and spec<>''order by spec,req_cta,desciption asc", MyConnection)
                'MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where desciption<>'' and spec<>''order by desciption asc", MyConnection)

                MyCommand.TableMappings.Add("Table", "Attendence")

                DtSet = New System.Data.DataSet

                MyCommand.Fill(DtSet)
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                DataGrid1.DataSource = DtSet.Tables(0)
                MyConnection.Close()

            Catch ex As Exception
                MyConnection.Close()
                MsgBox("Error de Datos", vbCritical)
            End Try

        End Function

#End Region



    End Class

End Module
0
 
ErnestoAuthor Commented:
0
 
ErnestoAuthor Commented:
tsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.