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
Solved

Text File Link

Posted on 2000-03-31
5
243 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

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

Suggested Solutions

Title # Comments Views Activity
pauing printer deamon in AIX 10 63
llcommand 6 98
ovirt 3.6 guest VM support oracle solaris 4 81
Ubuntu install gnome-session-fallback is failing 4 114
Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
Java performance on Solaris - Managing CPUs There are various resource controls in operating system which directly/indirectly influence the performance of application. one of the most important resource controls is "CPU".   In a multithreaded…
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…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

790 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