Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    Spreadsheet for import.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding Duplicates when Importing XL data (2003)

    The Import button in the startup form imports a spreadsheet selected by the user into the PortBarz table of the attached mdb.
    Because of the change from daylight saving time on the last weekend in October, the table contains duplicates(see data for 30th October 2005) so I cannot set a primary key for the table. The problem is that, in so doing, the user can accidentally import the same data multiple times, is there a way to check the content of the spreadsheet so that only data which is not already present in the table is imported except for the month of October?
    I've put the spreadsheet to be imported here.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    I'd weed out the duplicates from the Excel spreadsheet for 30 October 2005, and put a primary key on the Access table. Or even better, store UTC time instead of local time in the spreadsheet, so that there is no potential conflict over daylight saving time.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    But how are you going to process the duplicate data? There is no indication in the Access table which records are from before the time change, and which from after it. You cannot rely on the physical order of the records in Access.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    Hi Hans,
    The problem is that the data is only duplicated at the Date/time level, the PORTATA column has different values for the same combination of date and time on October 30 so I cannot weed it out from the spreadsheet. As to the other option, the spreadsheet comes as is, no chance of changing its time to UTC.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    I do the average of PORTATA for the whole day.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    OK. Does this do what you want?

    Private Sub Command142_Click()
    Dim fp As String
    Dim FD As FileDialog
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim d As Date
    Dim t As Date

    On Error GoTo Err_SalvaMdb
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    FD.Title = "Select File to Import"
    If FD.Show = False Then
    GoTo Exit_SalvaMdb
    End If
    fp = FD.SelectedItems.Item(1)
    Set FD = Nothing
    If InStr(fp, "barzesto") = 0 Then
    MsgBox "I Need Barzesto", , "Warning!"
    Exit Sub
    End If
    cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4. 0;" & _
    "Data Source=" & fp & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    rst.Open Source:="SELECT * FROM [Foglio1$]", ActiveConnection:=cnn
    d = rst!Data
    t = rst!Ora
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    If DCount("*", "PortBarz", "Data=#" & Format(d, "mm/dd/yyyy") & _
    "# AND Ora=#" & Format(t, "hh:nn") & "#") > 0 Then
    MsgBox "This month has already been imported!", vbInformation
    Exit Sub
    End If

    DoCmd.TransferSpreadsheet , , "PortBarz", fp, True

    Exit_SalvaMdb:
    Exit Sub

    Err_SalvaMdb:
    MsgBox Err.Description
    Resume Exit_SalvaMdb
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    Come to think of it, if you only need the average of PORTATA, you wouldn't need to import the entire table, you could use ADO (as in my previous reply) to retrieve the average, and store that in a table.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    Thank you Hans, that's very good.
    If the PortBarz table were to contain data up to December 20 instead of December 31, would there be a way to still import the missing data(i.e. from December 21 onwards) from the XL file previously attached?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    You could import the spreadsheet in a separate table or link it, and create an append query that only appends records from this table to PortBarz that do not have a match on Data and Ora.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    I may be stupid, but I cannot find anything to download in that link. Please reduce your file size. We don't need hundreds of records.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    Sorry Hans, I've fixed the link, can you please check if it works?
    In any case I've whittled down the mdb further so I've attached it here too.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)


  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    You could have removed the form, and you could have left a handful of records in each table. There is NO need to include 7298 and 1440 records, respectively, they do not make the problem any clearer than, say, 10 and 5.

    Open the query in design view.
    Double click one of the join lines.
    Select the option to return all records from Portbarzx (the second option)
    Click OK.
    Repeat for the other join line.
    The result is a left join.
    This should do what you want.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Duplicates when Importing XL data (2003)

    I was trying to display a link that, when clicked, would point directly to the file to download. Evidently it doesn't work so I'll just take a step back and give the link to the page containing the download instead. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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