Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Posts
    2

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

    11/11/2010,12:00:00,41,12.767
    11/11/2010,12:00:00,31,0.122
    11/11/2010,12:00:00,1,0.4365
    11/11/2010,12:00:00,21,1.3439
    11/11/2010,12:00:00,11,0.230

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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 table
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    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
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •