[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

connection needed for upload text over 250 characters in excel

Posted on 2013-01-25
10
Medium Priority
?
234 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:Ernesto
  • 5
  • 3
  • 2
10 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38820296
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
 

Author Comment

by:Ernesto
ID: 38820707
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38820749
>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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ernesto
ID: 38820765
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
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38820806
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38820812
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
 

Author Comment

by:Ernesto
ID: 38820838
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 38820844
0
 

Author Comment

by:Ernesto
ID: 38820884
0
 

Author Closing Comment

by:Ernesto
ID: 38820896
tsm
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question