Import of long text is truncated at 255 chars

[daredan]

Hi,



You helped me a lot setting up the following code:

Private Sub btnImport_Click()
Dim fDialog As FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String
Dim varFile As Variant

Me.FileList.RowSource = ""

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

With fDialog
    .AllowMultiSelect = True
    .Title = "Please select one or more files"
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
    If .Show = True Then
        For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
            strFileName = varFile
            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])"
            db.Execute sqlStr
            MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
        Next
     Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

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

During the testing I didn't consider triggering length limits (apparently 255 characters seem to be an issue as on more than one occasion the reference is http://allenbrowne.com/ser-63.html, which I've been reading through).

I'm still having problems understanding why the import of below Excel example...


...results in being truncated as below.


The issues described in above link (aggregation, uniqueness, format property, union query, concatenated fields, row source) do, as far as I see it, not apply to my coding.

I also tried a few shots in the dark based on recommendation for other users facing this issue (e.g., put the memo field in the front in the related statements), however this did not help resolving the issue.

So obviously the question is: How can I make sure, that the whole content of the affected field can be imported into Access?

[ranman256]

did you also try:
A:
1. attached the excel sheet as external table,
2. run append query

B: try import command
docmd.ImportSpreadsheet

[Minty]

Try removing the replace command from the import routine, I suspect that is forcing the field to short text as far as Access is concerned.

You can always update the crlf later.

[daredan]

Thanks for the quick answers. I will look into it and will revert asap.

[daredan]

@Minty: This would have been the preferred approach, as you say, the replace can also be run subsequent to the import. However it still truncates after 255 chars:


I look now into @ranman256 solutions, however that brings me partly back where I initially started from here, where there were valid arguments to rather go with a query than a transferspreadsheet. Also your solution A will not be viable, as there will be 30 - 40 Excel files per month, which will be imported in the DB.

I also tried the standard import function (External data / New data source / from file / Excel). This also truncates at 255.

[daredan]

I tried linking the table to simply see if the complete field content would be available. But same behavior here, truncation after 255:

[daredan]

I'm getting closer to the root cause of the problem. It must have to do with the fact, that Access merely inspects the first few (likely 8) rows of data and decides what it applies no matter if it's a designated LONG field. This article, although not directly related, helped me isolating the issue.

When I put a long (> 255 character) comment in the first to import row, then Access reliably imports this and all subsequent short/long comments correctly.


So a solution could be to put a dummy record with a 'real' long text to start the import, however this is of course not ideal. But can I 'force' Access somehow else to respect the LONG field setting?

[daredan]

Think this might work: As soon as our IT will help me adjusting the 8 records to 1000. I will test again and revert with results.

[Welshgasman]

Can't you just use a specification?
https://www.google.com/search?q=acce...%20for%20excel

[daredan]

Tried that, post #5

I also tried the standard import function (External data / New data source / from file / Excel). This also truncates at 255.

[June7]

Import/Export Saved Specification Name argument is not available with TransferSpreadsheet, it is with TransferText.

[daredan]

Still waiting for our IT to bypass the registry limitation. I'd really like to test this.
The bypass solution with the dummy record works fine and I applied this short term to fix the issue.

[CJ_London]

I also tried the standard import function (External data / New data source / from file / Excel). This also truncates at 255.

it doesn't if you specify memo/longtext.


Pretty sure changing typeguessrows to 0 will cause all rows to be evaluated.

Another method is to save the xl file as a .csv, then instead of

"FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes]"

use

"FROM [TEXT;DATABASE=" & strPath & " ;HDR=Yes]." & strcsvFileName & ".csv"

[daredan]

The registry fix works!


However I'll go with the long text dummy record, because this will be easier instead of asking any database user doing import tasks to ask IT to increase the limit.

@CJLondon: Only saw your input now.

The standard import function didn't give me the option specifying data types per field (probably changed from Access2010 to Access365?). I could only select worksheets/named ranges and if the first row contains headings or not. Then it went straight to the import.

Yes 0 in this registry value will go through all records. However in my case 1000 is sufficient as currently there are approx. 830 records per import. That also will not fluctuate much in the future.

The .csv approach is interesting, however in my case too cumbersome to consider. Our process is to distribute an Excel template, entities/clusters will update some financial information and fill in explanations and send the file back. Then I 'bulk' import around 30 files in one go, so that people looking at numbers/explanations don't have to go through lots of Excel files, but simply open reports in the database.

[CJ_London]

not that cumbersome, don't have the time to provide the proper code, but would be along the lines of

open an excel application object
for each workbook
    open workbook
    select appropriate worksheet if necessary
    save as .csv (could be a generic import name)
    close workbook
    import .csv
next workbook

providing your template does not include formulae (or at least not required once value is calculated), you could just send out a .csv which will automatically open with excel