Text File to Label

[akafrog18]

Our Unix system has a feature to print labels. We print the labels to text files and then FTP them to our Windows machines. Once here there is no easy way to print these. We must copy and paste them into Word to get them to print. Is there some way to easily print these by doing a merge directly from the file. The sample of the output is placed below.

SAMPLE LABEL FROM TEXT FILE:
Cust P/N: 900803260
Cust PO#: HAM-0000500540
OUR P/N: NVV5FS4-01T-041-03T
ASSY, MANIFOLD, 4 STA

 

[SkipVought]

Hi,

I'd recommend having your file from UNIX formatted like this, either FIXED LENGTH or Comma Delimited...
[tt]
Cust P/N Cust PO#: P/N Nomen
900803260 HAM-0000500540 NVV5FS4-01T-041-03T ASSY, MANIFOLD, 4 STA
[/tt]
Then you could do a simple mail merge

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[akafrog18]

We cannot change the way the system outputs the label. It is a built in function for our customer database. It basically prints it to a text file when we do a label. Any other suggestions?

 

[SkipVought]

1. Is each record ALWAYS 4 lines in the same format?

2. Does every 5th line begin a new record?

Please post several record's worth of lines.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[akafrog18]

The label will always be 5 lines and the 6th line will always be blank to seperate the labels. I forgot the last line on the first post. I've posted some examples below. Thanks for any help you can give.

Example 1:
Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104828
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME




Example 2:
Cust P/N: 900120854
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120854
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120854
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120854
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120854
Cust PO#: HAM-0000526588
OUR P/N: NVFS2400-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME


Example 3:
Cust P/N: 900120862
Cust PO#: HAM-0000526588
OUR P/N: NVFS3201-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120862
Cust PO#: HAM-0000526588
OUR P/N: NVFS3201-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120862
Cust PO#: HAM-0000526588
OUR P/N: NVFS3201-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900120862
Cust PO#: HAM-0000526588
OUR P/N: NVFS3201-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

 

[SkipVought]

You have 3 examples.

Why are the labels identical?

Will there always be the same number of identical labels?

Is there anything different when you transition from one label to a different label, other than the obligatory empty row?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[akafrog18]

The labels are identical due to those orders only having one item ordered on each order but multiple of the item. This will not always be the case. As in the example listed below you can see that there were multiple different items ordered on the one order. There is only the empty row between each label to seperate it. There will not always be the same number of labels for each order. It could range from 1 label for an order to 100 labels. The system should be flexible and be able to notice this.

Thanks


EXAMPLE:
Cust P/N: 900104835
Cust PO#: HAM-0000530237
OUR P/N: NVFS3101-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900104835
Cust PO#: HAM-0000530237
OUR P/N: NVFS3101-5FZ
VALVE SOL/PILOT 24VDC
OUR COMPANY NAME

Cust P/N: 900123920
Cust PO#: HAM-0000530237
OUR P/N: G36-P10-01-X30
GAUGE
OUR COMPANY NAME

Cust P/N: 900123920
Cust PO#: HAM-0000530237
OUR P/N: G36-P10-01-X30
GAUGE
OUR COMPANY NAME

Cust P/N: 900413095
Cust PO#: HAM-0000530237
OUR P/N: NVFS3301-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900413095
Cust PO#: HAM-0000530237
OUR P/N: NVFS3301-3FZ
VALVE SOL/PILOT 110VAC
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900420783
Cust PO#: HAM-0000530237
OUR P/N: AS2211F-U02-06
FLOW CTRL, UNIFIT, METRI
OUR COMPANY NAME

Cust P/N: 900801316
Cust PO#: HAM-0000530237
OUR P/N: NAN200-N02
MUFFLER 1/4" NPT
OUR COMPANY NAME

Cust P/N: 900801316
Cust PO#: HAM-0000530237
OUR P/N: NAN200-N02
MUFFLER 1/4" NPT
OUR COMPANY NAME

