Page 1 of 1


PostPosted: Wed Apr 10, 2013 3:14 pm
by ebear
Hello All,

I'm testing to find out a good way to create an excel file (csv) from a DBPro query, see the test code below.

Now I have two problems:
1. I try to write a header on the top with FileWrite. This works perfect (without the dbpExportToCSV), but if I execute the dbpExportToCSV the header is overwritten (Append=YES)
2. I want to have the FieldNames on top of the table, I tried several possibilities with IncludeFieldNames=Yes;UseColumnTitles=No, but the FieldNames are not written on top.

My DB is MS-SQL Server 2008



Eric Beerhorst

Code: Select all
SetVar "[RID]" "123456"
SetVar "[CUSTOMER]" "Beerhorst"
SetVar "[CustomerID]" "1"
SetVar "[FisID]" "111"
SetVar "[RID]" "123456"

CreateFolder "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]"
ExtractFile "[Embedded]ocreceiptdatasheet.csv" "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv"

FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv" "1" "OC RECEIPT DATA SHEET;;;;;;"
FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv" "2" ";;;;;;"
FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv" "3" "Cust. Name:;;[Customer];;;;;;"
FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv" "4" "Cust. Code:;;[CustomerID];;;;;;"
FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\\[RID] - [CUSTOMER].csv" "5" "RID:;;[RID];;;;;;"
FileWrite "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\\[RID] - [CUSTOMER].csv" "6" "FisID:;;[FisID];;;;;;"

dbpExportToCSV "Fis" "Fis_CoreSortItem" "[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv" "Range=All;[#34]Delimiter=;[#34];IncludeHidden=Yes;IncludeFieldNames=Yes;UseColumnTitles=No;Append=Yes"

Re: dbpExportToCSV

PostPosted: Wed Apr 10, 2013 4:11 pm
by Gaev
Eric Beerhorst :

1) Instead of [#34]Delimiter=;[#34] ... try Delimiter=[#34];[#34]

2) Surprised that NeoBook did not attempt to do Math on ...

"[PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv"

... suggest you do ...

SetVar "[csvFileName]" "![PubDir]export\[Year]\[Year]-[MonthNum]-[DayNum]\[RID] - [CUSTOMER].csv"
AlertBox "csvFileName" "![csvFileName]"

... note the exclamation mark ... ! ... and then use ![csvFileName] in the other commands

If all this still does not work as expected, you may have to "export the records to a temp file" ... then follow up by appending the "temp file" to the "header lines".

Re: dbpExportToCSV

PostPosted: Mon Apr 15, 2013 4:06 pm
by ebear
Hello Gaev,

I found the solution see the code below. My issue was that I needed to create an Excel file with export out of a database (MS-SQL). My first thoughts where to export to a CSV file which Excel opens.
But with CSV file format there isn't any possibility to do any kind of formatting. Now I using an Embedded XLSX file formatted in the style needed and with use of possibility DBPro I add the data in this file.
My code is perhaps not yet perfect but it works.

For anyone with a similar problem below my code, questions I'm available.

Thanks A lot,


Code: Select all
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_CoreCrossOver.OE, dbo.Fis_CoreCrossOver.XX_CORE, dbo.Fis_CoreCrossOver.TT_CORE, dbo.Fis_CoreCrossOver.TYPE, dbo.Fis_CoreCrossOver.BRAND, 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 LEFT OUTER JOIN dbo.Fis_CoreCrossOver ON dbo.Fis_CoreSortItem.ItemNo = dbo.Fis_CoreCrossOver.XX_CORE WHERE (dbo.Fis_CoreIn.ID = [Fis.CoreIn.ID]) ORDER BY dbo.Fis_CoreCrossOver.XX_CORE" "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$" "3"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.CustomerName]"
dbpGotoRecord "Export" "ORDS$" "4"
SetVar "[Export.ORDS$.2]" ""
dbpGotoRecord "Export" "ORDS$" "5"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.RID]"
dbpGotoRecord "Export" "ORDS$" "6"
SetVar "[Export.ORDS$.2]" ""
dbpGotoRecord "Export" "ORDS$" "7"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.DateIn]"
dbpGotoRecord "Export" "ORDS$" "8"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.DateSort]"
dbpGotoRecord "Export" "ORDS$" "9"
SetVar "[Export.ORDS$.2]" "[Fis.CoreInSort.ID]"

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$.6]" "[Fis.CoreInSort.Q1]"
SetVar "[Export.ORDS$.7]" "[Fis.CoreInSort.Q2]"
SetVar "[Export.ORDS$.8]" "[Fis.CoreInSort.Q3]"
SetVar "[Export.ORDS$.9]" "[Fis.CoreInSort.Q4]"
SetVar "[Export.ORDS$.10]" "[Fis.CoreInSort.Note]"
dbpNext "Fis" "CoreInSort"

dbpCloseDatabase "Export"
Run "[ExcelFileName]" "" "Normal" "" ""