T
Takumi
Guest
Hi there fellow developers,
I'm trying to export a large amount of data to Excel using Progress 4GL. We're talking about ±5500 records and ±170 columns. The problem is that this is too much for Excel to handle and it raises the following error: Excel error. I can continue and it exports all the data, but then it's all messed up and it sometimes creates new lines for data from the last columns of the previous line, resulting in more than 159000 records in Excel.
I'm using the following code to export all data from different CSV files into one single Excel file with a seperate worksheet for each CSV file:
/* Initialize Excel file & add new workbook */
CREATE "Excel.Application" vchExcel.
vchExcel:SheetsInNewWorkbook = NUM-ENTRIES(ipcSheets,',').
vchExcel:WorkBooks:ADD().
ASSIGN vchWorkBook = vchExcel:WorkBooks:Item(1).
/* Import each file's data into a new sheet of the workbook */
Sheet:
DO iTab = 1 TO NUM-ENTRIES(ipcFiles) ON ERROR UNDO, RETRY Sheet:
/* Import CSV data into Excel */
ASSIGN cConnection = SUBSTITUTE("TEXT;" + gvcExportPath + "csv_files\" + "&1",ENTRY(iTab,ipcFiles))
vchWorkSheet = vchExcel:Sheets:ITEM(iTab)
vchWorkSheet:NAME = ENTRY(iTab,ipcSheets)
lResult = vchWorkSheet:QueryTables:ADD(cConnection,vchWorkSheet:cells(1,1)).
ASSIGN
vchQueryTable = vchWorkSheet:QueryTables(1)
vchQueryTable:FieldNames = TRUE
vchQueryTable:RowNumbers = FALSE
vchQueryTable:FillAdjacentFormulas = FALSE
vchQueryTable
reserveFormatting = FALSE
vchQueryTable:RefreshOnFileOpen = FALSE
vchQueryTable:RefreshStyle = 1
vchQueryTable:SavePassword = FALSE
vchQueryTable:SaveData = TRUE
vchQueryTable:AdjustColumnWidth = TRUE
vchQueryTable:RefreshPeriod = 0
vchQueryTable:TextFilePromptOnRefresh = FALSE
vchQueryTable:TextFilePlatform = 437
vchQueryTable:TextFileStartRow = 1
vchQueryTable:TextFileParseType = 1
vchQueryTable:TextFileTextQualifier = 1
vchQueryTable:TextFileConsecutiveDelimiter = TRUE
vchQueryTable:TextFileTabDelimiter = TRUE
vchQueryTable:TextFileSemicolonDelimiter = TRUE
vchQueryTable:TextFileCommaDelimiter = FALSE
vchQueryTable:TextFileSpaceDelimiter = FALSE
vchQueryTable:TextFileTrailingMinusNumbers = TRUE
lResult = vchQueryTable:REFRESH
vchQueryTable:BackgroundQuery = FALSE.
/* Catch all errors */
CATCH eAnyError AS Progress.Lang.ERROR:
RUN disp_mesg(INPUT SUBSTITUTE("ERROR! -> &1",eAnyError:GetMessage(1))).
RUN adnew_log(INPUT SUBSTITUTE("ERROR OCCURRED!")).
RUN adnew_log(INPUT SUBSTITUTE("=> &1: &2",eAnyError:GetMessageNum(1),eAnyError:GetMessage(1))).
END CATCH.
END.
/* Save & close */
vchExcel:Visible = FALSE.
vchExcelisplayAlerts = FALSE.
vchWorkBook:SaveAs(gvcExportPath + ipcName + '.xlsx',,,,,,).
vchWorkBook:CLOSE().
/* Release All Objects */
RELEASE OBJECT vchQueryTable NO-ERROR.
RELEASE OBJECT vchWorkSheet NO-ERROR.
RELEASE OBJECT vchWorkBook NO-ERROR.
vchExcel:QUIT(). /* Quit Excel */
RELEASE OBJECT vchExcel NO-ERROR.
Does any of you know how to solve this issue and get the desired result? Thanks in advance!
Continue reading...
I'm trying to export a large amount of data to Excel using Progress 4GL. We're talking about ±5500 records and ±170 columns. The problem is that this is too much for Excel to handle and it raises the following error: Excel error. I can continue and it exports all the data, but then it's all messed up and it sometimes creates new lines for data from the last columns of the previous line, resulting in more than 159000 records in Excel.
I'm using the following code to export all data from different CSV files into one single Excel file with a seperate worksheet for each CSV file:
/* Initialize Excel file & add new workbook */
CREATE "Excel.Application" vchExcel.
vchExcel:SheetsInNewWorkbook = NUM-ENTRIES(ipcSheets,',').
vchExcel:WorkBooks:ADD().
ASSIGN vchWorkBook = vchExcel:WorkBooks:Item(1).
/* Import each file's data into a new sheet of the workbook */
Sheet:
DO iTab = 1 TO NUM-ENTRIES(ipcFiles) ON ERROR UNDO, RETRY Sheet:
/* Import CSV data into Excel */
ASSIGN cConnection = SUBSTITUTE("TEXT;" + gvcExportPath + "csv_files\" + "&1",ENTRY(iTab,ipcFiles))
vchWorkSheet = vchExcel:Sheets:ITEM(iTab)
vchWorkSheet:NAME = ENTRY(iTab,ipcSheets)
lResult = vchWorkSheet:QueryTables:ADD(cConnection,vchWorkSheet:cells(1,1)).
ASSIGN
vchQueryTable = vchWorkSheet:QueryTables(1)
vchQueryTable:FieldNames = TRUE
vchQueryTable:RowNumbers = FALSE
vchQueryTable:FillAdjacentFormulas = FALSE
vchQueryTable
reserveFormatting = FALSEvchQueryTable:RefreshOnFileOpen = FALSE
vchQueryTable:RefreshStyle = 1
vchQueryTable:SavePassword = FALSE
vchQueryTable:SaveData = TRUE
vchQueryTable:AdjustColumnWidth = TRUE
vchQueryTable:RefreshPeriod = 0
vchQueryTable:TextFilePromptOnRefresh = FALSE
vchQueryTable:TextFilePlatform = 437
vchQueryTable:TextFileStartRow = 1
vchQueryTable:TextFileParseType = 1
vchQueryTable:TextFileTextQualifier = 1
vchQueryTable:TextFileConsecutiveDelimiter = TRUE
vchQueryTable:TextFileTabDelimiter = TRUE
vchQueryTable:TextFileSemicolonDelimiter = TRUE
vchQueryTable:TextFileCommaDelimiter = FALSE
vchQueryTable:TextFileSpaceDelimiter = FALSE
vchQueryTable:TextFileTrailingMinusNumbers = TRUE
lResult = vchQueryTable:REFRESH
vchQueryTable:BackgroundQuery = FALSE.
/* Catch all errors */
CATCH eAnyError AS Progress.Lang.ERROR:
RUN disp_mesg(INPUT SUBSTITUTE("ERROR! -> &1",eAnyError:GetMessage(1))).
RUN adnew_log(INPUT SUBSTITUTE("ERROR OCCURRED!")).
RUN adnew_log(INPUT SUBSTITUTE("=> &1: &2",eAnyError:GetMessageNum(1),eAnyError:GetMessage(1))).
END CATCH.
END.
/* Save & close */
vchExcel:Visible = FALSE.
vchExcel
isplayAlerts = FALSE.vchWorkBook:SaveAs(gvcExportPath + ipcName + '.xlsx',,,,,,).
vchWorkBook:CLOSE().
/* Release All Objects */
RELEASE OBJECT vchQueryTable NO-ERROR.
RELEASE OBJECT vchWorkSheet NO-ERROR.
RELEASE OBJECT vchWorkBook NO-ERROR.
vchExcel:QUIT(). /* Quit Excel */
RELEASE OBJECT vchExcel NO-ERROR.
Does any of you know how to solve this issue and get the desired result? Thanks in advance!
Continue reading...