By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Fixed Width Text File Import Programmtically

P: n/a
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.

Here's a sample of the data:

15473AB Cavity Mold D1 06/18/2002 10,000 3,500 1,000
15473A 50.17099.01

As you can see, the records are on two lines and, being fixed width, there
are no delimiters or text qualifiers present.

I've been importing these text files via Excel and using Excel VBA(which is a
long tedious process since the text files have more rows than Excel does.)

My question: is it possible to read a text file line by line and perform logic
on them in order to extract the records properly?

I can hold my own with VBA, but I don't even know where to begin on this one.
I have Access 97 and 2002. I do not have VB6, or C++, so I can't write any
utilities outside VBA.

Thanks,
Johnny Meredith
"On a quest to save time"
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Jet text import does not have a "forgiving" mode for badly formatted data.
The best thing you can do is to import every row into a one-column text
field (or memo if you need more than 255 characters), and then fix it all up
later with queries and VBA code.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.

Here's a sample of the data:

15473AB Cavity Mold D1 06/18/2002 10,000 3,500 1,000 15473A 50.17099.01

As you can see, the records are on two lines and, being fixed width, there
are no delimiters or text qualifiers present.

I've been importing these text files via Excel and using Excel VBA(which is a long tedious process since the text files have more rows than Excel does.)

My question: is it possible to read a text file line by line and perform logic on them in order to extract the records properly?

I can hold my own with VBA, but I don't even know where to begin on this one. I have Access 97 and 2002. I do not have VB6, or C++, so I can't write any utilities outside VBA.

Thanks,
Johnny Meredith
"On a quest to save time"

Nov 12 '05 #2

P: n/a
You could also use the Import Wizard to do this. When in the Tables tab of
the database window, choose File/Import and instead of pointing to a
database, choose .txt, .csv from the file filter box at the bottom. Follow
the steps that come next to bring the data into a new or existing table. I
recommend setting the minimum amount of restrictions (ie primary keys,
indexes etc) on the table data. Once it is imported with no errors, then you
can change the field properties to suit.

Mike Storr
www.veraccess.com
"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.

Here's a sample of the data:

15473AB Cavity Mold D1 06/18/2002 10,000 3,500 1,000 15473A 50.17099.01

As you can see, the records are on two lines and, being fixed width, there
are no delimiters or text qualifiers present.

I've been importing these text files via Excel and using Excel VBA(which is a long tedious process since the text files have more rows than Excel does.)

My question: is it possible to read a text file line by line and perform logic on them in order to extract the records properly?

I can hold my own with VBA, but I don't even know where to begin on this one. I have Access 97 and 2002. I do not have VB6, or C++, so I can't write any utilities outside VBA.

Thanks,
Johnny Meredith
"On a quest to save time"

Nov 12 '05 #3

P: n/a
"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.

Here's a sample of the data:

15473AB Cavity Mold D1 06/18/2002 10,000 3,500 1,000 15473A 50.17099.01

As you can see, the records are on two lines and, being fixed width, there
are no delimiters or text qualifiers present.

I've been importing these text files via Excel and using Excel VBA(which is a long tedious process since the text files have more rows than Excel does.)

My question: is it possible to read a text file line by line and perform logic on them in order to extract the records properly?

I can hold my own with VBA, but I don't even know where to begin on this one. I have Access 97 and 2002. I do not have VB6, or C++, so I can't write any utilities outside VBA.

Thanks,
Johnny Meredith
"On a quest to save time"

If you find that writing an import routine to be a less tedious than using
your current method, then this is the way to go. You need to explain how
one can be sure of what text makes up a record since I believe a record may
be spread over 2 lines, so how can you tell by looking at the data?
You also need to think carefully about error handling - what errors should
cause the import to stop and which could be handled. For example if a
required numeric field in a table is about to have the value "-", because
some Excel user typed it in to mean nothing, not applicable, zero, or
whatever. Your code simply replace this for zero without any warning, log
this error to an errors table but continue, or abort the import since the
value is not acceptable.
If you really have no idea where to start, then here is the beginning of an
import routine which has a form "frmImport" with a button "cmdImport".
****NB**** This is only to give you an idea of structure and the example
assumes that the end of a record has the character ";" (wouldn't it be nice
if it was that easy). It also does not contain code to split the record
line in to different fields but you could use functions like split, trim,
left, right, mid, etc to break up the line properly. It also does not
contain any code to log errors to an errors table, but if you can code, then
you should be able to adapt this.
Private Sub cmdImport_Click()

On Error GoTo Err_Handler

Dim strPath As String
Dim intFile As Integer
Dim strFileLine As String
Dim strRecordLine As String
Dim strTableName As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

