Use of Excel as database "Numbers"

[ebear]

Hello All,

This will be a simple one I think, but after several days experimenting I didn't found the solution.

I use the possibility of DBPro to export to an Excel file, The way I do this is the following:

- I have an Embedded XLSX file as template
- With "ExtractFile" I save this file to an folder
- After this I open the file with DBPro
- Than I write the date in the file (Numeric and Text)

All works, but the numeric values I write in the XLSX file are written as Text values. In the template I have some calculations but with Text this of course don't work.

How do I get NeobookDBPro to get write Numeric values in XLSX???

Thanks and regards,

Eric

[Neosoft Support]

That's an interesting question, that I can't answer. The article below may be helpful:

http://support.microsoft.com/kb/257819

The article is about VisualBasic, but many of the concepts are the same as DBPro. The article suggests that adding "IMEX=1" to the connection string might help, but you will have to experiment.

[ebear]

Hello Dave,

Thanks a lot. It was not the "IMEX=1".

Solution:

My template has a header of 9 rows with mixed data types. When you write data to Excel using a provider such as Jet or ODBC then the type is inferred from the first 8 rows of data in each column.
So if there is text in one of the first 8 columns, the added records will be text, numeric or not. I have 3 columns in which I need numeric input (for the calculation in the template).
The solution was to redesign the template to have in the header no text in these 3 columns.
It is a lack in the ODBC driver, in my case it was possible to redesign the template.

Below my code if someone needs to export from NeoBook to excel. (as a sample to understand my idea)

Best regards,

Eric

Code: Select all

:CoreInSort_Excel
ndzn_CenterMiddle_Object "P101_Container" "P101_Loader_Gif"
ShowObject "P101_Loader_Gif" "None" "0"
dbpExecSQL "Fis" "SELECT TOP (100) PERCENT dbo.Fis_CoreIn.RID, dbo.Fis_CoreIn.ID, dbo.Fis_CoreIn.DateIn, dbo.Fis_CoreSortItem.Datesort, dbo.Fis_CoreCustomer.CustomerName, dbo.Fis_CoreIn.Qty AS CustQty, dbo.Fis_CoreIn.QtyType, dbo.Fis_CoreIn.TotalWeight, dbo.Fis_CoreIn.Note AS RecNote, dbo.Fis_CoreSortItem.IDKmenZbozi, dbo.Fis_CoreId.XX_CORE, dbo.Fis_CoreId.TYPE, dbo.Fis_CoreId.BRAND, dbo.Fis_CoreId.OE, dbo.Fis_CoreId.TT_CORE, dbo.Fis_CoreSortItem.Q1, dbo.Fis_CoreSortItem.Q2, dbo.Fis_CoreSortItem.Q3, dbo.Fis_CoreSortItem.Q4, dbo.Fis_CoreSortItem.Note FROM dbo.Fis_CoreSortItem INNER JOIN dbo.Fis_CoreIn ON dbo.Fis_CoreSortItem.CoreInID = dbo.Fis_CoreIn.ID INNER JOIN dbo.Fis_CoreCustomer ON dbo.Fis_CoreIn.CustomerID = dbo.Fis_CoreCustomer.ID INNER JOIN dbo.Fis_CoreId ON dbo.Fis_CoreSortItem.ItemNo = dbo.Fis_CoreId.XX_CORE WHERE (dbo.Fis_CoreIn.ID = [Fis.CoreIn.ID])" "CoreInSort"
dbpOpenTable "Fis" "CoreInSort" ""
SetVar "[ExcelFileName]" "![PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[Fis.CoreInSort.RID] - [Fis.CoreInSort.CustomerName].xlsx"
CreateFolder "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]"
ExtractFile "[Embedded]ocreceiptdatasheet.xlsx" "[ExcelFileName]"
dbpOpenDatabase "Export" "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[ExcelFileName];Extended Properties=[#34]Excel 12.0;HDR=YES;[#34]"
dbpOpenTable "Export" "ORDS$" ""
dbpGotoRecord "Export" "ORDS$" "2"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.CustomerName]"
dbpGotoRecord "Export" "ORDS$" "3"
SetVar "[Export.ORDS$.2]"""
dbpGotoRecord "Export" "ORDS$" "4"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.RID]"
dbpGotoRecord "Export" "ORDS$" "5"
SetVar "[Export.ORDS$.2]" ""
dbpGotoRecord "Export" "ORDS$" "6"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.DateIn]"
dbpGotoRecord "Export" "ORDS$" "7"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.DateSort]"
dbpGotoRecord "Export" "ORDS$" "8"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.ID]"
dbpGotoRecord "Export" "ORDS$" "3"
SetVar "[Export.ORDS$.5]" "[Fis.CoreInSort.CustQty]"
dbpGotoRecord "Export" "ORDS$" "3"
SetVar "[Export.ORDS$.6]" "[Fis.CoreInSort.QtyType]"
dbpGotoRecord "Export" "ORDS$" "4"
SetVar "[Export.ORDS$.5]" "[Fis.CoreInSort.TotalWeight]"
dbpGotoRecord "Export" "ORDS$" "7"
SetVar "[Export.ORDS$.5]" "[Fis.CoreInSort.RecNote]"
dbpLast "Export" "ORDS$"
Loop "1" "[Fis.CoreInSort.$RecCount]" "[CounterStat]"
dbpAddRecord "Export" "ORDS$"
SetVar "[Export.ORDS$.1]" "[Fis.CoreInSort.OE]"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.TT_CORE]"
SetVar "[Export.ORDS$.3]" "[Fis.CoreInSort.XX_CORE]"
SetVar "[Export.ORDS$.4]" "[Fis.CoreInSort.TYPE]"
SetVar "[Export.ORDS$.5]" "[Fis.CoreInSort.BRAND]"
SetVar "[Export.ORDS$.7]" "[Fis.CoreInSort.Q1]"
SetVar "[Export.ORDS$.8]" "[Fis.CoreInSort.Q2]"
SetVar "[Export.ORDS$.9]" "[Fis.CoreInSort.Q4]"
SetVar "[Export.ORDS$.10]" "[Fis.CoreInSort.Note]"
dbpNext "Fis" "CoreInSort"
EndLoop
dbpSaveEdits "Export" "ORDS$"
dbpCloseDatabase "Export"
HideObject "P101_Loader_Gif" "None" "0"
Run "[ExcelFileName]" "" "Normal" "" ""
Return

[Neosoft Support]

I'm glad you found a solution. Thanks for posting your code too!