error when exporting to text format

[lkot]

Hello,

I need to export to .txt (delimited) an Access table containing 250 fields and 115000 records. I use the export wizard. Shortly after hitting 'finish' I get the following message:

The field FIELD contains a start position of '32802'. The maximum start position allowed is 32767.

Field name and position number may vary, sometimes the field may be just empty but still evoke the error message.

I managed to export the table after deleting about 20 fields. This error message reappears when I use a blank database, another PC or another version of Access. Changing NumLocksPerFile does not help.

The data are imported into the table from a .csv (produced monthly), and after some manipulations exported back to text format. Recently we had some increase in number of records/fields (+ 3000 records and 5 fields). So I assume it has to do with some Access boundaries. Any idea what to do? Deleting fields is not always an option, so I would prefer to avoid it.

Thanks!

 

[DCrake]

It sounds like you have an integer field that should be a Long field. Also you tettering on the point of maximum limits.

You do not say what version of access you are using.

When you first import the csv file are you doing it to a new table or appending to an exiting table? It would be better if you appended it to an existing table as you can set up an import spec to handle the field types and sizes. Letting Access manage this can result in unexpected errors, similar to what you are encountering.

 

[lkot]

Hi David,
Thanks for your reply. I have no integer fields for which Long representation would be needed. The fields mentioned in error message mostly have a range like 1-1000, or are (half) empty. I also find it strange that they are all located at the right-hand end of the table, it looks like Access is exporting data column by column. I always import the data into an empty table because the number and order of fields often change and it is very cumbersome to check and fix this every time when you have 250 fields.
At work , I use Windows XP. I can check which version of Access that is if you are interested. BUT I also tried exporting the data using my home computer, and there I have Access 2007. Result was the same error message.
How can I find out the maximum limits of Access for importing/exporting a csv?
Regards,
lkot

 

[gemma-the-husky]

reading the thread again, the 32767 sounds like it is the maximum SIZE of an individual row/record, rather than a row count - ie the start postion of a given field

do you have any large memo text fields in your data selection. That may be the problem.

I dont think I export anything more than 1000 bytes or so, so I guess I would never see something like this.

 

[lkot]

do you have any large memo text fields in your data selection. That may be the problem.

No, never. My text fields are rarely longer than 20 symbols. Once I got this error message about an integer field with client age (maximum 98 years). I was really surprised to see in the message numbers like 32999 etc. Obviously something else is going on....

 

[DCrake]

Is it possible to post the first couple of lines from your txt file to examine?

 

[lkot]

Is it possible to post the first couple of lines from your txt file to examine?

Unfortunately I cannot do that, I work with bank client data.

 

[DCrake]

Re reading you original post it suggests that the average size of each field is 131 characters in length (32802/250). Access is finding it hard to parse this data line by line. You next approach is to do it field by field by row.

To do this you will need to use VBA and the Open File For Output As #1 approach.

To do this you first generate a recordset of the table

Then instigate an EOF Do Until Loop through the recordset

Then instigate an For n = 0 To Fields count

Parse/Concat the trimmed contents of each field together with "," delimiters to create one long string
Then output this string to the txtfile

When reached EOF close the txt file and the recordset.

Not if you want the field heading to appear in the table you need to output these before you commence the Recordset loop

 

[lkot]

Re reading you original post it suggests that the average size of each field is 131 characters in length (32802/250).

Thanks for your advice, David. I find it hard to believe when I see the first lines of the input text file in Notepad. But I realise that I do not see the rest of the file, so probably it is worth to check out. Do you have any idea how to check the field size programmatically?

 

[DCrake]

The problem may not exist in the first few lines in the file it may the 10,200th line, you see Access reads ALL the lines first before it does anything, it tries not to take anything for granted.

Try the following

Function TestLen()
Dim StrFile as String
Dim StrTxt As String
Dim N as Long

