Excel 2007, although the spreadsheet that I'm importing is an xls file and not an xlsx. It seems to be working now with the changes in the connection string.
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Friday, October 30, 2009 9:05 AM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Datagridview and Excel...
Exactly...
http://www.sqldts.com/254.aspx
What version of Excel are you guys using? My ODBC Data Sources is showing a Excel 4.0 and Excel 12.0 driver. Try using those instead of Oledb.
http://support.microsoft.com/kb/257819
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Thursday, October 29, 2009 4:30 PM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Datagridview and Excel...
Just read this on connectionstrings.com
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Thursday, October 29, 2009 5:13 PM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Datagridview and Excel...
vb
Dim connectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + FileName & ";" & "Extended Properties=""Excel 8.0;IMEX=1"""
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Thursday, October 29, 2009 3:56 PM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Datagridview and Excel...
Yes...
Here's how I'm loading the Excel spreadsheet into the datagridview...
dgvItems is the datagridview...
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim fileName As String = OpenFileDialog1.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" _
& "data source=" & fileName & "; " _
& "Extended Properties=Excel 8.0;"
'First DataGrid
Dim objConn As New OleDb.OleDbConnection(strConn)
Dim cmd As OleDbCommand = objConn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * From [Sheet1$] "
Dim daItemTypes As OleDb.OleDbDataAdapter
daItemTypes = New OleDb.OleDbDataAdapter()
Dim ds As New DataSet()
daItemTypes.SelectCommand = cmd
Try
objConn.Open()
daItemTypes.Fill(ds, "[Sheet1$]")
dgvItems.DataSource = ds.Tables(0)
Catch exc As Exception
MessageBox.Show(exc.Message, "Error Loading Spreadsheet", MessageBoxButtons.OK, MessageBoxIcon.Error)
Throw exc
Finally
ds.Dispose()
objConn.Close()
objConn.Dispose()
End Try
End If
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx [mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of ibm
Sent: Thursday, October 29, 2009 4:47 PM
To: .net use with the System i
Subject: Re: [SystemiDotNet] Datagridview and Excel...
Hi Antonio, can you show us how you bind the xls to the control?
-----Original Message-----
From: systemidotnet-bounces@xxxxxxxxxxxx
[mailto:systemidotnet-bounces@xxxxxxxxxxxx] On Behalf Of Mira, Antonio
Sent: Thursday, October 29, 2009 3:33 PM
To: systemidotnet@xxxxxxxxxxxx
Subject: [SystemiDotNet] Datagridview and Excel...
Hello,
Even though technically this is not .net and iSeries, indirectly it is.
I am loading an Excel spreadsheet into a datagridview. One of the
columns has alphanumeric data. Now, some of the values in the cells on
this column contain only numbers. When I load the data into the
datagridview, the cells that have only numbers show up blanks on the
datagridview while the cells that have characters and numbers show up
ok. This data will be used by the application later to cross-reference
it to a table on the iSeries.
Can anyone tell me why this is happening and give me any suggestions on
how to resolve this?
As always, your help is greatly appreciated...
Thank you,
Antonio Mira
Application Developer - Mid-Ohio Division
Time Warner Cable
1015 Olentangy River Road - 2nd Floor
Columbus, OH 43212
http://www.timewarnercable.com
phone: 614 827 7949
This E-mail and any of its attachments may contain Time Warner
Cable proprietary information, which is privileged, confidential,
or subject to copyright belonging to Time Warner Cable. This E-mail
is intended solely for the use of the individual or entity to which
it is addressed. If you are not the intended recipient of this
E-mail, you are hereby notified that any dissemination,
distribution, copying, or action taken in relation to the contents
of and attachments to this E-mail is strictly prohibited and may be
unlawful. If you have received this E-mail in error, please notify
the sender immediately and permanently delete the original and any
copy of this E-mail and any printout.
midrange.com
