How Do I Read Into Excel File And Extract Certain Data Which Means I Do Not Extract Everything

Question

0.00/5 (No votes)

See more:

how do i read into excel file and extract certain data which means i do not extract everything

[edit]
I have multiples excel file report. I'm suppose to extract data from those excel file save it in a database so that i can have 1 big report.
so far i am able to browse the file and save data into a dataset. I want to read into data and extract certain data that i need only. im stuck when looping into data extracting data that i need

VB

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim fBrowse As New OpenFileDialog
With fBrowse
    .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
    .FilterIndex = 1
    .Title = "Import data from Excel file"
End With
If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then

    Dim fname As String
    fname = fBrowse.FileName
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")

    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Apr14$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "Billing_report")
    DtSet = New System.Data.DataSet


    MyCommand.Fill(DtSet)

    DataGridView1.DataSource = DtSet.Tables(0)
    MyConnection.Close()
    Dim sr As System.IO.StreamReader = New System.IO.StreamReader(fname)
    Dim line As String
    line = sr.ReadLine().Trim
    Dim n As Integer
    Do While line.StartsWith("CNC")
        sr.ReadLine()
        If line.StartsWith("CNC") Then
            line.Substring(10, 11).Length.ToString().Trim()
            n += 1
            If line.Contains("Circuit no") Then

            End If
        End If

    Loop
End If

[/edit]

Posted 26-Oct-14 21:28pm

Member 11182446

Updated 26-Oct-14 22:35pm

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 27-Oct-14 4:38am

You need to provide a clearer explanation of your problem. If you can read the data from the worksheet then what is your difficulty in extracting specific columns and saving their content elsewhere? And what is the significance of the streamreader in the above code?

Member 11182446 27-Oct-14 5:00am

I am struggling to extract data i need from the excel because the code above only take everything from the work sheet. i used streamreader hoping it will help to read into a file and have if statement to extract required data only but its not working. I hope it is clearer

Richard MacCutchan 27-Oct-14 5:09am

You cannot use a streamreader to read an Excel file, you need to use the OLEdb as above. Once you have your spreadsheet data in the dataset it is just a matter of looping through each row extracting the relevant fields that you are interested in. What exactly are you finding difficult in that?

Member 11182446 27-Oct-14 5:24am

the data its not structured that i can just take columns.for example in a cell there's information that i need to cut of when extracting, there columns that i don't need at all. the excel sheet looks like a invoice. i don't know how to go about Extracting the information that a
I need only. that's why i tried streamreader so maybe i can tell it what to take such as the sub-string that i had on the code above. its my first time working in such project so i'm really strungling. Am not sure if im making sense.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-26T22:51:00

Have a look here: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]. It might help you.

If you want to select only part of 'table', you need to use:

SQL

SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [WorksheetName$A1:G500]

If you have named ranges,

SQL

SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [NamedRange]

To be able to join data from another sheet, use:

SQL

SELECT t1.<ColName1>, t1.<ColName2>, t2.<ColName1>, t2.<ColName2>
FROM [Worksheet1$A1:G500] AS t1 INNER JOIN [Worksheet2$A1:G500] AS t2 ON t1.ColName1 = t2.ColName3

If the data type for each column in Sheet1 corresponds to data type for each column in Sheet2, use:

SQL

SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [Worksheet1$A1:G500]
UNION ALL
SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [Worksheet2$A1:G500]

If you want to recognize the source of data, here is simple trick:

SQL

SELECT 'Sheet1' AS SheetName, <ColName1>, <ColName2>, <ColNameN>
FROM [Worksheet1$A1:G500]
UNION ALL
SELECT  'Sheet2' AS SheetName, <ColName1>, <ColName2>, <ColNameN>
FROM [Worksheet2$A1:G500]

Good luck!