READ FIXED LENGTH ASCII FORMAT

[Ann]

Hi All,

Its been a few years when I used to do this and feel stupid to ask. Can
anyone help by providing a sample to read fixed length ascii format file and
create access table.
I have several text file that is always 520 character length about(37
fields) I need to read quite often.
Additional if possible col 15 has a citeria that if col 15 is "a" then
create table "tbl1" col 15 = "b" create another table "tbl2" and so on....
I promise to save the sample for future use.
Thanks a lot to all.

Ann

 

[John Nurick]

Hi Ann,

If you click the Advanced... button in the Text Import Wizard you can
create and save an import specification with details of your 37 fields,
ready to use for future imports of files with the same layout.

Having done this, you can either use the wizard for future imports or
else the TransferText macro or (in VBA) DoCmd.TransferText.

In ordinary database work it's usually a very bad idea to create
multiple tables with the same structure on the basis of differences in
the data. Normally it is vastly better to leave all this data in a
single table, and then use queries to return subsets of the data when
required. I.e. instead of creating tbl1 for records where Column 15 =
"a", tbl2 for "b" and so on, just create a Select query that uses "a" as
a criterion in the corresponding field.

 

[Ann]

Thanks John, I understand what you are saying. The reason I have to do this
is because I have to email the diferrent table to different people and I
can't let them see each others data. I actually use to do it this way but the
file is over 2.5 millions records and it takes long time to do anything with
this huge table. I am going to try your solution. Thanks for the help. If you
have any other suggestions please let me know.

Thanks Again
Ann

 

[Ann]

Where is the import specification stored? Just Curious

 

[Ann]

Sorry to be so annoying It worked great. But still trying to make it
further automated. I have 9 files with almost million record in each file .
Again With the criteria in column 25 How can I create seperatre table based
on change in value of column 25. Once again Thanks a lot.

Ann

 

[John Nurick]

Import/export specs are stored in the (undocumented) system tables in
the database. For a documented, human-readable alternative, use a
schema.ini file instead.

 

[John Nurick]

Ann,

I still don't see the need for creating separate Access tables in this
database for each recipient. If you're going to email the data in text
files or Excel workbooks you can create them from the query/ies I
suggested.

I also don't feel confident I understand what you're trying to do. You
say you have 9 text files each containing about 1 million 520-character
records, i.e. about 500 megabytes each or over 4 gigabytes total. As I
understand it, each record has a field in column 15, or possibly column
25, which indicates where it needs to be sent.

When you say "column 15", do you mean (as usual with fixed-width files)
the 15th character of the 520 in the record, or the 15th *field* of the
37?

How do you map from the contents of this column or field to the email
address?

In order to find all the records that need to be sent to a given
recipient, do you have to read all the 9 input files, or are all the
records for a given recipient always contained in a single file?

I'd probably do something like this:

1) Create a query (qryRecipients) that returns all the different values
in column 15 (or 25 or whatever), each with the associated email
address. I.e. two fields, which I'll call Recipient and Email.

2) Create a parameter query (qryRecordsToSend) that returns all the
records from the textfile for a given recipient (i.e. a given value of
column 15), getting this parameter from a textbox (txtRecipient) on a
form.

3) Put a commandbutton on the same form, and in the button's Click event
procedure write code along these lines (pseudocode):

Dim rstR as DAO.Recordset
Dim strFileSpec As String 'name of file to export to

Set rstR = CurrentDB.OpenRecordset("qryRecipients")

Do Until rstR.EOF
Me.txtRecipient.Value = rstR.Fields("Recipient")
DoCmd.TransferText acExportFixed, "SpecName", _
"qryRecordsToSend", strFileSpec, True
Create email message to rstR.Fields("Email")
Attach file strFileSpec
Send message
Kill strFileSpec
rstR.MoveNext
Loop
rstR.Close

 

[Ann]

Thanks Again John. Your are the best. I am sorry about the confusion. Actualy
Field 25 id the field where the code changes and I wanted the new table. I am
talking about.
And File can contain multiple receipient in the same file. Also this Access
DB is in network drive and with Aceess security they can only see certain
table.
Beleive me this really helped me a lot. I guess I was further trying to be
greedy with automization and also small tables are easy to work with.
Thanks Again for all your help You are truly the best

Ann

 

[Ann]

Sorry to bother you John. I am trying to find the schema.ini files with no
luck. Can you tell me how can I take this schema.ini from one computer to
another. Also I changes the name of the db and now I cannot even find the
saved specs in Access.
Can you help one more time?

Thanks
Ann

 

[Douglas J. Steele]

Try going to
http://search.microsoft.com/search/search.aspx?view=msdn&st=a&c=0&s=1 and
doing a search on schema.ini