Solved

Text File Link

Posted on 2000-03-31
5
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 21

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 21

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A metadevice consists of one or more devices (slices). It can be expanded by adding slices. Then, it can be grown to fill a larger space while the file system is in use. However, not all UNIX file systems (UFS) can be expanded this way. The conca…
I promised to write further about my project, and here I am.  First, I needed to setup the Primary Server.  You can read how in this article: Setup FreeBSD Server with full HDD encryption (http://www.experts-exchange.com/OS/Unix/BSD/FreeBSD/A_3660-S…
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…

688 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