Cust P/N: 901016698
Cust PO#: HAM-0000530237
OUR P/N: D-F79WSAPC
AUTO SWITCH
OUR COMPANY NAME

Cust P/N: 901016698
Cust PO#: HAM-0000530237
OUR P/N: D-F79WSAPC
AUTO SWITCH
OUR COMPANY NAME

Cust P/N: 901016951
Cust PO#: HAM-0000530237
OUR P/N: AF50-N06-Z
FILTER 3/4" NPT PORTS
OUR COMPANY NAME

 

[SkipVought]

1. Use Data/Import External Data/Import Data... to get the data into Sheet1 at A2. Make A1 the Column Heading Record

2. Make B1 Seq and put this formula in B2
[tt]
=row()-1
[/tt]
and copy it down thru all the rows of data.

3. On Sheet2, insert a Query in A1: Data/Import External Data/New Database Query - Excel Files -- YOUR WORKBOOK -- Sheet1$ and Edit the Query.

4. In the MS Query Editor, drag Record into the display grid.

5. Hit the SQL button and paste in this string at the END of the SQL...
[tt]
where seq mod 6 = 1
[/tt]
6. Edit/Return data to Excel

7. Copy Column A, paste into columns B thru E

8. Select in each of columns B thru E and Edit/Import External Data/Edit Query and CHANGE
[tt]
where seq mod 6 = [red]2[/red]
[/tt]
corresponding to the NUMBER of the COLUMN that you are editing.

Here are the results I got with about 3 minutes of work...
[tt]
Record Record Record Record Record
Cust P/N: 900104835 Cust PO#: HAM-0000530237 OUR P/N: NVFS3101-5FZ VALVE SOL/PILOT 24VDC OUR COMPANY NAME
Cust P/N: 900104835 Cust PO#: HAM-0000530237 OUR P/N: NVFS3101-5FZ VALVE SOL/PILOT 24VDC OUR COMPANY NAME
Cust P/N: 900123920 Cust PO#: HAM-0000530237 OUR P/N: G36-P10-01-X30 GAUGE OUR COMPANY NAME
Cust P/N: 900123920 Cust PO#: HAM-0000530237 OUR P/N: G36-P10-01-X30 GAUGE OUR COMPANY NAME
Cust P/N: 900413095 Cust PO#: HAM-0000530237 OUR P/N: NVFS3301-3FZ VALVE SOL/PILOT 110VAC OUR COMPANY NAME
Cust P/N: 900413095 Cust PO#: HAM-0000530237 OUR P/N: NVFS3301-3FZ VALVE SOL/PILOT 110VAC OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900420783 Cust PO#: HAM-0000530237 OUR P/N: AS2211F-U02-06 FLOW CTRL, UNIFIT, METRI OUR COMPANY NAME
Cust P/N: 900801316 Cust PO#: HAM-0000530237 OUR P/N: NAN200-N02 MUFFLER 1/4" NPT OUR COMPANY NAME
Cust P/N: 900801316 Cust PO#: HAM-0000530237 OUR P/N: NAN200-N02 MUFFLER 1/4" NPT OUR COMPANY NAME
Cust P/N: 901016698 Cust PO#: HAM-0000530237 OUR P/N: D-F79WSAPC AUTO SWITCH OUR COMPANY NAME
Cust P/N: 901016698 Cust PO#: HAM-0000530237 OUR P/N: D-F79WSAPC AUTO SWITCH OUR COMPANY NAME
Cust P/N: 901016951 Cust PO#: HAM-0000530237 OUR P/N: AF50-N06-Z FILTER 3/4" NPT PORTS OUR COMPANY NAME[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[akafrog18]

This works great. Now is there a way to make the excel file automatically import the data from the text file? This way I could open the excel file it would import the new data from the file, then the query would run and fill in the Sheet2 and then I could open a preset word file to print the labels. Thanks

 

[SkipVought]

Check each QueryTable's Data/Import Data/Data Range Properties for automatic refresh options

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}