Tag Archives: csv

convert csv to excel

Convert CSV to Excel – How to open CSV and save as Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

CSV files are commonplace nowadays. Hence knowing how to convert CSV to Excel is a useful skill. CSV files are especially useful when wanting to save a table worth of data in a simple to read format. CSVs are also easy files to create and read from – as they are basically text files with a .csv file extension. That means they easily open in Wordpad, Notepad, Word and virtually any other text editor.

CSV fileCSV (Comma-separated values) – in computing these are text files that store tabular data (numbers and text in columns):

  • Each row represent a single record
  • Each column represents a certain property
  • Each “cell“, or in other words columns within a row, are separated by a certain delimiter. Usually a comma , character (but not only!)
  • Usually the first row is the header – contains names for each column

Today I want to show how you can quickly convert a CSV file into an Excel XLSX file. I will base this tutorial on the following example of a CSV file:

Sample CSV (Comma-separated values file)
Sample CSV (Comma-separated values file)

Open the file in Excel

Simply double click on the file or if needed right-click and Open with the file in Microsoft Excel. The file should open showing only a single Worksheet like shown below:

CSV file opened in Excel
CSV file opened in Excel

If columns in a CSV are separated by your default Windows list separator the columns will be separated automatically without needing to proceed further. Where to find you default Windows locale list separator? Control Panel->Region and Language->Additional Settings->List Separator. In my case it was a ;

Select first column and proceed to Text to Columns

Select the entire first column where all your data should be located. Next click on the Text to Columns button in the DATA ribbon tab:

Data ribbon: Text to Columns
Data ribbon: Text to Columns

Proceed according to Wizard instructions

This is the hard part. Text to Columns need additional information on the delimiter and format of your columns.

Delimited or Fixed width?

CSV files are usually delimited using a specific character (like a comma or semicolon), in this case select Delimited. Sometimes however columns are fixed width, separated by spaces, in this case select Fixed width. Next click Next to proceed.

Text to Columns Wizard: Step 1
Text to Columns Wizard: Step 1

Select delimiter

Assuming your columns are separated with a specific delimiter you need to provide this delimiter in the Wizard. Look at the Data preview to make sure your columns will be separated correctly. When finished proceed with Next

Text to Columns Wizard: Step 2
Text to Columns Wizard: Step 2

Format your columns (optional)

The last step is to format your columns if needed. If your columns represent Dates or you want to pull a column containing numbers/dates as text instead – be sure to format it appropriately. Usually, however, you are fine with hitting Finish:

Text to Columns Wizard: Step 3
Text to Columns Wizard: Step 3

Admire your Excel converted CSV file

If you have proceeded according to the steps above you should have a neatly formatted spreadsheet like the one below.

Convert CSV to Excel file
Convert CSV to Excel file

One last thing as Steve Jobs used to say… Remember to save the file as an Excel XSLX (or XLSB or similar) file:
save csv

Convert multiple CSV to Excel with VBA

If you have multiple CSVs you want to convert or pull into an Excel file the above approach may be a big burden. So lets use some VBA macros to help.

Import entire CSV

The below is take straight from my Read file in VBA blog post. This pulls a single file into the destRng Excel range.

Dim ws as Worksheet, destRng as Range, fileName as String
Set destRng = Range("A1")
Set ws = ActiveSheet
fileName = "C:\text.csv" 'Replace with file name
With ws.QueryTables.Add(Connection:= "TEXT;" & fileName & "", Destination:=destRng)
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 852
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    'Select your delimiter - selected below for Comma
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileTrailingMinusNumbers = True
    'This will refresh the query
End With

Import selected CSV rows

Now a more interesting and complex scenario – let us assume we want to import just some rows of our CSV. Fortunately Excel (as well as Access) support SQL queries. We can therefore do a simple SELECT query to upload all records or add some filtering (using WHERE clause), grouping (using GROUP BY clause) and etc.

'Assuming file looks like this. File path: C:\test.csv
'"Col1", "Col2", "Col3"
'1     , 2     , 3
'11    , 12    , 1
'2     , 5     , 6
 
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM test.csv WHERE Col1 > 10"
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
   col1 = rs("Col1")
   col2 = rs("Col2")
   col3 = rs("Col3")
   rs.MoveNext
