Importing XLS file to DataTable
-
What I want to do for my company is turn our price book into a desktop application. I figured I could use an external file for the price data, for future editing purposes. I've been trying to do this in different ways for a while now, and it's sort of frustrating (this is my first real exposure to database programming and the like). So I'm trying to read an Excel File into a DataTable. I have a sub going, but for some reason I keep getting a null reference exception: System.NullReferenceException was unhandled Message="Object reference not set to an instance of an object."
Private Sub ReadExcelData() 'Try xlBook = GetObject("C:\\Documents and Settings\\Administrator.OFFICE2\\Desktop\\Michael\\All\_Item\_Prices\_2008.xls") Dim xlsSheet As Excel.Worksheet = xlBook.Worksheets(1) xlBook.Application.Visible = True xlBook.Windows(1).Visible = True xlBook.Application.WindowState = Excel.XlWindowState.xlMinimized Dim irow As Integer = 1 Dim icol As Integer = 1 'DataTable: '104 Rows '91 Columns icol irow 'For icol = 1 To 91 'xlsSheet.Cells(ROW, COLUMN).value MsgBox(xlsSheet.Cells(1, icol).Value.ToString) If xlsSheet.Cells(1, icol).Value <> Nothing Then New\_Column = New DataColumn \_ (xlsSheet.Cells(1, icol).Value.ToString) Else New\_Column = New DataColumn("FILLER " & icol) End If '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 'NULL REFERENCE HAPPENS HERE ItemDataTable.Columns.Add(New\_Column) 'NULL REFERENCE HAPPENS HERE '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 'For irow = 1 To 104 'MsgBox(xlsSheet.Cells(irow, icol).Value) 'ItemDataTable.Rows.Add = xlsSheet.Cells(irow, icol).Value.ToString New\_Row = ItemTable.NewRow New\_Row.Item(icol) = xlsSheet.Cells(irow, icol).Value.ToString 'Next 'Next 'Catch 'End Try End SubIf someone can tell me either what I'm doing wrong, or knows a better way (I have all of the prices and stuff saved as an Excel spreadsheet), please let me know. I would really appreciate it!
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
where have you declared your data table? Couldn't see the definition in the code you have posted.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
-
where have you declared your data table? Couldn't see the definition in the code you have posted.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
Whoops!
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports Microsoft
Imports System.Data
Imports System.Data.OleDbPublic Class Form1
Dim WithEvents xlBook As Excel.Workbook'DataTable: '104 Rows '91 Columns '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Dim ItemDataTable As DataTable '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Dim con As OleDbConnection Dim da As OleDbDataAdapter Dim ds As New DataSet Dim fileName As String = "" Dim New\_Column As DataColumn Dim New\_Row As DataRow Dim ItemTable As New DataTableSome of this stuff is unused in this example, like the OleDb stuff.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
-
Whoops!
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports Microsoft
Imports System.Data
Imports System.Data.OleDbPublic Class Form1
Dim WithEvents xlBook As Excel.Workbook'DataTable: '104 Rows '91 Columns '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Dim ItemDataTable As DataTable '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Dim con As OleDbConnection Dim da As OleDbDataAdapter Dim ds As New DataSet Dim fileName As String = "" Dim New\_Column As DataColumn Dim New\_Row As DataRow Dim ItemTable As New DataTableSome of this stuff is unused in this example, like the OleDb stuff.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
CodeMonkey85 wrote:
Dim ItemDataTable As DataTable
You have declared this table but not defined it. It must be null and hence the exception.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
-
CodeMonkey85 wrote:
Dim ItemDataTable As DataTable
You have declared this table but not defined it. It must be null and hence the exception.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
How would I define a DataTable? By the looks of it, you specify it as a source for another object or something. This might not even be the right way to do what I want to do, bear in mind. I'm looking for any easy way to store the data from my sheet so that I can programmatically retrieve it.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
-
What I want to do for my company is turn our price book into a desktop application. I figured I could use an external file for the price data, for future editing purposes. I've been trying to do this in different ways for a while now, and it's sort of frustrating (this is my first real exposure to database programming and the like). So I'm trying to read an Excel File into a DataTable. I have a sub going, but for some reason I keep getting a null reference exception: System.NullReferenceException was unhandled Message="Object reference not set to an instance of an object."
Private Sub ReadExcelData() 'Try xlBook = GetObject("C:\\Documents and Settings\\Administrator.OFFICE2\\Desktop\\Michael\\All\_Item\_Prices\_2008.xls") Dim xlsSheet As Excel.Worksheet = xlBook.Worksheets(1) xlBook.Application.Visible = True xlBook.Windows(1).Visible = True xlBook.Application.WindowState = Excel.XlWindowState.xlMinimized Dim irow As Integer = 1 Dim icol As Integer = 1 'DataTable: '104 Rows '91 Columns icol irow 'For icol = 1 To 91 'xlsSheet.Cells(ROW, COLUMN).value MsgBox(xlsSheet.Cells(1, icol).Value.ToString) If xlsSheet.Cells(1, icol).Value <> Nothing Then New\_Column = New DataColumn \_ (xlsSheet.Cells(1, icol).Value.ToString) Else New\_Column = New DataColumn("FILLER " & icol) End If '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 'NULL REFERENCE HAPPENS HERE ItemDataTable.Columns.Add(New\_Column) 'NULL REFERENCE HAPPENS HERE '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 'For irow = 1 To 104 'MsgBox(xlsSheet.Cells(irow, icol).Value) 'ItemDataTable.Rows.Add = xlsSheet.Cells(irow, icol).Value.ToString New\_Row = ItemTable.NewRow New\_Row.Item(icol) = xlsSheet.Cells(irow, icol).Value.ToString 'Next 'Next 'Catch 'End Try End SubIf someone can tell me either what I'm doing wrong, or knows a better way (I have all of the prices and stuff saved as an Excel spreadsheet), please let me know. I would really appreciate it!
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
Hello Friend, You can use excel sheet as database it self and can retrieve data using a oledb connection to it from your application, or Manually you can do it by ->Go to access ->Create a new blank database ->Now go to File, GetExternal Data, Import ->after clicking import you you will get file dialog open, just select your excel spreadsheet in it.. rest of things you can do it easily... call this acess database easily from your application if you feel it easy. Thanks
-
Hello Friend, You can use excel sheet as database it self and can retrieve data using a oledb connection to it from your application, or Manually you can do it by ->Go to access ->Create a new blank database ->Now go to File, GetExternal Data, Import ->after clicking import you you will get file dialog open, just select your excel spreadsheet in it.. rest of things you can do it easily... call this acess database easily from your application if you feel it easy. Thanks
Yeah, I tried using oledb before, and for some reason certain cells came up blank for no apparent reason. I dunno. I could try that Access thing too, I guess. Frankly, I'm about to pull my hair out on this one. Thanks!
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
-
Yeah, I tried using oledb before, and for some reason certain cells came up blank for no apparent reason. I dunno. I could try that Access thing too, I guess. Frankly, I'm about to pull my hair out on this one. Thanks!
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
access thing should work fine for you buddy, :) :)
-
How would I define a DataTable? By the looks of it, you specify it as a source for another object or something. This might not even be the right way to do what I want to do, bear in mind. I'm looking for any easy way to store the data from my sheet so that I can programmatically retrieve it.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
In that case, you can use Excel as any other database. You can connect to it. Run SQL queries. Do many things. If you want ot get ths into a grid, then a sinmple google search will help you.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
-
access thing should work fine for you buddy, :) :)
Yeah, I'm now trying to figure out how to read data from the Access file.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
-
In that case, you can use Excel as any other database. You can connect to it. Run SQL queries. Do many things. If you want ot get ths into a grid, then a sinmple google search will help you.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
I have done many things. And I've performed at least ten Google searches and tried everything I've seen. But for some unbeknownst reason to me, I can't seem to get what I want. It seems like it should be a simple matter to read data from a spreadsheet and use that data as you please, but for example, I can't seem to retrieve the correct data from my DataTable even when I think I finally populated it with the spreadsheet data. I don't think it was even meant to be used that way, honestly.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
-
Yeah, I'm now trying to figure out how to read data from the Access file.
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836
Dim connect As New OleDbConnection connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabase.mdb;Persist Security Info=True" connect.Open() your code and sql here.. connect.Close()
-
Dim connect As New OleDbConnection connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabase.mdb;Persist Security Info=True" connect.Open() your code and sql here.. connect.Close()
Yeah, tried that... see this thread[^] I dunno. I'll keep trying different things, but if anyone has ever done anything like this again and has some code (or can at least figure out what's wrong with my code), I would appreciate it a bunch. Thanks!
As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836