[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I change record delimiters in Access txt exports?

Posted on 2008-10-11
12
Medium Priority
?
1,007 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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