Bangers uses Presto to transform their green screens for increased efficiency
Hi Ralph,
Many users find green screen interfaces cumbersome to use. Learn how Bangers, a sporting goods distributor, used Presto to modernize their green screen warehousing application in only two weeks.
Faced with the challenge of making one of their most essential RPG applications more intuitive for millennial users, Bangers IT director Todd Glassford knew he needed a solution that could leverage existing, proven RPG code to deliver an updated application.
Using Presto, Bangers’ own PRG programmers were able to deliver an updated application that now has:
- A modern UI accepted by all users
- Mobile-accessibility
- Easier point and click navigation
- Visual display of information with charts and graphs.
Bangers are very happy with the performance of the modernized application.
“Our millennial staff now have the web UI they wanted,” Says Todd.
Read Bangers’ success story here.
Best Regards,
![]() |
Marcel Sarrasin VP of Corporate Marketing and Business Development Fresche BCD | Quadrant Software | Looksoftware (250) 655-1882 ext. 114 |
SNADS over IP
by Jun 17, 2012 | Configuration Documents and Tips | 0 comments
|SNADS for AS/400 iSeries shops is an invaluable tool. A few years ago I found this document written by Pam Phillips and I would like to thank her for her great work in producing it.
For those of you not familiar with SNADS, it offers a wide variety of helpful features. From sending files, executing remote jobs etc. and it also facilitates more easily moving objects from one machine to another regardless of the OS release.
I hope you find this as helpful as I did. SNADSoverIP
Configuring a RMTOUTQ to Send SPLFs from one IBM System i to Another using LPR/LPD
![]() |
This document contains information on configuring and using a Remote Output Queue (RMTOUTQ) to send spooled files to one IBM System i to another using the LPR/LPD (Line Printer Requester/Line Printer Daemon) protocol. Requirements include a TCP/IP interface and IP address configured on both systems and a Line Print Daemon (LPD) server running on the destination system. For information on configuring a Remote Output Queue (RMTOUTQ) for a printer or another type of system (for example, a Microsoft Windows NT Server or UNIX Server), refer to one of the following documents: 8983237, Configuring a Remote Output Queue (RMTOUTQ) For information on configuring and using a Remote Output Queue (RMTOUTQ) to send spooled files to one IBM System i to another using SNA, refer to the following document: 11855333, Printer Passthrough or Remote Output Queues This document was last updated on 4 January 2011.
Requirements include a TCP/IP interface configured in the operating system, an IP address on the printer side, and a Line Print Daemon (LPD) TCP/IP process running on the print server. Restrictions include no page range printing. Controlling the output can also be a problem because of the lack of control over the sending of spooled files. Once a spooled file has started sending, a user may have a tough time stopping the printing from the operating system side. Putting the spooled file on hold or deleting it may not be successful. For a more complete listing of restrictions, refer to the following Rochester Support Center knowledgebase document: 14143423, Capabilities and Limitations of Remote Output Queues (RMTOUTQs):
There are no required PTFs in order to print using a Remote Output Queue (RMTOUTQ), but getting the latest Print Group PTF will typically help you avoid problems:
Otherwise for a list of the latest PTFs, including PTFs that are not yet included in the latest Print Group PTF, contact the Rochester Support Center or refer to the Recommended Fixes data base at the following Web site: http://www-912.ibm.com/s_dir/slkbase.nsf/recommendedfixes
To create a remote output queue, use the Create Output Queue (CRTOUTQ ) command. Specify the following parameters: Output Queue and Library (OUTQ) 7995114, Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ): Remote System (RMTSYS) If a TCP/IP address is used, verify that the system can reach the other system by using the PING or Verify TCP/IP Connection (VFYTCPCNN ) command with that TCP/IP address. If a host name or DNS name is used, verify that the system can reach the other system by using the PING or Verify TCP/IP Connection (VFYTCPCNN ) command with that host name or DNS name. Remote Printer Queue (RMTPRTQ) Writers to Autostart (AUTOSTRWTR) Connection Type (CNNTYPE) Destination Type (DESTTYPE) Host Print Transform (TRANSFORM) User Data Transform and Library (USRDTATFM) Manufacturer Type and Model (MFRTYPMDL) Workstation Customizing Object (WSCST) Internet Address (INTNETADR) Destination options (DESTOPT) Print separator page (SEPPAGE)
The following is an example of a Remote Output Queue (RMTOUTQ) to another iSeries or AS/400 system. The “>” indicates important parameters to set. The Remote Output Queue (RMTOUTQ) can be configured using the Create Output Queue (CRTOUTQ ) command. Type CRTOUTQ on an operating system command line, press the F4 (Prompt) Key, fill in the following parameters, and press the Enter Key to create the RMTOUTQ:
Dummy device descriptions are typically used when printing from applications that do not support printing directly to an output queue but only to a printer device description (including Query/400 and the System/36 Environment). They are also useful when moving spooled files from one printer to another from the Work with Output Queue (WRKOUTQ ) or Work with Spooled Files (WRKSPLF ) commands or when using Operations Navigator to drag and drop spooled files from one printer to another. This information can also be found in the following Rochester Support Center knowledgebase document: 7995114, Creating a Dummy Device Description to Use with a Remote Output Queue (RMTOUTQ) Use the following steps to create a dummy device description:
Notes:
Once a Remote Output Queue (RMTOUTQ) has been configured, there is no need to delete and re-create the RMTOUTQ to make any changes. The RMTOUTQ description can be changed by doing the following:
Refer to the following Rochester Support Center knowledgebase documents: 8983237, Configuring a Remote Output Queue (RMTOUTQ): IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright
1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information. |
Moving data from one iSeries server to another
A number of situations occur in enterprise operations that could require moving data from one iSeries server to another. For example, a new dealership might open in a region, and some clients from one or two other dealerships might be transferred to the new dealership as determined by client address. Perhaps a dealership closed or no longer represents Spiffy Corporation sales and service. That dealer’s inventories and required service information must be allocated to either the regional office or other area dealerships. Perhaps a dealership has grown to the extent that it needs to upgrade its server, and the entire database must be moved to the new server.
Some alternatives for moving data from one iSeries server to another are:
- User-written application programs
- Interactive SQL (ISQL)
- DB2® UDB for iSeries Query Management functions
- Copy to and from tape or diskette devices
- Copy file commands with DDM
- The network file commands
- iSeries server save and restore commands
Creating a User-Written Application Program
A program compiled with DUW connection management can connect to a remote database and a local database and FETCH from one to INSERT into the other to move the data. By using multi-row FETCH and multi-row INSERT, blocks of records can be processed at one time. Commitment control can be used to allow checkpoints to be performed at points during the movement of the data to avoid having to start the copy over in case of a failure.
Querying a database using Interactive SQL
Using the SQL SELECT statement and interactive SQL, you can query a database on another iSeries server for data you need to create or update a table on the local server. The SELECT statement allows you to specify the table name and columns containing the desired data, and selection criteria or filters that determine which rows of data are retrieved. If the SELECT statement is successful, the result is one or more rows of the specified table.
In addition to getting data from one table, SQL allows you to get information from columns contained in two or more tables in the same database by using a join operation. If the SELECT statement is successful, the result is one or more rows of the specified tables. The data values in the columns of the rows returned represent a composite of the data values contained in specified tables.
Using an interactive SQL query, the results of a query can be placed in a database file on the local server. If a commitment control level is specified for the interactive SQL process, it applies to the application server (AS); the database file on the local server is under a commitment control level of *NONE.
Interactive SQL allows you to do the following:
- Create a new file for the results of a select.
- Replace and existing file.
- Create a new member in a file.
- Replace a member.
- Append the results to an existing member.
Consider the situation in which the KC105 dealership is transferring its entire stock of part number ‘1234567’ to KC110. KC110 queries the KC105 database for the part they acquire from KC105. The result of this inventory query is returned to a database file that already exists on the KC110 server. This is the process you can use to complete this task:
Use the Start SQL (STRSQL) command to get the interactive SQL display. Before you enter any SQL statement (other than a CONNECT) for the new database, specify that the results of this operation are sent to a database file on the local server by doing the following steps:
- Select the Services option from the Enter SQL Statements display.
- Select the Change Session Attributes option from the Services display.
- Enter the Select Output Device option from the Session Attributes Display.
- Type a 3 for a database file in the Output device field and press Enter. The following display is shown:
Change File Type choices, press Enter. File . . . . . . . . . QSQLSELECT Name Library . . . . . . QGPL Name Member . . . . . . . . *FILE Name, *FILE, *FIRST Option . . . . . . . . 1 1=Create new file 2=Replace file 3=Create new member 4=Replace member 5=Add to member For a new file: Authority . . . . . *LIBCRTAUT *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE authorization list name Text . . . . . . . . F3=Exit F5=Refresh F12=Cancel
- Specify the name of the database file that is to receive the results.
When the database name is specified, you can begin your interactive SQL processing as shown in the example below.
Enter SQL Statements Type SQL statement, press Enter. Current connection is to relational database KC000. CONNECT TO KC105__________________________________________________________ Current connection is to relational database KC105. ====> SELECT * FROM INVENTORY_____________________________________________ WHERE PART = '1234567'____________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ __________________________________________________________________________ Bottom F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line F12=Cancel F13=Services F24=More keys
For more information on the SQL programming language and interactive SQL, see the SQL Programming Concepts and the SQL Reference topics in the iSeries Information Center.
Querying remote servers using DB2 UDB for iSeries Query Management function
The DB2 UDB for iSeries Query Management function provides almost the same support as interactive SQL for querying a remote server and returning the results in an output file to the local server.
Both interactive SQL and the query management function can perform data manipulation operations (INSERT, DELETE, SELECT, and so on) for files or tables without the requirement that the table (or file) already exist in a collection (it can exist in a library). Also, query management uses SQL CREATE TABLE statements to provide data definition when a new table is created on the server as a result of the query. Tables created from a query management function follow the same guidelines and restrictions that apply to a table created using SQL.
However, the query management function does not allow you to specify a member when you want to add the results to a file or table. The results of a query function are placed in the first file member unless you use the Override with Database File (OVRDBF) command to specify a different member before starting the query management function.
For more information on the query management function, see the Query Management Programming book.
Copying files to and from tape or diskette
You can copy a table or file to tape or diskette using the Copy to Tape (CPYTOTAP) andCopy to Diskette (CPYTODKT) commands on the iSeries server.
Data on tape or diskette can be loaded on another server using the Copy from Tape (CPYFRMTAP) and Copy from Diskette (CPYFRMDKT) commands. For more information about using these commands, see the Storage solutions topic.
You can also use the Copy File (CPYF) command to load data on tape into DB2 UDB for iSeries. This is especially useful when loading data that was unloaded from DB2 UDB for z/OS®, or DB2 UDB Server for VM (SQL/DS™). Nullable data can be unloaded from these servers in such a way that a single-byte flag can be associated with each nullable field. CPYF with the *NULLFLAGS option specified for the FMTOPT parameter can recognize the null flags and ignore the data in the adjacent field on the tape and make the field null in DB2 UDB for iSeries. Another useful FMTOPT parameter value for importing data from IBM® mainframes is the *CVTFLOAT value. It allows floating point data stored on tape in System/390® format to be converted to the IEEE format used by DB2 UDB for iSeries.
Moving data between iSeries servers using Copy File Commands
Another way to move data from one iSeries server to another is to copy the data using the copy file commands with DDM. You can use the Copy File (CPYF), Copy Source File (CPYSRCF), and Copy From Query File (CPYFRMQRYF) commands to copy data between files on source and application source (AS)s. You can copy local relational database or device files from (or to) remote database files, and remote files can also be copied to remote files.
For example, if a dealership closes, the distributed relational database administrator can copy the client and inventory tables from the remote server to the local regional server. The administrator needs a properly authorized user profile on the application source (AS) to access and copy the tables and must create a DDM file on the application requester (AR) for each table or file that is copied. The following example shows the command the database administrator would use to copy a table called INVENT in a collection called SPIFFY from a server with a remote location name of KC105 to a regional center server called KC000. A DDM file called INCOPY in a library called TEST on the application requester (AR) KC000 is used for the file access. These commands are run on the KC000 server:
CRTDDMF FILE(TEST/INCOPY) RMTFILE(SPIFFY/INVENT) RMTLOCNAME(KC105) CPYF FROMFILE(TEST/INCOPY) TOFILE(TEST/INVENTDDM) MBROPT(*ADD)
In this example, the administrator runs the commands on the KC000 server. If the administrator is not on the KC000 server, then pass-through must be used to run these commands on the KC000 server. The Submit Remote Command (SBMRMTCMD) command cannot be used to run the above commands because the iSeries server cannot be a application requester (AR) and a application source (AS) for the same job.
Consider the following items when using this command with DDM:
- A DDM file can be specified on the FROMFILE and the TOFILE parameters for theCopy File (CPYF) command and Copy Source File (CPYSRCF) commands.
Note:For the Copy From Query File (CPYFRMQRYF), Copy from Diskette (CPYFRMDKT), and Copy from Tape (CPYFRMTAP) commands, a DDM file name can be specified only on the TOFILE parameter; for the Copy to Diskette (CPYTODKT) and Copy to Tape (CPYTOTAP) commands, a DDM file name can be specified only on the FROMFILE parameter.
- When a delete-capable file is copied to a non-delete capable file, you must specify COMPRESS(*YES), or an error message is sent and the job ends.
- If the remote file name on a DDM file specifies a member name, the member name specified for that file on the Copy File (CPYF) command must be the same as the member name on the remote file name on the DDM file. In addition, the Override with Database File (OVRDBF) command cannot specify a member name that is different from the member name on the remote file name on the DDM file.
- If a DDM file does not specify a member name and if the Override with Database File (OVRDBF) command specifies a member name for the file, the Copy File (CPYF) command uses the member name specified on the OVRDBF command.
- If the TOFILE parameter is a DDM file that refers to a file that does not exist, CPYF creates the file. Following are special considerations for remote files created with theCopy File (CPYF) command:
- The user profile for the target DDM job must be authorized to the Create Physical File (CRTPF) command on the application source (AS).
- For an iSeries target, the TOFILE parameter has all the attributes of the FROMFILE parameter except those described in the File Management topic in the iSeries Information Center.
- When using TCP/IP, the second element of the RMTLOCNAME parameter of theCreate Distributed Data Management File (CRTDDMF) command must be *IP.
For more information about using the Copy File commands to copy between servers, see theDistributed Data Management topic in the iSeries Information Center.
Transferring data over networks using Network File Commands
Data can be transferred over networks protocols that support SNA distribution services (SNADS). In addition to APPC and APPN protocols used with distributed relational database processing, SNADS can be used with binary synchronous equivalence link (BSCEL) and SNA Upline Facility (SNUF) protocols. An iSeries server supported by SNADS can send data to another server with the Send Network File (SNDNETF) command and receive a network file from another server with the Receive Network File (RCVNETF) and Work with Network Files (WRKNETF) commands.
Moving a table using server save and restore commands
You can move a table from another iSeries server using the Save Object (SAVOBJ) andRestore Object (RSTOBJ) commands. The save commands save database files on tape, diskette, or a save file. The save file can be distributed to another server through communications.
The save and restore commands used to save and restore tables or files include:
- Save Library (SAVLIB) command saves one or more collections or libraries
- Save Object (SAVOBJ) command saves one or more objects (including database tables and views)
- Save Changed Object (SAVCHGOBJ) command saves any objects that have changed since either the last time the collection or library was saved or from a specified date
- Restore Library (RSTLIB) command restores a collection or library
- Restore Object (RSTOBJ) command restores one or more objects (including database tables and views)
For example, if two dealerships were merging, the save and restore commands could be used to save collections and tables for one relational database, which are then restored on the remaining server’s relational database. To accomplish this an administrator would:
- Use the Save Library (SAVLIB) command on server A to save a collection or use theSave Object (SAVOBJ) command on server A to save a table.
- Specify whether the data is saved to a save file, which can be distributed using SNADS, or saved on tape or diskette.
- Distribute the save file to server B or send the tape or diskette to server B.
- Use the Restore Library (RSTLIB) command on server B to restore a collection or use the Restore Object (RSTOBJ) command on server B to restore a table.
A consideration when using the save and restore commands is the ownership and authorizations to the restored object. A valid user profile for the current object owner should exist on the server where the object is restored. If the current owner’s profile does not exist on this server, the object is restored under the QDFTOWN default user profile. User authorizations to the object are limited by the default user profile parameters. A user with QSECOFR authority must either create the original owner’s profile on this server and make changes to the restored object ownership, or specify new authorizations to this object for both local and remote users.
For more information about the save and restore commands, see the Backup and Recoverytopic in the iSeries Information Center.
How to remove leading spaces in cells in Excel?
Sometimes there are some blank spaces before strings in cells, these spaces are called leading spaces. It must be arduous to remove leading spaces in cells one by one. Actually, there are several smart tricks that can help us remove leading spaces in cells conveniently and quickly.
Remove leading spaces in cells with Trim function
Remove leading spaces in cells with VBA
Remove leading spaces in cells with Kutools for Excel
Suppose we paste some content from Microsoft Word with indent spaces, then it shows the leading spaces in each cell as the following screenshot shows.
Recommended Productivity Software
Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
Hint
Remove leading spaces in cells with Trim function
The Trim function in Microsoft Excel can remove all spaces from text except for single spaces between words.
Step 1: Enter the formula =TRIM (A1) in the adjacent cell C1 and press the Enter key.
Step 2: Select cell C1 and drag the fill handle down to the range cell that you want to remove the leading space. Then you can see all cell content are extracted with all leading spaces removed. See screenshot:
You can replace the original content with the extracted ones by copying and pasting them as values.
This method is quite easy if you only need to remove leading spaces in contiguous cells within a row or a column. However, when you need to remove leading spaces in a range with several rows and columns, you have to use the function several times.
Remove leading spaces in cells with VBA
If you are experienced with Microsoft Excel, VBA macro is a better choice to remove the leading spaces in cells.
Step 1: Hold down the Alt + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
Step 2: Click Insert > Module, and paste the following macro in the Module window.
VBA: Remove leading spaces:
1
2
3
4
5
6
7
8
9
10
11
12
|
Sub RemoveLeadingSpace() 'Updateby20131129 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox( "Range" , xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Rng.Value = VBA.LTrim(Rng.Value) Next End Sub |
Step 3: Press the F5 key to run this macro. In the pop-up KutoolsforExcel dialog box, you need to select a range with leading space you want to remove, and then click the OK button. See screenshot:
Then you can see all leading spaces are moved from the selected range, see screenshots:
![]() |
![]() |
![]() |
Remove leading spaces in cells with Kutools for Excel
If you have Kutools for Excel installed, you can remove all leading spaces of cells in a selection conveniently and quickly.
Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
Step 1: Select the range which you want to remove all leading spaces in cells.
Step 2: Click the Kutools > Text > Remove Spaces. See screenshot:
Step 3: In the Remove Spaces dialog box, check the Leading Spaces option in Spaces Type section, and then click the OK or Apply button. See screenshot:
Now all leading spaces are removed from the selected cells.
Kutools for Excel’s Remove Spaces tool makes it possible to delete all kinds of spaces from strings in cells, including all extra spaces, leading spaces, trailing spaces, or all spaces. Click to know more…
Related articles:
- Remove trailing spaces from cells
- Remove spaces between character and numbers within cells
- Remove leading and trailing spaces
- Remove indents within cells
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Kutools for Excel
Amazing! Increase your productivity in 5 minutes. Don’t need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools…
how to split text cells into multiple columns in excel
If you copy data from another program and paste it into Microsoft Excel, Excel may condense several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.
To create an example that demonstrates how to use the Text To Columns command:
- Create a new workbook.
- In Sheet1, type the following data:
A1: The,dog,ran A2: abc,def A3: abcdefg,hijkl A4: 2554,4874,41
- Select the range A1:A4.
- On the Data menu, click Text to Columns.
- In Step 1 of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then clickNext.
- In Step 2 of the Convert Text To Columns Wizard, click to select the Comma check box under Delimiters, and then click Next.NOTE: All of the check boxes under Delimiters should be cleared except for the Comma check box.
- In Step 3 of the Convert Text To Columns Wizard, type B1 in the Destination box, and then click Finish.
Your data is parsed into separate columns, beginning with column B, and the data in Sheet1 should resemble the following:
A1: The,dog,ran B1: The C1: dog D1: ran A2: abc,def B2: abc C2: def A3: abcdefg,hijkl B3: abcdefg C3: hijkll A4: 2554,4874,41 B4: 2554 C4: 4874 D5: 41
Note The example illustrates data with commas as the delimiter. This is only one of several delimiters that you may see in your data. For example, if you have Names in a column of data, consisting of first and last name in a single cell separated by a Space, the Space character can be used as a delimiter to separate the first and last names into adjacent cells instead of the same cell.
How to delete duplicate records from a table in Access 2000
Novice: Requires knowledge of the user interface on single-user computers.
SUMMARY
This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.
MORE INFORMATION
To remove duplicate records from a table, follow these steps:
- Make a copy of the structure of the table from which you want to remove the duplicate records.To copy a table:
- Select the table in the Database window
- On the Edit menu, click Copy.
- On the Edit menu, click Paste.
- Enter a name for the new table.
- Select Structure Only
- Click OK.
- Open the new table in Design view.
- Select the field(s) that contain the duplicate values.
- To make your selection the primary key for the table, click the Primary Key button on the toolbar.
- Save and close the table.
- Create an append query based on the original table containing duplicates.
- In the query Design View, on the Query menu, click Append Query.
- In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.
- Include all the fields from the original table by dragging the asterick (*) to the query design grid.
- On the Query menu, click Run.
- Click Yes in the dialog box advising you that you are about to append records.
- Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:
Microsoft Access can’t append all the records in the append query.Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn’t add <number> record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help. - Click Yes.
- View the contents of the new table. When you’re sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.
REFERENCES
For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type Automatically delete duplicate records from a table. in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Properties
APPLIES TO
- Microsoft Access 2000 Standard Edition
Keywords: |
kbhowto kbdatabase kbusage KB209183 |
HTML Copyright and trademark symbols
Copyright and trademark symbols
Copyright and trademark symbols HTML code
Copyright and registered trademark symbols can not easily be copy pasted into your html webpage so you should use the Copyright or registered trademark html symbol code instead.
Find the Copyright or registered trademark symbol you want below and then copy/paste the html smiley Copyright or registered trademark code highlighted in red into your html document.
™
™
℠
℠
©
©
®
®
℗
℗
How to: Remove or replace line breaks and carrage returns in cells
There are few quick methods to remove the line breaks and carriage returns in cells.
By default you might not know an easy and quick way to do this but it is fairly simple, either with just Excel, or with the help of ASAP Utilities.
Let’s say we have the following example.
In this case the author has used the shortcut Alt+Enter to put the last name on a new line.
But now we need it together on a single line, with a space between the first- and last name instead of a line break.
Instead of:
Bastien
Mensink
we need
Bastien Mensink, etc. etc.
Method 1, with Excel only
If you want to replace these line-breaks with for example a space, you can do this with Excel’s find/replace command.
In the “Find what:” box you can enter the line-break code, which is character number 10.
To do this, press and hold the Alt key, and then enter the numbers 010 from the numeric part of the keyboard.
On my computer in the box there is then a small dot blinking when the cursor is still in the entry box.
To enter the numbers you need to use the numbers from the Num-lock part of the keyboard (with the numbers above the letters on your keyboard it doesn’t work). If you have data imported from another source, sometimes the line breaks are also represented by character 13 instead of 10.
In the “Replace with:” dialog you can enter a space to have the line-breaks replaced with spaces. You can also leave that blank to have the line-breaks removed.
Method 2, with ASAP Utilities
You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Text » Advanced character remove or replace…
In the dialogue you can click on the “Line breaks” button to remove or replace the line break characters in the selected cells:
Method 3, with ASAP Utilities
You can use the following tool from ASAP Utilities to remove or replace the line-breaks:
Range » Find and/or replace in all sheets…
To search for the line break you can enter the code {lf}, to search for the carriage return you can use {cr}:
Filed under: ASAP Utilities, How to, how to with ASAP Utilities on January 18th, 2010 by Bastien | 3 Comments »
3 Responses to “How to: Remove or replace line breaks and carrage returns in cells”
-
Dave, on January 18th, 2010 at 8:21 pm Said:
You can also use ctrl-j instead of alt-0010 in excel’s edit|replace dialog.
-
Bastien, on January 20th, 2010 at 8:53 pm Said:
Hello Dave,
Thank you for your feedback.
I didn’t know that, thanks for sharing!Kind regards,
Bastien -
Kanwaljit, on February 1st, 2010 at 1:49 pm Said:
Hi Bastien,
May the Couple Live 1000 years !
If we put , (comma) when using the File Types option “Text File, Delimited by :” in Utility 16-Import->10 Merge and Import Multiple Files” the Utility starts a give an error message “Error 1004 : Method ~ of Object ~ Failed”. Other file type options seems to be working fine.
Regards
CA Kanwaljit Singh Dhunna
How to: Split data table into multiple worksheets (and save time) »