Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

import fixed length, undelimited text file for labels

Posted on 1999-06-30
12
Medium Priority
?
412 Views
Last Modified: 2012-08-14
I have a flat file of label field information (name,
address, city, state zip) formatted with fixed length
fields but no delimiters (That's how the other program
unloaded the information).

How can get this into a Word document that will merge
the information into one of the standard Avery label
formats?

Thanks,
Richard

0
Comment
Question by:rlg111
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 13

Expert Comment

by:cri
ID: 1607667
If it is a one time task: Use Excel as converter to delimited format of your liking. Ask back if needed.

If task done more than a few times: Wait for the VBA gurus. In the meanwhile: Indicate the length for each field. Less to adapt afterwards....

0
 

Author Comment

by:rlg111
ID: 1607668
Cri,

Thanks for the fast turnaround!  I looked at the Excel help for imports
but only found references to Microsoft Query for ODBC-based links
to databases.  Where do I define the fixed length format?
0
 
LVL 13

Expert Comment

by:cri
ID: 1607669
I guess this flat file is just an ASCII file ? No help required. Just open it with *.* (All Files) or *.txt and follow the Text Import Wizard. If none, then see if you can load the File Import Wizzard add-in. If none, then it must be installed i.e. run MS-Office setup. If too shorthand: Complain.

0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 1

Expert Comment

by:BarbMpls
ID: 1607670
Richard,

In Excel, choose File, Open, select the text file, and a wizard will appear to ask all the format-related questions.  Add column headings if they didn't come in with the data; save the file; and use it as the data source for your Mail Merge in Word.

Once you've done that and are comfortable with it, you can close the (imported) file without saving, start the Macro recorder, and do it again.  Then you'll have a macro which will allow you do repeat the process whenever you need to.

The next layer of enhancements could be done in VBA or manually depending on how often you need to recreate this file.  They would include things like prompting the user for the name of the file to import, automatically adding titles, even automatically firing up Word and completing the merge based on the list you just imported.  Let us now how far down this road you want to travel.
0
 

Author Comment

by:rlg111
ID: 1607671
Ok, Cri and BarbMpls,

I got the File Converter installed, changed my input file name to list.txt
from list.csv (it wasn't csv'd so Excel assumed incorrectly that the fields
were delimited), opened the file, and got it formatted into an Excel
spreadsheet.  I know I can save this as csv (a real csv) or whatever, and
open that in Word.

Do you know how to get Word to read the csv and parse the fields into
a field-based list that can be laid out on an Avery label formatted page?

Thanks,
Richard

p.s. I upped the points to 200 since this is becoming more involved
than I originally expected.
0
 
LVL 13

Expert Comment

by:cri
ID: 1607672
I think BarbMspls should take over. Must stop now and VBA/Word is neither my strenght nor my main interest.

Please state if this is a one time task or not. I makes me almost sad to see really nice code applied on stuff done faster by hand. Or do you have hundreds of records ?

Thanks for the idea of upping. However this makes it impossible to split points w/o bothering customer service. If applicable, I hand out points separately, see my question regarding Autoshapes.
0
 

Author Comment

by:rlg111
ID: 1607673
Cri,

This is a one-time task on a set of hundreds of records.  If it was just a
few items I would have rekeyed them and been done with it.

Thanks for your help.  I'll have to see how to award points separately
to make sure you get your fair share.

Richard

0
 
LVL 1

Expert Comment

by:BarbMpls
ID: 1607674
Richard,

Actually, I *think* it may be even easier than you are imagining.  Once you have the file converted and saved as an Excel spreadsheet, here's what I would try next:

Close the Excel file, and open Word.

Now, if your Tools menu contains a choice called Avery Wizard, choose that, pick the label size, and on the next screen browse to select your Excel spreadsheet as the data source.  Complete the wizard, and all should be well.

If you don't have the Avery Wizard, you can do essentially the same thing with Tools, Mail Merge.  Choose Create, Mailing Labels.  Then move on to step 2.  Choose Get Data Source and browse to find the Excel file.  Then Word will tell you it needs to lay out the Main Document.  Choose the label number, and you will see a sample of one lable.  Use the Insert Merge Field to select the fields you want on the label (typing in your own commas, spaces, and returns for new lines).  The third step is to click the Merge button, merge to a new document, and view the results.

You now have two documents open in Word:  The Main Document, which is the layout with field names representing the data to be merged, and a document called Labels# which is the finished product after that Main Document was "blended" with the data source (the Excel spreadsheet).

If your list will be volatile, you don't need to save the Labels# document, but definitely save the Main Document.  The next time you need to print labels, just open it, go to Tools Mail Merge (or click the Mail Merge button on the new toolbar which was displayed), and repeat only step 3 to create a new, updated set of labels.

Hope that helps.  If you try it and it works for you, I'll submit it as an official Answer; if there are still questions, please ask.

0
 

Author Comment

by:rlg111
ID: 1607675
Barb,

Thanks for your help.  I've been playing with the Word Mail Merge Wizard and
ended up doing pretty much what you suggested.  The labels look fine.

Thanks for your help!

Richard

0
 
LVL 1

Accepted Solution

by:
BarbMpls earned 800 total points
ID: 1607676
Glad it worked!  Here's the content of my last comment posted as an answer:

Once you have the file converted and saved as an Excel spreadsheet, here's what I would try next:

Close the Excel file, and open Word.

Now, if your Tools menu contains a choice called Avery Wizard, choose that, pick the label size, and on the next screen browse to select your Excel spreadsheet as the data source.  Complete the wizard, and all should be well.

If you don't have the Avery Wizard, you can do essentially the same thing with Tools, Mail Merge.  Choose Create, Mailing Labels.  Then move on to step 2.  Choose Get Data Source and browse to find the Excel file.  Then Word will tell you it needs to lay out the Main Document.  Choose the label number, and you will see a sample of one lable.  Use the Insert Merge Field to select the fields you want on the label (typing in your own commas, spaces, and returns for new lines).  The third step is to click the Merge button, merge to a new document, and view the results.

You now have two documents open in Word:  The Main Document, which is the layout with field names representing the data to be merged, and a document called Labels# which is the finished product after that Main Document was "blended" with the data source (the Excel spreadsheet).

If your list will be volatile, you don't need to save the Labels# document, but definitely save the Main Document.  The next time you need to print labels, just open it, go to Tools Mail Merge (or click the Mail Merge button on the new toolbar which was displayed), and repeat only step 3 to create a new, updated set of labels.


0
 
LVL 13

Expert Comment

by:cri
ID: 1607677
rgl111,

Glad it worked out. Do not bother with the points _this_ time. Not worth it.

BTW: I did not mean to re-key it. If you follow this section in future you will see that there are some VBA addicts here, whom I love pulling the legs, because they make elaborate code for stuff you faster done with some cut and paste IF it is an one time job....

BTW2: I think the file import wizard of Excel actually works. You must define at the begining what you have, the default is delimited if my memory does not fail me.

Regards
Cri
0
 

Author Comment

by:rlg111
ID: 1607678
Cri,

Thanks again for your help.  I'll save the points for another time then.
The Excel import wizard worked just fine once I figured out that the
problem was that it wasn't even installed.

Richard

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

704 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