Parsing a Comma Delimited Values File

[silva]

I plan on creating a DB using a .csv file that's automatically generated.
Since commas are only being used as delimiteers and nothing else, it
shouldn't represent a problem intially populating a table. However, one field
(call it Field A) contains three field's worth of information, some of which
is extraneous, and another field (we'll call it Field B) contains two field's
worth of information, again some being extraneous. For example, I'll use @ to
designate an alpha character and # as a numeral:

Field A
@@@ ##-@@@@@-@@ ##
@@@ #-@@@@-@@@@-@@ ##

Field B
## @@@@@@@@
# @@@@@@@@

In Field A, the first and last dash is essentially a delimiter. At times
there is one in the middle, but it's part of the text of the middle value,
and using a dash as a delimiter to seperate the data causes problems. The
first value is extraneous as it has another field. This data is displayed
three times. Anyway, for Field A, all I want is the data after the last dash,
at the end of the field, which is always two letters, a space, then two
digits.

In Field B, I want to remove the digits that begin the value each time. They
are unnecessary, and since I have no access to what creates this text
document, I can't remove it before it is generated. How do I get rid of the
preceding digits?

If it helps, Field A's data is Location #-Location Name-Terminal #, Field
B's data is Location # Location Name. Annoyingly enough, what I'll call Field
C, is nothing but Location #.

 

[KARL DEWEY]

for Field A, all I want is the data after the last dash, at the end of the
field, which is always two letters, a space, then two digits.
Just use Right([FieldA], 5)
Trim(Right([FieldB], Len([FieldB])-Len(Val([FieldB]))))

 

[silva]

Now, just so I'm certain... I enter this into Expression Builder for a text
box? Or is this VBA code?

field, which is always two letters, a space, then two digits.
Just use Right([FieldA], 5)
Trim(Right([FieldB], Len([FieldB])-Len(Val([FieldB]))))

--
KARL DEWEY
Build a little - Test a little

I plan on creating a DB using a .csv file that's automatically generated.
Since commas are only being used as delimiteers and nothing else, it
shouldn't represent a problem intially populating a table. However, one field
(call it Field A) contains three field's worth of information, some of which
is extraneous, and another field (we'll call it Field B) contains two field's
worth of information, again some being extraneous. For example, I'll use @ to
designate an alpha character and # as a numeral:

Field A
@@@ ##-@@@@@-@@ ##
@@@ #-@@@@-@@@@-@@ ##

Field B
## @@@@@@@@
# @@@@@@@@

In Field A, the first and last dash is essentially a delimiter. At times
there is one in the middle, but it's part of the text of the middle value,
and using a dash as a delimiter to seperate the data causes problems. The
first value is extraneous as it has another field. This data is displayed
three times. Anyway, for Field A, all I want is the data after the last dash,
at the end of the field, which is always two letters, a space, then two
digits.

In Field B, I want to remove the digits that begin the value each time. They
are unnecessary, and since I have no access to what creates this text
document, I can't remove it before it is generated. How do I get rid of the
preceding digits?

If it helps, Field A's data is Location #-Location Name-Terminal #, Field
B's data is Location # Location Name. Annoyingly enough, what I'll call Field
C, is nothing but Location #.

 

[KARL DEWEY]

Open a query in design view, select the table, and enter in the Field row of
the grid putting an alais field name followed by a colon ahead of it.
--
KARL DEWEY
Build a little - Test a little

Now, just so I'm certain... I enter this into Expression Builder for a text
box? Or is this VBA code?

for Field A, all I want is the data after the last dash, at the end of the

field, which is always two letters, a space, then two digits.
Just use Right([FieldA], 5)

How do I get rid of the preceding digits?

Trim(Right([FieldB], Len([FieldB])-Len(Val([FieldB]))))

--
KARL DEWEY
Build a little - Test a little

I plan on creating a DB using a .csv file that's automatically generated.
Since commas are only being used as delimiteers and nothing else, it
shouldn't represent a problem intially populating a table. However, one field
(call it Field A) contains three field's worth of information, some of which
is extraneous, and another field (we'll call it Field B) contains two field's
worth of information, again some being extraneous. For example, I'll use @ to
designate an alpha character and # as a numeral:

Field A
@@@ ##-@@@@@-@@ ##
@@@ #-@@@@-@@@@-@@ ##

Field B
## @@@@@@@@
# @@@@@@@@

In Field A, the first and last dash is essentially a delimiter. At times
there is one in the middle, but it's part of the text of the middle value,
and using a dash as a delimiter to seperate the data causes problems. The
first value is extraneous as it has another field. This data is displayed
three times. Anyway, for Field A, all I want is the data after the last dash,
at the end of the field, which is always two letters, a space, then two
digits.

In Field B, I want to remove the digits that begin the value each time. They
are unnecessary, and since I have no access to what creates this text
document, I can't remove it before it is generated. How do I get rid of the
preceding digits?

If it helps, Field A's data is Location #-Location Name-Terminal #, Field
B's data is Location # Location Name. Annoyingly enough, what I'll call Field
C, is nothing but Location #.

 

[Graham Wideman [Visio MVP]]

Silva:

I agree with KARL, but I'd elaborate a little.

I would recommend considering doing this in two steps:

1. Import the data as-is into a "raw import" table.

2. Use a query which you can refine as needed to copy/append the data,
suitably parsed, from the raw table into another table.

This way, you can audit any mistakes or messed up data that arise, and do
some cycles of refinement on your import strategy.

As for functions you can use: Basically within Access SQL you can actually
use any VBA functions, so Left, Right and Mid are obvious ones.

However, you can also use VBA functions that you write in a module:

function MyFunc(InputString as string): string;
Begin
MyFunc = Mid(InputString, 3, 4) ' simple example
end function

then in the query design grid:

MyDigestedField: MyFunc([FieldA])

So if your fields have complicated variable formats, you can pass the field
to MyFunc, and have more complicated code there to figure out where the
delimiters are and parse it and produce a desired result. (InStr and
InStrRev are often handy, though sometimes you need to write something more
extensive.)

Hope that helps,

Graham

 

[silva]

Thanks guys. Using a combination from both of your suggestions, my data
displays exactly as it needs to.

Karl, your suggestion for Field A works just like I need it to. Graham, your
idea about using a function helped me get exactly what I needed for Field B,
since Karl's idea worked, but not for all pieces of data entered. I used both
ideas with Karl's suggestion for creating fields defined by expressions
within the query.