tranferspreadsheet does not import all text from source cells

[CBR1000f]

Hello

I'm a user not a programmer and I'm hoping for a bit more help following up on assistance from John Big Booty a few months back.

I used some code from John Big Booty to import a spreadsheet into a temp table and then run a series of queries to move the data to their final tables. Here it is:

Private Sub cmdImportOPR_Click()

DoCmd.SetWarnings False

'import the Operational Phase Report
DoCmd.TransferSpreadsheet acImport, , "tbl_opr_temp", Me!txtFilePath, False, "a1:z600"

'associate OPR with a discrete project
DoCmd.OpenQuery "q80_update_application_id_to_opr_temp"
DoCmd.OpenQuery "q80_update_tbl_opr_temp_f28"

'move the data from the temporary table to the appropriate tables
DoCmd.OpenQuery "q80_append_cdm/cp_6"
DoCmd.OpenQuery "q80_append_ohp/n_links_6"
DoCmd.OpenQuery "q80_append_opr_days_6"
DoCmd.OpenQuery "q80_append_practice_services_6"
DoCmd.OpenQuery "q80_append_tbl_opr_team_based_processes_6"
DoCmd.OpenQuery "q80_append_tbl_opr_training_programs_6"
DoCmd.OpenQuery "q80_append_tbl_opr_training_provided_6"
DoCmd.OpenQuery "q80_append_to_tbl_opr_workforce_professional"
DoCmd.OpenQuery "q80_update_barriers_to_accessibility"
DoCmd.OpenQuery "q80_update_clinical_training_reflect_project_plan_12"
DoCmd.OpenQuery "q80_update_clinical_training_reflect_project_plan_6"
DoCmd.OpenQuery "q80_update_clinical_training_story_12"
DoCmd.OpenQuery "q80_update_clinical_training_story_6"
DoCmd.OpenQuery "q80_update_community_benefits"
DoCmd.OpenQuery "q80_update_ed_relief"
DoCmd.OpenQuery "q80_update_extended_hours_reflect_project_plan_12"
DoCmd.OpenQuery "q80_update_extended_hours_reflect_project_plan_6"
DoCmd.OpenQuery "q80_update_extended_hours_story_12"
DoCmd.OpenQuery "q80_update_extended_hours_story_6"
DoCmd.OpenQuery "q80_update_focus_on_preventive_health"
DoCmd.OpenQuery "q80_update_focus_on_priority_areas"
DoCmd.OpenQuery "q80_update_future_planned_activities"
DoCmd.OpenQuery "q80_update_good_news_story"
DoCmd.OpenQuery "q80_update_ohp/n_12"
DoCmd.OpenQuery "q80_update_pa_story_12"
DoCmd.OpenQuery "q80_update_pa_story_6"
DoCmd.OpenQuery "q80_update_patient_waiting_times"
DoCmd.OpenQuery "q80_update_practice_benefits"
DoCmd.OpenQuery "q80_update_practice_services_12"
DoCmd.OpenQuery "q80_update_processes_to_share_patient_records_within_practice_12"
DoCmd.OpenQuery "q80_update_processes_to_share_patient_records_within_practice_6"
DoCmd.OpenQuery "q80_update_promotional_activities"
DoCmd.OpenQuery "q80_update_recruitment_story_12"
DoCmd.OpenQuery "q80_update_recruitment_story_6"
DoCmd.OpenQuery "q80_update_services_story_12"
DoCmd.OpenQuery "q80_update_services_story_6"
DoCmd.OpenQuery "q80_update_tbl_opr_cdm/cp_12"
DoCmd.OpenQuery "q80_update_tbl_opr_days_12"
DoCmd.OpenQuery "q80_update_tbl_opr_training_programs_12"
DoCmd.OpenQuery "q80_update_tbl_opr_training_provided_12"
DoCmd.OpenQuery "q80_update_tbl_opr_workforce_professional_12"
DoCmd.OpenQuery "q80_update_team_based_story_12"
DoCmd.OpenQuery "q80_update_team_based_story_6"
DoCmd.OpenQuery "q80_update_team_based_processes_12"

'Advise project officer that upload was successful
MsgBox "The Operational Phase Report upload was successful"

'erase the Operational Phase Report from the temp table ready for the next OPR to be loaded.
DoCmd.OpenQuery "q80_delete_tbl_opr_temp"

DoCmd.SetWarnings True
End Sub

But the table only contains the first part of the text in several cells in the source spreadsheet.

