I need help with a text file

  • Thread starter Thread starter Al
  • Start date Start date
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
 
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?
 
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
 
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
 
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", _
 
Back
Top