02-22-11, 16:33 #1Registered User
- Join Date
- Feb 2011
Unanswered: Import Comma Delimited to Access via SQL.
I have a data logger text file I would like to import into ultimately into Access. This is one of 6 Data Loggers (NODES) we have. So as you can imagine data adds up quickly.
The text file has comma delimited entries for a date and time, but the problem is that there are 5 logged data entries every 5 minutes. But each data entry is logged on a new line with the same date/time. This creates a problem when importing into Excel as we end up with 5 times the lines when importing a textfile than we want.
Given the 5 minute recording interval and quarterly reporting, we are looking at 130,000 line entries in a quarter. Double Excel's limitation of 65,000 lines. So we're looking at Access for this job. We're using Office 2000.
The File looks like this ... Date,Time,Tag,Value; (there are no column headers in the file)...
Tag numbers would ideally be changed to a field as follows;
41 = FLVOLTAGE
31 = FLTEMP
1 = FCDEPTH
21 = FLDEPTH
11 = FCTEMP
What I would like in Access is one row based on DATETIME (combined) with a seperate column for each tag value.
DATETIME FCDEPTH FCTEMP FLDEPTH FLTEMP FLVOLTAGE
This would take us down to 26000 lines per quarter.
I was thinking one table per NODE. I would then use excel or access to generate the reports. Daily averages, min/max, monthly averages etc. for various tags.
Am I going about this the right way? I am thinking of Excel's line limitation and we'll be over it within 3 quarters if we store the data in Excel. So is Access the way to go?
02-23-11, 06:03 #2(Making Your Life Easy)
Provided Answers: 8
- Join Date
- Feb 2004
- New Zealand
What I would do is LINK the csv file to msaccess run a query over link table fix the records then do a append query to the main tablehope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
02-23-11, 10:17 #3Grumpy old man (training)
Provided Answers: 7
- Join Date
- Sep 2006
- Surrey, UK
This is related to a project that I've just completed at work, except that I was exploding a single record into as many as seven, depending on which fields were populated!
The process is pretty much the same, though. Create a table structure that mimics the text file and write an IMEX specification to import the files to this table. Create a table (or series of tables) to hold the data in the format that you need. Then you can write a series of append queries to take the imported data from the import table to the final one.
In this situation, I would say that Access is the way to go. It's better at handling and manipulating large volumes of data.10% of magic is knowing something that no-one else does. The rest is misdirection.
Beers earned: 1