Loop Until rs.EOF

The above will only pull the second row as Col1 is > 10.

Convert CSV to Excel tips

CSV files are usually used when a file contains a large amount of data. Excel tends to bloat pretty quickly although it compresses the data pretty well. Nevertheless you might find yourself struggling with Excel performance or even experiencing an Excel crash. What to do? Save the file as an XLSB – read more here.

merge csv files

Merge CSV files or TXT files in a folder – using Excel or CMD

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Often we face the challenge of having to merge csv files or txt files in a folder, into a single file. Excel is the obvious tool for such tasks and today I will show a couple of easy ways for merging multiple files, in a single or even a whole structure of folders, into a single CSV or text file. To merge csv files or other text files it is often best to use Visual Basic for Applications in Excel.

Let’s start with the simplest approach using Windows Command line without having to use Excel.

Merge CSV files using Windows CMD

This approach uses the Windows Command line Copy command.

Open the folder which should contain your CSV or TXT files

Open in Windows Explorer the folder containing CSV or TXT files to be merged. These should be without headers or only the first file should be with headers.
merge CSV files Windows CMD

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.
merge CSV files Windows CMD

Merge the files using COPY command

The CMD Windows command line Window should open. Type the following command and hit ENTER to merge files

copy *.csv merge.csv

merge CSV files Windows CMD
The result will be the newly created merge.csv file with merged data across all CSV files within the directory.

Simply replace *.csv with *.txt to merge text files instead of CSV files

Merge list of csv, txt files

The previous method was very simple and didn’t require the use of Excel or MS Office. The below and the following approaches will provided you with more flexibility when merging files. If you don’t know how to use Macros in Excel read my Tutorial first.

Assuming you want to merge a list of files in a String Array you can use the procedure below. It will merge all provided csv or text files into a single new text file.

