Solved

connection needed for upload text over 250 characters in excel

Posted on 2013-01-25
10
226 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
  • 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Variable Event ? 3 23
C# guarantee sql connection close 6 30
How do I crate a Pivot table in Excel 2 10
Excel range I cannot find 8 20
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now