MS Access to space delimited text 1

[jrassette]

I am trying to convert a name and address table in an access database to a space delimited text file. 130,000 records in a Access XP db.

This is turning into a much bigger and complicated process than I thought it would be. Any help is much, much, much appreciated.

 

[Dreamboat]

Are you SURE you want space-delimited? That's not a good idea. I assume some of your data already contains spaces...

Anyway, you can always save it as text, open it in Word and replace whatever you use for the delimiter with a space.

But the REAL way, is to open the table or query, and hit File-Export, choose TXT/CSV as the type, and you'll be asked what delimiter you want...

What are the problems you're having? I was able to do this in a moment or two in Access 2000. Perhaps you don't have your Wizards installed? Better run install again and choose a custom install and "run all from my computer"...

Hoping something I said helps...

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121

 

[jrassette]

First - -thanks for the reply, this place is fantastic!!!

I know it sounds weird - - I like to consider myself a pretty savvy power office user.

Maybe space delimited isn't the correct term. Going back to the older days I think they would call what I am trying to do a 'block' or 'bulk' file.

When the easy to use export feature is employed the result is a mixture of mis-aligned text. Example:

last name first name address city
last name first name address city

I work with a vendor that requires the file to be this way. The easiest way I have found to do it is:

- Open Access
- Export 64,000 records to excel
- Change to courier new
- Manually set column widths to fit the data
- Save the excel file as a 'space delimited text file' (.prn)
- repeat this procedure with the remaining records
- Then combine the two files as one .txt file

It just seems to me that there should a much simpler solution . . . .

Hope to hear from ya!!!

 

[Dreamboat]

Okay. I'm checking it out.
You need fixed width...

I just did the same thing.

Choose TXT/CSV (it's a single choice called Text file), and then the wizard kicks in and asks ya... tell it you want fixed width...

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121

 

[Dreamboat]

If ya can't get it, you just upload or email that bad boy.
anne@vbaexpress.com

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121

 

[jrassette]

when I am back in the office tomorrow I'll give it another whirl.

It seems like the siliiest things can trip ya up sometimes!

 

[Dreamboat]

I hear ya.
But I have to warn you...lots of companies and people do a TYPICAL install of Office, which doesn't include the wizards. I would NOT know what to do without them--I'm no Access geek.

Also, there's an Access forum here, so you might want to ask your Access questions there. You'll probably get a quicker, better answer.

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121

 

[jrassette]

beautiful, wonderful, perfect - - -i tell ya, i am pretty embarrassed by that one. What you said worked flawlessly. Thanks!!!!

 

[Dreamboat]

Don't be embarrassed. We all do it!
I'm just so glad that you got it done. It can be scary knowing you need something done, when you have no idea how you're going to accomplish it. And so cool when you can accomplish it so easily! I've been there.

Anne Troy
Killer Coder Live Chat!

http://www.vbaexpress.com/forum/showthread.php?p=2121