Re: Excel frustration with AIE and Data Imports
Hi Ron! CSVed is a small freeware tool and automatically keeps the leading zeroes. As a consultant working across many projects where you are confronted with requests to import data into Remedy from various legacy systems (that can only provide .csv files), you frequently get a lot of .csv files that must be checked for data quality and possibly minor manual correction before it can be fed to the Import Tool. So, I was looking for a small app that could allow me to view edit .csv files without having to have to use Excel with all it's overboard automatic features such as truncating zeroes . CSVed was the closest thing I could find. It is function-rich, but it's version of direct editing is not as direct as I would like. I use it every now and then if I get large .csv files that I browse but do not need to edit. Best Regards, Theo Sent from my Black/Silver Personal Computer Try not to become a person of success, but a person of value. - Albert Einstein From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ron Tavares Sent: 29 June 2011 21:20 To: arslist@ARSLIST.ORG Subject: Re: Excel frustration with AIE and Data Imports ** All, Thank you for your responses. Michelle, thanks for listing out the steps. I was somewhat aware of this process, but felt there were too many clicks to make it worth the effort. BUT, you are correct, once I do it enough times, it becomes second nature. This is especially useful with large files. Theo, This tool looks pretty cool and could be exactly what I am looking for. I looked over the website, but I cannot download it where I am currently working. Will need to give it a try when I am home. Have you used it? Can you confirm that it can retain the leading zeros without having to go through a bunch of steps? Also, is there a cost or is this freeware? Thanks, .ron On Wed, Jun 22, 2011 at 1:39 PM, Theo Fondse t...@remex.co.zamailto:t...@remex.co.za wrote: ** Hi Ron! You could try CSVed to see if it works for you: http://csved.sjfrancke.nl/index.html Best Regards, Theo Sent from my Black/Silver Personal Computer Try not to become a person of success, but a person of value. - Albert Einstein From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG] On Behalf Of Ron Tavares Sent: 22 June 2011 15:31 To: arslist@ARSLIST.ORGmailto:arslist@ARSLIST.ORG Subject: Excel frustration with AIE and Data Imports ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron _attend WWRUG11 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.comhttp://www.wwrug.com/ ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Excel frustration with AIE and Data Imports
All, Thank you for your responses. Michelle, thanks for listing out the steps. I was somewhat aware of this process, but felt there were too many clicks to make it worth the effort. BUT, you are correct, once I do it enough times, it becomes second nature. This is especially useful with large files. Theo, This tool looks pretty cool and could be exactly what I am looking for. I looked over the website, but I cannot download it where I am currently working. Will need to give it a try when I am home. Have you used it? Can you confirm that it can retain the leading zeros without having to go through a bunch of steps? Also, is there a cost or is this freeware? Thanks, .ron On Wed, Jun 22, 2011 at 1:39 PM, Theo Fondse t...@remex.co.za wrote: ** Hi Ron! ** ** You could try CSVed to see if it works for you: ** ** http://csved.sjfrancke.nl/index.html ** ** ** ** Best Regards, Theo ** ** Sent from my Black/Silver Personal Computer “Try not to become a person of success, but a person of value.” – Albert Einstein ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Ron Tavares *Sent:* 22 June 2011 15:31 *To:* arslist@ARSLIST.ORG *Subject:* Excel frustration with AIE and Data Imports ** ** ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Excel frustration with AIE and Data Imports
I've used Access for things like that -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ron Tavares Sent: Wednesday, June 22, 2011 8:31 AM To: arslist@ARSLIST.ORG Subject: Excel frustration with AIE and Data Imports ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Excel frustration with AIE and Data Imports
Hi, Ron: The trick to retaining the leading zeros found in a CSV file in Excel is to import the file instead of opening. If using Excel 2007, I've made a habit of... 1. Clicking the 'Data' tab 2. Clicking 'From Text' in the Get External Data section 3. Select the CSV file from the Import Text File window 4. Follow the steps through the Import Text Wizard a. Step 1: Choose 'Delimited' b. Step 2: Choose 'Comma' c. Step 3: Select the column with the leading zeroes and click 'Text' from the Column Data Format d. Click Finish to close the Import Text Wizard window. 5. Click OK on the Import Data window. It might sound like a lot of steps, but it'll save you some heartache. If you make a habit out of it, it takes less than 30 seconds to run through those steps above. In all fairness, you can use Excel or Access. I believe Access works the same way. Thank you, Michelle Lucero Service Desk Design and Build CNSLT - APPS PROG -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Wednesday, June 22, 2011 8:33 AM To: arslist@ARSLIST.ORG Subject: Re: Excel frustration with AIE and Data Imports I've used Access for things like that -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ron Tavares Sent: Wednesday, June 22, 2011 8:31 AM To: arslist@ARSLIST.ORG Subject: Excel frustration with AIE and Data Imports ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are -- This message w/attachments (message) is intended solely for the use of the intended recipient(s) and may contain information that is privileged, confidential or proprietary. If you are not an intended recipient, please notify the sender, and then please delete and destroy all copies and attachments, and be advised that any review or dissemination of, or the taking of any action in reliance on, the information contained in or attached to this message is prohibited. Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Sender. Subject to applicable law, Sender may intercept, monitor, review and retain e-communications (EC) traveling through its networks/systems and may produce any such EC to regulators, law enforcement, in litigation and as required by law. The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or free of errors or viruses. References to Sender are references to any subsidiary of Bank of America Corporation. Securities and Insurance Products: * Are Not FDIC Insured * Are Not Bank Guaranteed * May Lose Value * Are Not a Bank Deposit * Are Not a Condition to Any Banking Service or Activity * Are Not Insured by Any Federal Government Agency. Attachments that are part of this EC may have additional important disclosures and disclaimers, which you should read. This message is subject to terms available at the following link: http://www.bankofamerica.com/emaildisclaimer. By messaging with Sender you consent to the foregoing. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Excel frustration with AIE and Data Imports
You got to set the format of the column type to text.. As someone else said, Access is much better for such jobs as it retains values as is.. It has got a broader range of functionality that is more SQL like which is easier for us that work with data stored in standard RDBMS’s in case you need to do some mass data cleaning. Cheers Joe From: Ron Tavares Sent: Wednesday, June 22, 2011 9:30 AM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Excel frustration with AIE and Data Imports ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: Excel frustration with AIE and Data Imports
Hi Ron! You could try CSVed to see if it works for you: http://csved.sjfrancke.nl/index.html Best Regards, Theo Sent from my Black/Silver Personal Computer Try not to become a person of success, but a person of value. - Albert Einstein From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ron Tavares Sent: 22 June 2011 15:31 To: arslist@ARSLIST.ORG Subject: Excel frustration with AIE and Data Imports ** Good Morning Listers, You how when you open a .csv file in Excel, Microsoft will do it's magic and start changing the format of certain values? As an example, it will drop the leading zeros form a value because it assumes it is numeric, (like a serial number). So you have to fix the data, format the column as 'text' and save the file. Then, the next time you open the file, the process starts all over again. I know I can use notepad to edit, but that is a pain. So I have two questions: 1) Is there a way to set a preference in Excel to say Hey! don't do that! 2) Assuming the answer to question one is no, is there another tool out there that works like Excel but is geared toward developer geeks? Sort of like what Notepad is for Windows, except it would display the data in column/row. I know I would really like a tool like that. Thanks, .ron _attend WWRUG11 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
