Solved

TXT file

Posted on 2011-02-21
27
414 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
ID: 34941558
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
ID: 34942439
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
ID: 34943760
Which tool are you referring to?
Excel or Crystal?

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
ID: 34947728
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
ID: 34947756
Which version of Crystal?

Is the text file comma separated?

mlmcc
0
 
LVL 7

Author Comment

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

Expert Comment

by:mlmcc
ID: 34947870
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
ID: 34947910
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
ID: 34948219
You can upload it here.

mlmcc
0
 
LVL 7

Author Comment

by:mcse2007
ID: 34948791
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
ID: 34948988
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
ID: 34949000
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
ID: 34949019
i've enclosed the txt file here...I only have CR9 & CRXI
shipment-gmk-20101221-081002-030.txt
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 34953814
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
ID: 34955035
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
ID: 34956658
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
ID: 34959156
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
ID: 34960028
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
ID: 34968139
 > 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
ID: 34968683
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
ID: 34968775
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
ID: 34968902
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
ID: 34974104
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
ID: 34974662
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
ID: 34974707
sorry......pls discard my last question...I forgot to refresh CR....now all rows are appearing.
0
 
LVL 7

Author Closing Comment

by:mcse2007
ID: 34977722
thank you for your help, James.
0
 
LVL 34

Expert Comment

by:James0628
ID: 34977804
You're welcome.  Glad I could help.

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

914 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

20 Experts available now in Live!

Get 1:1 Help Now