Open Excel .xlsx file and convert to .csv

[poopall]

Is there anyway of doing this without having to have Excel on the machine,  (Windows)

Is there a lib to do this

[Bernard Mouille]

Hello Poopall,

If you search in this forum you find some dll(s) or class to do this.

If you have installed the free Apache Libre Office ( clac.exe ), you can do this with.

I make later a basic sample ( My brain is not clear in this moment ).

Regards,

Bernard.

[Auge & Ohr]

hi,

it depend on your *.XLSx File

if you "just" have plain Data in a Sheet there are some harbour Source to handle it
you also can install ADO and use ADODB to read/write Excel Sheet
same with ODBC so you got 3 Ways

Jimmy
p.s. if you ask for harbour please tell us which Co,piler you want to use : MinGW, BCC or MSVC

[poopall]

Im using BCC

[Auge & Ohr]

hi,

if you use MinuGUI Extended Version have a look at
        c:\minigui\Samples\Advanced\HbXlsxWriter\ which have *.LIB

!note : c:\minigui\Samples\HbXlsxWriter\ have *.a as LIB for MinGW

Jimmy

[Auge & Ohr]

sorry i make a Misttake

HbXlsxWriter  is a port  of the Libxlsxwriter (please see below ). As original
library it can only create new files. It cannot read or modify existing files!

wonder that it can "not read" (only) *.XLSx

Jimmy

[Auge & Ohr]

ok, next try : ADO

it you have NOT Office installed you must download "Driver"

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

Samole
   c:\minigui\Samples\Advanced\ReadXLS_2\ReadExcel.PRG
it can "read" a Excel Sheet and "output" a TXT

Question : why you want to have a *.CVS ... to "import" it into DBF ?

you can use the Sample and "direct" transfer Data into DBF

---

search for 'ADODB.Connection'. you find "other" Sample which use it but have "other Connectionstring"
after change "Connectionstring" you can use "ADODB.Recordset" to "navigate"

Fivewin use ADO "direct" in XBROWSE e.g. for MySQL / MariaDB

Jimmy

[Bernard Mouille]

Hello Poopall,

You can take some idea in the code below my signature.

Regards,
Bernard.

// Begin code.

/*
   LC_XlsxToCsv.prg
   Save all sheets of an Excel 2007 or later file to csv files.

   Using HB32, Windows10, Libre Office last version.

   Compile with -lhbwin

   *** Change the parameters before run this code. ***
   ***  This code works only when Calc is not hidden.   ***

   This code is a basic sample for tests.

   web : http://bernard.mouille.free.fr/mso-hb32/LC_XlsxToCsv.txt
   Last change : 2023-07-05
*/

// Parameters : filters options for csv file.
// https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
//#define BM_cListSeparator  "44"    // ASCII Field separator      : 44 = commas.
#define BM_cListSeparator  "59"    // ASCII Field separator      : 59 = Semicolon.
#define BM_cFieldDelimiter "34"    // ASCII Text field delimiter : 34 = double quotes.
//#define BM_cEncoding       "76"    // Encoding : 76 = Unicode (UTF-8).
#define BM_cEncoding        "0"    // Encoding :  0 = System.
#define BM_cFirstLine       "1"    // First line to be treated in csv file.
// You can add columns options ( look the before web link ).
#define BM_FiltersOptions  BM_cListSeparator + "," + BM_cFieldDelimiter + "," + BM_cEncoding + "," + BM_cFirstLine

// Parameter : csv output file.
#define BM_FILE_CSV hb_dirbase() + "_Result_"          // Base files CSV to create with his path.

procedure Main
   local i                  // Numeric count.
   local Excel_File         // Excel file to convert.
   local aFiles             // Files array.
   local args_Book          // Book create or open parameter(s).
   local args_Csv           // Save as csv file parameters.
   local oServiceManager    // Libre Office Object first.
   local oDesktop           // Libre Office Object 2.
   local oBook              // Book object.
   local oSheet             // Sheet object.

   setmode( 25, 80 )
   setcolor( "GR+/B" )
   @ 0, 0, maxrow(), maxcol() box space( 9 )

   ? "Save all sheets of an Excel 2007 or later file to csv files."
   ?

   aFiles := hb_Directory( BM_FILE_CSV + "*.csv" )
   for i := 1 to len( aFiles )
      ferase( aFiles[ i, 1 ] )               // Delete the old files created with this program.
   endfor

   Excel_File := win_GetOpenFileName( , "Select an Excel file",,, { "Excel files ; *.xlsx" } )
   if .not. file( Excel_File )
      Alert( "Excel file not found, abort." )
      return
   endif

   // Create the Libre Office objects.
   oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" )
   oDesktop        := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )

   // Create the create book property : display Calc and open xlsx file.
   args_Book := { lo_PropertyCreate( oServiceManager, "Hidden"    , .F.                      ) ;
                , lo_PropertyCreate( oServiceManager, "ReadOnly"  , .T.                      ) ;
                , lo_PropertyCreate( oServiceManager, "FilterName", "Calc MS Excel 2007 XML" ) }

   // Create the book.
   oBook := oDesktop:loadComponentFromURL( bh_FileToUrl( Excel_File ) ;
                                         , "_blank" ;
                                         , 0, args_Book )

   for i := 0 to oBook:Sheets:Count - 1
      oSheet  := oBook:GetSheets:getByIndex( i )
      oBook:CurrentController:setActiveSheet( oSheet ) // *** Not works if book is hidden. ***
      // Create the save as csv file properties.
      args_Csv := { lo_PropertyCreate( oServiceManager, "FilterName"   , "Text - txt - csv (StarCalc)" ) ;
                  , lo_PropertyCreate( oServiceManager, "FilterOptions", BM_FiltersOptions             ) }
      oBook:storeAsURL( bh_FileToUrl( BM_FILE_CSV + hb_ntos( i ) + ".csv" ), args_Csv )  // Write the csv file on the disk.
   endfor

   oBook:Close( .T. )

   oServiceManager := nil
   return

// Tools functions Libre Office.

// Create a property object.
function lo_PropertyCreate( oServiceManager, cName, xValue )
   local oProperty
   oProperty       := oServiceManager:bridge_GetStruct( "com.sun.star.beans.PropertyValue" )
   oProperty:Name  := cName
   oProperty:Value := xValue
   return oProperty

// Tools functions common.

// Convert a file and path Windows name to URL.
function bh_FileToUrl( cFile )
   local wFile := cFile
   if hb_at( "/", cFile ) > 0
      return cFile
   endif
   if left( wFile, 1 ) == "\"
      wFile := left( hb_dirbase(), 2 ) + wFile
   endif
   wFile := strtran( wFile, ":", "|" )
   wFile := strtran( wFile, "\", "/" )
   wFile := strtran( wFile, " ", "%20" )
   wFile := "///" + wFile
   return "file:" + wFile

// End code.

[fdaniele]

to mr.  Bernard Mouille

complements, i like very much

[Bernard Mouille]

Hello fdaniele,

Thanks for your comment.

Maybe it is usefull for somebody the code below my signature.

Regards,

Bernard.

// Begin code.

/*
   LC_XlsxToDbf.prg
   Save all sheets of an Excel 2007 or later file to dbf files.

   Using HB32, Windows10, Libre Office last version.

   Compile with -lhbwin

   *** Change the parameters before run this code. ***
   ***  This code works only when Calc is not hidden.   ***

   This code is a basic sample for tests.

   web : http://bernard.mouille.free.fr/mso-hb32/LC_XlsxToDbf.txt
   Last change : 2023-07-05
*/

// Parameters : filters options for dbf file.
// https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options

//#define BM_cEncoding       "76"    // Encoding : 76 = Unicode (UTF-8).
#define BM_cEncoding        "0"    // Encoding :  0 = System.

// Parameter : dbf output file.
#define BM_FILE_DBF hb_dirbase() + "_Result_"          // Base files DBF to create with his path.

procedure Main
   local i                  // Numeric count.
   local Excel_File         // Excel file to convert.
   local aFiles             // Files array.
   local args_Book          // Book create or open parameter(s).

   local args_Dbf           // Save as dbf file parameters.

   local oServiceManager    // Libre Office Object first.
   local oDesktop           // Libre Office Object 2.
   local oBook              // Book object.
   local oSheet             // Sheet object.

   setmode( 25, 80 )
   setcolor( "GR+/B" )
   @ 0, 0, maxrow(), maxcol() box space( 9 )

   ? "Save all sheets of an Excel 2007 or later file to dbf files."
   ?

   aFiles := hb_Directory( BM_FILE_DBF + "*.dbf" )

   for i := 1 to len( aFiles )
      ferase( aFiles[ i, 1 ] )               // Delete the old files created with this program.
   endfor

   Excel_File := win_GetOpenFileName( , "Select an Excel file",,, { "Excel files ; *.xlsx" } )
   if .not. file( Excel_File )
      Alert( "Excel file not found, abort." )
      return
   endif

   // Create the Libre Office objects.
   oServiceManager := win_oleCreateObject( "com.sun.star.ServiceManager" )
   oDesktop        := oServiceManager:createInstance( "com.sun.star.frame.Desktop" )

   // Create the create book property : display Calc and open xlsx file.
   args_Book := { lo_PropertyCreate( oServiceManager, "Hidden"    , .F.                      ) ;
                , lo_PropertyCreate( oServiceManager, "ReadOnly"  , .T.                      ) ;
                , lo_PropertyCreate( oServiceManager, "FilterName", "Calc MS Excel 2007 XML" ) }

   // Create the book.
   oBook := oDesktop:loadComponentFromURL( bh_FileToUrl( Excel_File ) ;
                                         , "_blank" ;
                                         , 0, args_Book )

   for i := 0 to oBook:Sheets:Count - 1
      oSheet  := oBook:GetSheets:getByIndex( i )
      oBook:CurrentController:setActiveSheet( oSheet ) // *** Not works if book is hidden. ***

      // Create the save as dbf file properties.
      args_Dbf := { lo_PropertyCreate( oServiceManager, "FilterName"   , "dBase"      ) ;
                  , lo_PropertyCreate( oServiceManager, "FilterOptions", BM_cEncoding ) }
      oBook:storeAsURL( bh_FileToUrl( BM_FILE_DBF + hb_ntos( i ) + ".dbf" ), args_Dbf )  // Write the dbf file on the disk.

// End code.