File2XL : Open a csv file into MS-Excel with pre-formatted cells

Documentation : File2XL.html

http://patrice.dargenton.free.fr/CodesSources/File2XL.html

http://patrice.dargenton.free.fr/CodesSources/File2XL.vbproj.html

https://github.com/PatriceDargenton/File2XL

www.codeproject.com

By Patrice Dargenton : patrice.dargenton@free.fr

http://patrice.dargenton.free.fr/index.html

http://patrice.dargenton.free.fr/CodesSources/index.html

Version 1.07 - 01/05/2023

 

File2XL lets you open any text file into MS-Excel and creates two sheets : one in text format, and a second one in standard format for numeric columns. File2XL adds a context menu titled : "Open in MS-Excel using File2XL" for any file in Windows File Explorer. Partially created workbook can be viewed without having to wait for the opening of the entire file.

 

Keywords

Excel Text Import Wizard, Csv2Excel, Csv to Excel, Txt2Excel, Txt to Excel, Text2Excel, Text to Excel.

 

 

Table of contents

 

Keywords. 1

Table of contents. 1

Features. 1

Explanations. 2

Context menu. 2

Multiple delimiter 2

Settings. 2

Projects. 2

Versions. 2

Version 1.07 - 01/05/2023. 2

Version 1.06 - 22/10/2021. 3

Version 1.05 - 25/01/2019. 3

Version 1.04 - 05/01/2018. 3

Version 1.03 - 20/05/2017. 3

Version 1.02 - 08/05/2017. 3

Version 1.01 - 25/06/2016 : First version. 3

Links. 3

See also. 3

 

 

Features

 

- Two sheets are created : one in text format, and one in standard format for numeric columns, because sometimes you need to see the original text before it was converted to numeric (for conversion problem investigation) ;

- Partially workbook viewing : check big file quickly before waiting the complete workbook to be created (pause/continue/cancel/show buttons are available) ;

- Excel limitations are checked : 256 columns and 65536 lines for Excel 2003, and 16384 columns and 1048576 lines for Excel 2007 (or >), and 32767 characters max. in one cell for both versions ; colored and text alerts are displayed within the sheet if these limits are exceeded, and you are only prompted once by this kind of limit exceed ;

- Source file encoding is detected (UTF7, UTF8, Unicode, BigEndianUnicode, UTF32 and ASCII) ;

- Temporary Excel file is removed after closing Excel, if you agree to delete it ;

- Delimiter detection : a few delimiters are counted (at the top of the file) : ,;| and tabulation ; possible delimiters are configurable ;

- Special delimiter : "," or ";" is supported (not configurable) ;

- Minus sign at the end of the value is supported, e.g.: 0.72- -> -0.72 ;

- Using Excel 2003 (or 2000/2002) and/or Excel 2007 (or >) is configurable ;

Autofilter on the header, the first line, is yet enabled (not configurable) ;

- Frozen column is configurable (1 column left is always visible by default, but 0 is possible too) ;

Autosizing columns is configurable ;

- The number of header lines analyzed is configurable ;

- The standard sheet can be disabled (only text sheet is then created) ;

- Removing NULL value in standard sheet is configurable (for example PhpMyAdmin NULL value in csv export).

 

 

Explanations

 

Context menu

The first time, run File2XL in administrator privilege (run as admin.), and add (or remove) context menu using the + (or - respectively) button ;

After that, use the context menu "Open in MS-Excel using File2XL" for any text file in Windows File Explorer.

 

Multiple delimiter

There are only two multiple delimiters (not configurable) : "," and ";"

Only a quick parsing is performed (splitting with "," or ";"), not a deep parsing. If a deep parsing is required (like the slow one that Excel use in his Text Import Wizard), there is a second context menu to choose for example comma (,) instead of "," : "Open in MS-Excel using File2XL (single delimiter)", otherwise the default context menu gives chance to choose the multiple delimiter ",".

Example of a file that is generated with multiple delimiter : phpmyadmin csv export (null value doesn't have "", so you should use the second context menu for it : single delimiter, if you have nullable fields).

 

Settings

There is no user interface to configure settings, simply edit the config. file in the notepad : File2XL.exe.config

 

 

Projects

 

- Numeric field : count how many decimal digits of precision are required (actually, no decimal is shown by default, but you can change it afterward in Excel as you want) ;

- Date field : show date (and time) fields in formatted and colored cells in the standard sheet ;

- Event handler for the writing of the Excel workbook (which may be cancelled) for large files : suggestion have been submitted to NPOI team but not yet implemented (possible way to do it : counting every row or every line of each sheet to be written).

 

 

Versions

 

Version 1.07 - 01/05/2023

- Log probable delimiter detection results in the log file ;

- Option detectEncodingFromByteOrderMarks used in StreamReader (don't try to detect the encoding on your own, instead use the standard .net option for this complex task) ;

- Test text-encoding-detect from https://github.com/AutoItConsulting/text-encoding-detect ;

- Indent code for Visual Studio 2022 ;

- .Net45 -> .Net472.

 

Version 1.06 - 22/10/2021

- Detecting UTF8 Encoding : one case added.

 

Version 1.05 - 25/01/2019

- Detecting UTF8 Encoding : one case added.

 

Version 1.04 - 05/01/2018

- Encoding reading : no need to read with write access right, just read access.

 

Version 1.03 - 20/05/2017

LogFile setting added : to log conversion time of each file ;

- Visual Studio 2017 code analysis : almost all rules are respected ;

- UTF8 encoding added in encoding detection ;

MinColumnWidth and MaxColumnWidth settings added ;

- Bug fixed (from 1.02 version) : object variable not set : fs.position while fs is null.

 

Version 1.02 - 08/05/2017

RemoveNULL setting : remove NULL in field value in standard sheet, for example PhpMyAdmin NULL value in csv export ;

SingleDelimiter : disable multiple delimiter (not simply prefer single one).

 

Version 1.01 - 25/06/2016 : First version

 

 

Links

 

- Library used : https://github.com/tonyqus/npoi (2.2.1.0 version, may 2016)

  NPOI : a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.

  Only one add (2.2.1.1) : GetColumnWidth : iNumRow++; if (iNumRow > iNbRowMax) break;

  const int iNbRowMax = 100;

  in order to perform a fast column autosize based on only the top 100 lines (suggestion have been submitted to NPOI team but not yet implemented in the github repository).

 

Neuzilla User Group (Tony Qu from Neuzilla is the main NPOI contributor) :

  https://www.linkedin.com/groups/6655065

 

 

See also

 

- (french) XL2Csv : Convertir un fichier Excel en fichiers Csv (ou en 1 fichier txt) (translate)

            Source code  : XL2Csv.vbproj.html