Importing excel spreadsheet truncates field to 255 characters (1 Viewer)

[Danick]

I'm trying to import an excel spreadsheet that has one field with more than 255 characters sometimes. So far, I've tried linking to the spreadsheet and using this ACImport VBA and both have continued to truncate this field to 255 characters.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, tblName, CurrentProject.Path & "\" & WbName, True

The only way I've been able to get it without truncation is by using the Access menus (External Data - Import) dialog. I've also saved the import and can import successfully every time. But I would like to use a button on a form with VBA instead.

Any ideas?

 

[Gasman]

DoCmd.RunSavedImportExport method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Danick]

I think I may have found the problem. Not sure if this is causing the truncation, but there are two columns in the excel table with the same column names. Maybe that's what's throwing off the TransferSpreadsheet VBA? The Access import menu dialog seems to fix this somehow on it's own.

 

[Danick]

Well, after trying a few ways to get the ACimport to work, I just gave up and went with your suggestion.

DoCmd.RunSavedImportExport "SavedImportExportName "


Too bad I couldn't get the AC import method to work - kid of feel like this method is a copout since I can only to it on my own machine. Unless there's a way to deploy it to others...

Thanks for your help.

 

[Gasman]

Well rename one of the columns and try another import?

@isladogs can expand more on his utility and where the specs are stored, but review this page on his site.

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

isladogs.co.uk

 

[Eugene-LS]

... to import an excel spreadsheet that has one field with more than 255 characters

When importing, the Microsoft driver despite the fact that the field is long text evaluates the imported data on the first 8 lines. If the data in them is less than 255 characters, it considers them as short text and everything further is cut off accordingly.
The same applies to any other data types. If the first lines contain numbers, and below that is text, then whatever you import into the text field will fail.

Solution:
- Before importing, add a row with text longer than 255 characters to the top of the Excel sheet, and delete it after importing.

 

[Danick]

Well rename one of the columns and try another import?

@isladogs can expand more on his utility and where the specs are stored, but review this page on his site.

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

isladogs.co.uk

WOW - that's a pretty nice module. Very powerful. But I wouldn't be able to use it to distribute the Front End to everyone as they all have the data stored in a different paths. The excel spreadsheet is downloaded from the company system and stored locally on the clients personal folder. I'm able to get to that folder by using standard transfer dialog shown above. I can't pre-program the module to get the IMEX task. But thanks for providing this link. I'm sure there will be a lot of other uses for it.

 

[Danick]

When importing, the Microsoft driver despite the fact that the field is long text evaluates the imported data on the first 8 lines. If the data in them is less than 255 characters, it considers them as short text and everything further is cut off accordingly.
The same applies to any other data types. If the first lines contain numbers, and below that is text, then whatever you import into the text field will fail.

Solution:
- Before importing, add a row with text longer than 255 characters to the top of the Excel sheet, and delete it after importing.

That's interesting. I can certainly do that on my computer, but I don't feel comfortable editing the client's spreadsheet without them knowing it. Plus I'm not even sure how I would go about building that sort of automation...

It's hard to believe Microsoft hasn't found a way to fix this problem instead of relying on workarounds. They certainly fixed it on their Import/Export task.

 

[ebs17]

When importing, the Microsoft driver despite the fact that the field is long text evaluates the imported data on the first 8 lines. If the data in them is less than 255 characters, it considers them as short text and everything further is cut off accordingly.

I would like to emphasize this boldly.
The saved import that @Gasman suggested also offers the use of an import specification for Excel tables, which determines how and with which data type table fields are to be read. With proper reading and recording you should then be able to import correctly.
A disadvantage of the saved import is that a specific path to the file is hardcoded. In the application you would have to use a path that can be used universally and then rename relevant files to this path before importing them.

 

[Gasman]

Well I was thinking the paths could be changed if @isladogs is doing it in his forms.
Perhaps a fair bit of work?, but still....

 

[isladogs]

WOW - that's a pretty nice module. Very powerful. But I wouldn't be able to use it to distribute the Front End to everyone as they all have the data stored in a different paths. The excel spreadsheet is downloaded from the company system and stored locally on the clients personal folder. I'm able to get to that folder by using standard transfer dialog shown above. I can't pre-program the module to get the IMEX task. But thanks for providing this link. I'm sure there will be a lot of other uses for it.

That's incorrect.
As @Gasman has suggested, one of the main purposes of my View & Edit Import/Export (IMEX) Data Task Specifications utility is to allow the end user to modify the path of the import source file (or the export destination path) without having to create a new data task.
This means you CAN use it in conjunction with a distributed front end application. The user would then modify the path (they would normally only need to do this once) so it works correctly on their workstation. Just allow them to browse to the path then use that path to modify the data task as in my example app

 

[Danick]

Hello isladogs

I imported the objects into my database and ran the "View New IMEX Data Tasks". The Report shows the import path as

"C:\Users\<UserName>\Documents\<Folder>\export.XLSX"

Is it possible hard code the front end import path to a common path and then delete your objects from my database? Something like this:
CurrentProject.Path & "\" & export.XLSX

I'm trying to make it without having the user do anything - similar to way the acExport VBA works

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True

 

[Gasman]

If you are going to hardcode the import path, then you could just use your spec?
Allow the user to select their file, then you copy it to your location and rename it as needed, then run the import, all within VBA.

 

[Danick]

If you are going to hardcode the import path, then you could just use your spec?

I can't use my spec because the <UserName> will be different for every user. That's why I thought if I could replace the first part of the spec with something generic, than they wouldn't need to select their file because the same file name would always be in the same folder for each relative user. Just have to find a way to modify the IMEX path that will be the same for everyone.

 

[June7]

Could automate copying user's file to common location/file name. Problem with that is multiple users could conflict overwriting same file.

Saved Import/Export is stored in system tables MSysIMEXSpecs, MSysIMEXColumns. It is usually advised to not mess with system tables but I have deleted records from these 2 tables without ill effect. Perhaps they can be added as well. Possibly use one as a master for creating one tailored for user "on-the-fly". These tables are in frontend of split db so every time frontend is replaced, they will be lost and code would have to check for user record and if not there, create. This seems to indicate is possible https://learn.microsoft.com/en-us/archive/blogs/thirdoffive/the-new-importexport-specification-om

Another approach could be for text files which can use Schema.ini file. Convert xlsx to csv (change file extension) and import. This does not seem to be well-documented and I've only experimented with once to help another poster somewhere - which I will never be able to find. If I remember correctly, schema file is created with Advanced tool on the import/export wizard or build from scratch. File could probably be programmatically copied to wherever the data file is located and edited or programmatically build from scratch every time. Code builds the path reference. Start with review of https://stackoverflow.com/questions/32209804/schema-ini-file-not-working-for-ms-access

 

[Danick]

Could automate copying user's file to common location/file name. Problem with that is multiple users will conflict overwriting same file.

This wouldn't be a problem in my case. Users are only IMPORTING the file into their standalone database. The original file is never touched. I've used isladogs module wi5h the format he provided in the download. It's awesome!! Does exactly what I want. I can change the import file at will from anywhere in my computer without modifying any of the import steps. It really works. Maybe it would be worth it to incorporate some of it into my front end. But it's pretty powerful with a lot of options and my users aren't that computer savvy. So there will be a good chance some will mess things up. That's why I prefer to hard code the task into a one button click. Hard to mess that up.

 

[June7]

I should have looked at Colin's blog first. That is basically what second paragraph in my previous post is talking about.