Combining working Excel spreadsheet import with preservering format

[daredan]

Hi,

I'm referring to
1. https://www.accessforums.net/showthread.php?t=86392 (Import data from a specfic Worksheet from Excel) and
2. https://www.accessforums.net/showthread.php?t=86344(Import data from Excel to Access long text preserving source formatting)

I would like to incorporate the SQL update query from thread#2 (86344)...

UPDATE tblTransaction
SET tblTransaction.Comment = Replace([comment],Chr(10),Chr(13) & Chr(10))
WHERE (((tblTransaction.Comment)<>""));

...into the VBA code from thread#1 (86392)...

Private Sub btnImportData_Click()

Dim fDialog As FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String

On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb

With fDialog
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
    If .Show Then
       strFileName = .SelectedItems(1)
       sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
        " SELECT * FROM (SELECT * FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
       db.Execute sqlStr
       MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
    Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

exitHere:
Set fDialog = Nothing
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

...as these two procedures could logically easy be unified.

What would be the best approach? Create another variable for the UPDATE statement and place it between below?

sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
 " SELECT * FROM (SELECT * FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
sqlStr1 = "UPDATE tblTransaction
SET tblTransaction.Comment = Replace([comment],Chr(10),Chr(13) & Chr(10))
WHERE (((tblTransaction.Comment)<>""))"
db.Execute sqlStr, sqlStr1

The approach would work (assuming the code is correct), but of course it would be enough to simply update the 'Comment' field for the imported records and not every time for the whole data set. Can this probably be achieved by altering the INSERT INTO statement accordingly?

[Gicu]

You could reuse the same strSQL variable, no need for a new one.

You can try to do it at the insert stage but you would need to check for data in the Comment field, something like this maybe:

sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
 " SELECT * FROM (SELECT YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Replace([comment],Chr(10),Chr(13) & Chr(10)) FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"

[daredan]

Thanks Gicu. I can comprehend the logic in your statement, however Access can't.

It comes up with the following error:


Would 'Expr1007' probably be related to the LfCr character replacement as I have no field name? Googling 'Error 3127' didn't really bring more insight for me and I couldn't find any errors in the statement with regards to inconsistent field names.

[Gicu]

Maybe try to add "as Comment":

sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
 " SELECT * FROM (SELECT YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Replace([Comment],Chr(10),Chr(13) & Chr(10)) As Comment FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"

Cheers,

[daredan]

Now it comes up with a circular reference

[daredan]

I just made a little test. I renamed the source column from 'Comment' to 'Comment1'...

...and amended the statement accordingly:

sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
        " SELECT * FROM (SELECT YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Replace([Comment1],Chr(10),Chr(13) & Chr(10)) As Comment FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"

That worked, imported the records and applied the format correctly, so that I can see line breaks in Access like I can see them in Excel.

There is only one strange thing, when I bypass the code like that. The number of imported records as per below code/msgbox...

MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"

...doesn't match the number of records that were imported for real:


I mean I'm happy that all records are imported, but why would db.RecordsAffected give me a lower number?

EDIT1: Just for sake of completeness: Running the original statement without the format adjustments gives me the correct number.

[Gicu]

Glad to see you're almost there, could it be that there are 9 records with no comments? Did you check to see if all 829 got imported when the message said 820?
An alternative would be to revert to your original insert and run the update query afterwards; you can modify the Where clause for the update to only include the newly imported records. I see you have a YearMonth field; store in a variable (lngYM) the greatest just prior running the insert (using a dMax) then use that in the update's Where clause to isolate the records with the YearMonth>lngYM.

Cheers,

[daredan]

Thanks for your answer Gicu.
No, I have three records with comments, so the variance of nine is not related.
And I just ran my bypass solution one additional time to be really sure. Yes, 829 records are imported. The related message says 820.

On a positive note I could isolate the nine records that are imported, but not counted.


And it must be related to the fact, that the marked accounts have equivalent related counterparts (e.g., 1000000_F has 1000000, EBT_F has EBT; OPEX_F however has no 'relative' so this is counted on the import).


So could this potentially be a bug I discovered here?

EDIT1: Important to mention that my primary key is a composite key made of the following fields:

[Gicu]

Not sure, but you could probably get your own counter by getting the number of rows in the spreadsheet and use that instead.

Cheers,

[daredan]

I tried different approaches. All basically bypassing the described issue. But knowing the origin and trying to keep the process simple, I've amended the code as follows:

MsgBox strFileName & vbCrLf & db.RecordsAffected + 9 & " records imported"