Access 2010 Import Long fields from Excel

[stevendt]

Hi,

I have an application that uses the DoCmd.TransferSpreadsheet method to import data from an Excel spreadsheet into an existing table (which is cleared before import) in an Access database.

The Excel sheet has a number of fields that can be up to 500 characters in length, so I need to import them into memo fields in the database. After seeing some issues with the data being truncated, a bit of "Googling" has thrown up the fact that regardless of the field type, Access seemingly does it's own thing and decides on how to process the data when importing it. If the data in the first few rows of the spreadsheet is less that 255 characters, Access imports the field as text for all rows, i.e., truncates the data at 255 characters, even for subsequent rows that are larger.

I can add a column to the spreadsheet and use the Excel Len() function to manually sort the data to have the rows at the beginning contain long text entries before I import it which forces Access to treat them as Memo fields, but this is a regular operation and that would be a pain. I guess that I can probably write some (probably bad) VBA to do this programmatically, but I was wondering if anyone had any better ways of solving this problem please?

regards
Dave

 

[Ranman256]

Instead of TRANSFERSPREADSHEET,
Attach the workbook as a table, then run an append query.
Be sure your target table has a memo field for this.

 

[stevendt]

Hi,
thanks a lot for the answer, but before I code the append query stuff, could you confirm that this will actually work please? I have created a linked table using DoCmd.TransferSpreadsheet acLink, . . . . is that what you meant?.

When I do this, the linked table has the same issues as using acImport, i.e., the long text fields are configured as text in the linked table and opening the table only shows 255 characters.

My VBA coding skills are not the best, and while I can write the code to do an update query into my target table, this will take me a little while to do and I am a bit concerned that the result would be that same, i.e., that the query would pull the data as a text field, rather than a memo, even though the target table has memo fields if the linked table has text.

Sorry for asking you to clarify this before I do the work, but my limited knowledge of VBA and general Access ignorance means that I have to ask stupid questions

If you didn't mean me to link the table like I have done, can you please tell me what you meant please?

Thanks again for the input

regards
Dave

 

[stevendt]

Hi Ranman,

well, after struggling through some VBA, I have created a linked table and tried to pull the data through it to send it to a target database.

As I mentioned above, as expected, the linked table is created with the fields coming up as text, the data IS truncated when I subsequently try and do an APPEND query into the target database with the fields set as Memo!

Can anyone suggest a mechanism to do what I need - preferably one that has been tested and proved to work

regards
Dave

 

[sneuberg]

I tried Ranman's solution and it worked for me but then I tried to duplicate your original problem using DoCmd.TransferSpreadsheet and was unable to. My test spreadsheet had five rows of short text before the row with long text and the table that was created had a long text field. The data was not truncated.

I wonder if this varies by Access version. I have Access 2013. The database and spreadsheet I was using for testing are attached.

 

[Galaxiom]

Link a modified version of the spreadsheet sorted with the long entries first. This should fix the linking pattern so further spreadsheets with the same name won't need to be modified.

 

[stevendt]

I tried Ranman's solution and it worked for me but then I tried to duplicate your original problem using DoCmd.TransferSpreadsheet and was unable to. My test spreadsheet had five rows of short text before the row with long text and the table that was created had a long text field. The data was not truncated.

I wonder if this varies by Access version. I have Access 2013. The database and spreadsheet I was using for testing are attached.

Hi Steve,

thanks a lot for the reply and the attachments, I will try it with you files and let you know.

I am using Access 2010

regards
Dave

p.s., if it's not too much trouble, could you try it with a larger spreadsheet please? From what I have read, Access uses more than the first dew rows to analyse the data, could you try putting the long line at, say, row 100?
I have just done this in Access 2010 and your example works as written, but when I have the long row much further down in the table, the field is truncated

 

[stevendt]

Link a modified version of the spreadsheet sorted with the long entries first. This should fix the linking pattern so further spreadsheets with the same name won't need to be modified.

That would work if only one field had long entries (in fact, I did try that), but there are three large text fields, any 1 or more of which can have long or short text data in any them. i.e., the longest first text field would not necessarily have long text in the second field, but another row will.

I am not keen on modifying the spreadsheet, as this will be a regular operation, but it seems that I might have little choice (other than updating Access which in not my preferred option) but to go with my idea above, i.e., insert a formatting row to the second row of the spreadsheet (after the column headers).

I need to see if I can do this programmatically

regards
Dave

 

[Galaxiom]

What about modify the records for the first import so that all the fields that need to be memo all have long strings?

Once the import is done the specification should stick as long as you substitute just the file.

 

[sneuberg]

What about modify the records for the first import so that all the fields that need to be memo all have long strings?

Once the import is done the specification should stick as long as you substitute just the file.

It doesn't stick in Access 2013 but then this doesn't seem to be a problem in Access 2013.


Edit: Actually what doesn't stick is the types in a linked spreadsheet. They change when the underlying spreadsheet is changed. What's does stick is the types in an imported tables. Once set to long text they remain long text even if the imported spreadsheets have all short text (< 256 characters)

 

[stevendt]

It doesn't stick in Access 2013 but then this doesn't seem to be a problem in Access 2013.

Hi Steve,

so, are you saying that even if the long string is at, say line 100, Access 2013 finds the string and sets the field to Memo? Access 2010 definitely doesn't

regards
Dave

 

[sneuberg]

Hi Steve,

so, are you saying that even if the long string is at, say line 100, Access 2013 finds the string and sets the field to Memo? Access 2010 definitely doesn't

regards
Dave

I said

My test spreadsheet had five rows of short text before the row with long text and the table that was created had a long text field. The data was not truncated.

but since I didn't test with more than five rows I felt I better before say anything. Here's the results.

129 Rows: Short Text
50 Rows: Short Text
10 Rows: Short Text
5 Rows: Long Text

Who would have thought that Access just checks between five and ten rows before establishing the type. So I guess Access 2013 has the same problem.

I've attached the test database and spreadsheets. If you trying this you may notice some automation code in the module in addition to the transfer spreadsheet code. I was working on a work around for you that would insert a row at the beginning of the spreadsheet with a cell with more than 256 characters. I was almost there but the code leaves a hanging Excel process. I may work on it some tomorrow.

 

[stevendt]

Hi Steve,

Thanks a lot for the update, it seems that 2010 and 2013 have the same "feature" then.

I have been messing around trying to write a dummy formatting line on the second row of the sheet, after the header row. It is bed time now though, but
I will be very interested to look at your zip file tomorrow. I'm pretty sure that it will be much better than my attempt !

Thanks for taking the time to look at this,

Regards
Dave

 

[static]

You could try converting it to csv. Linking a CSV allows you to define the datatype of each field.

With New Excel.Application
    .DisplayAlerts = False
    With .Workbooks.Open("C:\TheData.xlsx")
        .Sheets(1).Select
        .SaveAs "C:\ForImport.csv", xlCSV
        .Close
    End With
    .DisplayAlerts = True
End With

 

[sneuberg]

I have an application that uses the DoCmd.TransferSpreadsheet method to import data from an Excel spreadsheet into an existing table (which is cleared before import) in an Access database.

When you say "cleared" do you mean that the table is deleted? I ask because I've found that if you only delete the records in it the types remain fixed for subsequent imports. You get the same bloat whether you delete the table or delete the records so I suggest that you set up the table as you need it and leave it. Just delete the records before each import.

But if for some reason you don't want to do that I have some code that will insert a line into a spreadsheet and insert a cell with 256 characters into the line so that the imported table will assume the long text type for that column. You would need to add a line of code to this to establish a unique identifier so that you can delete the records after the import, e,g,

ExcelWorksheet.Cells(2, 1) = "UniqueKey##@@@##"

Here's the code which you will also find in the attached database.

Public Sub InsertLongText()
    
Dim ExcelApp As Excel.Application
Dim ExcelWorksheet As Excel.Worksheet
Dim ExcelWorkbook As Excel.Workbook
Dim strLongText As String
strLongText = "1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF" & _
           "1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF"
           
Set ExcelApp = CreateObject("Excel.Application")
'change path as required
Set ExcelWorkbook = ExcelApp.Workbooks.Open("C:\Users\sneuberg\Desktop\Book1\Book129.xlsx")
'change to the applicable worksheet
Set ExcelWorksheet = ExcelWorkbook.Worksheets(1)
'insert row after row one
ExcelWorksheet.Rows(2).Insert Shift:=xlDown
'insert text into the second column of the inserted row.
ExcelWorksheet.Cells(2, 2) = strLongText
ExcelWorkbook.Save
ExcelWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Sub

This is set up for early binding so you will need to either add a reference to the Microsoft Excel Object Library or change the code to late binding like:

Public Sub InsertLongText()
    
Dim ExcelApp As [COLOR="Blue"]Object[/COLOR]
Dim ExcelWorksheet As[COLOR="blue"] Object[/COLOR]
Dim ExcelWorkbook As [COLOR="blue"]Object[/COLOR]
Dim strLongText As String
[COLOR="blue"]Const xlDown As Long = -4121
[/COLOR]
strLongText = "1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF" & _
           "1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF1234567890ABCDEF"
           
Set ExcelApp = CreateObject("Excel.Application")
'change path as required
Set ExcelWorkbook = ExcelApp.Workbooks.Open("C:\Users\sneuberg\Desktop\Book1\Book129.xlsx")
'change to the applicable worksheet
Set ExcelWorksheet = ExcelWorkbook.Worksheets(1)
'insert row after row one
ExcelWorksheet.Rows(2).Insert Shift:=xlDown
'insert text into the second column of the inserted row.
ExcelWorksheet.Cells(2, 2) = strLongText
ExcelWorkbook.Save
ExcelWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Sub

This code also needs error handling for missing spreadsheets and spreadsheet being open, etc.

 

[stevendt]

When you say "cleared" do you mean that the table is deleted? I ask because I've found that if you only delete the records in it the types remain fixed for subsequent imports. You get the same bloat whether you delete the table or delete the records so I suggest that you set up the table as you need it and leave it. Just delete the records before each import.

But if for some reason you don't want to do that I have some code that will insert a line into a spreadsheet and insert a cell with 256 characters into the line so that the imported table will assume the long text type for that column. You would need to add a line of code to this to establish a unique identifier so that you can delete the records after the import, e,g,

Hi Steve,
thanks a lot for the code etc. - much appreciated.

The way that my application works is that the target table lives in a backend database, the table doesn't get deleted, prior to a new import, the table is cleared with an SQL DELETE *, followed by compaction with DBEngine.CompactDatabase. Pretty much what you described I think

I found that even though the table structure didn't change, i.e., the fields were Memo, the DoCmd.TransferSpreadsheet to the backend database ignored the Memo field type and did it's own thing, i.e., just read the first few rows to decide how it was going to process the data. (Maybe the table being in a different database has an effect.)


The code is remarkably close to what I have been putting together, so my VBA might not be as bad as I thought

Since I was having to do this, I write the formatting line to include short text strings for every field (so that I don't get a mix of numbers and text) except the three long ones, which get a 300 character string put in them. Then I delete the record with the "dummy" identifier at the end of the import.

Thanks again for your help!
regards
Dave