Importing txt file to access with column (vertical data)

[sap1958]

Im looking for a function or sub routine to import a column oriented text file into MS Access. Here is some sample data I want to import:
c
12334

Albany
d
11245
Central
New York

The fields are to be
Category
ID
Region
City

In some cases there may be some blanks between the columnar data in the text file.

 

[Andrzejek]

Will there always be 4 lines of data per record, even if there is a blank sometimes?

Could you do any validation? Ie, is the Category always one letter, is ID always 5 digits, etc?

Are there any required fields, like: do you have to have a Category / ID or could it be blank?

Have fun.

---- Andy

 

[SkipVought]

Assuming 4 rows per record, it can be accomplished simply in Excel with a simple formula.

Import into sheet1 column A

in sheet2, row 1 is your column headings

[tt]
A2: =INDEX(Sheet1!$A:$A,(ROW()-2)*4+COLUMN(),1)
[/tt]
copy fomula as paste across and down.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I had a VBA solution in mind, but yours, Skip, looks a lot simpler

Option Explicit

Private Sub Command1_Click()
Dim strTextLine As String
Dim intR As Integer
Dim strCat As String
Dim lngID As Long
Dim strReg As String
Dim strCity As String

intR = 1

Open "C:\Temp\YourTextFile.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, strTextLine
    Select Case intR
        Case 1
            strCat = Trim(strTextLine)
        Case 2
            lngID = CLng(strTextLine)
        Case 3
            strReg = Trim(strTextLine)
        Case 4
            strCity = Trim(strTextLine)
    End Select
    intR = intR + 1
    If intR > 4 Then
        Call InsertIntoDB(strCat, lngID, strReg, strCity)
        strCat = ""
        lngID = 0
        strReg = ""
        strCity = ""
        intR = 1
    End If
Loop
Close #1

End Sub

Private Sub InsertIntoDB(strC As String, lngI As Long, _
    strR As String, strCt As String)
Dim strSQL As String

strSQL = "INSERT INTO MyTable (Category, ID, Region, City) " _
    & " VALUES('" & strC & "', " & lngI & ","

If strR <> "" Then
    strSQL = strSQL & "'" & strR & ", "
Else
    strSQL = strSQL & "NULL, "
End If

If strCt <> "" Then
    strSQL = strSQL & "'" & strCt & ")"
Else
    strSQL = strSQL & "NULL)"
End If

Debug.Print strSQL

End Sub

Have fun.

---- Andy