[AccessD] exporting one to many tables into flat file

[John R Bartow]

I have one option. Create a csv with stated fields:  
Record ID, first name, last name, first and last name, prefix, suffix,
title/role, department, background, organization name, Home address line 1,
Home address line 2, Home address line 3, Home city, Home state/province,
Home postal code, Home country, Work address line 1, Work address line 2,
Work address line 3, Work city, Work state/province, Work postal code, Work
country, Postal address line 1, Postal address line 2, Postal address line
3, Postal city, Postal state/province, Postal postal code, Postal country,
Other address line 1, Other address line 2, Other address line 3, Other
city, Other state/province, Other postal code, Other country, Home phone,
Home phone extension, Fax phone, Fax phone extension, Mobile phone, Mobile
phone extension, Pager phone, Pager phone extension, Work phone, Work phone
extension, Work Email, 
Personal Email, Other Email, Work IM, Personal IM, Other IM, Work Website,
Personal Website, Other Website, Contact Tag List, Contact Tag 1, Contact
Tag2, Contact Tag3, Contact Tag4, Contact Tag5, Contact Tag6, Contact Tag7,
Contact Tag8, Contact Tag9...

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Monday, March 20, 2017 4:43 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] exporting one to many tables into flat file

What's the db organization of the target database?

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
John R Bartow
Sent: Monday, March 20, 2017 12:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] exporting one to many tables into flat file
Importance: High

This would actually add a field for every organization that is currently
related. The scheme is to add Tag1, Tag2, Tag3, etc.

I've done a lot of flat file to relational db migrations and quite a few
normalizations of weak RDMSs but I haven't had to do this before. I started
on System 36 which was a predecessor to the AS400 so that's my reference
point. That's what they used to do on those systems - "tag" everything in a
new field. I hated that idea. It hurts my brain to think this way which is
why I'm asking.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Monday, March 20, 2017 9:54 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] exporting one to many tables into flat file

1toM is hard to represent in a flat file.  The many should, in theory be
strung out in columns but the problem is how many columns to allow for? For
a CSV file the problem is not as great because you can just create a string
of the primary fields, and keep on concatenating the Many values (the
Organizations) to the end of the string, separated by commas. You should end
up with a nice CSV file with the records each containing a different number
of fields.  

Which of course, means on the import, you need to parse the string and
extract the n number of organizations.  Since it's a CRM app with , I
assume, some kind of import function that may be problematical since an
import function typically wants to see a consistent number of fields in an
import file.

If the CRM app is based on a database into which you can push data then a
stand-alone importer would work.  But, of course, at that point you could
just push the data right from the MDB into the CRM database.  Is that an
option?

If, however, you need to use the CRM imported, and the importer needs a
consistent record with the same number of fields in the same place, , then I
think your solution is to create an export record with one Organization per
record.  Would that work?

HTH


Rocky Smolin
Beach Access Software
760-683-5777
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin
 



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
John R Bartow
Sent: Sunday, March 19, 2017 9:50 PM
To: DBA-Access
Subject: [AccessD] exporting one to many tables into flat file
Importance: High

I have a project where I need to export some of the data from a relational
database (MDB) to a comma delimited file for import into web based CRM
program which utilizes a flat file. The client data will now have one row
with a "tag" field for each organization that it is related to (some of
these clients are linked to hundreds of organizations). I'm l looking for
suggestions on how best to approach this:

 

Client 1toM > Phone > Mto1 PhoneType

Client 1toM > ClientOrg <Mto1 Organization

 

Thanks in advance,

John B


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com