Neep help converting xlsx file to csv without Excel installed

[labrint]

Hi Guys,

Neep help converting xlsx file to csv without Excel installed.

I've seen somewhere in this forum that a script was developed to open excel files without having excel installed. Could it be used to generate CSV files?

Thanks

[mikeyww]

I'm not sure of a direct method-- some here may know-- but one thought is that you might be able to use a command line or COM with OpenOffice or LibreOffice. I believe that there are some posts about using AHK with those programs, so I'd search for them.

[BoBo]

https://docs.microsoft.com/en-us/answers/questions/597931/convert-xlsx-to-csv-using-powershell.html

Someone should be able converting/connecting this [in]to AHK.

"If the absence of the necessary COM object for Excel is your problem you can try the ImportExcel module. I don't think there's a direct-to-csv cmdlet, but you should be able to export rows by piping them into the regulate PowerShell Export-CSV cmdlet. The upside of this is that you don't need Excel."

https://github.com/dfinke/ImportExcel
https://www.powershellgallery.com/packages/ImportExcel/7.0.1

[flyingDman]

You can export an Excel file to a .csv file using ADODB.

You will need to install the Microsoft Database Engine, a free utility which can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=54920.

I created a function - getXcells() - using ADODB to extract data out of Excel files. getXcells() returns an array representing the content of all cells of a particular sheet. To extract all data and convert to a .csv use:

Code: Select all

datasource := a_scriptdir . "\testb2.xlsx"
array := getXcells(datasource,  "sheet1")
for x,y in array
	{
	for a,b in y
		lst .= a=1 ? """" b """" : ",""" b """"
	lst .= x=rowcnt ? "" : "`n"
	}
filedelete, adodb.csv
fileappend, %lst%, adodb.csv
run, adodb.csv										; if excel is installed, the csv will be opened by excel
return

The function:

Code: Select all

;link: https://www.autohotkey.com/board/topic/61204-adodb-excel-database-query-ahk-l/
;requires: https://www.microsoft.com/en-us/download/details.aspx?id=54920

getXcells(datasource, sheet := "sheet1") 
	{
	global colcnt, rowcnt
	arr := [], rowcnt := 0
	objConnection := ComObjCreate("ADODB.Connection"), 	objRecordSet := ComObjCreate("ADODB.Recordset")
	try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
	catch
		{
		msgbox,48,, Error! Data could not be retrieved, 2
		return
		}
	try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1) 			; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
	catch
		{
		msgbox,48,, Error! %Sheet% does not exist, 2
		return
		}
	pFields := objRecordset.Fields
	while !objRecordset.EOF
		{
		row := [], ++rowcnt
		Loop, % colcnt := pFields.Count
			row[A_Index] := pFields.Item(A_Index-1).value
		arr.push(row)
		objRecordset.MoveNext
		}
	objRecordSet.Close()
	objConnection.Close()
	objRecordSet := ""
	objConnection := ""	
	return arr
	}

The function also return 2 variables: colcnt and rowcnt which contain the dimensions of the "usedrange". Note that array.1.1 is the top left cell of the usedrange (which might or might not be cell "A1".

[ahk7]

1. Perhaps http://ebstudio.info/home/xdoc2txt.html - see viewtopic.php?p=110511#p110511

2. Perhaps OfficeOpenXML https://forum.uipath.com/t/howto-create-or-edit-docx-without-word-ahk-ooxml/16286

[flyingDman]

@ahk7 Do you know of any examples using Excel files?

[BoBo]

I've seen somewhere in this forum that a script was developed to open excel files without having excel installed

Cool, would you mind showing/linking it?

[labrint]

You can export an Excel file to a .csv file using ADODB.

You will need to install the Microsoft Database Engine, a free utility which can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=54920.

I created a function - getXcells() - using ADODB to extract data out of Excel files. getXcells() returns an array representing the content of all cells of a particular sheet. To extract all data and convert to a .csv use:

Code: Select all

datasource := a_scriptdir . "\testb2.xlsx"
array := getXcells(datasource,  "sheet1")
for x,y in array
	{
	for a,b in y
		lst .= a=1 ? """" b """" : ",""" b """"
	lst .= x=rowcnt ? "" : "`n"
	}
filedelete, adodb.csv
fileappend, %lst%, adodb.csv
run, adodb.csv										; if excel is installed, the csv will be opened by excel
return

The function:

Code: Select all

;link: https://www.autohotkey.com/board/topic/61204-adodb-excel-database-query-ahk-l/
;requires: https://www.microsoft.com/en-us/download/details.aspx?id=54920

getXcells(datasource, sheet := "sheet1") 
	{
	global colcnt, rowcnt
	arr := [], rowcnt := 0
	objConnection := ComObjCreate("ADODB.Connection"), 	objRecordSet := ComObjCreate("ADODB.Recordset")
	try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
	catch
		{
		msgbox,48,, Error! Data could not be retrieved, 2
		return
		}
	try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1) 			; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
	catch
		{
		msgbox,48,, Error! %Sheet% does not exist, 2
		return
		}
	pFields := objRecordset.Fields
	while !objRecordset.EOF
		{
		row := [], ++rowcnt
		Loop, % colcnt := pFields.Count
			row[A_Index] := pFields.Item(A_Index-1).value
		arr.push(row)
		objRecordset.MoveNext
		}
	objRecordSet.Close()
	objConnection.Close()
	objRecordSet := ""
	objConnection := ""	
	return arr
	}

