Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Unanswered: i need to import a text file into MS Access database using java code

    I am using eclipse IDE.So far i have managed to get the text file contents to the console of eclipse.

    What i need.
    1.Complete Source code(because i am a student and need to learn)
    2.Code to include time stamp in the last column of the MS Access table

    My current setup
    Windows 7,JDK 7(since this supports JDBC:ODBC connections),eclipse Luna(java ee version)

    INFO:
    the text file contains input from sensors,around 5 readings eg. 1221 123 434 123 343 (separated by spaces)
    the MS Access database will have 5 columns to accommodate the above values and an extra column to to add the time stamp.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumably you will also have a sample date and time from the time the sample was taken

    when designing your table you should and a sample time column of type datetime and set its default to now().
    that gives you the option of either specifying the sample time as part of the insertion or letting it default to the current system time that the instance of your Java progrma is runnign on. bear in mind that Access like most file server implementations has no central box.server which determines the date time values. so if the time of the sample is important then make certain that your applciation is running with an up to date / sufficiently accurate system time

    when inserting dates into Access they shoudl be delimited by # AND be in the the ISO form YYYY/MM/DD HH:NNS OR the US form MM/DD/YYYY HH:NNS
    eg
    Code:
    INSERT INTO mytable (SampledOn, Sensor1, Sensor2, Sensor3, Sensor4, Sensor5) VALUES (#2015/01/27 12:34:56#,1221, 123, 434, 123, 343)
    as to how you populate a database with values from a flat file, then unless you have the sample date & time then unless you know the starting point in time I dont' see a way to magic a sample time as part of the import process.
    you could fake it after the event
    as a temporary process...
    add two columns
    one is an autonumber kye
    the other is your sample date (as a datetime)
    import the text file directly into Access (using import in Access)
    then write a process that updates the sample time based on your specified start point + whatever sample interval

    to make this work you need to understand how Access stores date time values, which are a double precisioon number. the integr protion counts the number of days (IIRC) 31/12/1899, ie Day 0 = 31/12/1899, day 1 is 01 jan 1900......
    the decimal portion is the proportion of the day as finely grained as you need it
    0.25 = 06:00:00, 0.5 = 12:00:00, 0.75 = 18:00:00
    0.04166666666667 |(or 1/24) 01:00
    0.00069444444444 (or 1/ (24*60) ) is one minute
    0.0000115741 is one second.
    if you need sub second then do the maths

    run an update query which sets the sample value based on a specified offset AND the value of the autonumber

    bear in mind that the first time you insert rows into an autonumber table it (should) start at 1. alwasy check before runnign your update query and make appropriate adjustgments so that the first value is correct.

    this approach works providing you take appropriate care and attention. but inm an IDEAL world you shlopuld insert rows with teh date and time from the actual process that records the sample in the first palce

    as with virtually anything in the systems world you can fake the process.
    import the data into a spreadsheet
    add a new column set the first cell in that new column to be the start sample date and time
    then set a forumla that adds whatever time interval you need to subsequent rows
    save the data as a CSV or other import format
    then import that into Acccess (either directly)
    ...or from say Excel
    ...or from a Java program
    DONT be tempted to add bulk rows with Now specified date IF YOU HAVE defined the sampletime as = NOW. not supplying a value means use the default.

    so if you had 1000 rows of data, you would have many with the same HH:MMS as formatted, even if the timestamp was a PK or unique value... as Access will store the microsecoinds alongsode DD/MM/YYYY HH:NNS
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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