Parsing Text File 2

[steve229922]

I'm struggling to write a procedure that will:

import a .txt file into access and then parse the data into fields. the data in the .txt file looks like this--

LAST NAME: Smith
FIRST NAME: PAUL
MIDDLE NAME: JOSEPH
ADDRESS 1: 318 Palisade Avenue
ADDRESS 2:
CITY: Newark
STATE: NJ
ZIP CODE: 06285
ADMISSION DATE: 8/1/2003

LAST NAME: Franks
FIRST NAME: AMANDA
MIDDLE NAME:
ADDRESS 1: 1245 Liberty Ave. #457
ADDRESS 2:
CITY: Pittsburgh
STATE: IL
ZIP CODE: 15222
ADMISSION DATE: 8/1/2003

LAST NAME: Washington
FIRST NAME: PETER
MIDDLE NAME: M
ADDRESS 1: 554 Pompton Turnpike, Apt Q
ADDRESS 2:
CITY: Pompton Plains
STATE: NJ
ZIP CODE: 07444
ADMISSION DATE: 8/1/2003

any samples of code, suggestions very much appreciated!!! thanks!

 

[SkipVought]

Hi steve229922,

Please post the code that you are struggling with.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[steve229922]

thanks for the reply....
here is some code i found that attempts to do this. but as a non-experienced vba programmer, i not even sure if this is the correct way to do it. and i'm having a little trouble folowing it...
Public Function getitdone()
Dim lFileHandle As Long
Dim sFileName As String
Dim sLine As String
Dim sData() As String
Dim sFieldName As String
Dim sFieldData As String
Dim sSQL As String

lFileHandle = FreeFile()
sFileName = "person1.txt"

Open sFileName For Input As lFileHandle

Do While Not EOF(lFileHandle)

Line Input #lFileHandle, sLine

sData = Split(sLine, ":")

sFieldName = Trim$(sData(0))
sFieldData = Trim$(sData(1))

sSQL = "INSERT INTO table1 ([" & sFieldName & "]) VALUES ('" & sFieldData & "')"

CurrentDb.Execute sSQL, dbFailOnError
Loop


End Function

 

[SkipVought]

Here's an Excel solution.

1. Import into excel, Parsing by : delimiter -- this gives you 2 columns of data; column A is the FIELD TITLES and column B is FIELD DATA

2. Insert a ROW in ROW 1

3. Copy on set of FIELD TITLES and Edit/Paste Special - Transpose in ROW 1 Column D1

4. Enter these formulas

in D2
[tt]
=TRIM(IF($A2=D$1,$B2,""))
[/tt]
in E2
[tt]
=TRIM(IF($D2="","",IF($A2=E$1,$B2,OFFSET($B2,COLUMN()-4,0,1,1))))
[/tt]
and copy this formula thru column L

5. Copy for formulas in Row 2 down thru the rows of source data.

6. select columns D:L - Copy - Edit/Paste Special - Values - Data/Sort

7. Copy the formatted table to a new sheet

8. Save Excel file

9. Import Excel file to Access

VOLA!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Golom]

Your code is inserting each line from your text file into a new record in the table ... probably with disastrous results since you won't be defining a primary key for most of them. Here's some skeleton code that buffers several records and then does an insert of all fields.
[tt]
Public Function getitdone()
Dim lFileHandle As Long
Dim sFileName As String
Dim sLine As String
Dim sData() As String
Dim sFieldName As String
Dim sFieldData As String
Dim sSQL As String
Dim bNames As String
Dim bValues As String

lFileHandle = FreeFile()
sFileName = "person1.txt"

Open sFileName For Input As lFileHandle

Do While Not EOF(lFileHandle)

Line Input #lFileHandle, sLine
If Len(Trim$(sLine)) > ) Then
sData = Split(sLine, ":")
sFieldName = Trim$(sData(0))
sFieldData = Trim$(sData(1))
If Len(bNames) = 0 then
bNames = "[" & sFieldName & "]"
bValues = "'" & sFieldData & "'"
Else
bNames = bNames & ", [" & sFieldName & "]"
bValues = bValues & ",'" & sFieldData & "'"
End If
Else
sSQL = "INSERT INTO table1 (" & bNames & ") VALUES (" & bValues & ")"
CurrentDb.Execute sSQL, dbFailOnError
bNames = ""
bValues = ""
End If
Loop

[/tt][tt]' Handle the last record if it wasn't followed by a blank.[/tt][tt]
If Len(bNames) > 0 then
sSQL = "INSERT INTO table1 (" & bNames & ") VALUES (" & bValues & ")"
CurrentDb.Execute sSQL, dbFailOnError
End If

End Function
[/tt]

 

[Golom]

Sorry ... typo ... this
[tt]
If Len(Trim$(sLine)) > ) Then
[/tt]
should be
[tt]
If Len(Trim$(sLine)) > 0 Then
[/tt]