Open Excel file, "clean" data, load into datatable

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
<p align=left><font face=Arial size=2>Im using VB Microsoft Visual Studio to try to open an excel file, clean a column of data, then load into a datatable for use by program (enter Excel data into Reflections, process / screenscrape, enter data into Access db).</font>
<p align=left> 
<p align=left>I can load the data into a datatable just fine, but Im having problems figuring out how to open the excel file BEFORE its loaded into the datatable so I can "Clean" a column of data the program reads from that can potentially have hidden characters.  Below is what I have so far.  Any help would be greatly appreciated!  <img src="http://forums.microsoft.com/MSDN/WebResource.axd?d=NySzF1eivP_rMoc50GQJzcvS4MHMOEKwYrCIgDtzuzlw7GsNki3H_INlfYaLgkxFCLVvZNcnIJT9x2uZNvyuIGWah9F3g0vyQYx7NayjHus1&t=633263991144971555
 
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;color:blue;font-family:Courier New Sub<span style="font-size:10pt;font-family:Courier New SelectnLoadExcelFile(<span style="color:blue ByRef errFlag)
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     dt.Clear()
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     errFlag = <span style="color:blue False
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue Dim xlFile <span style="color:blue As <span style="color:blue New OpenFileDialog, vbCan <span style="color:blue As DialogResult
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:green Select excel file to process
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     vbCan = xlFile.ShowDialog()
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue If vbCan = Windows.Forms.DialogResult.Cancel <span style="color:blue Then
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       MsgBox(<span style="color:#a31515 "Click the Process button again to restart.", MsgBoxStyle.MsgBoxSetForeground, <span style="color:#a31515 "Error")
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       errFlag = <span style="color:blue True
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       <span style="color:blue Exit <span style="color:blue Sub
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue End <span style="color:blue If
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     file = MsgBox(<span style="color:#a31515 "Is the file below the correct file?" & _
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     Chr(13) & Chr(13) & xlFile.FileName & <span style="color:#a31515 "", MsgBoxStyle.YesNo, <span style="color:#a31515 "Verify File")
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue If file = vbNo <span style="color:blue Then
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       MsgBox(<span style="color:#a31515 "Click the Process button again to select the correct <span style="  file.", MsgBoxStyle.MsgBoxSetForeground, <span style="color:#a31515 "Verify File")
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       errFlag = <span style="color:blue True
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="       <span style="color:blue Exit <span style="color:blue Sub
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue End <span style="color:blue If
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;color:blue;font-family:Courier New  
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:green Upload Excel spreadsheet into dt datatable
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue Dim connStr <span style="color:blue As <span style="color:blue String = <span style="color:#a31515 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="                             <span style="color:#a31515 "Data Source=" & xlFile.FileName & _
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="                             <span style="color:#a31515 ";Extended Properties=Excel 8.0;"
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue Dim sqlStr <span style="color:blue As <span style="color:blue String = <span style="color:#a31515 "SELECT * FROM [Sheet1$]"
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:blue Dim dataAdapter <span style="color:blue As <span style="color:blue New OleDb.OleDbDataAdapter(sqlStr, connStr)
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New  
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     <span style="color:green Try
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     dataAdapter.Fill(dt)
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     dataAdapter.Dispose()
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New  
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="    Catch ex As OleDb.OleDbException
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     MsgBox("The Excel Spreadsheet selected does not contain a " & _
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     "Worksheet named Sheet1." & Chr(13) & Chr(13) & "Open the spreadsheet, save the appropriate worksheet as Sheet1 and restart.", MsgBoxStyle.MsgBoxSetForeground, "Worksheet Name Error")
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     errFlag = True
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     Finally
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;color:green;font-family:Courier New  
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;color:green;font-family:Courier New  
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     xlFile = <span style="color:blue Nothing
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     vbCan = <span style="color:blue Nothing
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     GC.Collect(0)
<p class=MsoNormal style="margin:0in 0in 0pt <span style="font-size:10pt;font-family:Courier New <span style="     End Try
<p align=left> 

View the full article
 
Back
Top