VBA to change record orientation of imported data

[Desilu via AccessMonster.com]

We’re getting an electronic file from a company that lists each record
vertically. A simple example of two dummy records would be (hair color, eye
color, age)

Red
Blue
43
Blond
Brown
35

Can VBA code be written to analyze these vertical records, identifying each
complete record (hair color, eye color, age) and turning them horizontally
and storing in a table which can be queried?

Thanks.
Desilu

 

[Douglas J. Steele]

It's possible to write code to open the file, assign the first value read to
a HairColour variable, the second value read to an EyeColour variable and
the third value read to an Age variable, then write an Insert statement
after the third value's been read. In fact, all you really need to do is add
the values to the SQL statement as you read them.

For instance, if you're talking about a simple text file, you could do
something like:

Dim dbCurr As DAO.Database
Dim intFile As Integer
Dim intLineCount As Integer
Dim strFile As String
Dim strSQL As String
Dim strVariable As String

Set dbCurr = CurrentDB()

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
intLineCount = 0
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strVariable
Select Case intLineCount
Case 1
strSQL = "INSERT INTO TableName (HairColour, EyeColour, Age) " & _
"VALUES('" & strVariable & "', '"
intLineCount = 2
Case 2
strSQL = strSQL & strVariable & "', "
intLineCount = 3
Case 3
strSQL = strSQL & strVariable & ")"
dbCurr.Execute strSQL, dbFailOnError
intLineCount = 0
End Select
Loop
Close #intFile
Set dbCurr = Nothing

 

[Desilu via AccessMonster.com]

Thank you Douglas. I'm going to study your code and try to apply it. I'm
hoping I can come back with any questions I might have.

Thanks.
Desilu

It's possible to write code to open the file, assign the first value read to
a HairColour variable, the second value read to an EyeColour variable and
the third value read to an Age variable, then write an Insert statement
after the third value's been read. In fact, all you really need to do is add
the values to the SQL statement as you read them.

For instance, if you're talking about a simple text file, you could do
something like:

Dim dbCurr As DAO.Database
Dim intFile As Integer
Dim intLineCount As Integer
Dim strFile As String
Dim strSQL As String
Dim strVariable As String

Set dbCurr = CurrentDB()

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
intLineCount = 0
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strVariable
Select Case intLineCount
Case 1
strSQL = "INSERT INTO TableName (HairColour, EyeColour, Age) " & _
"VALUES('" & strVariable & "', '"
intLineCount = 2
Case 2
strSQL = strSQL & strVariable & "', "
intLineCount = 3
Case 3
strSQL = strSQL & strVariable & ")"
dbCurr.Execute strSQL, dbFailOnError
intLineCount = 0
End Select
Loop
Close #intFile
Set dbCurr = Nothing

Here's an addtional comment:

[quoted text clipped - 26 lines]

 

[Guest]

Hi

I am still learning to use VBA for Access and going through the code, I
don't understand few things.

1. Line Input #intFile, strVariable
I understand that it is reading the content of #intFile into strVariable.
But "Line Input" I have not seen this method before. Could not find any
helpful documentation on MSDN either.

2. intLineCount = 0
I don't know how it is going to go into the SELECT CASE

Should not this be initialized to 1 and then incremented and then
reinitialized to 1 once it has reached 3?

Thank you Douglas. I'm going to study your code and try to apply it. I'm
hoping I can come back with any questions I might have.

Thanks.
Desilu

It's possible to write code to open the file, assign the first value read to
a HairColour variable, the second value read to an EyeColour variable and
the third value read to an Age variable, then write an Insert statement
after the third value's been read. In fact, all you really need to do is add
the values to the SQL statement as you read them.

For instance, if you're talking about a simple text file, you could do
something like:

Dim dbCurr As DAO.Database
Dim intFile As Integer
Dim intLineCount As Integer
Dim strFile As String
Dim strSQL As String
Dim strVariable As String

Set dbCurr = CurrentDB()

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
intLineCount = 0
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strVariable
Select Case intLineCount
Case 1
strSQL = "INSERT INTO TableName (HairColour, EyeColour, Age) " & _
"VALUES('" & strVariable & "', '"
intLineCount = 2
Case 2
strSQL = strSQL & strVariable & "', "
intLineCount = 3
Case 3
strSQL = strSQL & strVariable & ")"
dbCurr.Execute strSQL, dbFailOnError
intLineCount = 0
End Select
Loop
Close #intFile
Set dbCurr = Nothing

Here's an addtional comment:

[quoted text clipped - 26 lines]

Thanks.
Desilu

 

[Douglas J. Steele]

Hi

I am still learning to use VBA for Access and going through the code, I
don't understand few things.

1. Line Input #intFile, strVariable
I understand that it is reading the content of #intFile into strVariable.
But "Line Input" I have not seen this method before. Could not find any
helpful documentation on MSDN either.

Don't know why Line Input # isn't showing up in an MSDN search, but the
information in
http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D6/S5B260.asp
is still valid.

2. intLineCount = 0
I don't know how it is going to go into the SELECT CASE

Should not this be initialized to 1

Sorry, yes, it should have been intialized to intLineCount = 1

and then incremented and then reinitialized to 1 once it has reached 3?

You could do it that way, but to me that doesn't appear any better than what
I'm doing. (When Case is 1, I'm setting it to 2 after I'm done, when Case is
2, I'm setting it to 3 after I'm done and when Case is 3, I'm setting it to
1 after I'm done)