• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

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

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!)   :)
0
mberumen
Asked:
mberumen
  • 5
  • 2
1 Solution
 
Erick37Commented:
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.
0
 
mberumenAuthor 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?
0
 
Erick37Commented:
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
0
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.

 
Erick37Commented:
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.
0
 
Erick37Commented:
Oh, and one last thing.
I originally stated that Unix used Chr(13), when in fact it uses Chr(10) as the LineFeed character.
0
 
mberumenAuthor 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
0
 
Erick37Commented:
No problem.
Thanks for the "A"
Good Luck.
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now