We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Determine which record has more than 32767 characters

High Priority
77 Views
Last Modified: 2020-06-26
Hi,

I am trying to link a CSV file to my Access database file and it gives me the attached error.

How can I determine which record/s are causing this?


Thanks
Untitled.png
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Have you checked it with a text editor like NotePad++
All these issues are handled nicely if you open the text file and read it line by line...full control
CERTIFIED EXPERT

Author

Commented:
Hi,
Have you checked it with a text editor like NotePad++
Guess i need instructions/tips how to get to the large record as we are talking of a file with 7.5K records.
See attached sample of what file looks like.
Untitled.png
Thanks
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I reckon is that the issue is "not" the length of lines but the enormous amount of columns needed to display your data.
So use my suggestion : Read Text file line by line 
Get an idea what your data should be mapped and do the necessary mapping
Split function would be your invaluable assistant....as you will be able to do
strlineData(0) - strlineData(10)  table 01 columns
strlineData(11) - strlineData(18)  table 02 columns
................
strlineData(n-x) - strlineData(n)  table nn columns
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Comments found on the internet regarding the same problem:

"I'm having the same problem exporting to csv file. I can import fine, but it won't let me export. I need all these columns as to duplicate a csv file for importing to another program. I'm working on developing an electronic timesheet system and I'm trying to automate as much of it as possible. If I could get access to export this file it would be ideal for the approach I'm testing right now. Nobody has come up with a way of working around this issue yet??"
 
Ok this is what I did to get around the issue. I went into the table design view, and for the fields that were always empty, but necessary to preserve for csv formatting reasons, I just changed the field size from 255 to 2. Until it worked fine. I would have made all empty fields this (maybe 0 would be a better idea, I never tried it. Seems that it would cause problems lol) but there were so many fields to go through I didn't bother.
 
 Hope that might help anyone else having this issue.
I tried to change the field size to 0, but that didn't work. Changing it to 2 worked for me.
 What an annoying error!
 
 Workaround with exporting to excel, saving as csv and changing extension to .txt also worked, but then I still had to change the delimiter, because I needed | as delimiter, not ;.
CERTIFIED EXPERT

Author

Commented:
The story is as follows...

We have an application designed and hosted by Caspio.com, we export data on a scheduled bases to MS Access and do some reporting from from there.

Now the export utility is failing due to one record.

By now I was able to figure out which record that is.
However I still need help determine what is wrong with that record in order to Manually fix it, as nothing unusual seems to be there.


Thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
That isn't a CSV file, it is JSON
CERTIFIED EXPERT

Commented:
This issue was once discussed on this site. Perhaps the solution will work for you:

The Field "XXXXX" Contains a Start Position of '32896', The Maximum Start Position allowed is 32767

Paul
CERTIFIED EXPERT

Author

Commented:
That isn't a CSV file, it is JSON
What makes you think so, it is CSV.

Perhaps the solution will work for you:
Not looking for solutions suggesting to re-fracture db for now as this is happening for one record only.

Again I'm looking for a way to figure out what is wrong with a particular record.

Thanks
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
When it is a length problem, then you can use  notepad++ and search by using a regular expression:

^.{32767,}

Open in new window

will match lines with 32767 characters or more.

Capture.PNG
p.s. your files screenshot looks like it could be indeed JSON. All those curly brackets and quotes and key-value pairs.. You may test it by formatting it as JSON using the JSON viewer plugin.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
As others mentioned its probably is a JSON file (it would be almost crazy to have a .CSV with such length)
So do use this code : https://dymeng.com/parsing-json-with-vba/
to read it and post back on your findings.
Even if you  did linked the CSV there isn't an object in Access to hold 32767 individual elements (in the concept of table - fields as Access has a hard limit of 255 columns)
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
What makes you think so, it is CSV.
The structure of the data:
{"name":value}
is the structure of JSON objects.

I'm concerned that it isn't a well formed JSON file, since you have pairs of quote characters around the name.  {""lx"":25,""ly"":55,""mx"":28,""my"":51}
There should only be single quote characters.
{"lx":25,"ly":55,"mx":28,"my":51}[/b]
CERTIFIED EXPERT

Author

Commented:
will match lines with 32767 characters or more.
Tested it, it didn't show the right record/s, we are looking for a record exceeding the limit not a line.

Even if you  did linked the CSV there isn't an object in Access to hold 32767 individual elements
There are no 32767 elements, there are 32767 characters in this record excluding the memo fields.
The structure of the data:
{"name":value}
is the structure of JSON objects.
Actually you right about that as there is one field displayed in JSON format, and it happened to be the the longest and therefore its showing up on that pic.
However the file is CSV as Caspio dont offer exports in JSON formats (Except for that single field).

Now the story is as follows, if I first export to CSV then I'm able to link it to Access, but CSV is not an option as it causes different issues.

At the moment I'm basically looking to figure out what is wrong with that record so I can.
1- Fix it manually.
2- Know which field contains the largest amount of characters so I can convert that field to Text(6400) in Caspio, which in that case its being handled differently and will not cause such problem.(Had this in the past long time ago and after converting field it fixed the problem).


