Crystal Report excel number filed as text field conversion

I am using Crystal Report  ver 2011, I have an excel file and text tab delimited version of the same excel file.
I want to use this excel file or text tab delimited version as data source for my Crystal report.

Problem is that  excel file have one filed that has numbers in and I need it in Crystal report as text field. I am using it  for connection with another table in report.
How to get this excel file as data source in crystal and change number field to text type filed in Crystal report?

I need some way that will ask not to much of  playing with and  changing excel file or at least very little.
 I know for  method of putting some alphabet character in filed at the end or beginning  of the  filed or putting comma as first character in the filed.
Is there any other way to get excel number filed converted to text type filed in Crystal report. I tried to reformat it as text field but it is not working again in crystal I see it as number type field when I set up new connection to that file.
With text file tab delimited is there any other way except setting ODBC and system DNS
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You could put  SINGLE QUOTE  in front of each number that will convert them to text.

You could also add about 10-15 rows (rows 1-15) to the file with data appropriately formatted and put a known values like AAAAA in that column then filter out the lines with {Field} <> 'AAAAA'

mlmcc
TarasAuthor Commented:
mlmcc.
I mentioned that I know for coma – single quote or rows with alphabets in the problematic excel field.

I was hoping that somehow exist another way that do not ask for user to  do a lot  of changing and interfering with the excel file before he/she run crystal report.

So every time when data are updated and new excel file is created (new excel file is  created and put in folder from where crystal report  will pull it user will need to go and do those adjustment in excel file first, then he run report).This is a bit problem.

As to your suggestion of entering  top 10-15 rows of AAAAA  with proper formatting  as option.
 I assume that you do not need to filter latter on if you use  an existing table  from SQL Server database to left outer join -link with that way adjusted excel  file in crystal report.  Assuming that link field will be our adjusted excel field (as it did not have those values in  database table side linking field).
mlmccCommented:
THe filter is probably not needed so long as the link is from the SQL Server table to the Excel file.

However, Crystal doesn't really support that.

You could write a small app that changes the file by adding the quotes.  The app could be set to run when the new file is added.

mlmcc
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

TarasAuthor Commented:
mlmcc as I have a large number of records in excel file I need formula and some step by setep direction ro Dummy as mi to put single quote in front somewhere i found =A1&"" then copy it down then pass value, it did not work probably I am missing someting in this very hard process:-)
mlmccCommented:
If A1 has the value then you could use this in an empty column

Text(A1,"#0")

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Thanks a lot I had problem as filed was formated as text and did not worked when I was trying ,when i swithc it to general it worked but  your suggestion is ok too.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.