truncated text files in export

[Allyson]

I am getting truncated reports (Access 2003) when exporting them into text
files. The truncation doesn't occur on every database record reported, but
consistently occurs on specific records. The size of the record text field
doesn't appear to matter. I have checked the Microsoft articles on this and
found this: http://support.microsoft.com/?id=288877 but it only says that
the problem is due to hard returns in the fields. There are no hard returns
in any of my fields - all are imported from Excel and I have checked. Any
suggestions on how I can cure this problem? Thank you!

 

[Klatuu]

How have you checked? What value are you looking for in the offending fields?
A hard return would not be visible.

 

[Allyson]

I followed the directions in the article and looked for truncated sections in
text fields in the report itself that would indicate a hard return but found
none. I also looked in my source Excel document and there are none there.
When I export to .rtf every line ends with a hard return which doesn't really
tell me much. Is there another way to look? Any help will be most
appreciated! Thanks.

 

[Klatuu]

All lines in text files end with a hard return. A hard return has the value
Chr(13) & Chr(13) & Chr(10) which is also the values for vbNewLine and vbCrLf.
Have you tried opening the text file with something like wordpad to see what
it looks like?
If you are using an import spec, review it to see if there is something
there that might cause this. If not, try creating an import spec to see if
that might clear it up.

As you may have guessed, I don't really know the answer, I am trying to help
with ideas on how you can fix this. Hope I'm helping rather than wasting
your time.

 

[Allyson]

You're not wasting my time at all, as I enjoy learning from all the weird
stuff that Access does, even if I don't solve my problem! I was opening it in
Notepad or Word (2003) so I tried Wordpad and get the same truncation. I'm
sorry but I don't know what an import spec is, or I would be happy to review
it. My training in Access is grossly limited so I know no programming or
coding whatsoever, much to my dismay.

I appreciate your help! Allyson

 

[Klatuu]

An import spec defines the attributes of a text file you want to import or
export. It allows you to define the format of the file, select which columns
to include, etc. You will find it when you do your File->Import. After you
select the file, you will see a command button called "Advanced". Click on
that and you can set up the specification. You then give the specification a
name, and you can then identify the specification by name on future imports
or exports. Look up TransferText in Help.

 

[Allyson]

Boy I feel very stupid here. I'm exporting from Access into a .txt file so I
have no importing going on. When I export there is no "advanced" button.
Should I import from a specific program? If so, which one? Word? I couldn't
find any way of doing that, either (importing an Access report into any kind
of Word-compatible file). Sorry for being so obtuse - it's the kind of day
I'm having, I guess. Thanks so much!

 

[John Nurick]

All lines in text files end with a hard return.

Except, often, the last line.

A hard return has the value
Chr(13) & Chr(13) & Chr(10) which is also the values for vbNewLine and vbCrLf.

I think you meant Chr(13) & Chr(10), which is the value for vbCrLf and -
on Windows systems only - for vbNewLine.

However, if the data was imported from Excel, remember that line breaks
in Excel cells consist of Chr(10) only, i.e. vbLF not vbCrLf, and that
these are not automatically converted on importing into Access.

 

[Klatuu]

Yes, typo, sorry.
The last line usually ends with an end of file mark, but I can't remember
the code for that.

 

[Allyson]

All the data is imported from Excel. Is there something I can do in Excel
that will avoid the breaks when I import? Is this where I click the
"Advanced" button, when I import from Excel? This might be starting to make
sense to me. Or not. Thanks.

 

[Allyson]

Upon further investigation I don't see an "Advanced" button while importing
and Excel spreadsheet either. Any suggestions? Thank you!

 

[Klatuu]

It isn't there for an xls file. You can open your excel file, save it as a
..csv file, and when you import that, you will see the advanced button.

 

[Michael J. Strickland]

Try exporting the Excel file to a text file.

Then open the text file and check the offending records.

See if there are any broken lines (CR and LF) or control characters
(LF alone or CR alone) which usually show up as square characters.

 

[Michael J. Strickland]

Try importing the text file to Excel.

Then export it to a tab-delimited text file.

Then import that file to Access.