What SQL Syntax works with Excel data source

[Bob Cergol]

Hi Carsten & Bo,

I've been asked to make an Excel sheet downloaded from our Payroll processing provider usable as a data source for reporting in Dynamic AI. For whatever reason, that provider creates this sheet with all data formatted as text. I thought I would cast/convert those columns to numeric and date as needed in plus columns but I get the "used SQL commands not allowed" error. I tried connecting to this data source directly with the Excel driver and with the ODBC connection type after defining the ODBC connection in Windows. I get the same behavior using either one.

Question: Is standard SQL syntax available for use in formulas in this case, or should I be using some other syntax.

If I can't make this work, I'll save the sheet as a CSV file and then define each columns data type in the Winows ODBC connection. I am trying to eliminate the need to reformat this spreadsheet every time it is downloaded. I just want to copy it to the appropriate folder ready for use with my reports.

Thanks, and regards,
Bob

[Bob Cergol]

My work-around in the mean time is to do a "save as" file type CSV in Excel of the original spreadsheet. This loses the unwanted formatting on the data. Then I do a "save as" of that file as file type "Excel" and that get numbers and dates back to those data types. I like using the Excel file as a data source over the CSV text file so I didn't have to define an ODBC connection on the Windows web server hosting Dynamic AI.

I have the result I need for now, but may need to create "case" type plus columns in the future, and need to know how to do that.

Regards,
Bob

[Bob Cergol]

I was also given another Excel sheet that contains accounting information.
For some inexplicable reason Dynamic or the Excel driver are seeing some of the numeric columns as text. Viewing the data in Excel I can see no difference in the numeric data in these columns. In fact I tried explicitly formatting them -- and they were already formatted as "accounting" -- same as the columns that are seen as numeric. Nevertheless I changed the formatting to numeric, saved the sheet, refreshed the database connections in Dynamic. No luck it still sees these columns as text.

I can't create the reports I need because I can't total text data.

Does anyone know what could be the problem?

Thanks,
Bob

[Bob Cergol]

...mood swings...

I just looked at the spreadsheet again.
I noticed the first 10 or more rows of the two problem columns were blank.
I had a memory from earlier today setting up an ODBC connection to an Excel sheet in Windows, an option to specify how many rows to scan. Hmmm...maybe Dynamic or the driver is only looking at less than the first 10 rows to determine the data type. I typed in a zero in the first row of data in those two columns. Now Dynamic sees those columns as numeric.


Bob

[Bob Cergol]

Greetings,

This is just an fyi for others wanting to report from Excel or CSV data sourced.

1) The errors I thought were syntax related look to be mostly due to not enclosing the column names in double quote marks. With an MSSQL source double quote marks cause problems, besides you would either enclose the column name in [ ] or nothing. If I place double quotes around the column name I can create a plus column without errors.

2) I still have issues controlling the data type the ODBC driver sees the column as being. I have a new data source with mostly numbers in an ID column, but quite a few rows also have letters. Even if I make the first row column value letters, and even if I explicity format the column in Excel as text, the column is still defined as numeric in Dynamic AI -- presumably because more rows have numbers than letters. Worse, if you group by that column it fails to report the data with letters. I will have to prefix every value in that column with a letter to make it work properly.

[Update to this post] Any column that has mixed data types will be problematic. In the screenshots I show a column that is recognized as being text in Dynamic AI -- yet when it encounters the rows with numeral values -- it blanks them in the report -- even if I explicitly format the column as text. ??? Rather than fight such issues -- its looking like it will take less time to create "one-off" SQL tables and load the data into those, instead of trying to use an Excel sheet and fight these issues -- and not be able to use all the SQL commands I know.]

Mixed_Data_1.png (1.26 KiB) Viewed 32786 times

Mixed_Data_2.png (1.39 KiB) Viewed 32786 times

Learning more about ETL lately ...

Bob

[Carsten Sørensen]

Hi Bob,

I remember something long time ago about Excel ODBC access scanning the first 25/50 rows to determine the field-type to expose to the requesting part. I think we got around such issues in the past by having a dummy row OR if you create a view with computed columns maybe adding a constant '' in-front of the value might trigger it correctly.

Best regards,
Carsten

[Bob Cergol]

Thanks Carsten,

I just updated my previous post before reading your reply.
I will try prefixing those columns I know will have mixed data type values with a double quote and see what happens.

FYI, I am working on a revenue allocation report that is very complicated and I cannot use my core Paradigm system tables -- so I am finding out first hand now about extraction and transformation of the data others' have to suffer with! Once I get this all worked out, I think I will create a permanent table in Paradigm and populate it with this data on the fly -- and elminate this "ETL" -- I don't like to have to do it. I am spoiled by the data I need "just being there".

Bob

[Bob Cergol]

Hi Carsten,

Its the single quote mark that Excel uses to flag the number as formatted as text.

In case my screenshots didn't make it clear in the previous post -- they illustrate the "flip-side" of the problem, i.e. a text column containing all-numeral values in some cells. You would think Excel/ODBC driver would understand them as characters, but apparently not, since they show up as blank values in the report!

I'll have to write conditional logic to prefix such values as well.

Regarding the scanning of 25 -50 rows -- apparently it looks at the "preponderance" of data and not just the first row, because I had already tried making sure the first row had alpha characters in the column that was seen as numeric but should have been typed as character.

Regards,
Bob

[Bob Cergol]

Microsoft ...

In an Excel sheet if I edit the cell containing " 123" to " '123" is treats the numerals as text and tags that fact with a little green triangle in the upper left corner of the cell.

When I prefix the 123 value with the single quote in the CSV file I export, load into Excel and save as an Excel sheet, it does not do this. It retains the quote mark as part of the data and it shows up in the report!

Maddening!

So am I resorting to testing for numeral values and then suffixing them with _ instead in the column it correctly recognizes as text data type, and in the column it wrongly recognizes as numeric, I am prefixing them with a single lower case letter ala "Hungarian" naming convention, according to the class of data in that column.

Hey, Excel is not a real database tool, so why should I expect its files to behave like one outside the Excel application itself!