StrataFrame Forum

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

http://forum.strataframe.net/Topic23744.aspx

By Edhy Rijo - 7/6/2009

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 http://www.filehelpers.com 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!
By Keith Chisarik - 7/6/2009

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.

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")
By Edhy Rijo - 7/6/2009

Thanks Keith, let me give it a try.
By Juan Carlos Pazos - 7/7/2009

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