Handle Access 2007 import crashes due to exceeded character limit

[JJ Smokes]

Hello All,

You guys have been wonderful in here at helping out, so hopefully one of you knows this off the top of your head! I have searched the web and help sections as well as as searched this forum, but haven't been able to find a solution for this (hopefully) easy-to-fix problem...

I am importing a recurring .csv file into Access 2007 (~ 120 columns/ 20k records), which normally imports fine, however during the most recent import, Access 2007 stops the import with a "one or more rows exceeds the 65,000 character limit" error and fails the import. I know there is a character limit in Access (though this same data opens fine in excel) and I also know there are probably only a handful of offending rows.

My questions is: Because Access does not tell me in the error which are the offending rows nor gives me an alternate option to handle it besides just failing the import, what is the best way to determine which (and delete) the rows from this .csv that exceed this limit? And/or is there an option in Access to increase the character limit for a row?

Thanks guys!
JJ

 

[gemma-the-husky]

try this - replace myfile with your file name
not tested, but this should pick up any errors with length of lines in a csv

sub scanfile

dim filenum as long
dim myline as string
dim lincount as long

filenum = freefile
open "myfile" for input as #filenum

lincount= 1
while not eof(filenum)
  on error goto fail
  line input #filenum , myline
  lincount= lincount+1
nextline:
wend


fail:
msgbox("Error Reading Line: " & lincount)
lincount= lincount+1 'because it failed before the increment
resume nextline

end sub

 

[JJ Smokes]

try this - replace myfile with your file name
not tested, but this should pick up any errors with length of lines in a csv

sub scanfile

dim filenum as long
dim myline as string
dim lincount as long

filenum = freefile
open "myfile" for input as #filenum

lincount= 1
while not eof(filenum)
  on error goto fail
  line input #filenum , myline
  lincount= lincount+1
nextline:
wend


fail:
msgbox("Error Reading Line: " & lincount)
lincount= lincount+1 'because it failed before the increment
resume nextline

end sub

Thank you so much! I will give this a shot when I get home and let you know if it worked! You guys are great here...