How to find row with more than 255 characters in a cell

[Judy Ward]

I have code to import an Excel spreadsheet into an Access database. What I
am finding is that Access decides the data type for a field by looking in the
first 25 rows of the spreadsheet. If it sees more than 255 characters in a
cell it decides the column is "memo", and if it doesn't it decided the field
is "text" and truncates after 255 characters--this is not desired!

Right now I am manually looking at the rows to find one that has a large
amount of data, cutting and inserting it at the top of the spreadsheet. I
would really like to include this step in my code but have no idea how to go
about it.

Can anyone help?

Thank you,
Judy

 

[Jim Cone]

Judy,
The "Len" function works in both VBA and Excel.

In VBA it can determine the length of string variables and the length of data in a cell....
x = Len(Range("B5") or x = Len(strVariable)

In Excel this works... = Len(B5)

I suspect that Access will do what you want, though I am not the person to ask.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


in message
I have code to import an Excel spreadsheet into an Access database. What I
am finding is that Access decides the data type for a field by looking in the
first 25 rows of the spreadsheet. If it sees more than 255 characters in a
cell it decides the column is "memo", and if it doesn't it decided the field
is "text" and truncates after 255 characters--this is not desired!

Right now I am manually looking at the rows to find one that has a large
amount of data, cutting and inserting it at the top of the spreadsheet. I
would really like to include this step in my code but have no idea how to go
about it.

Can anyone help?

Thank you,
Judy

 

[Barry]

Hi

If you change the MSAccess code so that it imports the data into an existing
table rather than a new one each time then it won't 'guess' what data type
the columns are but will use the field types already in the table.
So
1. Create a new table with the correct field types

2. Update your MSAccess code so that it
Imports the data into your new staging table
Copies the data from the staging table into the desired data table
Clears the data from the staging table so it's ready for next time.

For specific code suggest you post in the Access discussion group.

HTH
Barry

 

[Judy Ward]

Thank you both for responding

I guess I wasn't clear enough in my question since you both think I am
looking for Access code.

I run Excel macros to clean up the data in the spreadsheet to get it ready
to import to Access. I do import into an existing table with the correct
field types. I perform this import on a daily basis and I can tell you from
experience that the import behaves differently if it doesn't find the defined
data type in the first 25 rows (and I read about this in Microsoft
documentation). Sorry, I don't remember what version of Excel we use at work
(I'm home now).

I think I can figure out how to loop through the rows and check the length
of the field I am interested in (thanks to Jim for suggesting the Len
function).

Thank you again for your responses,
Judy

 

[Rick Rothstein \(MVP - VB\)]

Would it help you any if you Conditionally Formatted the range to highlight
any cells whose text was longer than 255 characters in order to see your
"too long" cells? Highlight the first 25 rows, click on Format/Conditional
Formatting in Excel's menu bar, select Formula Is in the first drop down
box, and put this formula in the second field...

=LEN(A1)>255

click the Format button and select a pale color from the Patterns tab, then
OK your way back to the worksheet. Any cells containing more than 255
characters should standout with the color you selected.

Rick