Results 1 to 12 of 12
  1. #1
    Benjam26 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Alberta, Canada
    Posts
    11

    Newbie - Would like to create multiple tables from a CSV import relating to a key field.

    Hi all, I am looking for some help in what I should be researching in order to help me tackle the project I am working on.
    I do have some experience with access but not advanced enough for something like this.

    I am working with land titles in Canada and would like to build a database to import the CSV file we receive with each order.

    Here is an example of the data I will be receiving. Titles_Example.txt This is just one title (public data) where I would usually be receiving 100 titles per CSV file and could have multiple of these with each order.

    The CSV is created from up to 21 tables during the order and are categorized by the first, three character field that can be seen. Each of these tables contain differing amount of columns ranging from 8 to 21.

    I would like some recommendations on what I should be looking for in order to be able to learn how to split the data into the necessary tables with each upload. I have tried to research but I feel I am not using the correct terms so it is hard to find anything relating to similar threads.

    The 4th, 6 if reading the couple of blanks, column in each line is the unique title number which I would like to use as the primary key to keep each title linked. From here I can then create forms to re arrange each title into an easier to read format, and from there take it into an excel sheet, if that is the correct way to go, which we will then upload into an online database we currently use (which I do not have any access to in order to help with any data uploads)



    So with that being said, what should I be looking at in order to help progress with this.

    Thank you for taking the time to read my request and I look forward to reading up on any ideas you are able to give.

    I apologize if I am asking too many questions for a single thread, I can adjust my request if needed.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You would not be able to use Column 6 as a Primary Key as it is a Text Field with Leading Zero's.

    Are there any other column(s) that identifies the Unique Title?

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557

    Imported Data

    The attached is the Imported data
    Attached Files Attached Files

  4. #4
    Benjam26 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Alberta, Canada
    Posts
    11
    Quote Originally Posted by mike60smart View Post
    Hi

    You would not be able to use Column 6 as a Primary Key as it is a Text Field with Leading Zero's.

    Are there any other column(s) that identifies the Unique Title?
    Thank you for your response. Could I add a unique field that iterates through each line? The first line of each title is always "LTL" in the first column.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would import the text files in a temporary table that you empty every time before starting a new file (like Mike did in his example above) then you run a series of append queries to move the data into the proper tables (tblLTL, tblPCL, tblLEG, tblMEX....). You should be able to set the unique title number as the primary key for tblLTL (011181662005) and the same as foreign keys in the related tables (use an autonumber as the primary keys for those).

    Cheers,
    Last edited by Gicu; 12-07-2019 at 09:18 AM.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Benjam26,
    Further to Mike's and Vlad's comments:
    Do you know what each of the records means? That is, have you identified how each of these relates to a Land Titles request?

    Field1
    AFB
    AFF
    HIS
    INS
    LEG
    LTL
    MEX
    OAF
    ONM
    OWN
    PCL
    PNM
    PRT
    PTC
    RLT

    These appear to be consistent throughout the sample data
    "2019-11-12 15:53:25",,38349216,,"011181662005" - I recognize a Date Time value, but the other seem to identify all record types and values to be part of this set. (whatever these values represent).

    You may be able to create a data model based on the 3 character line prefixes and if so, use that model a some record type specific routines to populate tables for further processing.

    A google search identified this for more info, but in order to get the data you have you/your org must be registered. That reference should lead you to details of the csv involved.

    Good luck with your project.

  7. #7
    Benjam26 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Alberta, Canada
    Posts
    11
    Quote Originally Posted by Gicu View Post
    I would import the text files in a temporary table that you empty every time before starting a new file (like Mike did in his example above) then you run a series of append queries to move the data into the proper tables (tblLTL, tblPCL, tblLEG, tblMEX....). You should be able to set the unique title number as the primary key for tblLTL (011181662005) and the same as foreign keys in the related tables (use an autonumber as the primary keys for those).

    Cheers,
    Thank you Vlad, this seems the most logical way to split and clean the data. I will be attempting this next. Would the temporary table be in the same dataset as the proper tables or would you recommend to keep them separate?

    Thanks!

  8. #8
    Benjam26 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Alberta, Canada
    Posts
    11
    Quote Originally Posted by orange View Post
    Benjam26,
    Further to Mike's and Vlad's comments:
    Do you know what each of the records means? That is, have you identified how each of these relates to a Land Titles request?

    Field1
    AFB
    AFF
    HIS
    INS
    LEG
    LTL
    MEX
    OAF
    ONM
    OWN
    PCL
    PNM
    PRT
    PTC
    RLT

    These appear to be consistent throughout the sample data
    "2019-11-12 15:53:25",,38349216,,"011181662005" - I recognize a Date Time value, but the other seem to identify all record types and values to be part of this set. (whatever these values represent).

    You may be able to create a data model based on the 3 character line prefixes and if so, use that model a some record type specific routines to populate tables for further processing.

    A google search identified this for more info, but in order to get the data you have you/your org must be registered. That reference should lead you to details of the csv involved.

    Good luck with your project.
    Hey! I know what each of the three character designate relate to, I also have the schema for the original tables the data came from when it was ordered so I am able to sort the data how I see fit.

    The three sections you selected there are the time of the order, the order number and then the unique title number. However, like the one I have chosen, the "00" before the "5" is actually a "+" in the official title which for whatever reason is changed during the ordering process, so this will be something I have to work out too, but that can wait for now.

    The link you have sent is where the data was ordered from, it is where I got the schema for the database they use, but that was all the help I received. Not that I am complaining, I haven't had the chance to do much access work lately so this will get me back at it.

    Thank you.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure what you mean by the same "dataset". I would have the "temporary" (as in raw data container) in the same Access file with the other tables to facilitate the data transfer (via append queries).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Benjam26 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Alberta, Canada
    Posts
    11
    Quote Originally Posted by Gicu View Post
    Not sure what you mean by the same "dataset". I would have the "temporary" (as in raw data container) in the same Access file with the other tables to facilitate the data transfer (via append queries).
    Cheers,
    Sorry by dataset I meant the Access file as a whole.

    When using the temporary table, would importing a new text file to it update all tables or just add to the data that is already there? Is there a way to clear all tables or would you recommend simply having an empty Access file with all the tables in and making a copy to use for each use? I feel I have just answered my own question with that one.

    Thank you!

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I would Import to Temp Table - append data as required and then delete all data from Temp Table

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would simply add the "temporary" table to the back-end where all the actual tables are (the ones for each of the codes listed in your Field1). Then when you import a new file you would simply empty the temporary table (DoCmd.RunSQL "DELETE * FROM tblTemporary;") then run your import (DoCmd.TransferTxt.....) and finaly run a series of append queries to move the freshly imported data into each of the actual tables (using WHERE Field1="LTL" for the main title record and WHERE Field1="PCL"....clauses to isolate the sub-components).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-14-2019, 05:22 PM
  2. Replies: 2
    Last Post: 02-28-2019, 02:00 PM
  3. Replies: 4
    Last Post: 09-01-2015, 05:38 AM
  4. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  5. Relating multiple tables to main table and each other
    By squirrly in forum Database Design
    Replies: 7
    Last Post: 02-15-2013, 09:26 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums