Solved

Text File Link

Posted on 2000-03-31
5
227 Views
Last Modified: 2010-04-21
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?

0
Comment
Question by:jordanj
5 Comments
 
LVL 40

Expert Comment

by:jlevie
ID: 2674838
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
 
LVL 20

Accepted Solution

by:
tfewster earned 50 total points
ID: 2677414
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
 
LVL 2

Expert Comment

by:festive
ID: 2678516
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
 
LVL 20

Expert Comment

by:tfewster
ID: 2681091
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
 

Author Comment

by:jordanj
ID: 2694654
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to monitor remote shell execution on linux 9 97
Where to get php 5.6 for AIX 7.1? 5 76
Skill Development 15 162
Linux hostname change 2 51
This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now