Importing GPS Waypoints into a Form from a CSV file

[santofish]

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.

[ItsMe]

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.

[santofish]

Thanks for the quick response-i will try this out.

[rpeare]

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)

[santofish]

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.

[rpeare]

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.

[santofish]

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...

[ItsMe]

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?

[santofish]

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.

[rpeare]

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?

[santofish]

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

[ItsMe]

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.

[santofish]

Sounds like a good idea, thanks...

[ItsMe]

Go ahead and see if you can start something and then let us know what you tried and where you believe you are stuck.

[santofish]

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.