parse txt file to add to table fields

  • Thread starter Thread starter Rhea
  • Start date Start date
R

Rhea

I have a comma separated txt file. I need to insert each value (a number)
into a field in a table based on what the number is. The first number will
always go in to the same field, but if the next number is a 5 it will go in
the S5 field, 10 will go in to the S10 field etc. Each line may contain a
different number of numbers. What is the best way to do this? Thanks.
 
I have a comma separated txt file. I need to insert each value (a number)
into a field in a table based on what the number is. The first number will
always go in to the same field, but if the next number is a 5 it will go in
the S5 field, 10 will go in to the S10 field etc. Each line may contain a
different number of numbers. What is the best way to do this? Thanks.
Click to expand...

Well, your best bet would be to STOP, and rethink your table design. Storing
data in fieldnames as you're evidently doing is going to get you into no end
of trouble, this query being just one example of many that you will see!

What is the structure of your table? What's the logic behind storing a 5 in a
field named S5, and storing 10 in a frield named S10? Even if that *weren't* a
violation of good design, it would still be redundant: you're storing the same
information in two different ways!

If this is some sort of intermediate process please post back and explain -
you'll need some VBA code I suspect but it can be done.
 
but it can be done.

It can ALWAYS be done, if you want it bad enough. <g>

I most certainly do agree with John though, there is almost never a good
reason to do what you are trying to do. And I use 'almost never' only
because I was taught never to say never <scratches head>, otherwise I would
say it's never a good idea...

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain
 
Sorry, I meant to say that I am storing a yes or no in the field. I have 101
fields in the table - the first one is the primary key which is the
technologist's number, then a yes or no in each of 100 fields as to whether
they have that security level or not. I didn't think it was a good idea to
name a field with only a number so I called them S1 thru S100. I will get
rows in the text file like this:

1173,2,6,10,12,14,15,16,19,21,43,59,72,89
1045,2,4,6,10,12,14,15,16,19,20
1058,2,6,10,12,14,15,16,19,20,24
1043,2,6,10,12,14,15,19,20,24,26

The first number is always the tech code, the numbers that follow are the
security levels they have. So, if the second number is a "2" as in the first
row above I want to store a "yes" in the field "S2", and so on for the rest
of the numbers. I have not used access for over 10 years, I am using access
97. Thanks.
 
Sorry, I meant to say that I am storing a yes or no in the field. I have 101
fields in the table - the first one is the primary key which is the
technologist's number, then a yes or no in each of 100 fields as to whether
they have that security level or not. I didn't think it was a good idea to
name a field with only a number so I called them S1 thru S100. I will get
rows in the text file like this:

1173,2,6,10,12,14,15,16,19,21,43,59,72,89
1045,2,4,6,10,12,14,15,16,19,20
1058,2,6,10,12,14,15,16,19,20,24
1043,2,6,10,12,14,15,19,20,24,26

The first number is always the tech code, the numbers that follow are the
security levels they have. So, if the second number is a "2" as in the first
row above I want to store a "yes" in the field "S2", and so on for the rest
of the numbers. I have not used access for over 10 years, I am using access
97. Thanks.
Click to expand...

You should reconsider your table design. You have a Many (technologists) to
Many (security levels) relationship. The proper design for this is NOT to have
100 fields - someday you might need 125! What will you do then? Redesign your
table, all your queries, all your forms? In addition searching the 100 fields
will be a real hassle.

The proper design would be *three tables*: Technologists (I presume you have
this already); SecurityLevels, currently a 100 row table LevelAssignments with
this number as its primary key and probably a description of the meaning of
the level (007: licensed to kill; 099: licensed to open paperclip boxes under
supervision); and a third table with a field for the TechnologistID and a
field for SecurityLevel.

You would need to parse your comma separated input file into multiple records.
If technologist 1173 has thirteen different approvals, there would be 13 rows
for her ID, with each of the values.

You could link to or import the existing text file and parse it with a
Normalizing Union Query. Assuming that you have a bare generic linked table
with names Field1, Field2, Field 3, ..., Field101 you could use a query like

INSERT INTO LevelAssignments (TechologistID, SecurityLevel)
SELECT ID, Level
FROM
(SELECT Field1, Field2 FROM inputfile WHERE Field2 IS NOT NULL
UNION ALL
SELECT Field1, Field3 FROM inputfile WHERE Field3 IS NOT NULL
UNION ALL
SELECT Field1, Field4 FROM inputfile WHERE field4 IS NOT NULL
<etc etc through all 101 fields>
);
 
Back
Top