The function also return 2 variables: colcnt and rowcnt which contain the dimensions of the "usedrange". Note that array.1.1 is the top left cell of the usedrange (which might or might not be cell "A1".

Unfortunately could not test this as the PC did not allow me to install the linked file either in 32 or 64 bit because the PC in question had office installed already. It seems to be the best option from all those mentioned. I thought someone in AHK figured out a script that can convert xlsx files to csv without using external non-AHK software / dlls.

There seems to be a relationship between excel files and XML files which can be read. I wonder if that method can be used?

[BoBo]

Neep help converting xlsx file to csv without Excel installed.

Requested.

…because the PC in question had office installed already.

Current environment [that, AFAIK contains Excel by default]

What's the reason for a completely contrary request?

[labrint]

Neep help converting xlsx file to csv without Excel installed.

Requested.

…because the PC in question had office installed already.

Current environment [that, AFAIK contains Excel by default]

What's the reason for a completely contrary request?

Good question @BoBo. In my case I would use COM because its easy and available. But my clients may not have office installed, hence COM won't work there, and need to think in advance for a universal solution, as rather than design 2 programs, I would design 1.

[BoBo]

So you should have a look at ImportExcel (as recommended above). You could bundle it with your executable, run it as a single line unattended installation routine and use it connected to the already available PowerShell that should be part of your customers system by default.

https://www.youtube.com/watch?v=YyAEsZG21ao&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq&index=2

And yes, you should charge the customer for that 'special' feature!

[flyingDman]

Unfortunately could not test this as the PC did not allow me to install the linked file either in 32 or 64 bit because the PC in question had office installed already

The Microsoft Database Engine can be installed alongside Microsoft Office. I have. What makes you believe you can't. What is the exact error message (assuming you get one)? Is it similar to the one here: https://superuser.com/questions/1557350/how-to-resolve-you-cannot-install-the-32-64-bit-version-of-microsoft-access-da. ?

[ahk7]

I can't really give useful examples as I only remembered some posts.

I forgot to include this one, it might be "best" as xlsx/docx are just ZIP files, you can unpack the xml from it, unpack example here:

OpenAhkXl - Automate Excel with native ahk. no excel need
viewtopic.php?style=17&t=95251
Here is another one more basic (just unzipping the data) viewtopic.php?f=6&t=29408

xdoc2txt
Excel viewtopic.php?style=19&p=9515#p9515
AutoHotkey thread viewtopic.php?t=253

AHK/CLR/OpenXml - Office docs w/o Office, perhaps @burque505 now has more Excel examples (in the thread it says working examples but I don't see them)
Thread viewtopic.php?t=49922

Edit: possible 4th one

xlsx2csv by Caltech (free to use and re-distribute)
Doc https://caltechlibrary.github.io/datatools/docs/xlsx2csv/
Download https://github.com/caltechlibrary/datatools/releases/tag/v1.0.4
(all tools, just look for xlsx2csv.exe in the zip file /bin/ folder)