?
Solved

Text File Link

Posted on 2000-03-31
5
Medium Priority
?
266 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 200 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Hello fellow BSD lovers, I've created a patch process for patching openjdk6 for BSD (FreeBSD specifically), although I tried to keep all BSD versions in mind when creating my patch. Welcome to OpenJDK6 on BSD First let me start with a little …
In tuning file systems on the Solaris Operating System, changing some parameters of a file system usually destroys the data on it. For instance, changing the cache segment block size in the volume of a T3 requires that you delete the existing volu…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
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.
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

800 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