StrFile = "PathAndNameOfYourTextFile"
Open StrFile For Input As #1
Do until EOF(#1)
     Line Input #1,StrText
     n = n + 1
     Debug.Print n & vbTab & Len(StrText)

Loop
Close #1
End Function

Stick this in a module and run from the immediate window

This will echo the line number and length of each incoming line in the txt file.

 

[lkot]

David,

This was the output. I do not quite understand what does it mean, you do?

114289 1055
114290 667
114291 1035
114292 1043
114293 1032
114294 1165
114295 1033
114296 1035
114297 1083
114298 1043
114299 1052
114300 1043
114301 1043
114302 1078
114303 1072
114304 1055
114305 1048
114306 688
114307 1061
114308 1043
114309 1051
114310 662
114311 1047
114312 1044
114313 660
114314 1041
114315 664
114316 1069
114317 1044
114318 1053
114319 1053
114320 1069
114321 663
114322 655
114323 1051
114324 1232
114325 1055
114326 1056
114327 688
114328 1055
114329 1114
114330 1035
114331 1041
114332 1041
114333 1038
114334 1043
114335 1056
114336 1056
114337 1055
114338 1061
114339 1036
114340 1047
114341 1120
114342 1083
114343 769
114344 1046
114345 1068
114346 1043
114347 1046
114348 1050
114349 1055
114350 1055
114351 1067
114352 1046
114353 1041
114354 1040
114355 1130
114356 1051
114357 1055
114358 1023
114359 1050
114360 1056
114361 1043
114362 1046
114363 1050
114364 1026
114365 1032
114366 1032
114367 1043
114368 1046
114369 1050
114370 1044
114371 1027
114372 1073
114373 774
114374 1021
114375 1043
114376 897
114377 1046
114378 667
114379 1069
114380 1055
114381 1046
114382 924
114383 1039
114384 1048
114385 1113
114386 688
114387 1046
114388 1244
114389 666
114390 1073
114391 1045
114392 666
114393 1025
114394 1114
114395 1043
114396 1066
114397 1059
114398 1033
114399 1057
114400 1045
114401 1060
114402 1198
114403 1054
114404 1057
114405 1038
114406 1067
114407 1046
114408 1037
114409 1078
114410 681
114411 1070
114412 809
114413 1055
114414 1056
114415 1065
114416 1050
114417 1076
114418 1078
114419 1041
114420 1067
114421 1051
114422 851
114423 1060
114424 1072
114425 687
114426 1046
114427 1054
114428 1136
114429 1077
114430 680
114431 1077
114432 708
114433 1043
114434 1060
114435 1043
114436 1138
114437 1042
114438 1058
114439 1273
114440 1125
114441 1055
114442 1049
114443 917
114444 1072
114445 877
114446 1107
114447 1055
114448 662
114449 1066
114450 1067
114451 1068
114452 1116
114453 1071
114454 1072
114455 1072
114456 1067
114457 1072
114458 1057
114459 1065
114460 1077
114461 1074
114462 1073
114463 1077
114464 1076
114465 1072
114466 1078
114467 1077
114468 1077
114469 1078
114470 1081
114471 1077
114472 1073
114473 1073
114474 1061
114475 1065
114476 1077
114477 1077
114478 1081
114479 1077
114480 1071
114481 1156
114482 1093
114483 1051
114484 1075
114485 1076
114486 1308
114487 1154

 

[DCrake]

In the immediate window it echos what was requested in the code

Debug.Print n & vbTab & Len(StrText)

Where n is the row number in the text file
and Len(StrText) is the length of all the fields in the string added together.

From what I can see there are none over 2000 chars in length. However this is only the back end of the file.

So doing it in code would not be a problem.

 

[lkot]

So the string is never too long (as far as the output window goes). However, I still do not see the reason for the error message....

Regarding using VBA for exporting: we will certanly review the procedure, but this will take time. Doing it in VBA right now is too risky, considering the number of fields.

 

[gemma-the-husky]

No, never. My text fields are rarely longer than 20 symbols. Once I got this error message about an integer field with client age (maximum 98 years). I was really surprised to see in the message numbers like 32999 etc. Obviously something else is going on....

what you said initially was the reported error related to the field start position .... you didnt mention a client age did you?

that was why i thought that maybe the error related to the overall size of the record

try reducing the number of fields output, just to see if that makes a difference.

 

[lkot]

I did mention that the range of integer fields causing the problem was rather small, say, 1-1000.
I was able to export the table when I deleted about 20 unused fields, so this is my solution of this problem at the moment.

 

[gemma-the-husky]

if you export to text (csv?), then access turns all the columns in your query/table into text, and puts a delimiter character in between them - it does not do this a column at a time.

I can only repeat that the description of your problem sounds like a particular assembled row has a length longer than access can handle - and maybe its even a windows system limit - although i suspect not.

you have achieved an export by deleting a number of columns, which clearly points to this being the problem, as this procedure will shorten the row length. I would open the txt file manually in notepad, say, and scroll down, to see if you can see a really long row.

alternatively, you could write a proc in vba, or something else, to read in the text file, and examine the row length.

----------------
here's another thought

look at your table

a) the standard text field size is 50 - are yours all 50, or are any/all set to longer lengths (if your export pads every field to the full maximum length, this might be the problem)
b) do you have any memo fields - these are the REAL targets for investigation.

so have a query and put in the query len(mymemofield), and sort these descending - this will identify if you have any unexpected very long data strings.

 

[DCrake]

Dave

If I remember rightly you cannot sort a memo field?

 

[lkot]

Dave,
Thanks for useful thoughts. I do not have any memo fields, otherwise I would already suspect something....I also do not see anything strange in Notepad, besides some missings (which is normal for this type of data).

 

[DCrake]

Ok Try this.

Edit the txt file in notepad/wordpad and crop off the first 100 or so records. Save them to a new txt file and try and import that.

If it errors then reduce the cropping until you spot the glitch. It's a case of trial and elimination

 

[gemma-the-husky]

Dave

If I remember rightly you cannot sort a memo field?

no, hence evaluate the length of the fields, and sort that value