?
Solved

How do I change record delimiters in Access txt exports?

Posted on 2008-10-11
12
Medium Priority
?
998 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 300 total points
ID: 22694394
No can do.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 700 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 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