Can I alter this part DoCmd.TransferSpreadsheet acImport, , "tbl_opr_temp", Me!txtFilePath, False, "a1:z600" to make access capture the entire contents of each cell?

Looking forward to hearing from anyone with a suggestion.

 

[CBR1000f]

Maybe if I ask the question more directly?

transferspreadsheet only imports a limited number of characters from each cell in the source spreadsheet to my temp table. Can I alter the code to capture all text in the target cells?

 

[boblarson]

So how much data is in the Excel cells? Are we looking at memo type data? I think part of your problem may be that you are deleting your temp table instead of just setting it up first with the correct datatypes and then CLEARING the table before importing and then not deleting it afterwards. Excel will be GUESSING which datatypes to use based on the first few rows and that can be bad so you want to set up the table the way you need it first and then don't have it be recreated each time.

 

[CBR1000f]

Hello Mr Larson; thank you for replying.

The Excel cells might contain up to 1000 keystrokes (guessing) which are comments from companies about their activities.

I had previously run into this incomplete text capture problem when copying from the temp table to final tables, so I have respecified the relevant fields as Memo in both temp and final tables. I put a ' in front of DoCmd.OpenQuery "q80_delete_tbl_opr_temp" to check where the problem comes from and I believe the temp table is being populated incompletely by the transferspreadsheet command.

Also you have bought to my attention that my query q80_delete_tbl_opr_temp is incorrectly named. This query does in fact clear imported data from the temp table ready for the next upload.

Have I helped clarify my problem?

 

[boblarson]

1. Have you verified by looking at the import table itself that the data is not completely imported. Because

2. Queries which have memo fields and have criteria (other than Is Null or Is Not Null) will truncate the data. For those you need to have the criteria first in a query without the memo fields and then add them back in to another query using that first query. Read this for more info:
http://allenbrowne.com/ser-63.html

 

[CBR1000f]

Yes. The Excel spreadsheet has complete text in each cell. I have worked backwards from the final (ultimate destination) table where I noticed the incomplete text. Then I prevented the delete query from erasing the temp table after all of the append and update queries (put an ' before the command) so that I could see if the information was being imported into the temp table correctly in the first place. The information that is being transferred from Excel to the temp table is incomplete.

Might there be something more that can be specified in the transferspreadsheet command syntax that can ensure that all text in source cells is transferred?

 

[ajvol01]

Depending on the version of Excel there are limits to the number of characters a cell can display and contain.

What version are you using?

See link below for similar discussion
http://bytes.com/topic/access/answers/879281-255-character-limit

EDIT 1: Sorry if I misunderstood the problem. I just realized you said data was in Excel. but not in Access.

Are you using memo fields in Access? Text fields have 255 char limit.

EDIT 2: This was bugging me so I searched and found this answer from Ken Snell.

When ACCESS/Jet looks at your spreadsheet prior to importing, it "guesses"
at the data type for each EXCEL column by looking at the first 8 to 25 rows
of data (depending upon the setting in your Registry). If Jet doesn't find a
"memo" type string in those columns, it will assign 255-character text to
the column/field, and this will lead to truncation.
Try making the frst row in the EXCEL spreadsheet be one that contains a
"long" string, and see if that helps.
--
Ken Snell
<MS ACCESS MVP>

http://help.wugnet.com/office2/Truncated-memo-Excel-Access-TransferSpreadsheet-ftopict620553.html

 

[CBR1000f]

I don't think that's it either. It's .xlsx.

But the access version is an older version, maybe 2003. Could that be the root of the problem?

 

[ajvol01]

I don't think that's it either. It's .xlsx.

But the access version is an older version, maybe 2003. Could that be the root of the problem?

I'm pretty sure the problem is Access related.

See edited post that shows a quote from Ken Snell.

He says the import function in Access tries to guess the correct data type (text or memo) based on the first few rows of data being imported.

So if the first row (for memo fields) has > 256 char the import should work as expected (no truncation).

 

[CBR1000f]

Thank you fellas. ajvol I ammended the range of cells I'm inporting to exclude some unnecessary rows at the top of the spreadsheet. Cell A19 had about 400 characters so I started the command there, and now all of the text is being imported.

I am so grateful to you programmers who provide this advice.

 

[ajvol01]

Thank you fellas. ajvol I ammended the range of cells I'm inporting to exclude some unnecessary rows at the top of the spreadsheet. Cell A19 had about 400 characters so I started the command there, and now all of the text is being imported.

I am so grateful to you programmers who provide this advice.

So glad that worked CBR.

BTW, I'm just an engineer using Access to make my life at work easier .