We help IT Professionals succeed at work.

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

mberumen
mberumen asked
on
194 Views
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!)   :)
Comment
Watch Question

Top Expert 2005

Commented:
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.

Author

Commented:
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

Commented:
This is all I could find, sorry:

"ACC: Text Import Wizard Doesn't Import Data Correctly"
http://support.microsoft.com/support/kb/articles/Q149/9/46.asp
Top Expert 2005

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
erick37

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

Commented:
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.

OR

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.