Thanks

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
You can define that particular field as a memo data type.
CERTIFIED EXPERT

Author

Commented:
You can define that particular field as a memo data type.
How do i know which field (Table has close to a hundred...topic for itself..)?

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
just some ideas to share, probably you can do a "reverse engineering"?

1. try verify the data stored in caspio and what's the data type and length
2. compare it with the Access target table's field data type and length
3. do you really want to import all fields to MS Access? the original error seems like a limitation error in MS Access.

 You can define that particular field as a memo data type. 
>>How do i know which field (Table has close to a hundred...topic for itself..)?

We probably need to test it out for the fields before field: ScheduleID. You may first import the data into the "staging" table (with field's data type set to Memo), and then you could have another step for your data cleansing phase.
CERTIFIED EXPERT

Author

Commented:
We probably need to test it out for the fields before field: ScheduleID.
At the moment somehow things changed as mentioned, I'm not getting that error with CSV, and in Access the error is as follows.

  • One or more records has reached the maximum data limit for Microsoft Access MDB files. To export this view, please remove some characters and try again.
So basically I'm looking for a shortcut instead of having to change field types field by field, export examine the error etc...

Thanks

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
So basically I'm looking for a shortcut instead of having to change field types field by field, export examine the error etc...

Yup, that's true. But as mentioned that we are exploring the possibility of using memo fields in a staging table not the original table, that would be helpful so that at least data can be imported, and then we can validate the length of data when it's necessary.

you could replicate the original table to staging table, and then modify the data type to memo for the testing.

hope this is practical to you.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Ben

Please post a representative sample of the data that has at least one of those too-long records.  I have an idea.
CERTIFIED EXPERT

Author

Commented:
@Aikimark,
Since data has PHI, I cannot post it.
Can you suggest something to obfuscate the data but should remain valid for testing?
Thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
bring it into Notepad++ and do some find/replace operations.  I only need enough records to see both normal and too-long records.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Also, you only need to clean the PII from one record.  You can copy/paste the clean PII to other records.
CERTIFIED EXPERT

Author

Commented:
@Aikimark,
I did some find/replace obfuscation, however to be on safe side will send you file via private message.
Thanks
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Author

Commented:
Hi,
Can you give me instructions how to run this.
Tried saving as .ps1 under my download folder, then right click/run with powershell script, nothing happened.
Thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
Did you change the path of the CSV on the first line?

if you are at a command line prompt, type this:
c:\users\bfuchs\downloads\> Powershell -file nameofscript.ps1

Open in new window


It should get you close.
CERTIFIED EXPERT

Author

Commented:
See attached. Untitled.png
CERTIFIED EXPERT

Author

Commented:
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
The c:\users\bfuchs\downloads\> was supposed to represent your DOS/cmd prompt.

Since you are already in Powershell, do the following:
cd \users\mf\downloads
& .\Aikimark.ps1

Open in new window

CERTIFIED EXPERT

Author

Commented:
What am I missing here?
Untitled.png
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Probably the second downloads in the path could be wrong..
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Curious is it a .csv ? after all
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Probably the second downloads in the path could be wrong..
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
What am I missing here?
Where did you save the PS script?
Confirm that you named it "aikimark.ps1"
Confirm that you changed the path and file name on the first line of the script.
CERTIFIED EXPERT

Author

Commented:
Hi,

Both files are under download folder, see attached.
Can you give me exact steps how to run this

thanks
Untitled.png
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
I posted the two steps in my earlier comment.  Did you try that?

Did you alter the first line of the PS script to reflect the path and name of your CSV file?
CERTIFIED EXPERT

Author

Commented:
Okay got this to work, will test now.
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
yaaay!

Keep us apprised of your progress.
CERTIFIED EXPERT

Author

Commented:
Hi,

Okay created a file containing those two record and when running with this script I get the attached.
How do I proceed now?

Thanks Untitled.png
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
what does your script look like?  

It looks like you ran the script once and got the expected output.

It looks like you're missing a leading "& " from your launching of the script
CERTIFIED EXPERT

Author

Commented:
See now.
Untitled.png
the goal is to figure out which field if we change will fix the problems?
thanks
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
1. Change the last line to eliminate duplicates in the output
  } | sort -Property fieldname -Unique | ft fieldname

Open in new window


2. Change the Access table definition so that the listed columns are data type memo instead of text
CERTIFIED EXPERT

Author

Commented:
1. Change the last line to eliminate duplicates in the output
this removes the character count, see attached.
Thanks

Untitled.png
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
You don't need the character count.  You only need to know which columns are longer than 255 characters.  Only these columns need to be memo data type.
CERTIFIED EXPERT

Author

Commented:
Thank you!
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
You don't need the character count.  You only need to know which columns are longer than 255 characters.  Only these columns need to be memo data type.
When I need an general importer, then I use always Memo for all columns in the staging table. Then a query evaluates the data type, thus also length, of the data in all columns. So you can produce a better error reporting, when the data is incorrect.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.