Import TXT file into Access

[joeynyandflorida]

I have to import a TXT file which has data that is not space or character
delimited. I have a schema provided to me about how the data should be
parsed. Example: Header is position 1-3 and its an alpha numeric field,
length of 3, required, ete. How can I import this file into Access and
parse the data as described in the schema?

 

[John Spencer]

Pardon me, but that sounds like it is position delimited also known as
fixed width text.

You should be able to use
== File: Get External Data: Import
== Choose text file as type
== Choose fixed width in the import wizard
== Double click whereever you need a new field to start
== Or Click the advanced button and set up the specification using the
schema you have been given.
== Save the specification for future use.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[joeynyandflorida via AccessMonster.com]

Pardon me, but that sounds like it is position delimited also known as
fixed width text.

You should be able to use
== File: Get External Data: Import
== Choose text file as type
== Choose fixed width in the import wizard
== Double click whereever you need a new field to start
== Or Click the advanced button and set up the specification using the
schema you have been given.
== Save the specification for future use.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

thanks that did allow me to import the data...however now Im getting a "Type
Conversion Failure" when the file is imported. There is a row called
"Filler" which breaks the header detail and trailer of the data file.

 

[John Spencer]

Sorry, I don't understand what you mean when you say
"There is a row called "Filler" which breaks the header detail and trailer of
the data file."

If there is a non-standard row in the text file at the beginning or the end
then you either need to open the text file and remove the row of non-standard
data or you need to write a custom VBA procedure to do the import and parsing
of the row or you need a custom VBA function to open the file and remove the
"bad" line(s).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[joeynyandflorida via AccessMonster.com]

Sorry for the confusion. The problem is that the header and trailer is
causing the data to be parsed incorrectly. I will remove the header and
trailer and try to import again.

Sorry, I don't understand what you mean when you say
"There is a row called "Filler" which breaks the header detail and trailer of
the data file."

If there is a non-standard row in the text file at the beginning or the end
then you either need to open the text file and remove the row of non-standard
data or you need to write a custom VBA procedure to do the import and parsing
of the row or you need a custom VBA function to open the file and remove the
"bad" line(s).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
[quoted text clipped - 24 lines]

Conversion Failure" when the file is imported. There is a row called
"Filler" which breaks the header detail and trailer of the data file.

 

[joeynyandflorida via AccessMonster.com]

The removal of the header and trailer did import the data correctly ....now
how do I automate the removal of the header and trailer?

Sorry for the confusion. The problem is that the header and trailer is
causing the data to be parsed incorrectly. I will remove the header and
trailer and try to import again.

Sorry, I don't understand what you mean when you say
"There is a row called "Filler" which breaks the header detail and trailer of

[quoted text clipped - 16 lines]

 

[John Spencer]

This isn't the cleanest code, but you can try something along these lines.

Public Sub fStripFirstAndLastLines()
Dim strPath As String
Dim iFileNo As Integer
Dim iOutNo As Integer
Dim strLine
Dim lLineCounter As Long
Dim LLineCount As Long

strPath = "C:\New Text Document"
iFileNo = FreeFile
Open strPath & ".txt" For Input As iFileNo

iOutNo = FreeFile
Open strPath & "A.txt" For Output As iOutNo

Do While Not EOF(iFileNo)
Line Input #iFileNo, strLine
LLineCount = LLineCount + 1
Loop

Close iFileNo
iFileNo = FreeFile
Open strPath & ".txt" For Input As iFileNo

Do While Not EOF(iFileNo)
Line Input #iFileNo, strLine
lLineCounter = lLineCounter + 1
If lLineCounter <> 1 And lLineCounter <> LLineCount Then
Print #iOutNo, strLine
End If

Loop


Close iOutNo
Close iFileNo

End Sub