We help IT Professionals succeed at work.

Append text to access using ITC and TransferText- end of line not recognized

mberumen asked
Last Modified: 2010-05-02
I am attempting to append data from an ftp server to my access database.  I am using the internet transfer control to retrieve a text file from the ftp server to a local directory. The text file contains no headers and I am trying to append it to an access table using the transferText command.  

the file downloads fine and i can open it using excel or notepad however when i try to import it to access (either manually or using VB) the end of line characters are ignored and access tries to import the contents of the entire file as a single field.

The ftp server is in a unix box and i am downloading the file to a NT machine.  could it be that the text file is in the wrong format?   Is the TransferText command the best way to append this text file to my table?

If anybody can point me in the right direction I'd really appreciate it (plus you get 100 points!)   :)
Watch Question

Top Expert 2005

Unix text files use a vbCr ( Chr(13) )character as the end of line, while Windows uses the vbCrLf ( Chr(13) + Chr(10) ) character sequence.

You may have to replace the vbCr with vbCrLf in order to import it correctly.


I'm aware that would work however during a day i need to download and append 960 text files (20 files every half hour).  I am looking for a solution that won't require file manipulation... is it doable?
Top Expert 2005

This is all I could find, sorry:

"ACC: Text Import Wizard Doesn't Import Data Correctly"
Top Expert 2005

The fastest way is to do the replace during the GetChunk method, when you recieve a chunk of text during the download.

You can then use the Replace function (VB6).  Be careful not to relpace if there is a vbCrLf already in the string.
Top Expert 2005
This one is on us!
(Get your first solution completely free - no credit card required)



I was trying to avoid that but I guess I don't have a choice.....

I hope you don't mind that I reduced the points awarded...   thanks
Top Expert 2005

No problem.
Thanks for the "A"
Good Luck.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.