Format on data to import to Access tables? (I need your advice)

[Niklas Östergren]

Good morning!

I´m in that luxury situation that I can format the data which need to be
imported into my application the way I want. I´m developing a db for storing
member data about members in our assosiation. Like names, addresses,
membership types but allso family members names and DoB:s.

Right now I´m having a dialoge with the webmaster of our website because
it´s possible to apply for a membership through our website. The diskussion
right now is exactly how shall the data that I get from this website be
formatted so I can import it into my db?

In my db I have 2 table for this purpose <tblMemberApplyRegistration> and
<tblFamilyMemberRegistration> with a ONE -> MANY relation. These tables is
only used to import the data to and also used in a wizard that I allready
have created with several forms.

The data, from the website, is today sent over with e-mail but my intention
is to ask the webmaster if he can´t send it as a attached textfile so the
user of my application can save the attached textfile in a folder only for
this and then, through my application, selecte the file to be imported (by
using Save/Open Dialoge). The data will then be imported into the two tables
and I start the wizard so the user can select further options.

Now to my problem:

Since I havn´t done this a lot before I´m a little bit unsure which way is
the best to go. I intend to use <DoCmd.TransfereText> to do this, I have
used this before.

The data that I need to import can look like this:

FirstName,LastName,DoB....etc.
Niklas,Östergren,041224

This is if the person applying for membership only for ONE person. But how
shall the data be formatted in the textfield if he applyés for membership
for his whole family? The data needs to go in 2 different tables (to start
with anyway). The "applyer´s" data (Name, DoB, Addreses etc.) in
<tblMemberApplyRegistration> and the family members names, DoB in table
<tblFamilyMemberRegistration>.

If I use acImportDelim or any of the other transfere types for importing
data I don´t know which data in the attached textfile to import into which
table, or?

I know that some of you now are going to mention that the names and DoB´s
needs to go into the same tables no matter if it´s family members on the
"applyer´s" names. In the end it will. What is actually happening in my
application is that I store ALL names, DoB´s in table
<tblFamilyMemberRegistration>, including the "applyer´s". But when the user
step´s on to the next form inmy wizard I copy the data into the real tables
<tblPerson> (Names,DoB) which have a ONE -> MANY relation to
<tblPersonAddress> in which I store the address for all people. And since a
family lives on ONE address (the have to to be able to join as a family
member in our assosiation it has to do with dispatches) I only need to
change the address in one place.

<tblPerson> is also related to <tblFamilyMembers> with a ONE -> MANY
relation. In this table I store primary key for each person whco is a family
member and which data is stored in <tblPerson>. But also primary key for the
membership that each person have (stored in <tblMember>). The family is then
linked together in this table with the main member´s MemberNo, which is the
person that applyed for the membership.

I recon that my db is petty much normalized so no I hope that I have
explaned the information needed to be able to help me out with this problem
of HOW the data shall be formatted for me to be able to import it in the
correct tables?

TIA!
// Niklas

 

[Niklas Östrergren]

Maby I wrote to much for you to read!? If so I´m sorry so I´ll try to be a
little bit more specific here with my Q.

I´d like to import data from a textfile into two different tables in my db.
How shall the data in the textfile be formatted for me knowing which data to
go in which table in my db?

I intend to use DoCmd.TransfereText to import this data and i know of fixed
import and delimeter import but that doesn´t tell me which data to import
where, or?

Is there a better way of doing this than to use TransfereText? Or is there a
better form to import from than a textfile?

The data I´d like to import is "applyer data":
Niklas,Östergren,041224

This data is allways ONLY one record. ONE FirstName, ONE LastName etc.


The second data, which is recived in the same textfile (or what ever
filetype I want, allmost anyway), is the names of family members:

Niklas,Östergren,041224
Erika,Svensson,041223
Magnus,Svensson,041231
etc.

This data, as you understand, can contain names and other data for 0 (zero)
to as many records as a person can produce children during a lifetime. And I
don´t know how many it is from time to time.

Some of the data is the same as for the "applyer" and my Q is how shall it
be seperated in the textfile so I know which data to import to which table
in my application?

The second Q is:
Is there a better way to do this then using DoCmd.TransfereText?

The last Q is:
Shall I ask to get the data in any other media then a textfile? If so, which
format?

TIA!
// Niklas

 

[Ken Snell [MVP]]

My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a | delimiter to
separate the data into fields; that will make it easy to parse out the data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.
--

Ken Snell
<MS ACCESS MVP>

 

[Niklas Östergren]

OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:

FirstName;LastName;Gender;DoB;Street;ZipCode;Country;PhoneHome;PhoneWork;Pho
neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541
05;Sweden;031-896566;;[email protected]
Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and the
other row´s shall go in table "tblFamilyMemberRegistration". Then when the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables and
delete all records in these two tables to make it possible for the user to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize for
it!

Thank´s for helping out!
// Niklas

 

[Ken Snell [MVP]]

If you import the entire line from the text file into one field in a
temporary table, you then can use a query to split out the data.

This can be done using "parsing" functions (Left, InStr, Mid, Right)
directly in expressions, which is a bit tricky, or you can use a VBA
function to parse the data into separate fields using the Split function and
then return the desired data to the query. For example, for the latter
example, use a public function similar to this:

Public Function SplitMyImportedData(strOriginalString As String, _
strDelimiter As String, intFieldNumber As Integer)
Dim varArray As Variant
varArray = Split(strOriginalString, strDelimiter)
SplitMyImportedData = varArray(intFieldNumber - 1)
End Function

You then would call this function for each field in the append query. For
example, to get the value of the first field, the "Field:" cell would
contain
Field1: SplitMyImportedData([MemoFieldName], ";", 1)


--

Ken Snell
<MS ACCESS MVP>

OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:

FirstName;LastName;Gender;DoB;Street;ZipCode;Country;PhoneHome;PhoneWork;Pho
neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541
05;Sweden;031-896566;;[email protected]
Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and the
other row´s shall go in table "tblFamilyMemberRegistration". Then when the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables and
delete all records in these two tables to make it possible for the user to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize for
it!

Thank´s for helping out!
// Niklas

My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a | delimiter to
separate the data into fields; that will make it easy to parse out the data
in your append query - or via an import spec if you decide to use that
method to parse the data immediately.

be

a my
db. data there And

shall

it

to

be

tables so
the only
for imported
(by way But
how

 

[Niklas Östergren]

OK!

Thank´s a lot for helping out Ken!

I´ll look closer into this and try to apply it in my application. Now I have
something to do for a while!

Thank´s a lot!
// Niklas

If you import the entire line from the text file into one field in a
temporary table, you then can use a query to split out the data.

This can be done using "parsing" functions (Left, InStr, Mid, Right)
directly in expressions, which is a bit tricky, or you can use a VBA
function to parse the data into separate fields using the Split function and
then return the desired data to the query. For example, for the latter
example, use a public function similar to this:

Public Function SplitMyImportedData(strOriginalString As String, _
strDelimiter As String, intFieldNumber As Integer)
Dim varArray As Variant
varArray = Split(strOriginalString, strDelimiter)
SplitMyImportedData = varArray(intFieldNumber - 1)
End Function

You then would call this function for each field in the append query. For
example, to get the value of the first field, the "Field:" cell would
contain
Field1: SplitMyImportedData([MemoFieldName], ";", 1)


--

Ken Snell
<MS ACCESS MVP>

OK!

But how do I seperate which data to go in which table if the only thing
seperating the data in the textfile is semicólon´s?

This is the data I need to repetadly imprt to my db:

FirstName;LastName;Gender;DoB;Street;ZipCode;Country;PhoneHome;PhoneWork;Pho

neMobile;EmailAddress etc.
Niklas;Östergren;Male;041224;Uperstreet 34;541
05;Sweden;031-896566;;[email protected]
Johan;Östergren;Male;041228
Erika;Östergren;Female;041231

The first row of data should go in ONE table "tblNewMemberShipEntry" and the
other row´s shall go in table "tblFamilyMemberRegistration". Then when the
wizard starts I copy [FirstName], [LastName], [Gender] and [DoB] to
"tblFamilyMemberRegistration" from table "tblNewMemberShipEntry" and set
field [tblFamilyMemberRegistration.mainMember] = True for that record.

When the wizard is finished I copy nessesary data to the final tables and
delete all records in these two tables to make it possible for the user to
import another memberapply.

So I still doesn´t know how to sort out which data from a delimeter textfile
to import in which table?

I hope I make myself cleare about my problem here!? If not I applogize for
it!

Thank´s for helping out!
// Niklas

My initial impression is that you should get the data in a text file. I
would import the data as a single field for each record in the text file.
Then use append queries to parse the data and copy it into the permanent
table.

If your web designer can do it, I'd recommend that he use a |

delimiter

to

to

be lifetime.
And shall to website
be which
way I
have on
the in
my

need

to

is

a key
for family
is which
is in
the