• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

TXT file

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
mcse2007
Asked:
mcse2007
  • 12
  • 8
  • 5
  • +2
1 Solution
 
StephenJRCommented:
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
 
sah18Commented:
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
 
mlmccCommented:
Which tool are you referring to?
Excel or Crystal?

mlmcc
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mcse2007Author Commented:
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
 
mlmccCommented:
Which version of Crystal?

Is the text file comma separated?

mlmcc
0
 
mcse2007Author Commented:
Hi mlmc, i'm using CR11 the TXT file is only separated by space.
0
 
mlmccCommented:
Are there spaces in any of the text?
In other words does a space always mark the column "end"?

mlmcc
0
 
mcse2007Author Commented:
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
 
mlmccCommented:
You can upload it here.

mlmcc
0
 
mcse2007Author Commented:
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
 
mlmccCommented:
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
 
mlmccCommented:
I don't seem to have a textfile database option for CR XI.

Do you have one for CR 10?

mlmcc
0
 
mcse2007Author Commented:
i've enclosed the txt file here...I only have CR9 & CRXI
shipment-gmk-20101221-081002-030.txt
0
 
mlmccCommented:
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
 
mcse2007Author Commented:
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
 
mlmccCommented:
I don't know.  I have CR7 installed elsewhere and will see if it has a text driver.

mlmcc
0
 
James0628Commented:
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
 
mcse2007Author Commented:
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
 
James0628Commented:
 > 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
 
mcse2007Author Commented:
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
 
James0628Commented:
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
 
mcse2007Author Commented:
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
 
James0628Commented:
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
 
mcse2007Author Commented:
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
 
mcse2007Author Commented:
sorry......pls discard my last question...I forgot to refresh CR....now all rows are appearing.
0
 
mcse2007Author Commented:
thank you for your help, James.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 8
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now