Results 1 to 15 of 15
  1. #1
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10

    Importing GPS Waypoints into a Form from a CSV file

    Hello,



    I need a little help on importing data, specifically GPS wayponts, from a CSV file into a sub-form. After reading up on Access manuals and such I'm assumming there is code involved to make this happen. I've just started with the VB editor in Access and do not know the command for Access to look for the CSV file on my computer. I'm a extreme novice and any help would be appreciated. Thanks.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would start with the import wizard and import the file into a new table. Afterwards, you can decide what the best way to import the file is and save the process as a named import process. You can use the name in VBA to repeat the import process.

  3. #3
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    Thanks for the quick response-i will try this out.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you're trying to relate the imported values to a specific, existing, record (it's implied when you are trying to 'import into a sub-form') you'd have to have a way of linking the two sets of data based on some value in your text file or somehow enforcing a reference upon the import. If you can do that it shouldn't be too hard to take the next step of appending those records to the table that's driving your subform with the correct main form FK (foreign key)

  5. #5
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    I linked the CSV table to the DB and i can copy the data into the sub form pretty easy but that creates redundancy in the DB.

    "it's implied when you are trying to 'import into a sub-form"- and you're correct. The ultimate goal is to create a control that would take the DB user to where the GPS waypoints are stored (CSV or text file since that's the format when downloading from a GPS) then just highlight which waypoints that are associated with the record. This is a fisheries DB that stores redd ("fish nests") count surveys; the GPS waypoints are each individual redd counted, and is asscoiated with the main form of date surveyed, stream, section, etc.

    Thanks for the suggestion... I'll try to make it work.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, you wouldn't do that directly in your subform, you'd likely want an unbound form where you could link items in your text file to your master list of fishery locations then on, say, a button click you could create a new record in your database table that took the PK from your fishery location and the data from your linked CSV. Unless there's an existing key in the csv file I don't think it's advisable to try and do this on a bound form, but that's just me.

  7. #7
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    Right now I have a ReddSurvey tbl (stream, date surveyed, section, brood year, etc) with a one-to-many relationship with the ReddWPT tbl, (reddname, numberredds, latitude, longitude) but sometimes you conduct a redd survey and do not observe any redds so you still need to enter the survey data in the ReddSurvey form and the sub form (ReddWPT) is left blank. I guess for now the copy from the linked tbl routine will suffice. The two tables are linked by the ReddSurveyID (AutoKey) in the ReddSurvey tbl...

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am having a hard time understanding why there is any discussion about forms. Data belongs in tables. Sure, use a form to provide a place for a user to click a button and maybe input some relevant data into unbound controls like a fishery location, etc.

    Why would a subform play a role during the import process? After all, we are not talking about data entry, we are talking about importing data from a CSV file. Import data into tables. I think the problem may be that there are not appropriate tables to import into. So I would ask, "Why do you care what the waypoints are? Why are the waypoints relevant?"

    There has to be a reason you are collecting waypoints and the relevance should be described by their relationship(s) to their parent tables. I would start by trying to cram the GPS raw data into your table ReddWPT and see what fields are not getting populated. There will be key values missing. This is where an unbound form can come in handy. Have the user make a decision that they are going to import select files that are relevant to such and such user input (the user input being key values that are not included in the csv file).

    One problem may be that a single CSV file may be missing key values from more than one (relative) record and or table. Or maybe additional tables are needed but have not been created. There may be a need to parse data from a large file.

    Maybe there are SN numbers from GPS units that are in the file that can help.

    Can you post some sample data from the CSV or better yet a sample CSV?

  9. #9
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    BYDBSampleTbl.zipBYDBSampleTbl.zip


    I've posted a sample of a CSV file from a GPS and also an Excel spreadsheet of the ReddSurvey tbl and the ReddWPT tbl.

    The waypoints are one of the most crucial data collected on a redd count survey, it provides spatial/temporal information and is used to calculate redd/KM. It would be great if you could some how link specific waypoints to a survey when importing the waypoints into a table but there is nothing from the GPS that could link the two tables; so that's why i used the sub-form because the DB user would have to enter the ReddSurvey data anyway even if redds were not observed. Hope this makes some sense...

    Thanks for the suggestions.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the REDDNAME is common to both sets of data so what is the problem with linking the CSV or importing the CSV directly into access?

  11. #11
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    I took your suggestion earlier with linking the CSV tbl to Access the problem is linking the waypoints to the ReddSurvey tbl, plus the ReddName values are not unique when looking at data across years, the redd name is the collecters intials, julian date, # redds observed starting at 1............etc

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Kinda confusing but it seems you will need to import your data into a temp table and then use an unbound form to get user input for the field ReddSurveyID. With the user input you can create new (permanent) records. Each record that gets created seems like it will produce an autonmber for WPTID.

    This would work behind the scenes and without a subform. I would use DAO to create the new permanent records. Then, you can display the imported results in your form/subform arrangement.

  13. #13
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    Sounds like a good idea, thanks...

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Go ahead and see if you can start something and then let us know what you tried and where you believe you are stuck.

  15. #15
    santofish is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    10
    I have found a solution to my problem.

    So I deleted the sub-form for my ReddWPT tbl and recreated the linked tbl to include fields that would make it unique to the ReddSurvey tbl (date, stream, & section) then created a subform from the linked tbl, and created a relationship with the parent tbl (ReddSurvey) using the date, stream, & section. Now when I enter the header information my waypoints are automatically linked to the survey.

    Thanks for everyone's time and suggestions.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-05-2014, 04:25 AM
  2. Importing file directly from a Sharepoint file (not list)
    By jstoler in forum Import/Export Data
    Replies: 1
    Last Post: 06-28-2013, 01:44 PM
  3. Importing .csv file
    By Emma35 in forum Import/Export Data
    Replies: 13
    Last Post: 04-03-2013, 08:40 AM
  4. Importing CSV file using VBA
    By falconfox in forum Import/Export Data
    Replies: 1
    Last Post: 12-05-2012, 10:32 PM
  5. Importing Txt file via Vb
    By jquickuk in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2009, 08:27 AM

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