Convert csv to xls without Excel installed

[penfold]

Hi All,

 

In the past I have used PowerShell to convert between csv and xls files but I think I did it in a pretty basic way by opening the file in excel and "saving as" and ran it against a few files. Now I have a csv with is huge and I think we will have an issue opening this in excel. So I want to convert it to xls as I have seen this reduces the file size. So basically I want a script to convert csv without opening excel. I'm pretty sure I have seen this somewhere online, but I was hoping someone here would already have a script they would be able to share?

 

Thanks

[andy_b]

From my understanding, an XLSX is only smaller than a CSV as they are a compressed file type.

 

As soon as Excel opens the file it is going to uncompress so I don't think this will make any difference.

[mavhc]

if powershell can't do it natively then ImportExcel will

[dhicks]

Now I have a csv with is huge and I think we will have an issue opening this in excel. So I want to convert it to xls as I have seen this reduces the file size. So basically I want a script to convert csv without opening excel.

 

Do you need an XLS file or an XLSX file? XLSX files are compressed as simple ZIP files - if you rename an XLSX file to ZIP you can uncompress it using the standard Windows right-click. As already pointed out, that file will expand to the same size in-memory when you open it in Excel.

 

Is this something you're doing on a regular basis - getting a bunch of data in CSV format and needing to open it in Excel, or is this a one off? There are a couple of Python libraries to read / write Excel files if that's actually what you need. If you think this is too much data for Excel to be able to handle, can you trim it down in some way - have a script go through and discard undeeded lines, maybe? Or load in to a database and query with SQL?

[penfold]

The task is creating some reports as a manual process as an interim process until it is all automated. So I am doing this as a one-off, but potentially this could be run regularly for a few months. I've done it in the past, but it was with much smaller files so I was concerned about the resources on my laptop

[dhicks]

I've done it in the past, but it was with much smaller files so I was concerned about the resources on my laptop

 

If you really are trying to load a lot of data ("a lot" being a somewhat variable term, of course), make sure you know how many rows / columns your CSV file has and how many rows whatever version of Excel you're using can handle - Excel 2010 upwards can handle just over a million rows and 16 thousand columns, seemingly, but that might be something to check.

[chaplic]

Converting it to XLS wont make it smaller

 

 

Converting it to XLSX probably will, if only because its a zip file.

 

But…… why?

[HPlum78]

I am not going to go in to the whys and all that (see above)...

 

When thinking automation using an application then you need to load a COM object and as it happens MS know a thing or two about that

$excel = New-Object -ComObject excel.application

 

The next line I would run is

 

$excel | GM and see what methods and such likes are exposed...

Edited September 30, 2022 by HPlum78

[browolf]

You can write xlsx files with python

 

https://www.geeksforgeeks.org/python-create-and-write-on-excel-file-using-xlsxwriter-module/#:~:text=XlsxWriter%20is%20a%20Python%20module,conditional%20formatting%20and%20many%20others.

[penfold]

I got this done using importExcel function. The wanted to convert it to xlsx due to being a smaller size to transfer across the network and we need to create a separate tab which needs to be done in excel. Reasons for this are not technical, it is requested by management.

 

One more thing now though, I have 2 files with information in both. What I want to do is take information from 1 - and have it populate it in another. Basically what i am trying to do is along the lines of this. We have a master file with information of specific software on clients. I have a second file with a list of the software and the Type it should be. I want to take the software from first file and have it check against file 2 and take the team details and add this into a new file.

 

File1

Software Type

Office Desktop

Google Browser

 

File2

Software

MS office 2010

MS office 2013

Google Ver 123

 

New File Type

MS office 2010 Desktop

MS office 2013 Desktop

Google Ver 123 Browser

 

So I want to check the content Software from the second file and if there is a match from File 1 I want to take the Type and add it as a column to File 2. I thought I could import-csv for this and then use if File2.software -contains File1.Software then pull the File1.Type value. Then just export it as anew csv file.

 

But I think I'm making things more complicated than it needs to be. Any ideas for how I can do this?

Edited October 6, 2022 by penfold

[mavhc]

Probably easier to just use xlookup in excel to do it all

[browolf]

One more thing now though, I have 2 files with information in both. What I want to do is take information from 1 - and have it populate it in another. Basically what i am trying to do is along the lines of this. We have a master file with information of specific software on clients. I have a second file with a list of the software and the Type it should be. I want to take the software from first file and have it check against file 2 and take the team details and add this into a new file.

 

 

Is the point of this to create a list of laptops that need software updating?

[penfold]

Probably easier to just use xlookup in excel to do it all

 

If I knew how to do that I would But also, I am trying to avoid opening up excel and doing anything manually so if I could add the formula via PS then that would be fine. Still I don't know the formula

Edited October 6, 2022 by penfold

[penfold]

Is the point of this to create a list of laptops that need software updating?

 

Not exactly, the point is I have inherited an old way of checking things which is done manually using a bad formula. I have found another spreadsheet which has the legend and I want to use this instead. The reason of doing it in PowerShell is because there are some other steps involved which I have scripted. If I can do this, it is pretty much automated without me having to open up excel at all. I want to be able to run my script and then let people know the file is there via email.

[mavhc]

Need to know the exact format of the files, but play around with xlookup and you should be able to work it out

[penfold]

Just realised the format didn't go as a planned

 

Basically I have this in 2 files

 

File1

[TABLE=width: 500]

[TR]

[TD]Software[/TD]

[TD]Type[/TD]

[/TR]

[TR]

[TD]Office [/TD]

[TD]Desktop[/TD]

[/TR]

[TR]

[TD]Google [/TD]

[TD]Browser[/TD]

[/TR]

[/TABLE]

 

 

File2

[TABLE=width: 500]

[TR]

[TD]Software[/TD]

[/TR]

[TR]

[TD]MS office 2010[/TD]

[/TR]

[TR]

[TD]MS office 2013[/TD]

[/TR]

[TR]

[TD]Google Ver 123[/TD]

[/TR]

[/TABLE]

 

File 2 has other data I want to keep, but basically I want to add another column to File 2 which is the Type column from File 1 to get the desired result below. So I want to match Software from File2 against Software from File 1 and then add a column with the value of Type from File 1. So I was thinking that it needs to be a wildcard for the value of Software (File1) is contained in Software in File 2.

 

File2 (Updated)

[TABLE=width: 500]

[TR]

[TD]Software[/TD]

[TD]Type[/TD]

[/TR]

[TR]

[TD]MS office 2010[/TD]

[TD]Desktop[/TD]

[/TR]

[TR]

[TD]MS office 2013[/TD]

[TD]Desktop[/TD]

[/TR]

[TR]

[TD]Google Ver 123

 

[/TD]

[TD]Browser[/TD]

[/TR]

[/TABLE]

 

I looked into xlookup and got a bit lost. Just for clarity File2 has a lot more data so I want to populate that file with the missing column Type. As this may be re-done in the future I am trying to use an existing file (file1) as the lookup

 

I tried this formula,

=xlookup(A1,"*"&File1A:A&"*",File1B:B) thinking that the wildcard would try and match MS Office 2012 in File2 to *Office* in File1 and pull in Desktop. But this doesn't find a match

Edited October 6, 2022 by penfold

[mavhc]

Partial match is always going to be tricky, https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 explains how to use wildcards, mode 2

[browolf]

done in python and you get an excel file out. It would have a problem if case was inconsistent but it wouldn't take much to fix.

 

 

import pandas as pd
from pyexcel.cookbook import merge_all_to_a_book
import glob

#pip install pandas
#pip install pyexcel pyexcel-xlsx

#file1lookup
#Software,Type
#Office,Desktop
#Google,Browser
df = pd.read_csv('file1lookup.csv', index_col=0).squeeze("columns")
lookups = df.to_dict()
#{'Office': ' Desktop', 'Google': ' Browser'}

#file2data
#Software
#Ms Office 2010
#Ms Office 2013
#Google 123

df = pd.read_csv('file2data.csv', index_col=False)
apps = df.software.values.tolist()
#['Ms Office 2010','Ms Office 2013','Google 123']

merged_list = [[app, lookups[key]] for app in apps for key in lookups.keys() if key in app]

df2 = pd.DataFrame(merged_list)
df2.columns=['Software','Type']
print(df2)
df2.to_csv("merged.csv", index=False)

#output
#Software,Type
#Ms Office 2010,Desktop
#Ms Office 2013,Desktop
#Google 123, Browser

#write excel file
merge_all_to_a_book(glob.glob("merged.csv"), "merged.xlsx")

Edited October 6, 2022 by browolf

[penfold]

I got something similar done in PS. I really need to learn Python (its on my to do list)

 

Although after looking at this for larger data I think just finding the formula in excel is probably quicker