Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2003, 10:03 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Text import - an extra bit

Hi All,
Ponder this if you would, please.
I am importing a text dataset produced by an old hospital system. The text file is fine. I have created an import spec which for the most part works fine as well but as you can see below, for each record it ends the line with \par. It doesn't matter which is the last complete field - it just appears where ever. If the field goes over the number of characters allocated then the \par gets spread. Is it some kind of crlf? Can I get rid of it? Over to you good people.

Investis Immunisation Proc/Rx code Anatom site Diag LR site Field60
XR AD 01 39\p a r
        OBS\par
XR AD 16 45 R \par
        AB\par
B REF 29\p a r
XR AD 47 45 L \par
        REF\par
        AB 20 45\p a r
XR AD 45 53 R \par
        RE\par
B NE 05\p a r
HA REF\par
E\pa r
HA\p ar
XR\p ar

Ta!
M


 
Old August 5th, 2003, 10:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by Morris
I am importing a text dataset produced by an old hospital system. The text file is fine. I have created an import spec which for the most part works fine as well but as you can see below, for each record it ends the line with \par.
Hmmm... without seeing the data I can only give you a first guess. When you import the data, are you importing as ASCII Delimited Text? If so, are you clearly checking off the paragraph mark as the delimiter? If not, if you're choosing a space or some other character, the CRLF codes may be mistakenly being converted to "\par" (i.e. "end of paragraph").

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old August 5th, 2003, 02:53 PM
Authorized User
 
Join Date: Jun 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It also might be possible to first import into Excel then imported as an Excel file into Access. But without seeing the file, I'm not sure this will be of any benefit.

-Shay Shepston
 
Old August 6th, 2003, 04:41 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi guys,
Thanks for the comments.
Shay, I used to import via Excel when I did similar reports in Office 97 but was trying to simplify the process as I might have to delegate the duty. I didn't have this problem when I tried it in 97.
I'm now using Office XP running on Windows 2000 and this is the first time I've done this with this setup and the \par thing has happened.
Greg, the end of paragraph does seem the most likely. I am importing using Fixed Width for the simple reason the the dataset in the text file often has the output from several fields all grouped together.The data shown above is just how it appears on the resulting table - in the text file it is fine. I suppose I could attempt to put a delimeter in the original query ( FOCUS used on a MUMPS database - yes, it is that old!). Our Info Dept haven't hit this problem yet as they're still on 97 but they do have a lot of stored queries, many with upto 100 fields. Upgrades are imminent. Could this just be an XP thing?
Cheese,
M :)

 
Old August 6th, 2003, 05:14 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Woooah, Hold the bus!
Sorted it. It wasn't Access after all, it was Wordpad.

The Access Import Wizard can't see the file produced on the original run but this file does open up in Wordpad. When saving as a text file ready for importing I need to specify .txt after the file name in the save as box - the File Type can be set as Text but unless I specify .txt as above I get the \par.
Thanks for your time and efforts.
Cheese M :D

 
Old August 11th, 2003, 10:54 AM
Authorized User
 
Join Date: Jun 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great Morris. I have found working with Notepad much more predictable then Wordpad...just for future reference!



-Shay Shepston
 
Old August 11th, 2003, 12:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Notepad is good, but can only handle small files.

For a text editor that can handle whatever you care to throw at it, and with lots of other useful features, I'd recommend TextPad (www.textpad.com).

It's shareware, so you'll have to shell out a few pounds, but I've been using it for years and would highly recommend it.


Brian Skelton
Braxis Computer Services Ltd.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Import- How to import the first 5 lines. nickzhang.zn Excel VBA 1 August 4th, 2007 05:06 PM
64 Bit - Issue in 64 bit IIS calling Win32 API Hubman General .NET 1 August 24th, 2006 09:19 AM
Excel IMport Text Help!! money Excel VBA 2 April 30th, 2004 12:43 AM
EXTRA TEXT AREA ON USER'S REQUEST satishb Classic ASP Databases 0 November 11th, 2003 07:39 AM
Mixing text and binary response yields extra chars brrrdog ASP.NET 1.0 and 1.1 Professional 1 June 11th, 2003 12:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.