Solved

TXT file

Posted on 2011-02-21
27
413 Views
Last Modified: 2012-05-11
Hi Guys,

This may sound silly, but is it possible to use a TXT file as your data source file? Or is there any way it can be used?

The reason I asked this question is because I was informed by overseas colleague that an exported job (schedule job) from SAP via FTP, can only be formated as TXT file. I find it hard to believe this.

When i view the TXT file from excel, you have to format it to "text to column" and spend some time dividing it by the column.

Thanks in advance.
0
Comment
Question by:mcse2007
  • 12
  • 8
  • 5
  • +2
27 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Is is delimited by commas or some other character? If so, text to columns should be straightforward. I don't know the details offhand myself but that process could be automated if you're doing it a lot.
0
 
LVL 9

Expert Comment

by:sah18
Comment Utility
Absolutely you can.  It depends on the specific version of Excel you are using where you might find the menu options, but the basic steps are the same (this is just one way to do this, btw, there are several).  Here are instructions for Excel 2007:

1. Open an empty Excel workbook
2. Click on the Data tab
3. Click "From Other sources" button
4. From Microsoft Query
5. Select "New Datasource"
6. Give the datasource a name you'll remember
7. From the list of drivers select "Microsoft Text Driver"
8. Click Connect, under options select the text file extension used by your file (if comma delimited choose csv, for example)
9. Click OK, then OK on the datasource to use it.
10.  Here you will select the file itself, along with the specific columns you want to pull in.

I hope this helps!


0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Which tool are you referring to?
Excel or Crystal?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hi sah18, is there a step prior to Step 2, besides the obvious.......i cannot seems to locate Data tab...i'm using MS Excel 2002, would that makes a difference?

Hi mlmcc..CR. I've tried few time using CR but all i'm getting is one big consolidated data rather than by column.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Which version of Crystal?

Is the text file comma separated?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hi mlmc, i'm using CR11 the TXT file is only separated by space.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are there spaces in any of the text?
In other words does a space always mark the column "end"?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Yes when I view the file in notepad.

Can I send the TXT file by email to you mlmcc...so you can see it?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You can upload it here.

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Is it possible with CR to divide it in column? at the moment, the data are only separated by space, no commas or delimited separation.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It probably is

Is the format something like

Text1 Text2 Text3 Text4
Col1 Col2 Col3 Col4
And you need

4 columns of data?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I don't seem to have a textfile database option for CR XI.

Do you have one for CR 10?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
i've enclosed the txt file here...I only have CR9 & CRXI
shipment-gmk-20101221-081002-030.txt
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
As I said, I don't have a driver that can use a text file with Crystal.

Is this supposed to be 1 record?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hi mlmcc, when the file comes out, it comes out like that.

But, the guys from overseas, told me that it is column by column but no delimited or commas to separate them only space. I got CR10, can it read this txt file then organise it by column? Or, can a formula be used to pick and chose the data that I want and organise them by column by column in CR?

appreciate you help on this mate, as always.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I don't know.  I have CR7 installed elsewhere and will see if it has a text driver.

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
Comment Utility
CR 10 can read text files.  When you are in the "Create New Connection" screen, the option is kind of hidden under the Access/Excel option.  Choose that, and then when the Connection window pops up, change the "Database Type" to Text.

 Without delimiters, CR won't be able to automatically separate the records into fields, so you'll get one field containing the entire record.  If the text file has fixed length fields (eg. characters 1 - 10 are always the customer number, characters 11 - 30 are always the customer name, and so on), then you can pick the pieces out easily enough.  You can use Mid to extract the characters from specific positions in the string.  For example, in the file that you posted, it looks like the first 8 characters are a date (yyyymmdd).  You could pull that out and put it in mm/dd/yyyy format using a formula like:

Mid ({field}, 5, 2) + "/" + Mid ({field}, 7, 2) + "/" + Mid ({field}, 1, 4)

 If you want CR to actually treat it as a date, you could use the Date function to convert the string to a date, but if those characters could ever be something other than a valid date, you'd need to check for that, or the formula will get an error.

 James
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hello James,

I've tried your formula, although it is lengthy, it works perfect. If I could right a formula on each column then perhaps I could use CR for each column, because the file has fixed characters position which was provided to me (please see the enclosed rule of char position)

