Solved

How do I change record delimiters in Access txt exports?

Posted on 2008-10-11
12
969 Views
Last Modified: 2013-11-15
I would like to be able to change the record delimiter in Access, but cannot find any information on how this can be done.  I know how to change the field delimiter.  That capability is easy to manipulate.  It seems the record delimiter is set as CR?

Best Regards,
Phil
0
Comment
Question by:rotnfire
12 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 75 total points
ID: 22694394
No can do.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22694404
how are you exporting the files as text?

you can modify the export spec by doing a manual export,
click the Advanced button in the import/export wizard to access the export spec

0
 
LVL 8

Assisted Solution

by:sstone55423
sstone55423 earned 175 total points
ID: 22694439
As you know, you can change the field delimeters when you export, but you cannont specify the record delimiter.  The Carriage Return is the defualt record delimiter using access.  I think that the default formats are tab delimited (txt), comma delimited  (csv) and space delimited (prn) as well as data interchange format (dif).
You could create a custom VBS script to export/import with a different delimiter.
Also, you could use SQL servers DTS tool to use a different delimiter.
 http://en.wikipedia.org/wiki/Comma-separated_values
http://en.wikipedia.org/wiki/Data_Interchange_Format
http://en.wikipedia.org/wiki/Tab_delimited
 
 
0
 
LVL 8

Expert Comment

by:sstone55423
ID: 22694466
If you are wanting to use this file on a Linux/Unix system, you could use a utility like dos2unix or unix2dos to change the CR to CRLF and vice verse.
0
 

Author Comment

by:rotnfire
ID: 22696004
No, I was hoping there was some method to create some other record delimiter in exports from Access.  I hadn't found any method for accomplishing this on my own, and it seems that my research is correct.  I realize I can use any field delimiter, and can set my default delimiter to be anything as well.  It's the record delimiter I want to change.
0
 

Author Comment

by:rotnfire
ID: 22696007
The advanced option in Access only allows for field delimiter change, not record delimiter.  I'm interested in setting a custom record delimiter, such as Char(124).
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Expert Comment

by:sstone55423
ID: 22696017
What use is a record delimiter of Char(124)?  You could modify the dos2unix command (open source available) and put your preferred delimiter there.  What program reads that kind of file?  A VBA would be easy to write to do that too.
0
 
LVL 8

Expert Comment

by:sstone55423
ID: 22699315
I was just curious as to what programs would use that as a delimeter, not trying to be difficult.
0
 

Author Comment

by:rotnfire
ID: 22708799
4D
0
 
LVL 8

Expert Comment

by:sstone55423
ID: 22711584
I have seen a variety of documentation that suggests that 4D accepts standard tab-delimited files for importing databases.  tab-delimited format uses CR as the record delimiter.  Have you tried to do it that way?
0
 
LVL 8

Expert Comment

by:sstone55423
ID: 22721733
Have you suceeded?  Do you need further help with anything?
0
 

Accepted Solution

by:
rotnfire earned 0 total points
ID: 22879902
Sorry, This is unacceptable that I took so long to respond to this.  I just accepted that I cannot do what I am trying to do in Access.  This long delay was my own fault, and I apologize.

In response to your question about 4D.  4D does accept tab delimited files for import, however, the problem I have is that some fields are memo fields, and they contain large amounts of information.  That information within the field contains CR's.  When exporting and importing, the CR's are interpreted as new records, and the entire export and import becomes corrupt.  It would be easy if I could change the end of record delimiter to something other than a CR.  I work in Access to manipulate the data.  I am considering changing to 4D to manipulate the data, because 4D does allow the delimiters to be set to anything, that includes both field and record delimiters.  I have found a way to change the CR's within the field to pipes, and then the export works fine with no corruption.  Then, the pipes are changed back to CR's when importing into 4D, and the information is correct.  The CR's are important in the particular field, since the information must line up in a specific orientation.

I just think this is a dead end, and I have to look for a different solution.  At this time, what is working is changing the CR to a pipe within the field, exporting, and then changing back to CR within the field when importing.  I will be looking into 4D to export the data, and then I will have the power I need, which isn't available in Access.

Thanks.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now