Text File Link

I am trying to link a large text file (delimited with a pipe sign field delimeter).  The file is first being FTPd as a text file from one server to another using a UNIX FTP process.  This is placing a end of record marker (a bold vertical bar similar to a pipe sign) after each text record (approx 80 chars and 15 fields).  The MS ACCESS link wizard is enterpreting properly as a delimeted text file but somehow the end of record marker is causing ACCESS to conclude that the text file is just one long text record; therefore the link wizard fails because it exceeds a single record size maximum.  The text file needs to be delimited vs fixed because some of the fields may be blank.  Is there a way in ACCESS to deal with this end of record marker so the link wizard will work?  If not, can the UNIX FTP process be reset to drop the end of record marker?

jordanjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jlevieCommented:
Are you transfering the file in binary or text mode? I suspect it's being done in binary mode and the "|" may be access's representation of the bare newline character. Or it could be an artifact of the Unix process that creates the file (what does create the text file?). If it's a special character that's actually in the data, there are a number of ways it could be stripped out before the transfer. You can find out what's there by looking at the file from the unix side with "od -b" or "od -a".
0
tfewsterCommented:
Your'e on the right track, Jim.

jordanj, The Unix file will only contain a linefeed character (Which Unix understands to mean Newline/Carriage Return). DOS doesn't see CR, so it assumes the file is one long line; Access doesn't understand the linefeed character, so it represents it as a | symbol :(

I'm not sure if transferring in ASCII mode will fix this automatically; If it dosen't, you can convert the Unix file with

awk '{print $0,"\r"}' < inputfile > newfile

to add the CR.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
festiveCommented:
I concur with tfewster, I normally use two scripts "unixtodos", and "dostounix" (these should be serachable on the net, and come standard on some operating systems).

PC programs such as wordpad are capable of converting these files and could be used as an alternative if you are unix-impaired.
0
tfewsterCommented:
jlevie was spot on: Having tested ftp-ing a bit more today, & checking the results, ASCII mode adds the CR to the file when transferring from Unix to Windows, so there's no need to convert the file "manually".

(At the FTP prompt, just type "asc" to switch to ASCII mode before starting the file transfer [or add the "asc" command to script/.netrc etc. if your ftps are automated])
0
jordanjAuthor Commented:
tfewster's awk stmt worked great.  This allows us to link to the master text file that is being replenished daily.

Thanks much for your help.    jordanj
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.