strPath = "C:\Test.txt"

strTableName = "tblImport"

intFile = FreeFile()

Open strPath For Input As #intFile

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)

While Not EOF(intFile)

Line Input #intFile, strFileLine

If Len(strFileLine) > 0 Then

strRecordLine = strRecordLine & strFileLine

If Right$(strRecordLine, 1) = ";" Then

rst.AddNew
rst("F1") = strRecordLine
rst.Update

strRecordLine = ""

End If

End If

Wend
Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

If Not rst Is Nothing Then
rst.Close
End If

Close #intFile

Exit Sub

Err_Handler:

Select Case Err.Number

Case 0
Resume Exit_Handler

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Sub
Nov 12 '05 #4

P: n/a
I see where you're going with this, but I want to ask a somewhat related
question. I've read some stuff from KB (see link earlier in this thread)
with respect to random access. Particularily, using Type statements to
identify records (this discussion is also relevant to binary access.)
Here's my "simple" question before I proceed down this path: when using
random access or binary access, can I "pause" the reading in order to
determine whether on not I want the particular line in the text file.

This will be easier with an example. Consider the following text file
excerpt (interpret all this character as actual characters in the text
file:

12850 This is the header
field1 field2 field3
field4 field5
------ ------ ------
145AB Asset 10.00
145AB1 50.17039.01
12850 This is the header again
etc...

At run time, I want to evaluate each character that comes out of the text
file. The "12850" identifies the beginning line of a header, and the header
proceeds for the next x lines, then data on two rows, then header, etc.
Notice how the header wraps over two lines to accomodate the two-line record.

Anyway, do you think it is possible to set up a Type statement for the record,
but tell Access when it actually encounters a record? Are there such things
as "bookmarks" using this technique that allow you to keep your place in
the text file as you proceed?

(Note - this heap of garbage comes out of an ancient version of JDE running
on AS-400. We are working on a solution that avoids that text file output,
but some serious roadblocks keep comming up. So pray for me.)

Thanks for your patient reading of this long post.
Johnny Meredith
Nov 12 '05 #5

P: n/a
"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
I see where you're going with this, but I want to ask a somewhat related
question. I've read some stuff from KB (see link earlier in this thread)
with respect to random access. Particularily, using Type statements to
identify records (this discussion is also relevant to binary access.)
Here's my "simple" question before I proceed down this path: when using
random access or binary access, can I "pause" the reading in order to
determine whether on not I want the particular line in the text file.

This will be easier with an example. Consider the following text file
excerpt (interpret all this character as actual characters in the text
file:

12850 This is the header
field1 field2 field3
field4 field5
------ ------ ------
145AB Asset 10.00
145AB1 50.17039.01
12850 This is the header again
etc...

At run time, I want to evaluate each character that comes out of the text
file. The "12850" identifies the beginning line of a header, and the header proceeds for the next x lines, then data on two rows, then header, etc.
Notice how the header wraps over two lines to accomodate the two-line record.
Anyway, do you think it is possible to set up a Type statement for the record, but tell Access when it actually encounters a record? Are there such things as "bookmarks" using this technique that allow you to keep your place in
the text file as you proceed?

(Note - this heap of garbage comes out of an ancient version of JDE running on AS-400. We are working on a solution that avoids that text file output, but some serious roadblocks keep comming up. So pray for me.)

Thanks for your patient reading of this long post.
Johnny Meredith


I don't think I would try setting up a type unless I had pretty regular data
without any line breaks in it:
*001abcr*002def*003ghi ... etc
This might help me if my program actually used this file as a datasource,
allowing me to move from one record to the next. But in your case the
requirement is a bit different and I'd guess that reading the text file
output line by line would be a better start.
The first example I gave showed multiple lines being read and combining them
into one line. Once you have built up a line (that is a string containing
all the data from one record with no line breaks) you can decide if you want
to import the record or not and if so, how to break it up into its separate
fields.
Your data seems to have spaces and commas in it as well as these line breaks
which can be a right pain. I would adjust my routine to try and get the
data (at least as a first stage) as shown below. Each record on one line,
values enclosed in quotes and separated by commas.
"15473AB","Cavity
Mold","D1","06/18/2002","10,000","3,500","1,000","15473A","50.17099. 01"
"15473AB","Cavity
Mold","D1","06/18/2002","10,000","3,500","1,000","15473A","50.17099. 01"

I would be pretty sure that this is possible with not too much coding and is
a great first step. The next bit of validating each field might be easier,
or even unnecessary, unless you have some dates like "06/18/2002" and others
like "06/??/2002" which is the sort of thing you get from importing Excel
spreadsheets which the HR Department has got their hands on<g>

Fletcher

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.