Solved

connection needed for upload text over 250 characters in excel

Posted on 2013-01-25
10
230 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:edo60
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:edo60
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:edo60
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:edo60
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 500 total points
ID: 38820844
0
 

Author Comment

by:edo60
ID: 38820884
0
 

Author Closing Comment

by:edo60
ID: 38820896
tsm
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

690 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