Question Importing data question (1 Viewer)

[Johnsonsimon]

The company I freelance for (doing graphic design not databases lol) has asked me to build a database for an exhibition they are running with the following brief;

The people will register through the website. My current plan is just for a webform to submit the data to a newly set up email address and find some way to import this into the database, but I have yet to speak to the web guy, so it is possible we may be able to get the data in some form of CSV, or excel file etc...

Each registrant must be able to register themselves and an unlimited number (although last year the max was around 30 additional people) of additional attendees. We only need minimal details for the additional people such as [NAME] [JOB TITLE] [COMPANY NAME] [COMPANY TYPE], whereas the main registrant will supply full details for themselves/their company i.e address, phone, email etc..
So basically we do not want a FULL Main registrant record for every attendee, just for the main registrant (this is so that multiple mail shots are not sent to the same address in future)

The system will then need to be able to print out the badges (lables (we have zeebra lable printers)) for all attendees.

Currently I have set the tables up as follows;

tbl_Registration
REGID (Primary Key)
This table needs to contains the details of the main registrant, with their contact details, address, company details etc.

tbl_Attendees
BadgeNo (Primary Key)
REGID (Foreign Key)
Plus all the required fields.
This will need to contain both the main registrant and additional attendee details.

What I need to know is that will it be possible to somehow import the data, be that from an email, CSV or Excel file into Multiple tables in the way I have laid out above.

I await your knowledgable responses.

Thanks

Simon

 

[CJ_London]

yes you can, whether the file be .csv or excel, investigate the transfertext method

Here is a link

http://msdn.microsoft.com/en-us/library/office/aa141567(v=office.10).aspx

 

[Johnsonsimon]

yes you can, whether the file be .csv or excel, investigate the transfertext method

Here is a link

http://msdn.microsoft.com/en-us/library/office/aa141567(v=office.10).aspx

Thanks for the link CJ.

Have done a bit of VBA before, can generally get my head around it once pointed in the right direct (or can usually google an answer lol).

Can you just point me in the right direction if you have used this function before.

Basically I just need to know how to use the command for my needs. Do I need to do a TransferText command for each field etc?
How would I go about it importing data first into the tbl_Registration and then create related records in tbl_attendees

Thanks

 

[CJ_London]

Transfertext 'transfers' the whole file. Since you want to update to a number of different tables I would import to a temporary table then have a number of queries update the different tables. By 'transfer' you can either link to the file or you can import to a table.

However if you want to avoid vba then I would suggest linking is the better way - I would expect your website to download a file with a name component and an identifier such as a date and time e.g. webregistrations_20012014_060600.csv.

Save this file to an archive directory then copy it to an 'import' directory and strip off the identifier from the name - so you are left with webregistrations.csv

Then link to the file (you only need to do it once) using the facilties under the external data tab - note check datatypes are correct, better to have a .csv file, excel can misinterprete datatypes occasionally)

Now write your queries to update the various tables

When the next file comes in, again save to the archive directory and copy to the import directory overwriting the existing file then run your queries again.

I've written a number of registration systems over the years so given what you are suggesting I would structure your tables as follows(not that different from what you are proposing):

tblDelegates (info to appear on badges, dietary requirements etc)
DelegateID autonumber PK
DelegateLeader long FK (links to DelegateID above)
DelegateName text
DelegateTitle text
etc

tblPrimary
PrimaryID autonumber PK
DelegateID long FK (links to DelegateID who is
DelegateDetails - billing etc, not repeating name, title etc)

A 'primary' delegate can then be identified on one of two ways - either by their delegate ID existing in the DelegateLeaderID field or it existing in the DelegateID of the tblPrimary

If delegates can elect to attend certain events then you would have another two tables

tblEvents
EventID autonumber PK
EventName Text
EventLocation text
EventTime datetime

tblAttendance
AttendanceID autonumber PK
DelegateID long FK

I note you are using 'name' as a field name. This is a reserved word and it's use can cause unexpected problems so recommend you change it.

 

[Johnsonsimon]

Thanks again for the info.

I don't do databases all that often and when I come to doing them I find i Have forgotten everything I learnt the last time.

So I apologise for asking lost of questions....

but...

I am so confused in my thinking haha. Basically as I have explained each "Registration" will contain the name of the main registrant, plus they will also be able to register the names and job titles of other attendees that are coming with them.
I forsee the details of the main registrant being added to the tbl_registration. Then the details of both the main registrant (just first name, surname, company) and the additional attendees being added to the tbl_attendees which are linked (via the FK) to the tbl_registration.

thanks again

 

[CJ_London]

So I apologise for asking lost of questions....

I can't see a question!

The way you are describing is fine - I was just suggesting a refinement which I have found works for the registration processes I have developed

 

[Johnsonsimon]

I am now having to revisit this as the number of registrations expected for the show is a lot higher than initially forecast, so having the ability to import into the database would be very helpful. I have tried following the link and reading the transfertext info, but it seems a bit over my head. :banghead:

I have attached both the access database, and a sample CSV so people can get a better idea of what the setup is like before I try to add any extra tables etc... I had to upload them as ZIP files as they wouldnt upload as CSV and ACCDB files.

Any help would be much appreciated. I just really don't know where to start with the importing process.

Thanks again all

 

[Johnsonsimon]

I can't see a question!

The way you are describing is fine - I was just suggesting a refinement which I have found works for the registration processes I have developed

CJ I was wondering if you could help again. I have now managed to get the transfertext command to get the data into a "temp" table.

How would i now go about writing a query to append the "primary registrant" to one table and the "additional registrants" to the other table whilst keeping them linked.

Sorry to be such a n00b.

Thanks for the help

 

[CJ_London]

You need to write two queries rather than one.

Not sure if the .csv file you sent is your actual source, but assuming it is it should be fairly straightforward - you have an ID which is presumably unique and you have a parent column which tells you who is the primary registrant.

Open your query builder and select your imported table.

Then from the ribbon at the top, select append - you'll be prompted for the table you want to append to. Then select the fields you want to append and which field you want to append to. So for registrants, I guess it will be ID. first name, last name, job title,company name and parent. Save the query then for the primary registrant table it will be the other fields plus the ID field