Importing text file. Access not recognising CR

[ApplePirate]

I need help fast.

i have hundreds of files which i need to import into access, they are all text files but access does not recognise the carriage return within the file so i get one record from each file, e.g:-
&quot;Field 1&quot;,Field 2,&quot;Field 3&quot;->blacksquare<-&quot;Field 1&quot;,Field 2,&quot;Field 3&quot;->blacksquare<-&quot;Field 1&quot;,Field 2,&quot;Field 3->blacksquare<- (the black square is actually a black square)

all in one record. of course when the file holds thousands of records i get an error that the record is too large.

how do i get access to recognise the carriage return and import. thus making someones life very easy Never ever, bloody anything, ever

 

[DrSimon]

Importing files like you want to do is never fun - I say that from bitter experience. You don't say which version of Access you are using, but older ones often failed to work where Excel had no problem. To overcome that I often used to import into Excel, save as .XLS and import; but in your cause there are 'hundreds' so I assume you want to use VBA.
A few thoughts:
You say there is a 'carriage return' in each record. &quot;Carriage return&quot; can be made up of chr(10) (Form Feed) And/Or chr(13) (carriage return) going back to the days of teletypes. This can sometimes cause problems as can odd escape characters that you don't know about. The best way to check for this is to use a Hex Editor (if you don't have one I recommend the excellent shareware 'Hextreme'

http://www.dreamsyssoft.com/

)
If this is a one-off import of 'hundreds of files' and they don't hit the Excel 65,536 rows by 256 columns limit, you could still use Excel as port - write the code in Excel to import and save the Excel files and then another in Access to read them in. This is a bit inelegant but for some reason Excel is often better at importing 'odd' files than Access.

Simon Rouse

 

[ApplePirate]

Ive managed to sort this.
its a bit of a &quot;round the houses&quot; three step route. but managed all the same.
Using three modules, one moves the files from one location to another, at the time of moving the file, it calls another module which fixes the single Line Feed with a Carriage Return and Line Feed. and places them in a &quot;/fixed&quot; location. then i frix the file extension using DOS. and then import all the files with one module. 250 files in total took less than 5 minutes to fix and import. Never ever, bloody anything, ever

 

[DrSimon]

Thanks for letting me know. There's nearly always a way round it - it just takes a bit of thought and experience which, thank goodness, is what this Forum is full of.