Solved

Can you change the data type in Crystal from say Number to String?

Posted on 2011-03-15
22
1,293 Views
1 Endorsement
Last Modified: 2012-05-11
Hello

I'm converting a Crystal 8.5 report into Crystal 11, and in the process I've had to change the database from a csv file to an excel file.

After doing this, the data map screen came up and a few of the fields have changed data type ie from String to Number etc

Is there a way I can change the data type back to what it was? ie via Add Command or something?

1
Comment
Question by:spectrum17
  • 11
  • 6
  • 5
22 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Not that I am aware of.

I assume they are numeric fields but that you want them to be strings.

One way to do it in the report is to use formulas for them like

ToText({YourField})
or
CStr({YourField})

You can control the number of decimals and the thousand separator as

CStr({YourField},0,"")

Another idea would be to modify the Excel file so the first row or 2 have dummy values that are definitely strings like xxxxx in those fields.  Crystal looks at the first few rows to determine the datatype.

I don't know if just putting ' in front of the numbers in Excel if Crystal would treat them as strings or not.

mlmcc
0
 

Author Comment

by:spectrum17
Comment Utility
I've tried totext and cstr in the formulas but Crystal doesn't seem to be recognising any of the converted text.

I can't edit the file as it's huge and it get recreated each week.

Any other ideas?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
ow does it get created?

Can that program be changed to insert a dummy record or 2?

What do you mean it doesn't recognize the converted text?

mlmcc
0
 

Author Comment

by:spectrum17
Comment Utility
The data file is created by a payroll program.

The program can't be changed to insert a dummy record.

Re the converted text isn't recognised an example is:

When this formula is used and the value in the field deduct.WCODE is "S1" Crystal is returning deduct.WCODED instead of "SUPERANNUATION FUND"

if  cstr({deduct.WCODE}) = "S1" then "SUPERANNUATION FUND"
else {deduct.WCODED}

0
 

Author Comment

by:spectrum17
Comment Utility

PS I saw something on google that said you could change the data type via Add Command, using something like this:

SELECT deduct.WCODE
 FROM deduct
CONVERT (STRING,WCODE)

I'm getting a syntax error when I try it.

I also saw a Crystal solution that JAMES0628 said you could change the data type via SQL. he didn't post the syntax though.

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I suspect it will more like

SELECT CONVERT(VarChar,Deduct.WCode) as WCode
FROM Deduct

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
CR decides what type each column in an Excel file is by looking at the values in some of the first rows.  I'm not sure exactly how many, but it seems to be somewhere around 15 to 25.  If it decides that a column is numeric, any string values in that column will simply be ignored/dropped.  So, your CStr ({deduct.WCODE}) = "S1" test simply can't work, because CR has decided that WCODE is numeric, so it can never be "S1".

 I handle this kind of thing by adding around 20 dummy rows to the beginning of the Excel file with the correct type of value in each column, like 0 in the numeric columns and "A" in the string columns, to get CR to interpret the columns the way that I want.

 I've never tried to write a CR Command for an Excel file.  It's an interesting idea.  I just tried it and I was able to use a Command to read an Excel file, but I don't know if that query language includes any kind of conversion function.  The MS SQL CAST and CONVERT functions don't seem to be recognized.  Either that, or the syntax for them is different for Excel and I just didn't happen to try the right syntax.

 It might be possible to use a Command to force the data type for the columns in a different way.  I'm not entirely sure.  I tried adding a SELECT (before the main SELECT) that put values of the desired type in each column (similar, in concept, to adding dummy rows to the file).  I took one of the columns that CR had decided was numeric in the file, and made it a string in the new SELECT.  But I got an error when I tried to save that Command, apparently because the first SELECT was just using literal values and not actually reading the file (or anything else).  So then I changed the first SELECT so that it was still using the literal values, but also reading the file, and I added a WHERE that would never be true, so that that first SELECT wouldn't actually produce any rows.  CR let me save that Command, and it made that one column a string instead of numeric.  I didn't do any testing beyond that, but my guess is that CR was getting the data type for each column from the first SELECT, even though it wasn't actually producing any data, and then using the same types for the columns in the second SELECT, where it was actually reading the file.  So, that _seemed_ to work, as far as it went, but whether or not the same thing would work for you, I really don't know.  And keep in mind that if you do that, the first SELECT is (presumably) going to be reading through the whole Excel file (even though it's not producing any data), so the report would be reading the file twice.  If it's a really large file, that could take some time.

 James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Another thought:

 If you happen to have MS SQL Server, another possibility would be read the Excel file through MS SQL Server (eg. using a stored procedure or a CR command).  You could create a linked server that pointed to that Excel file, or use the OPENROWSET function.  If you read the Excel file in MS SQL, you can use the CONVERT/CAST functions to change the data type for the columns.

 If you have some other db, like Access, you may be able to do the same kind of thing in that db.

 James
0
 

Author Comment

by:spectrum17
Comment Utility
Hi mlmcc

I've tried your solution first, and inserted the following command:

SELECT CONVERT(VarChar,deduct.WCODE) as WCODE
FROM "deduct"

I get the following syntax error:

Database Connector Error: 'DAO Error Code 0xd7a
Source: DAO.database
Description: Syntax Error in Query. Incomplete Query Clause'

Any idea how to get rid of this error?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
The problem is probably the Convert syntax.  I don't know how to get rid of it since that is database dependent.  Check how convert is supposed to be written.

I always thought it was

Convert(Field,format)  yours is reversed.

mlmcc
0
 

Author Comment

by:spectrum17
Comment Utility
No luck with that one either mlmcc. I've had another thought....The 8.5 report (that I am trying to convert to 11) didn't have this problem, probably because it was a csv file and was run using a text driver.

