Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TXT file

Posted on 2011-02-21
27
Medium Priority
?
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 101

Expert Comment

by:mlmcc
ID: 34943760
Which tool are you referring to?
Excel or Crystal?

mlmcc
0
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!

 
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 101

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 101

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 101

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 101

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 101

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
 
LVL 101

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 101

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 35

Accepted Solution

by:
James0628 earned 1000 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 35

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 35

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 35

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 35

Expert Comment

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

 James
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

609 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