Crystal decisions on field types from Excel

[jahgardener]

My question is this, how exactly does the Crystal program decide on what field type to specify for data in a spreadsheet, if that field is a mixed bag of data or missing data altogether? Is it based on whatever data happens to be in the first row?

I am using Crystal XI to hook up a different tables to create output to import into a database.

The problem currently is that data in the some of the Excel spreadsheets may or may not come over to Crystal as the field types I expect.

For instance, if a comment or remark field is under 255 characters in the first row in the spreadsheet (under a heading field of Remarks) and 50 rows down that field contains text of 500 characters it seems as though Crystal decides what a field really is based on the first line of data in the spreadsheet. So, while there may be tons of cells containing well over 500 characters which should make the field a "Memo" field, it ends up as a "String" (255)

The tables and data involved in my work are often replete with null fields and ever so often, the odd bits of bad data entry ex: a date field of 01/01/0040. Date fields appear as date fields are selected and formatted as dates in Excel, but once linked in Crystal at times will show up as string field types. Will one errant date entry foul up the formatting of an entire column?

As a side issue to this, the data in the excel spreasheet originated in a 4D database, and was filtered is someway via Access before I recieved the actual spreadsheet.
(heavy sighs...) The spreadsheets may also contain thousands of records, with as many as 50 different columns to manipulate to import data into just one database table - so if I have to edit each table to populate a row of data to make sure it carries forward to Crystal in a certain way, I'll do it, but I would really like to know how this works when Crystal gets a spreadsheet/access table.

Any insight folks could provide would be really appreciated.

 

[pelajhia]

A vague answer to your question: you're about right, there's not a 100% clear way to understand this unless you research what the driver is doing.
I don't know if that's worth the time; it may be better to pull your data into access and from there create access views to merge/link your data. Have you considered this?

My point is, I think your initial approach is flawed, you are trying to use Crystal for something it's not exactly designed for.

 

[jahgardener]

Hi pelajhia

Thanks for your response. I should have given a better description of the application. I use Crystal to connect diverse database tables and then create output in Excel that can be imported into a custom database running from MS SQL.

So depending on who I happen to be working for, the source data comes from a variety of systems: File Maker Pro, Access, perhaps excel spreadsheets, and these tables need to be connected to tables in a custom database running SQL. It is a moving target as to what I get from clients. While I am used to Crystal, I am not as familiar with Access. I was just curious as to what Crystal looks at before deciding on a field type.

If you know of a better approach to managing these issues, I am all ears. I had heard that Access has some limitations with very large datasets, but perhaps I am misinformed.

Thanks for any insights -
J

 

[elsenorjose]

Access has problems when its .mdb file hits 2GB. Not sure if they're fixing that in Office 2007, but that is one challenge to take into consideration.

One question though: If you're dumping all this data back into MS SQL, have you considered using DTS, Data Transformation Services? It comes with MS SQL and is an application that allows you to import various types of data into the MS SQL database without the intermediary step of using Crystal as a GUI SQL tool. With the appropriate ODBC drivers, you should be able to connect to all the data sources you mentioned.