Need help getting data to excel from text file

[alloyd7]

First off I am new to VB.NET, I took one course in college, but it is not helping me here... We did simple programs (1+1=2) type of things....


I am trying to create an application that will read from a text tab delimited file and input certain data into an excel spreadsheet template.


The text file has 6 columns (these titles of the columns do not appear in the text file)
ID REF_ID RESPONSE LOCATION SURVEY_DATE SUBMIT_DATE
Here is an example of the data......

32993 6 1 AZ: Phoenix Retail Activation Center (RAC) 02/02/04 02/02/04

I need code to basically say....

If REF_ID = 2 and location = "HERE" and SURVEY_DATE is = to tbstart.txt then PLACE RESPONSE in excel document. Cells (14, 2)
If REF_ID = 6 and location = "HERE" and SURVEY_DATE is = to tbstart.txt then PLACE RESPONSE in excel document. Cells (3, 2)

And so on and so on....

I have tried several different things and nothing is working.

[Andy]

here is a routine I wrote in vb6 (I haven't done anything with it in .net yet). It takes a text file, reads a header section and that section instructs the program as to where to parse information and where to send it. Very much like what you're needing. feel free to alter this code to match your needs. Hope it helps.

Private Sub btnImport_Click()
 
'**********************************************************************************
'Variables
Dim Handle As Integer
Dim Start, Length, p, HeaderSize As Long
Dim Tmp, Field, Filename, ConnStr As String
Dim HeaderLines As Long
Dim Point As Integer
Dim i As Integer
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
 
 
 
'set variables*********************************************************************
    For i = 0 To FileSelect.ListCount - 1
        If (FileSelect.Selected(i) = True) Then Filename = DirSelect.Path & "\" & FileSelect.List(i)
        DoEvents
    Next i
 
    HeaderLines = 0
    HeaderSize = 0
    Handle = FreeFile()
 
    If (Filename = "cvsale.src") Then
        TableName = "sale" 'change this name
    Else
        TableName = "changeme"
    End If
 
ConnStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=admin;Initial Catalog=Everest;Data Source=PC1284"
 
    db.Open (ConnStr)
    rs.Open "SELECT * FROM " + Table(0).TableName + ";", db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic
'**********************************************************************************
 
Open (Filename) For Input As #Handle
 
   'get header info****************************************************************
   i = 0
    Do While (Not EOF(Handle))
        DoEvents
        Line Input #Handle, Tmp
        If (Left(Tmp, 1) = "%") Then
            HeaderLines = HeaderLines + 1
            HeaderSize = HeaderSize + Len(Tmp) + 2
            Tmp = Mid(Tmp, 2) 'this takes out the '%' character
            p = InStr(1, Tmp, " ") 'this reads from 1st char to the 1st 'space'
            If (p) Then
            'set the header info into variables
                CvcallHeader(i).FieldName = Trim(Mid(Tmp, 1, p - 1))
                CvcallHeader(i).Offset = Val(Mid(Trim(Mid(Tmp, p)), 1, InStr(1, Trim(Mid(Tmp, p)), " "))) + 1
                CvcallHeader(i).Length = Val(Right(Tmp, 3))
            End If
            
            i = i + 1
        Else
            Exit Do
        End If
'start getting data from file*******************************************************
    Loop
    
    Do While (Not EOF(Handle))
        DoEvents
        
        rs.AddNew
            rs("TELEPHONE_MEMBER").Value = Mid(Tmp, CvcallHeader(0).Offset, CvcallHeader(0).Length)
            rs("TELEPHONE_NUMBER").Value = Mid(Tmp, CvcallHeader(1).Offset, CvcallHeader(1).Length)
            rs("MEMBER_NUMBER").Value = Mid(Tmp, CvcallHeader(2).Offset, CvcallHeader(2).Length)
            rs("LISTED_NAME").Value = Mid(Tmp, CvcallHeader(4).Offset, CvcallHeader(4).Length)
        rs.Update
        
        Line Input #Handle, Tmp
        If (EOF(Handle)) Then
            rs.AddNew
                rs("TELEPHONE_MEMBER").Value = Mid(Tmp, CvcallHeader(0).Offset, CvcallHeader(0).Length)
                rs("TELEPHONE_NUMBER").Value = Mid(Tmp, CvcallHeader(1).Offset, CvcallHeader(1).Length)
                rs("MEMBER_NUMBER").Value = Mid(Tmp, CvcallHeader(2).Offset, CvcallHeader(2).Length)
                rs("LISTED_NAME").Value = Mid(Tmp, CvcallHeader(4).Offset, CvcallHeader(4).Length)
            rs.Update
            Exit Do
        End If
    Loop
    
    
    
    Close (Handle)
    
    
    
    'Debug.Print (Name & vbCrLf & Start & vbCrLf & Length)
    
End Sub

[alloyd7]

I have go the program reading now... but there is something wrong with my array and I can not figure it out.

Private Sub mnRetailWeekly_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnRetailWeekly.Click
        Dim line As String, i As Integer
        'open file
        FileOpen(1, "C:\UnityReports\Weekly1.txt", OpenMode.Input)
        'read each line, seperate by :: and add to variable "line"
        Do While Not EOF(1)
            'temp = Split(LineInput(1), "\t")
            line = line & "::" & LineInput(1) & "::"
        Loop
        'close file
        FileClose(1)
        '-----------------------------------------
        '----- Open excel file
        '-----------------------------------------
        'setup excel doc
        xlApp = New Excel.Application()
        xlApp.Visible = True
        'location of template
        xlFile = "C:\UnityReports\Lakewood.xls"
        xlApp.Workbooks.Open(xlFile)
        '--------------------------------------------------------------------------------------
        '----- write data from tab delminated file to excel doc
        '--------------------------------------------------------------------------------------
        'write date of survey, based on date picked in calendar
        xlApp.Cells(1, 1) = "Weekly Unity Survey Summary for Week of: " & tbEnd.Text
        'split data from file by line,check date, send to function to write data
        '----------------------------------------
        '-----  Write v2 data to excel
        '---------------------------------------
        Dim v2_data As Array, temp_v2_data As Array, tempData As String
        v2_data = Split(line, "::")
        For i = 0 To UBound(v2_data)
            If (Len(v2_data(i)) > 0) Then
                tempData = v2_data(i)
                temp_v2_data = Split(tempData, vbTab)
                If (temp_v2_data(2) = compare_date) Then
                    WriteWeekly(temp_v2_data)
                End If
            End If
        Next
    End Sub

The red area is where my program stops to be dbuged. I dont know whats wrong.

[Andy]

what error does it give you?

[Andy]

Dim v2_data As Array, temp_v2_data As Array, tempData As String

actually, this MAY help: instead of declaring v2_data as an array type, try this:

dim v2_data() as string

, and the same for

temp_v2_data() as string

. that declares them as arrays. Ive never used (or even seen) the array data type (obviousely it exists ) but that MAY be the problem because you're trying to work with string data.

[alloyd7]

I have tried that and I still get the same error message
" Index was outside the bounds of the array"

[Andy]

For i = 0 To UBound(v2_data) -1

try that out. Since it's zero based, you will need to subtract one to keep it in bounds. can't garuntee that'll fix it but after looking over your code and the error, that makes logical sense to me now.

[Webtest]

Alloyd7:

Feb was a long time ago ... did you find your solution?

Of course, if you split a string into an array of substrings, if there is a null string, that element of the array is not created and references to it are out of bounds.

All you needed was a "redim" to the appropriate number elements for the array.

redim temp_v2_data(n)

where "n" is the appropriate or maximum number of elements in the array. This forces the array to have an element "n" even though it contains a null.