[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export field with carriage return into text file problem

Posted on 2006-05-24
9
Medium Priority
?
587 Views
Last Modified: 2008-02-01
I am trying to export records from an excel query into a text file using the following commnand
DoCmd.TransferText acExportDelim, , "qryTimeSheetExport", Application.CurrentProject.Path & "\Temp.txt", True
The export happens but where I have a (Memo) field that has a carriage return (new line) the transfer starts a new record at that break. Any suggestions on how to export reliably?
0
Comment
Question by:Rob4077
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 300 total points
ID: 16749227
I dont know if creating import/export specifications may help here

u initially define a specification by going into File/GetExternalData/Import, select text data type, pick a file, then from dialog, go into Advanced
Here you can define how you wish to import/export a file. You then save it as a specification

Now on your DoCmd.TransferText, the 2nd parameter (after acExportDelim, Before qryTimeSheetExport, you enter your specification name
0
 
LVL 58

Accepted Solution

by:
harfang earned 1700 total points
ID: 16749330
Hello Rob4077

Delimited text is not meant to be used with multi-line fields. Smart programs will accept new lines between the text delimiters, eg:

108; "John"; "Doe"; "Appartment 42
Crescent Groove"; "London"; etc...

Most don't, and will instead "repair" the "missing" delimiters... For text fields, you can use Replace to export a clened field. I usually use '§' for that:

    AddressML: Replace([Address], Chr(13)+Chr(10), '§')

Producing this:

108; "John"; "Doe"; "Appartment 42§Crescent Groove"; "London"; etc...

I then reinsert the line breaks in the receiving program.

However, this does not work with Memo fields, because any function will truncate the text at 255 characters. Sadly, the same is true when exporting to Excel directly; multi-line fields transfer just fine, but only the first 255 characters! For a simple "export to Excel" function that doesn't mangle anything, see {http:/Q_21856234.html#16715197}

If you need to easily transfer text containing memo fields, XML is the only safe format. Can you use that?

Good luck!
(°v°)
0
 

Author Comment

by:Rob4077
ID: 16749641
Thanks rockiroads and Markus for your comments.

rockiroads, I don't know if you suggestion would work - Markus' comments seem to imply it woouldn't and his tone is confident. And since I don't really mind truncating it seems like an easy solution in this case. I appreciation your input nevertheless.  

Markus, the reason I need to export is to get it into an accounting package called MYOB. It seems to only accept comma or tab delimited files. I think what I will do is a two step process - first truncate the field to 255 characters and then try your replace trick on the result (    I hope     NewFieldName:iif(len(myLongFieldName) < 252, myLongFieldNAme, Left(replace(myLongFieldName,chr(13) + chr(13,'§'),251) & " ..."     in the query will do the trick). Truncating to 255 there should not be too much of a problem. I can't see how XML will help under the circumstances.

Incidentally Markus, how did you get from Prehistorical Archaeology to MS Access? The two fields seem so diametrically opposed that the jump seems unusual!

Rob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16749941
He uncovered a protohuman holding a 386 notebook loaded with Access 1.0 on it and never looked back ;-)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16749970
Rob4077

Actually, you do not need to truncate. Replace() will never return more than 255 characters when used in a query, so your IIf() is probably and overkill. If you need the "...", your expression contains a flaw: it applies Replace() only on long memos. Use this instead:

NewFieldName: Replace(myLongFieldName, Chr(13)+Chr(10), '§') & IIf(Len(myFieldName)>255, '…', '')

About your other question, and to make a long story short...

Computers have always been my hobby, and my "travail de diplôme" (final paper of a bachelor's?) was about "artifical intelligence and archaeology". I ended up doing the first databases of findings on digs, archaeozoological measures, etc. I started teaching IT on the side because archaeology doesn't pay enough to feed a family. From there, I got more and more jobs in small application development, and it ended up being my main occupation.
More details on http://www.harfang.ch/mgf/infoarcheo.html (in French)

Cheers!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16750083
LOL, Leigh ;)
Ever seen that fake add for Windows NT — Neanderthal Technology?
(°v°)
0
 

Author Comment

by:Rob4077
ID: 16750226
Thanks for the added clarification on the iif and for the update on your background. You are a man of many talents. How many languages to you speak? English, French, VBA... At least I understand the transition better. It was the shift from an outdoor focus to an indoor focus that most perplexed me. It seems you must have always had that indoor focus. Anyway, thanks again for your help.  
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16756607
(Never have seen that Markus - and of course couldn't possibly comment as to it's appropriateness ;-)

Perhaps there's an analogy of them having a larger cranium - but poorer communication?
(Hmm demands more memory - but doesn't interconnect well? :-)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16757033
NT@ http://www.lohrere.com/images/funny_winnt.gif
(note: site found from a link "what you should't do when building a web site", but it has a jokes page...)
(°v°)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

834 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