I've attached a document which shows how I set this up in 8.5. I can't see the option to set the same connection up in 11. Do either of you know how to do this? createdatasource.doc
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
Comment Utility
They moved the TEXT option to the ACCESS/EXCEL driver

Select that then change the file type from Access to TEXT

mlmcc
0
 

Author Comment

by:spectrum17
Comment Utility
I would so have loved this to have worked!! I used the TEXT option under the ACCESS/EXCEL at DAO and didn't get any data when I refreshed the screen. All gone.

Any ideas why this would be?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
First of all, as far as the CONVERT syntax goes, in MS SQL, it is data type and then value, as you had it, although with a type like varchar, you would probably also include a length (not sure if that's required), so it would be something like CONVERT(VarChar (10), deduct.WCODE).  However, as I said, CONVERT and CAST don't seem to work when accessing an Excel file.  I tried various parameters in various arrangements and couldn't get them to work.  This isn't MS SQL.  It's a SQL-like language being used to read an Excel file.  I don't know if it has any kind of conversion function.

 As for the text option, as mlmcc said, it's under Access and it does work.  What did you do exactly?  You said that you "used the TEXT option" and "refreshed the screen".  Was this in the report that was reading the Excel file, or the old report that read the text file?  Were you trying to change the datasource or add a new one?  etc., etc.

 Going back to your original post, you said that you "had to change the database from a csv file to an excel file".  Why?  Forgive the obvious, but did you try running the old report under CR XI?

 James
0
 

Author Comment

by:spectrum17
Comment Utility
Hi James, answers to your questions as to what paragraph they were in are below:

Para1: The number of characters in the field could vary. Would this matter?

Para2: I went into Set Datasource Location, clicked on Create New Connection - Access/Excel DAO, Selected the csv file from the drop down list, changed the database type to 'Text', clicked Finish, then updated the datasource to read this new connection.

Para3: When I tried to run the old report it brought up the OLE DB ADO screen and asked to choose an ODBC DCN from a list.
0
 

Author Comment

by:spectrum17
Comment Utility
PS Re Para1 : I changed the formula to:

SELECT CONVERT(VarChar(10),deduct.WCODE) as WCODE
FROM "deduct"

The same error was received. (Description: Syntax Error in Query. Incomplete Query Clause')
0
 

Author Comment

by:spectrum17
Comment Utility
PSS

Re Para2:

I was trying to change the datasource location, as Crystal couldn't recognise the old one.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
 > The number of characters in the field could vary. Would this matter?

 That shouldn't be a problem.  That's what varchar is.  You set a maximum length (eg. varchar (10)), but the field only uses as many characters as are required for each value.  Or, you can use char (eg. char(10)), and the field will always be that length, with any "extra" characters filled with spaces.

 But that's really a moot point, since, as far as I can tell, there is no way to do a conversion like that in an Excel query.


 Looking back at your screenshots of how you set up the connection in CR 8.5, it appears that you used a file DSN, which means that the connection information is stored in a file somewhere.  You would have selected that DSN file in the last screenshot that you posted, and then gone on from there to enter the connection details that would be saved in that file.

 If that's the case, it kind of seems like the old report should have worked under CR XI, since it supports the same kind of connection, but maybe there are some differences between 8.5 and XI that prevented CR from just automatically making the connection (I haven't used 8.5, so I really don't know).

 If the old report is using a DSN file, you could try re-creating the connection under CR XI.

 Go to the Database Expert in CR XI and open up "Create New Connection" and then ODBC.
 Double-click "Make New Connection".
 Click the "Find File DSN" option and browse to the DSN file that the old report is using.


 If you like, you could also post your report here.  Maybe we can see something that might help.

 James
0
 

Author Comment

by:spectrum17
Comment Utility
Hi James,

I've tried to do what you mentioned above, but I'm still not getting any results.

The 8.5 report was written so long ago, I can't remember if it used a File DSN or not. How can I check?

I would post the report on here, but it includes a lot of sensitive information, so instead I've attached a file below of the steps I followed to update the datasource location. I've also posted the code behind the file dsn.

Can you have a look at this and see if I have made any mistakes in re-establishing the connection?

Many Thanks!!
payslip.doc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
Comment Utility
To see if the old report used a DSN file, all I can suggest is to go to "Set Datasource Location" and open up the "Properties" entries and see if it says anything there.  I don't use DSN files, so I don't know if it will say anything there or not, but if it's not there, I don't know where else to look.  OTOH, in your screenshots, you're using a payslips.dsn to create a new connection.  I was assuming that payslips.dsn came from the old report.

 Looking at your screenshots, the fact that CR listed some CSV files (I assume that they're the correct files) would seem to indicate that CR is getting the correct folder from the DSN file.  Beyond that, I don't know why the report is not getting any data, although one thing did just occur to me.

 If the report is reading multiple CSV files, I assume that they're linked.  I wonder if CR may be automatically relinking the files when you update the datasources for them?  If so, it may be doing it incorrectly.  It would probably try to link any fields with the same name.  I'd go to Database > "Database Expert" in the old report and check the links, and then check them in the new report with the new datasources and see if they've changed.  If the links are different, you could look for an "auto link" option, or something like that, and, if it's on, see if you can turn it off, and try changing the datasources again, or just change the links manually.

 If that doesn't pan out, have you tried creating a new report using that DSN file, to see if it can read anything from any of the CSV files?  I'd try it with just one of the CSV files first, to avoid any possible linking issues.

 James
0
 

Author Comment

by:spectrum17
Comment Utility
Bingo, that helped find the problem! There were some selection formulas that weren't working with the report. I have since changed them (added Cstr) now they work.

Now I just have to solve some other conversion issues with the report!!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Glad you were finally able to figure it out.

 James
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

762 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

9 Experts available now in Live!

Get 1:1 Help Now