How do I use your formula to write a formula using the char position rule?

Thanks in advance  
text-position.xls
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
 > I've tried your formula, although it is lengthy ...

 FWIW, the length was because that happened to be a date, so I pulled out the year, month and day separately so that I could rearrange them.  Other fields will normally be much simpler.

 There really isn't much to the basics.  Characters 9 - 18 are the shipment number.  Create a formula named shipment_number (for example):

Mid ({field}, 9, 10)

 That will give you the shipment number as a string.  If you want to interpret it as a number, use:

ToNumber (Mid ({field}, 9, 10))


 If you're just going to treat each field as a string, then all that you need may be a series of formulas with Mid functions to pull the appropriate characters.  But if you're going to try to interpret any of the fields in a specific way, like converting the first 8 characters into a date or converting the next 10 characters (shipment number) into a number, then you may need to add some checks.  For example, if it's possible that characters 9 - 18 might not contain a number - For example, maybe there are cases where there is no shipment number and the field is blank - then using ToNumber on those characters will give you an error.  You can use NumericText to check the string before converting it.

if NumericText (Mid ({field}, 9, 10)) then
  ToNumber (Mid ({field}, 9, 10))
else
  0


 There is a similar function named IsDate that can be used to see if a string can be converted into a date.

 James
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hello James,

Am I right saying, when interpreting your formula, from 9th Position of the CHARACTERS with max of 10 characters? What does the "Mid" represents.

Mid ({field}, 9, 10)

Also, is there a difference between STRING and ToNumber when the data from CR is exported as CSV file?

Thanks in advance
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Yeah, extract 10 characters, starting at the 9th character.  If this helps, Mid is like the Substring function found in some other environments.  You can look up Mid in the CR Help.

 As for string vs numeric, it depends on how you want to interpret the data.  For example, if you have an amount field and you want to produce a total for that field, you'd need to convert it to a number.  Or, if you have a quantity of 1234.56 that's stored in the file as 123456 (no decimal point), and you want to format it on the report as 1,234.56, the simplest thing would be to convert it to a number and then use CR's formatting options to format that number.

 If your intent is simply to put the fields on the report and then export that to a CSV text file, then it depends on how you want the fields formatted in that CSV file.  If you just want to leave them in their current forms, then you can just use Mid to pull out the appropriate characters.

 James
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Hello James,

I can see there is major difference as you mentioned if the data is threated as Number particular when it is exported and view in notepad.

For instance, if I used this formula Mid ({field}, 19, 10) the exported data when view in notepad, it leaves TWO blank spaces...rightly so because

91738571  ,

Whereas, if I use this formula ToNumber (Mid ({field}, 19, 10)) , format the result (e.g., no commas and no decimal place). When the data is viewed in notepad after it has been exported, it removes the TWO blanks spaces.

91738571,

What should be my formula if it contains NUMERIC and TEXT (e.g 123ABC)?

thanks again
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
If a field could include trailing spaces and you don't want to include those, you can use TrimRight to remove them.  For example:

TrimRight (Mid ({field}, 19, 10))

 If the characters in those positions were "91738571  ", with 2 spaces at the end, TrimRight would remove the spaces and you'd get "91738571", without the spaces.

 If you could have leading spaces instead of trailing and want to remove those, you can use TrimLeft.  Or, you can use Trim to remove any leading or trailing spaces.


 If a field could contain non-numeric characters, like "123ABC", you can't use ToNumber if you want to produce the same value.  You'd just use Mid, and maybe one of the trim functions to remove spaces.

 James
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
Thanks for clarification, much appreciated.

In addition, If the TXT file contains multiple rows (e.g. pls see attached file), how does CR handles and show each of those lines in the report in the detail report section? What should be the formula for it? Each row is separated by X as noted in the enclosed file. There are 30 rows in this file.

thanks again.
shipment-gmk-20101221-081002-030.txt
0
 
LVL 7

Author Comment

by:mcse2007
Comment Utility
sorry......pls discard my last question...I forgot to refresh CR....now all rows are appearing.
0
 
LVL 7

Author Closing Comment

by:mcse2007
Comment Utility
thank you for your help, James.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now