locked
Reading from Excel Problem? RRS feed

  • Question

  • User-1104215994 posted

    Hi,

    I am trying to read from excel file (xls) and the problem is there is header and only one row of information in this excel but it gets 3 rows?! Where is the problem? My connection string is like below:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';


    Here is the sample code:

    Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
    Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
    Dim adapter As DbDataAdapter = factory.CreateDataAdapter

    Dim selectCommand As DbCommand = factory.CreateCommand
    selectCommand.CommandText = "SELECT VENDORNO,VENDORNAME,DEBIT FROM [Sheet1$]"

    Dim connection As DbConnection = factory.CreateConnection
    connection.ConnectionString = connectionString
    selectCommand.Connection = connection
    adapter.SelectCommand = selectCommand
    Dim debits As DataSet = New DataSet
    adapter.Fill(debits)

    'app path
    Console.WriteLine(System.Reflection.Assembly.GetExecutingAssembly.Location())
    remove applicaiton file from folder
    Dim path As String = System.Reflection.Assembly.GetExecutingAssembly.Location()
    Dim i As Integer
    i = path.Length - 1
    Do Until path.Substring(i, 1) = "\"
        i = i - 1
    Loop

    path = path.Substring(0, i)
    If (Not System.IO.Directory.Exists(path + "\Debit")) Then
                System.IO.Directory.CreateDirectory(path + "\Debit")
    End If
    Dim dt As Date = Date.Now
    Dim d As String = Format(dt, "yyMMdd")
    Dim txtname As String = "boa_" + d + "01_debit_w_interim.txt"
    Dim sw As StreamWriter = New StreamWriter(path + "\Debit\" + txtname)

    Dim total_amount As Double = Nothing
    Dim total_num As Integer = Nothing

    'Write a line of text
    sw.WriteLine("H" + Format(dt, "ddMMyyyy") + "0640"True)
    Dim row As DataRow
    For Each row In debits.Tables(0).Rows -----------------------> 3 rows!!! But it is 1 in the excel file
       Console.WriteLine(row(0).ToString)
       Console.WriteLine(row(1).ToString)
       Console.WriteLine(row(2).ToString)
       Dim amount As Double = Convert.ToDouble(row(2).ToString.Replace("."String.Empty))
       Dim length As Integer = Nothing
       length = amount.ToString.Length
       Dim toLimit As Integer = 15 - length
       Dim str2 As String = Nothing
       For value As Integer = 0 To toLimit - 1
            str2 = str2 + "0"
    Next
      Dim temp_text As String = vbTab & vbTab & Format(dt, "yyyyMMdd") + str2 + amount.ToString
      sw.WriteLine("D00" + row(0).ToString() + row(1).ToString + temp_text, True)

      'Totals
      total_amount = total_amount + row(2)
      total_num = total_num + 1
    Next

    Here is the sample excel:

    VENDORNO VENDORNAME DEBIT
    102005 Spartus-S MMC 1.87
    Monday, January 31, 2011 6:19 AM

Answers

  • User-1104215994 posted

    Changed query to


    SELECT VENDORNO,VENDORNAME,DEBIT FROM [Sheet1$] where VENDORNO is not null

    its working now
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 31, 2011 9:47 AM