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

How do I change record delimiters in Access txt exports?

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
rotnfire
Asked:
rotnfire
3 Solutions
 
dqmqCommented:
No can do.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
sstone55423Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sstone55423Commented:
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
 
rotnfireAuthor Commented:
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
 
rotnfireAuthor Commented:
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
 
sstone55423Commented:
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
 
sstone55423Commented:
I was just curious as to what programs would use that as a delimeter, not trying to be difficult.
0
 
rotnfireAuthor Commented:
4D
0
 
sstone55423Commented:
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
 
sstone55423Commented:
Have you suceeded?  Do you need further help with anything?
0
 
rotnfireAuthor Commented:
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

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.

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