Importing *.CSV Files


E

Eskimo

After running a Propriety Program, I end up with a folder (C:/caribou/csv)
with several *.csv files. Each file looks like this....

Telonics Data Report
Report Format,Condensed,1
Headers Row,22
Data Source,Argos

CTN,613844A
Comment,
Argos ID,83769

Report Period Begins,05/07/2008 1:00:36 PM
Report Period Ends,05/24/2008 11:36:42 PM

Report File Number,1 of 1
File Coverage Begins,05/07/2008 1:00:36 PM
File Coverage Ends,05/24/2008 11:36:42 PM

Measurement
Units,,,degrees,degrees,meters,,degrees,degrees,,meters,meters,Hit/Miss/Undetermined,Yes/No,
Resolution,,,,,,,0.000043,0.000043,,5,5,,,
Range Minimum,,,,,,,-90.000000,-180.000000,,,,,,
Range Maximum,,,,,,,90.000000,180.000000,,,,,,
,,,,,,,,,,,,,,
Acquisition Time,Acquisition Start Time,Argos Location Class,Argos
Latitude,Argos Longitude,Argos Altitude,GPS Fix Attempt,GPS Latitude,GPS
Longitude,GPS UTM Zone,GPS UTM Northing,GPS UTM Easting,Satellite
Uplink,Predeployment Data,Error
2008.05.07 13:00:36,2008.05.07
13:00:00,,,,,Succeeded,61.353707,-94.803128,15V,6803518,403571,,No,
2008.05.08 13:00:37,2008.05.08
13:00:00,,,,,Succeeded,61.516271,-94.378009,15V,6821069,426686,,No,
2008.05.09 13:00:37,2008.05.09
13:00:00,,,,,Succeeded,61.693082,-94.354234,15V,6840735,428360,,No,
2008.05.10 13:00:37,2008.05.10
13:00:00,,,,,Succeeded,61.912723,-94.267802,15V,6865107,433409,,No,
2008.05.11 13:00:36,2008.05.11
13:00:00,,,,,Succeeded,62.050395,-94.157081,15V,6880334,439498,,No,
2008.05.12 13:01:07,2008.05.12
13:00:00,,,,,Succeeded,62.217679,-94.112535,15V,6898927,442147,,No,
2008.05.13 13:00:37,2008.05.13
13:00:00,,,,,Succeeded,62.337070,-94.043441,15V,6912167,445954,,No,
2008.05.14 13:00:52,2008.05.14
13:00:00,,,,,Succeeded,62.438951,-94.132361,15V,6923593,441547,,No,
2008.05.14 16:46:41,2008.05.14 16:46:41,,,,,,,,,,,Hit,No,
2008.05.14 16:50:01,2008.05.14 16:50:01,,,,,,,,,,,Hit,No,
2008.05.14 22:35:00,2008.05.14 22:35:00,B,62.450,-94.298,0,,,,,,,,,
2008.05.14 22:36:41,2008.05.14 22:36:41,,,,,,,,,,,Hit,No,
2008.05.15 13:00:38,2008.05.15
13:00:00,,,,,Succeeded,62.588038,-94.077687,15V,6940153,444646,,No,
2008.05.16 13:00:37,2008.05.16
13:00:00,,,,,Succeeded,62.640481,-94.229608,15V,6946135,436955,,No,
2008.05.17 13:00:36,2008.05.17
13:00:00,,,,,Succeeded,62.653613,-94.286170,15V,6947654,434085,,No,
2008.05.18 13:00:53,2008.05.18
13:00:00,,,,,Succeeded,62.718329,-94.203429,15V,6954781,438459,,No,
2008.05.19 13:00:36,2008.05.19
13:00:00,,,,,Succeeded,62.653956,-94.214501,15V,6947621,437758,,No,
2008.05.19 19:36:41,2008.05.19 19:36:41,,,,,,,,,,,Hit,No,
2008.05.19 20:16:41,2008.05.19 20:16:41,,,,,,,,,,,Hit,No,
2008.05.19 20:36:41,2008.05.19 20:36:41,,,,,,,,,,,Hit,No,
2008.05.19 21:16:41,2008.05.19 21:16:41,,,,,,,,,,,Hit,No,
2008.05.19 22:53:21,2008.05.19 22:53:21,,,,,,,,,,,Hit,No,
2008.05.19 22:56:41,2008.05.19 22:56:41,,,,,,,,,,,Hit,No,
2008.05.19 23:53:21,2008.05.19 23:53:21,,,,,,,,,,,Hit,No,
2008.05.19 23:56:41,2008.05.19 23:56:41,,,,,,,,,,,Hit,No,
2008.05.20 13:00:37,2008.05.20
13:00:00,,,,,Succeeded,62.640309,-94.264369,15V,6946150,435173,,No,
2008.05.21 13:00:37,2008.05.21
13:00:00,,,,,Succeeded,62.651038,-94.287028,15V,6947368,434035,,No,
2008.05.22 13:00:52,2008.05.22
13:00:00,,,,,Succeeded,62.641511,-94.255786,15V,6946275,435615,,No,
2008.05.23 13:00:36,2008.05.23
13:00:00,,,,,Succeeded,62.636189,-94.300761,15V,6945728,433298,,No,
,2008.05.24 13:00:00,,,,,Succeeded,,,,,,,No,
2008.05.24 16:16:42,2008.05.24 16:16:42,,,,,,,,,,,Hit,No,
2008.05.24 17:30:02,2008.05.24 17:30:02,,,,,,,,,,,Hit,No,
2008.05.24 17:33:22,2008.05.24 17:33:22,,,,,,,,,,,Hit,No,
2008.05.24 18:36:42,2008.05.24 18:36:42,,,,,,,,,,,Hit,No,
2008.05.24 19:10:02,2008.05.24 19:10:02,,,,,,,,,,,Hit,No,
2008.05.24 19:33:22,2008.05.24 19:33:22,,,,,,,,,,,Hit,No,
2008.05.24 19:36:42,2008.05.24 19:36:42,,,,,,,,,,,Hit,No,
2008.05.24 21:56:42,2008.05.24 21:56:42,,,,,,,,,,,Hit,No,
2008.05.24 23:33:22,2008.05.24 23:33:22,,,,,,,,,,,Hit,No,
2008.05.24 23:36:42,2008.05.24 23:36:42,,,,,,,,,,,Hit,No,