Sub MergeFiles(fileNames() As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    For Each fileName In fileNames
        fileNo = FreeFile
        Open fileName For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
    Next fileName
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files example

See an example below of how to use the MergeFiles procedure:

Dim fileNames(0 To 1) As String
fileNames(0) = "C:\somefolder\test.csv"
fileNames(1) = "C:\somefolder\test1.csv"
    
MergeFiles fileNames, "C:\Merged.csv", True, False

MergeFiles Parameters

fileNames()
Array of Strings representing full file paths to files that are to be merged

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for CSV TXT files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within specified folder

Another case is when you want to merge all csv files within a single folder. This procedure is similar to the previous one with the exception that it runs through all files within a single directory (excluding subdirectories – for that scroll to next procedure). You can also use wildcards such as “*.csv” to be sure that only csv files are merged a not other files – read my post on the VBA Dir function to learn more.

Sub MergeFilesInFolder(folderName As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    fileName = Dir(folderName)
    Do Until fileName = ""
        fileNo = FreeFile
        Open (Left(folderName, InStrRev(folderName, "\")) & fileName) For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
        fileName = Dir
    Loop
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files in folder example

See an example below of how to use the MergeFilesInFolder procedure:

MergeFilesInFolder "C:\somefolder\*.csv", "C:\MergedFolder.csv", True, False

MergeFilesInFolder Parameters

folderName
A folder including all files to be merged. Wildcards are permitted if supported by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within all subfolders

The most complex case is when you want to merge files not only within a certain directory but also within all subdirectories. This will equally work for a scenario when there are no subfolders.

Sub MergeFilesInSubFolders(folderName As String, pattern As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, folder As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    Dim col As Collection
    Set col = New Collection
    col.Add folderName
    TraversePath folderName, col
    For Each folder In col
        fileName = Dir(folder & pattern)
        Do Until fileName = ""
            fileNo = FreeFile
            Open (Left(folder, InStrRev(folder, "\")) & fileName) For Input As #fileNo
            textData = Input$(LOF(fileNo), fileNo)
            Close #fileNo
            If headers Then
                result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
                firstHeader = False
            Else
                result = result & IIf(addNewLine, vbNewLine, "") & textData
            End If
            fileName = Dir
        Loop
    Next folder
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Function TraversePath(path As Variant, allDirCollection As Collection)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
     
    currentPath = Dir(path, vbDirectory)
    'Explore current directory
    Do Until currentPath = vbNullString
        If Left(currentPath, 1) <> "." And Left(currentPath, 2) <> ".." And _
            (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add path & currentPath & "\"
            allDirCollection.Add path & currentPath & "\"
        End If
        currentPath = Dir()
    Loop
     
    'Explore subsequent directories
    For Each directory In dirCollection
        TraversePath directory, allDirCollection
    Next directory
End Function
Read this post to learn more on using the VBA Dir function to traverse directories and subdirectories

Merge csv, txt files within subfolders example

See an example below of how to use the MergeFilesInSubFoldersprocedure:

MergeFilesInSubFolders "C:\somefolder\", "C:\MergedSubFolders.csv", True, False

MergeFilesInSubFolders Parameters

folderName
A folder with or without subfolders including all files to be merged. Use wildcards with pattern parameter

pattern
If needed a pattern using wildcards permitted by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge CSV files – filter records

Sometimes we want to download just a subset of records in our CSV files. One way is uploading the data and then filtering it in Excel. But why not do it in one go? See my SQL AddIn or my read CSV file using SQL example in this post here.

Scrape Google Search Results to CSV

Scrape Google Search Results to CSV using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Google is today’s entry point to the world greatest resource – information. If something can’t be found in Google it well can mean it is not worth finding. Similarly SEO experts blog and write about how to optimize your web pages to rank best in Google Search results often ignoring other search engines which contribution to page impressions & clicks is almost irrelevant nowadays due to the G-Monopoly. Naturally there are tons of tools out there for scraping Google Search results, which I don’t intend to compete with. On the other hand even Google makes scraping it’s web results dead easy by facilitating its dedicated Web API. My goal in this post we be to show how easy it is to utilize Excel & VBA to scrape those search results into a simple CSV file which as little effort as possible.

Before you start reading on remember that violation of Googles TOS will get you temporarily banned!

As Google has retired its old AJAX Query API the post has been updated to use the new Custom Search JSON/Atom API. However I have not tested the new code yet :) so let me know if it works

Google Ajax Search API

The obvious way in which we obtain Google Search results is via Googles Search Page. However, such HTTP requests return lot’s of unnecessary information (a whole HTML web page).

In my case a simple “cats and dogs” Google Search almost 1 second and contains over 300kb of data, as according to pingdom:

https://www.google.com.ua/?q=cats+and+dogs#
HTTP Google Request stats
HTTP Google Request stats

Thankfully Google is not oblivious to the fact that many people want to scrape it’s search results and facilitates a simple AJAX Google Search API. Let’s see how that compares. In my case the AJAX API call of “cats and dogs” returned in approx. 400 miliseconds and contained less than 2kb of data:

https://www.googleapis.com/customsearch/v1?key=GOOGLE_API_LICENSE_KEY&cx=017576662512468239146:omuauf_lfve&q=cats%20and%20dogs

REST Google API Request stats
REST Google API Request stats

That’s an improvement right? The results are returned in JSON and we can leverage a whole range of parameters.

JSON Google Search Results format

The AJAX Google Search Web API returns results in JSON. To be able to scrape these results we need to understand the format in which Google returns these results.

The only part of the JSON query that interests us is the “results”: [ … ] section. This is the array that will iterate through the results providing the following parameters separated by commas:

  • “GsearchResultClass” – “GwebSearch” for simple text search
  • “unescapedUrl” – the unescaped URL of the results (e.g. http://en.wikipedia.org/wiki/Paris_Hilton)
  • “url” – escaped url
  • “visibleUrl” – shortened version of the URL of the result, stripped of protocol and path
  • “cacheUrl” – cached Google url
  • “title” – title of the page
  • “titleNoFormatting” – tile of the page w/o formatting
  • “content” – brief snippet of information from the page

Great. Seems like we have our data source and query string. Let’s get started with leveraging that in Excel VBA.

Scrape Google Search Results using VBA

As we know the structure of each Google Result let’s follow programming best practices and create a suitable VBA Class (I will explain why not a Type structure afterwards):

Public unescapedUrl As String
Public url As String
Public visibleUrl As String
Public cacheUrl As String
Public title As String
Public titleNoFormatting As String
Public content As String

Each object of type VBA Class GoogleSearchResult will represent a single search result record. To download the records and save the data as CSV file I have divided the task into 3 distinct steps:

Below the main Sub procedure SaveGoogleResultsCSV:

Sub SaveGoogleResultsCSV()
    Dim res As String, resCol As Collection
    'Step 1
    res = GetResult("https://www.googleapis.com/customsearch/v1?key=GOOGLE_API_LICENSE_KEY&cx=017576662512468239146:omuauf_lfve&q=cats%20and%20dogs")
    'Step 2
    Set resCol = ExtractResults(res)
    'Step 3
    SaveToCSV resCol, "C:\results.csv"
End Sub

Below each section will address these steps.

Get the JSON query result

To scrape the JSON results we can welcome the XMLHttpObject which will efficiently download the entire HTTP response of the query:

Function GetResult(url As String) As String
    Dim XMLHTTP As Object, ret As String
    Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "Cache-Control", "no-cache"
    XMLHTTP.setRequestHeader "Pragma", "no-cache"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send
    ret = XMLHTTP.ResponseText
    GetResult = ret
End Function

Extract the query results to a VBA Collection

The JSON result is mashed up and unfortunately is harder to read than an XML which we could more easily read in VBA. In this case we need to revert to using VBA Regex:

Function ExtractResults(res As String) As Collection
    Dim resCol As Collection, tmp As String, matches, match, subMatch, GoogleRes As GoogleSearchResult
    Set resCol = New Collection
    Dim regex As Object, str As String
    Set regex = CreateObject("VBScript.RegExp")
    With regex
      .Pattern = """results"":\[([^\]]+)\]": .Global = False
    End With
    Set matches = regex.Execute(res)
    tmp = matches(0).SubMatches(0)
    With regex
      .Pattern = "\{([^\}]+)\}": .Global = True
    End With
    Set matches = regex.Execute(tmp)
    For Each match In matches
      If match.SubMatches.Count > 0 Then
        For Each subMatch In match.SubMatches
          Set GoogleRes = New GoogleSearchResult
          GoogleRes.cacheUrl = ExtractAttribute(CStr(subMatch), "formattedUrl")
          GoogleRes.content = ExtractAttribute(CStr(subMatch), "snippet")
          GoogleRes.title = ExtractAttribute(CStr(subMatch), "title")
          resCol.Add GoogleRes
        Next subMatch
      End If
    Next match
    Set ExtractResults = resCol
End Function
Function ExtractResult(res As String, attrib As String) As String
    Dim regex As Object, str As String
    Set regex = CreateObject("VBScript.RegExp")
    With regex
      .Pattern = "{.*}": .Global = False
    End With
    Set matches = regex.Execute(str)
    ExtractAttribute = matches.SubMatches(0)
End Function
Function ExtractAttribute(res As String, attrib As String) As String
    Dim regex As Object, str As String, matches
    Set regex = CreateObject("VBScript.RegExp")
    With regex
      .Pattern = """" & attrib & """:""([^\""]*)""": .Global = False
    End With
    Set matches = regex.Execute(res)
    ExtractAttribute = matches(0).SubMatches(0)
End Function

Write the data to CSV

Lastly having all results uploaded to our resCol VBA Collection object we can write all the records to an CSV file:

Sub SaveToCSV(resCol As Collection, fileName As String)
    Dim textData As String, delimiter As String, it As GoogleSearchResult, fileNo As Integer
    delimiter = ";"
    For Each it In resCol
        textData = textData & it.cacheUrl & _
            delimiter & it.content & _
            delimiter & it.title & _
            delimiter & it.cacheUrl & vbNewLine
    Next it
    fileNo = FreeFile
    Open fileName For Output As #fileNo
    Print #fileNo, textData
    Close #fileNo
End Sub

Google Search API parameters

Listed below are some key parameters that will definitely prove useful. You can find the rest here.

  • rsz – the number of results (e.g. rsz=4 will be 4 results)
  • hl – host language (e.g. hl=en for english)
  • start – the start index for the search results

A simple example below:

https://www.googleapis.com/customsearch/v1?key=GOOGLE_API_LICENSE_KEY&cx=017576662512468239146:omuauf_lfve&q=cats%20and%20dogs&hl=pl&rsz=2

Will return 2 results, for host language Polish for the query “cats and dogs”.

Proxies

Do read my post on using proxies HTTP servers to distribute your queries.

Conclusions

Running the SaveGoogleResultsCSV procedure will provide us with a single result – as CSV file:

Scrape Google Results CSV
Scrape Google Results CSV

Obviously we can also refrain from actually saving the results and proceed with traversing our list of results to do an analysis or to save them to an Excel worksheet. For this we need only traverse the resCol VBA Collection and read only the attributes we need.

Before you start using the Google API extensively be sure to read Google’s Terms of service. Remember – scrape Google Search Results in a responsible way!

xlsb vs xlsx

XLSB vs XLSX. The Pros and Cons of XLSB Files

1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.57 out of 5)
Loading...

Working with large Excel files is often a drag. They open slower, they take an eternity to save and they often need to be uploaded to the Cloud to be shared with your coworkers or family. Why not explore the benefits of the XSLB file format then?

What is a XLSB file format?

Inside an XLSX file
Inside an XLSX file
This is what is inside an XLSX (or XLSM) file (image on the right). What do you mean inside? – you ask.

XLSX and XLSM files are in fact compressed archives with XML files inside. That is because Microsoft has opened the Excel file format and decided to break the insides into XML files. When an XLSX or XLSM file is saved Excel needs to break it down into separate XML files, compress it and finally save it as XLSX or XLSM.

large excelThe XLSB file format on the other hand is a binary Excel file. It resembles the old XLS file format which was also a binary file.
What happens when Excel wants to save an XSLB file? Not much really. It mostly dumps the binary data into a binary file and saves it as XLSB.

XLSB advantages (XLSB vs. XLSX)

  • XLSB files are smallerSmaller file size – the Excel binary file uses noticeably less space. This is more noticeable especially when working with large Excel files. In some cases I heard of there being XLSB files that required 10% of the original file size – this is more visible with VERY large files
  • XLSB files save or open fasterOpens/saves more quickly – loading binary data is faster than parsing text (XML) files – similarly as you would compare opening a book in Spanish and having to translate every sentence to English as opposed to picking up a ready translated copy. Similarly, when saving the file – the binary format is more efficient than dumping the data back into the XML and then compressing it. From my experience XLSB files open and get saved 2x faster
  • XLSB files support longer formulasSupports formulas above the 8192 character limit. In other file formats they don’t save properly

Yahoo! Well it would seem there is nothing less obvious to do then to start working only on the XLSB file format. However, it’s not a straightforward decision as there are some minor setbacks. Here are some that come to mind…

XLSB disadvantages (XLSB vs. XLSX)

  • Compatibility – the XLSB Excel format is not supported by Excel 2003 and earlier versions, which frankly is less of a problem nowadays
  • Security (VBA) – with the distinction between the XLSM and XLSX format you know which files may or not contain VBA macros. With XLSB you won’t know for sure. So beware when opening XLSB files from unknown sources or from people/websites you don’t trust
  • You can’t make changes to the Excel Ribbon when working on an XLSB. You must temporarily save your file as XLSX or XLSM, makes changes and save back as XLSB.
  • Lack of interoperability with third-party tools. XLSB is a binary file format unlike the open XML XLSX and XLSM files. Hence you often won’t see your XLSB files working everywhere – like in OpenOffice

Other suggestions when working with large Excel files or datasets

I usually start with the quick wins suggestions and leave the more complex for desert. The XLSB file format is a good start and often won’t require that you meddle with the data/formatting/content of your workbook, rightfully as you shouldn’t have too. However, from time to time there will be those moments when that won’t be enough and you just won’t be able to work any longer with a slow and large Excel file. Here are some useful tips:

  • Reduce the file size by deleting unused cells – as stupid as it sounds this is often the reason for your Excel files mysteriously growing in size over a short period of time. Here is the solution:
    1. Find the last used row in your worksheets

    2. Delete all rows below

    3. Find the last used column in your worksheets

    4. Delete all columns forward

    5. Save the file and close Excel

    6. Reopen the file

  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format
  • Save data files without formatting – formatting may account for a lot of storage space and if you are simply working with a dataset and don’t need formatting save your file in .xml format or as a .csv
  • Turn automatic calculations off – often even not so large Excel files cause Excel to freeze or crash. That may be because of an abundance of Excel formulas having to recalculate each time a change is made etc. Try to turn Calculation Options to Manual to get rid of this problem
  • PowerQuery Add-In – Excel has its limitations when working with large datasets. The Excel PowerQuery Add-In by Microsoft has been designed to handle Big Data and complex data queries to external databases or datasources. PowerQuery will allow you to work more efficiently with these data sets and will not limit the amount of records you can handle within a single Excel Worksheet

Common myths about XLSB

Do XLSB runs formulas faster than XLSX?

  • Not true

XLSB file are only loaded and unloaded faster (saved and closed) than XLSX files. Afterwards both formats run in RAM memory with similar performance on the same Excel engine. Hence, you won’t see your Excel formula’s running significantly faster. However, if you do save your file frequently you will definitely notice that the XLSB file format saves faster.

Do XLSB files crash more often?

  • Not true

The XLSB file format does not in any way increase the probability of crashing. However, if the file does crash it may be harder to recover. XLSB are binary files, where XLSX and XLSM files are in fact compressed XML files – text files in XML format. Therefore, in a critical situation you have definitely a better chance of reading a text file than a binary file. Then again I wouldn’t worry about this too much.

Conclusions

On a daily basis I would recommend sticking to XLSX and XSLM as standard Excel file formats. It is worth reaching out to the XLSB file format whenever you file starts running slow or uses an enormous amount of space.

XLSB will actually not benefit small Excel files and you might even see small XLSB files taking more space than small XLSX/XSLM files. Your clients / coworkers may also have doubts when opening XLSB files as Excel treats these file formats with an extra dose of caution.

Use the XLSB file format MAINLY with very LARGE Excel files.

lync spy

Lync Spy – monitor and get notified of anyones status changes

1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 4.36 out of 5)
Loading...

Lately I was having issues with getting in contact with some of my colleagues. The problem was that I could not contact them by phone and Lync (now Skype for Business) / Email were my only 2 options. Sending emails is not a quick way of contacting anyone and unfortunately you can’t send a Lync message to people who are offline so they get it as soon as they log in or are available. Also, I didn’t want to stalk my laptop all the time just waiting for an availability notification from someone I needed to connect to. Desperate and frustrated, I mashed up a simple app for tracking Lync statuses of any Lync contacts – a Lync Spy (aka Lync Tracker) application.

The Lync Sky works equally for Skype for Business – this is only a branding change on Microsoft’s end

What does the Lync Spy do?

Basically it connects with Lync to track any changes in status of any number of your Lync contacts. In detail it:

  • Tracks any changes in users Lync statuses
  • Sends email notification of any changes to a Lync status
  • Logs user status changes to a CSV file (in case you want to spy on changes in their availability)
Lync Spy / Lync Tracker
Lync Spy / Lync Tracker

How to use it to track Lync/Skype status changes?

Add users to track

Add any number of users to the watch list by providing their Lync / Skype for Business email and clicking the Add to list button.

Similarly you can remove users by selecting them and clicking Remove selected.

Set tracked statuses

Check the status you want to watch out for e.g. Available, Away etc. Whenever anyone from your watch list will change status to any of these statuses you will be notified and/or this will be logged

Set email notifications or CSV log

Check whether you want to receive an email notification in case anyone changes their status to the statuses which you selected.
Check whether you want to log all change status events to a CSV file

Turn it On!

All you need to do now is change the On / Off option to On.

That’s it! Just remember not to turn Lync on and to keep your Outlook Exchange application open if you want to send/receive notification of status changes!

Feel free to download the app here (requires .NET 4):


The app is licensed under a standard GNU Public Licence