I need help with a text file

A

Al

I need to import a text file into access. the files is
large but the following is a sample of how it looks:
'''''''''''''''''''''''''''''''''''''''''
CLIA NUMBER,LABTYPE,FACILITY NAME,FACILITY
NAME2,STREET,STREET2,CITY,STATE, ZIP,ZIP2,PHONE
"01D0026356","10","SHELBY BAPTIST MEDICAL CENTER","","1000
FIRST STREET,
NORTH","","ALABASTER","AL","35007","","2056208902"
"01D0026428","10","WOODLAND COMMUNITY HOSPITAL
LAB","","1910 CHEROKEE
AVE SW","","CULLMAN","AL","35055","5502","2567393500"
"01D0026438","10","CULLMAN REGIONAL MED CTR/RESP CARE
DEP","","1912 AL
HIGHWAY 157","","CULLMAN","AL","35055","","2567372494"
"01D0026498","10","ST CLAIR REGIONAL HOSPITAL
LABORATORY","","2805 DR
JOHN HAYNES DRIVE","","PELL
CITY","AL","35125","","2053383301"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
the first 2 lines represent the column headers or field
names the every comma afterwards is a column separator
that lines the data with the field name in the first 2
lines respectively, ex:


CLIA NUMBER LABTYPE FACILITY NAME
01D0026356 10 SHELBY BAPTIST MEDICAL CENTER

Can someone help thanks
al
 
K

Ken Snell [MVP]

What type of help are you seeking? It's not clear from your post about which
you need specific advice? Is it the two-line headers? Something else?
 
A

Al

I need to see if there is a code that can help me import
the file with the data lined up correctly under the column
header. I used access import wizard but the data was all
mixed up in the table. I need to know if there is a
different way to import this txt file. It is very large to
fix manually.
thanks
 
K

Ken Snell [MVP]

This generic code should get you started. This particular code assumes that
the text file is comma-delimited (which yours is) and that the order of data
are the same as the field order in the table -- if


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableToWhichDataWillBeAppended", _
dbOpenDynaset, dbAppendOnly)
strFile = "C:\MyFolderName\NameOfFile.txt")
intFile = FreeFile()
' open the text file in order to read the data
Open "C:\MyFolderName\" & strFile As #intFile For Input
' discard the first two lines in the text file (they're your headers)
Line Input #intFile, strLine
Line Input #intFile, strLine
' append each two records from the text file to the table
' (read the first line of the record, and append those data,
' then read the second line and append those data)
Do While EOF(intFile) = False
Line Input #intFile, strLine
varArray = Split(strLine, ",")
rst.AddNew
rst.Fields("FieldName1").Value = _
varArray(0)
rst.Fields("FieldName2").Value = _
varArray(1)
' etc. for all fields in this record
Line Input #intFile, strLine
varArray = Split(strLine, ",")
rst.Fields("FieldName101").Value = _
varArray(0)
rst.Fields("FieldName102").Value = _
varArray(1)
' etc. for all fields in this record
rst.Update
Loop
Close #intFile
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
A

Al

Thank you so very much Ken, I used it and it worked.
Al
-----Original Message-----
This generic code should get you started. This particular code assumes that
the text file is comma-delimited (which yours is) and that the order of data
are the same as the field order in the table -- if


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset
Click to expand...
("TableToWhichDataWillBeAppended", _
 
Top