StrataFrame Forum » .NET Forums » General .NET Discussion » Working with Excel file for importing the data with VB.NET

Working with Excel file for importing the data with VB.NET

Edhy Rijo — Tue, 07 Jul 2009 11:45:30 GMT

Hi guys,

I have some Excel or CSV files which I need to import data from, all these files have over 5 columns and I only care from 2 of them, so I am looking for a way to remove all the extra columns or just select the ones I care for and create another file so I can import the data. These file can have over 120,000 rows so the need to remove unwanted columns and just import the needed ones.

I am using the import class library from [url=]http://www.filehelpers.com[/url] which is incredibly easy to use for importing data and manipulate this data via strong type BOs, but unfortunately they have not way to pre-select just the columns I need from the imported file.

So I would appreciate if anybody have any link or sample code on how to manipulate an Excel file to remove some specific columns and create a new file to be ready to import using the FileHelpers classes.

Thanks!

RE: Working with Excel file for importing the data with VB.NET

Juan Carlos Pazos — Tue, 07 Jul 2009 11:45:30 GMT

Hi Edhy

I was trying to do what you are working now (import from Excel to a BO) but I'not succedd. I´m pretty sure you will solve this, if is possible please put some sample of the solution, I think than more than one will be found very usefull.

Actuallly as I use SQL Server 2008 express, the managment console let me import from Excel to the database; in 2005 express edition did not had this option. Of course it will be better if people don't have to deal with this tools.

Regards

RE: Working with Excel file for importing the data with VB.NET

Edhy Rijo — Mon, 06 Jul 2009 11:31:21 GMT

Thanks Keith, let me give it a try.

RE: Working with Excel file for importing the data with VB.NET

Keith Chisarik — Mon, 06 Jul 2009 11:26:27 GMT

I think you should just be able to select the columns you want from the Excel file once you have an OLEDB connection to it.

[quote]

Dim sheetname As String = String.Empty

sheetname = GetExcelSheetNames(fullfilename)

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _

& fullfilename & ";Extended Properties=""Excel 8.0;HDR=No;"";"

objConn.ConnectionString = sConnectionString

objConn.Open()

Dim objCmdSelect As New OleDbCommand("SELECT COL1,COL2,COL3 FROM [" & sheetname & "]", objConn)

Dim objAdapter1 As New OleDbDataAdapter()

objAdapter1.SelectCommand = objCmdSelect

Dim objDataset1 As New DataSet

objAdapter1.Fill(objDataset1, "tablename")

Dim atable As DataTable = objDataset1.Tables("tablename")

[/quote]