What I am trying to do with Access, is import all the csv files by looping
through them, and import only the rows that indicate "success" in the GPS Fix
Attempt column, while adding the argos id Number (from Column 2/Row 8) in the
first column.

Here is what I would end up with from the first CSV file....

Argos ID,Acquisition Time,GPS Fix Attempt,GPS Latitude,GPS Longitude,GPS UTM
Zone,GPS UTM Northing,GPS UTM Easting
83769,2008.05.07 13:00:36,Succeeded,61.353707,-94.803128,15V,6803518,403571
83769,2008.05.08 13:00:37,Succeeded,61.516271,-94.378009,15V,6821069,426686
83769,2008.05.09 13:00:37,Succeeded,61.693082,-94.354234,15V,6840735,428360
83769,2008.05.10 13:00:37,Succeeded,61.912723,-94.267802,15V,6865107,433409
83769,2008.05.11 13:00:36,Succeeded,62.050395,-94.157081,15V,6880334,439498
83769,2008.05.12 13:01:07,Succeeded,62.217679,-94.112535,15V,6898927,442147
83769,2008.05.13 13:00:37,Succeeded,62.33707,-94.043441,15V,6912167,445954
83769,2008.05.14 13:00:52,Succeeded,62.438951,-94.132361,15V,6923593,441547
83769,2008.05.15 13:00:38,Succeeded,62.588038,-94.077687,15V,6940153,444646
83769,2008.05.16 13:00:37,Succeeded,62.640481,-94.229608,15V,6946135,436955
83769,2008.05.17 13:00:36,Succeeded,62.653613,-94.28617,15V,6947654,434085
83769,2008.05.18 13:00:53,Succeeded,62.718329,-94.203429,15V,6954781,438459
83769,2008.05.19 13:00:36,Succeeded,62.653956,-94.214501,15V,6947621,437758
83769,2008.05.20 13:00:37,Succeeded,62.640309,-94.264369,15V,6946150,435173
83769,2008.05.21 13:00:37,Succeeded,62.651038,-94.287028,15V,6947368,434035
83769,2008.05.22 13:00:52,Succeeded,62.641511,-94.255786,15V,6946275,435615
83769,2008.05.23 13:00:36,Succeeded,62.636189,-94.300761,15V,6945728,433298
83769,,Succeeded,,,,,

Then the next CSV with a different Argos ID would follow below the example
above.

The Access database name is GPSIV and the Table name to import into is
"Filtered"

If there is any quick way of importing all of these CSVs and the Argos ID as
shown above would be great.

Thanks,
ESKIMO
 
Ad

Advertisements

R

Roger Carlson

I would import a complex CSV file programmatically with the LineInput
command, which will read each line into a string variable. If it has
information you want, you parse the line into various fields. If it
doesn't, you skip it and go to the next. This way, you can skip all the
header information in addition to only reading the records that succeeded.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImportLineInput.mdb" which illustrates how to do this. It's
not exactly what you need, but you should be able to generalize from it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Ad

Advertisements

E

Eskimo

Hi Roger,

I get the idea with the line-input and looked at the code.

I'll try it with my system and see what I can do with it.

I'll keep you posted.

Thanks,

Jonathan AKA Eskimo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top