Source language: Translate to:

dbpExportToCSV

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

dbpExportToCSV

Postby ebear » Wed Apr 10, 2013 3:14 pm

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

Anyone???

Regards,

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"
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: dbpExportToCSV

Postby Gaev » Wed Apr 10, 2013 4:11 pm

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".
User avatar
Gaev
 
Posts: 3728
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: dbpExportToCSV

Postby ebear » Mon Apr 15, 2013 4:06 pm

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,

Eric


Code: Select all
:CoreInSort_Excel
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"
EndLoop

dbpCloseDatabase "Export"
Run "[ExcelFileName]" "" "Normal" "" ""
Return
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron