Greetings, Open the file as delimited in the DataStation. Since it's comma delimited, no need to specify a delimiter.
This will keep your leading zeros. Then use the Report feature to generate a file of just the errors - setting Status and filtering the report based on the Status field= Error. If you rename the DataStation fields, then the field names will show up in the report. There is an option to have the report outputted as an Excel spreadsheet. Regards, Thom Thom C. Blackwell Product Manager Boston Software Systems (866) 653-5105 ex 807 www.bossoft.com LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately, then delete this message and empty from your trash. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Brungardt Sent: Friday, January 26, 2007 2:31 PM To: [email protected] Subject: RE: [Talk] .txt to .xls I guess to be more specific, I am getting a comma delimited text file from a vendor. Some of the data in this file has leading zeros. I need to open this file with Boston Workstation's Datastation to script the data into Meditech. When I run into errors with the data, I would like to write those errors back to a file for an end user to manually correct. The only DataStation option I know of to do that is with and Excel file. If I could find a way to first convert the comma delimited text file into an Excel file and keep the leading zeros, I would be able to open that Excel file with DataStation and have the ability to write errors back to it. I can manually open the text file with Excel and get the import wizard, which gives me the option to format each column as text. This will work, but it requires manually setting up the file before running the script. Is there a way to convert that text file to Excel formatting the columns as text through a script? _____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neil Carman Sent: Friday, January 26, 2007 12:34 PM To: [email protected] Subject: Re: [Talk] .txt to .xls Using VB there are a couple of options 1. If the number has a fixed length, for example 10 characters then you can initially set the number to "0000000000" & "1234" then use the right function to get the 10 char with leading zeros. In this case the result would be "0000001234" Num1="0000000000" & Num Num2=right(Num1,10) 2. Probably the best way is to use the format function Num2=format(Num, "0000000000") if Num = "1234" then the format function returns "0000001234" Titmus, Steve wrote: If the first cell value in the column is seen by Excel to be alpha, it will force additional cells in that column to be alpha only. While creating your output file, prefix the at least the first value with a single quote. The single quote will usually not display on the screen and wouldn't require an edit later to remove an alpha char. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paul Brungardt Sent: Friday, January 26, 2007 12:29 PM To: [email protected] Subject: [Talk] .txt to .xls I'm trying to find a way to convert text files with values that contain leading zeros into Excel files that will keep those leading zeros. I can manually do this via Excel's Import wizard but really need to find a way to script it. Command line, VB, something other than manually! Is there any hope? Thanks! Paul Paul Brungardt Clinical Analyst HaysMedicalCenter PH: 785-623-2196 PG: 785-650-5989 -- Neil Carman Director Technical Development Manigault & Associates LLC http://www.m-allc.com Work: (805) 680-5601 Fax: (714) 968-5632
