Rectangle 27 1

With a very simple coding, I was able to read the files. Now, the only thing we need to do is to make this code a bit fancy to loop thorough all the CSV files in the folder given and collect data. Once we read the data, it can be filtered and put to an excel as we want. Of course, excel can import CSV itself, but it is not that practical to do this every time. And again we can add the code to application to use in flexibility, exactly what I am trying to do.

public static System.Data.DataTable GetDataTable(string strFileName)

{
        System.Data.OleDb.OleDbConnection dbConnect = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + ";Extended Properties = \"Text;HDR=YES;FMT=TabDelimited\"");
        dbConnect.Open();
        string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
        System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, dbConnect);
        System.Data.DataSet dSet = new System.Data.DataSet("CSV File");
        adapter.Fill(dSet);
        dbConnect.Close();
        return dSet.dbTables[0];
 }

c# - Import data from multiple CSV files to an Excel sheet - Stack Ove...

c# excel csv
Rectangle 27 2

Fastest way could be using FileHelpers to read CSV into a DataTable :

and then with EPPlus export that DataTable in excel, use method DataTableToExcelXlsx from this snippet:

With EPPlus you don't have to have Excel installed on machine that is executing this code, and you can use it on server (ASP.NET)

c# - Import data from multiple CSV files to an Excel sheet - Stack Ove...

c# excel csv
Rectangle 27 2

Microsoft Text Driver allows you to read CSV data in a DataSet, making data manipulation easy.

c# - Import data from multiple CSV files to an Excel sheet - Stack Ove...

c# excel csv
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 331

A simple workaround is to use Google Spreadsheet. Paste (values only if you have complex formulas) or import the sheet then download CSV. I just tried a few characters and it works rather well.

NOTE: Be careful of sensitive data with Google Sheets.

EDIT: Another alternative - basically they use VB macro or addins to force the save as UTF8. I have not tried any of these solutions but they sound reasonable.

I tried Notepad++ first and that worked only in some cases. This worked brilliantly.

With the normal Windows Notepad (using save as, and then choosing utf-8 in the encoding option) worked for me. For me that's the best approach for me since that must be done by users that have no admin rights in their machines, so installing additional software is not required.

Uhhh. I just had a shiver down my back. What if your excel file has 200,000 lines? Or contains sensitive data you do not want to include in an excel spreadsheat? Use Openoffice/Libreoffice if you need to.

Excel to CSV with UTF8 encoding - Stack Overflow

excel encoding csv utf-8
Rectangle 27 7

In his blog entry Speed Up Reading Excel Files in PowerShell, Robert M. Toups, Jr. explains that while loading to PowerShell is fast, actually reading the Excel cells is very slow. On the other hand, PowerShell can read a text file very quickly, so his solution is to load the spreadsheet in PowerShell, use Excels native CSV export process to save it as a CSV file, then use PowerShells standard Import-Csv cmdlet to process the data blazingly fast. He reports that this has given him up to a 20 times faster import process!

Leveraging Toups code, I created an Import-Excel function that lets you import spreadsheet data very easily. My code adds the capability to select a specific worksheet within an Excel workbook, rather than just using the default worksheet (i.e. the active sheet at the time you saved the file). If you omit the SheetName parameter, it uses the default worksheet.

function Import-Excel([string]$FilePath, [string]$SheetName = "")
{
    $csvFile = Join-Path $env:temp ("{0}.csv" -f (Get-Item -path $FilePath).BaseName)
    if (Test-Path -path $csvFile) { Remove-Item -path $csvFile }

    # convert Excel file to CSV file
    $xlCSVType = 6 # SEE: http://msdn.microsoft.com/en-us/library/bb241279.aspx
    $excelObject = New-Object -ComObject Excel.Application  
    $excelObject.Visible = $false 
    $workbookObject = $excelObject.Workbooks.Open($FilePath)
    SetActiveSheet $workbookObject $SheetName | Out-Null
    $workbookObject.SaveAs($csvFile,$xlCSVType) 
    $workbookObject.Saved = $true
    $workbookObject.Close()

     # cleanup 
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) |
        Out-Null
    $excelObject.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) |
        Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()

    # now import and return the data 
    Import-Csv -path $csvFile
}
function FindSheet([Object]$workbook, [string]$name)
{
    $sheetNumber = 0
    for ($i=1; $i -le $workbook.Sheets.Count; $i++) {
        if ($name -eq $workbook.Sheets.Item($i).Name) { $sheetNumber = $i; break }
    }
    return $sheetNumber
}

function SetActiveSheet([Object]$workbook, [string]$name)
{
    if (!$name) { return }
    $sheetNumber = FindSheet $workbook $name
    if ($sheetNumber -gt 0) { $workbook.Worksheets.Item($sheetNumber).Activate() }
    return ($sheetNumber -gt 0)
}

Does the Import-CSV give you the ability to select specific cell and column data?

Best practices for a command-line utility (going back to the Unix days) is that it should do one thing well. So Import-Csv just imports the whole thing. But then you just apply the power of PowerShell, typically Where-Object to select rows, Select-Object to select columns.

Hmm Ill check it out. Going to be tough because the excel data is not formatted consistently. Columnar data is not uniform the entire way day. I'll play with it and see. I might need to have powershell save the excel doc as a CSV as is because there isn't going to be any user interaction getting these documents.

This works superfast and great! Only issue I have is that it's having difficulties with special characters like '' and ''. I tried different types of CSV numbers, but none seem to work.

Awesome! The solution was simple. Instead of channging the value $xlCSVType = 6, I had to change this Import-Csv -path $csvFile -Encoding Default. Hope it helps anyone :)

Is there a faster way to parse an excel document with Powershell? - St...

excel powershell
Rectangle 27 1

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxtoCSV {

    static void xlsx(File inputFile, File outputFile) {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();

        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            FileInputStream fis = new FileInputStream(inputFile);
            Workbook workbook = null;

            String ext = FilenameUtils.getExtension(inputFile.toString());

            if (ext.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (ext.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fis);
            }

            // Get first sheet from the workbook

            int numberOfSheets = workbook.getNumberOfSheets();
            Row row;
            Cell cell;
            // Iterate through each rows from first sheet

            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    // For each row, iterate through each columns
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {

                        cell = cellIterator.next();

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_STRING:
                            data.append(cell.getStringCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            data.append("" + ",");
                            break;
                        default:
                            data.append(cell + ",");

                        }
                    }
                    data.append('\n'); // appending new line after each row
                }

            }
            fos.write(data.toString().getBytes());
            fos.close();

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }

    // testing the application

    public static void main(String[] args) {
        // int i=0;
        // reading file from desktop
        File inputFile = new File(".//src//test//resources//yourExcel.xls"); //provide your path
        // writing excel data to csv
        File outputFile = new File(".//src//test//resources//yourCSV.csv");  //provide your path
        xlsx(inputFile, outputFile);
        System.out.println("Conversion of " + inputFile + " to flat file: "
                + outputFile + " is completed");
    }
}

This faces exactly the same problem that the OP's code does, which is that it ignores blank/missing cells, you need to use @Reji's code to properly handle them

excel - java converting .xls to csv - Stack Overflow

java excel csv apache-poi
Rectangle 27 2

Downvote: Importing XLS files with SSIS is terrible. SSIS will try to guess at the datatypes of the Excel data, but can guess wrong and there's nothing you can do about it. Much better to use CSV.

Well, I'd suggest csv too, but if you had read the OP's scenario, he had some special scenarios especially with delimiters which are not an issue with xls sheets. Usually special case scenarios like these do not require an extensive solution, but a fix that preserves the data. While uploading the file, SSIS lets you choose the data mapping between source and destination tables which again, eases the effort involved. Which is why this method was suggested as a quick hack.

SSIS can already handle CSV text delimiters. If you're using SSIS anyway, going to the trouble of saving your CSV as an XLS first just strikes me as adding potential breakage for no reason.

Import CSV file into SQL Server - Stack Overflow

sql sql-server csv bulkinsert bulk
Rectangle 27 3

If I try to save data in xls/xlsx, then I get multiple sheets in a workbook.

Your answer is in your question, don't use text/csv (which most certainly can not do multiple sheets, it can't even do one sheet; there's no such thing as a sheet in text/csv though there is in how some applications like Excel or Calc choose to import it into a format that does have sheets) but save it as xls, xlsx, ods or another format that does have sheets.

Both XLSX and ODS are much more complicated than text/csv, but are each probably the most straightforward of their respective sets of formats.

The data which I am getting is in CSV and I am told to keep it in csv only. Because the server might not support Excel and Office.Interop where I want to retrieve certain values.

There is no library in c#/Asp.net which can make multiple sheets ?

You wouldn't want to use Excel on the server anyway -- it's explicitly not designed for heavy multithreaded, non-UI use. There are plenty of libraries out there that will generate Excel files in a server environment (in pure .NET code, no external dependencies); there are plenty of good paid ones, and probably some free ones.

You can't have multiple sheets in CSV, because CSV doesn't have sheets.

Ask your supervisor to point to the bit in ietf.org/rfc/rfc4180.txt that shows how sheets are specified. They won't be able to, because it's not there. It's like asking how to turn the sound up on a CSV. Meanwhile, XLSX files are internally zip files of a few related XML and text files. They aren't exactly easy to build programmatically, but they aren't terribly hard either.

c# - Is csv with multi tabs/sheet possible? - Stack Overflow

c# asp.net excel csv
Rectangle 27 1

import csv, xlwt

with open('file.csv', 'rb') as csvfile:
    data = csv.reader ((csvfile), delimiter=",")
    wbk= xlwt.Workbook()
    sheet = wbk.add_sheet("file")


    for rowi, row in enumerate(data):
        for coli, value in enumerate(row):
            sheet.write(rowi,coli,value)

    wbk.save("workbook_file")

Even though print(rowi,coli,value) shows 'value', the cell in the outputted file should show it without quotes.

1, 2, 3
1,2,3
for coli, value in enumerate(row):
value = value.lstrip(" ")

Sorry for the misleading example, but the values are a mix of types including text, dates, floats, and ints. I just need to write them as they are, not wrap them with '' so they're interpreted as strings

I only want to write the value, as is, into the sheet. I say I want to read the csv as (0,0,1) instead of (0,0,'1') for example. But I want to then use this to write into the sheet at cell(0,0) the value 1, not the text '1'

@user3757554 Sorry about that, I skimmed the post and didn't realize you were outputting to excel. See edit.

excel - Read CSV without string formatting in python - Stack Overflow

python excel csv
Rectangle 27 26

xlrd/xlwt (standard): Python does not have this functionality in it's standard library, but I think of xlrd/xlwt as the "standard" way to read and write excel files. It is fairly easy to make a workbook, add sheets, write data/formulas, and format cells. If you need all of these things, you may have the most success with this library. I think you could choose openpyxl instead and it would be quite similar, but I have not used it.

To format cells with xlwt, define a XFStyle and include the style when you write to a sheet. Here is an example with many number formats. See example code below.

Tablib (powerful, intuitive): Tablib is a more powerful yet intuitive library for working with tabular data. It can write excel workbooks with multiple sheets as well as other formats, such as csv, json, and yaml. If you don't need formatted cells (like background color), you will do yourself a favor to use this library, which will get you farther in the long run.

csv (easy): Files on your computer are either text or binary. Text files are just characters, including special ones like newlines and tabs, and can be easily opened anywhere (e.g. notepad, your web browser, or Office products). A csv file is a text file that is formatted in a certain way: each line is a list of values, separated by commas. Python programs can easily read and write text, so a csv file is the easiest and fastest way to export data from your python program into excel (or another python program).

Excel files are binary and require special libraries that know the file format, which is why you need an additional library for python, or a special program like Microsoft Excel, Gnumeric, or LibreOffice, to read/write them.

import xlwt

style = xlwt.XFStyle()
style.num_format_str = '0.00E+00'

...

for i,n in enumerate(list1):
    sheet1.write(i, 0, n, fmt)

You probably want to use the the csv module included in the standard library if you're going to write a CSV file.

Not that it particularly matters, but Tablib, like so many other higher-level packages, uses xlwt to output Excel files.

Python - Write to Excel Spreadsheet - Stack Overflow

python excel csv spreadsheet
Rectangle 27 26

xlrd/xlwt (standard): Python does not have this functionality in it's standard library, but I think of xlrd/xlwt as the "standard" way to read and write excel files. It is fairly easy to make a workbook, add sheets, write data/formulas, and format cells. If you need all of these things, you may have the most success with this library. I think you could choose openpyxl instead and it would be quite similar, but I have not used it.

To format cells with xlwt, define a XFStyle and include the style when you write to a sheet. Here is an example with many number formats. See example code below.

Tablib (powerful, intuitive): Tablib is a more powerful yet intuitive library for working with tabular data. It can write excel workbooks with multiple sheets as well as other formats, such as csv, json, and yaml. If you don't need formatted cells (like background color), you will do yourself a favor to use this library, which will get you farther in the long run.

csv (easy): Files on your computer are either text or binary. Text files are just characters, including special ones like newlines and tabs, and can be easily opened anywhere (e.g. notepad, your web browser, or Office products). A csv file is a text file that is formatted in a certain way: each line is a list of values, separated by commas. Python programs can easily read and write text, so a csv file is the easiest and fastest way to export data from your python program into excel (or another python program).

Excel files are binary and require special libraries that know the file format, which is why you need an additional library for python, or a special program like Microsoft Excel, Gnumeric, or LibreOffice, to read/write them.

import xlwt

style = xlwt.XFStyle()
style.num_format_str = '0.00E+00'

...

for i,n in enumerate(list1):
    sheet1.write(i, 0, n, fmt)

You probably want to use the the csv module included in the standard library if you're going to write a CSV file.

Not that it particularly matters, but Tablib, like so many other higher-level packages, uses xlwt to output Excel files.

Python - Write to Excel Spreadsheet - Stack Overflow

python excel csv spreadsheet
Rectangle 27 8

As others have suggested, csv reading is faster. So if you are on windows and have Excel, you could call a vbscript to convert the Excel to csv and then read the csv. I tried the script below and it took about 30 seconds.

# create a list with sheet numbers you want to process
sheets = map(str,range(1,6))

# convert each sheet to csv and then read it using read_csv
df={}
from subprocess import call
excel='C:\\Users\\rsignell\\OTT_Data_All_stations.xlsx'
for sheet in sheets:
    csv = 'C:\\Users\\rsignell\\test' + sheet + '.csv' 
    call(['cscript.exe', 'C:\\Users\\rsignell\\ExcelToCsv.vbs', excel, csv, sheet])
    df[sheet]=pd.read_csv(csv)

Here's a little snippet of python to create the ExcelToCsv.vbs script:

#write vbscript to file
vbscript="""if WScript.Arguments.Count < 3 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file> <worksheet number (starts at 1)>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
worksheet_number = CInt(WScript.Arguments.Item(2))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.Worksheets(worksheet_number).Activate

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
""";

f = open('ExcelToCsv.vbs','w')
f.write(vbscript.encode('utf-8'))
f.close()

python - Faster way to read Excel files to pandas dataframe - Stack Ov...

python pandas import-from-excel
Rectangle 27 8

As others have suggested, csv reading is faster. So if you are on windows and have Excel, you could call a vbscript to convert the Excel to csv and then read the csv. I tried the script below and it took about 30 seconds.

# create a list with sheet numbers you want to process
sheets = map(str,range(1,6))

# convert each sheet to csv and then read it using read_csv
df={}
from subprocess import call
excel='C:\\Users\\rsignell\\OTT_Data_All_stations.xlsx'
for sheet in sheets:
    csv = 'C:\\Users\\rsignell\\test' + sheet + '.csv' 
    call(['cscript.exe', 'C:\\Users\\rsignell\\ExcelToCsv.vbs', excel, csv, sheet])
    df[sheet]=pd.read_csv(csv)

Here's a little snippet of python to create the ExcelToCsv.vbs script:

#write vbscript to file
vbscript="""if WScript.Arguments.Count < 3 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file> <worksheet number (starts at 1)>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
worksheet_number = CInt(WScript.Arguments.Item(2))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.Worksheets(worksheet_number).Activate

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
""";

f = open('ExcelToCsv.vbs','w')
f.write(vbscript.encode('utf-8'))
f.close()

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

python - Faster way to read Excel files to pandas dataframe - Stack Ov...

python pandas import-from-excel
Rectangle 27 0

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxtoCSV {

    static void xlsx(File inputFile, File outputFile) {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();

        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            FileInputStream fis = new FileInputStream(inputFile);
            Workbook workbook = null;

            String ext = FilenameUtils.getExtension(inputFile.toString());

            if (ext.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (ext.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fis);
            }

            // Get first sheet from the workbook

            int numberOfSheets = workbook.getNumberOfSheets();
            Row row;
            Cell cell;
            // Iterate through each rows from first sheet

            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    // For each row, iterate through each columns
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {

                        cell = cellIterator.next();

                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_STRING:
                            data.append(cell.getStringCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            data.append("" + ",");
                            break;
                        default:
                            data.append(cell + ",");

                        }
                    }
                    data.append('\n'); // appending new line after each row
                }

            }
            fos.write(data.toString().getBytes());
            fos.close();

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }

    // testing the application

    public static void main(String[] args) {
        // int i=0;
        // reading file from desktop
        File inputFile = new File(".//src//test//resources//yourExcel.xls"); //provide your path
        // writing excel data to csv
        File outputFile = new File(".//src//test//resources//yourCSV.csv");  //provide your path
        xlsx(inputFile, outputFile);
        System.out.println("Conversion of " + inputFile + " to flat file: "
                + outputFile + " is completed");
    }
}

This faces exactly the same problem that the OP's code does, which is that it ignores blank/missing cells, you need to use @Reji's code to properly handle them

excel - java converting .xls to csv - Stack Overflow

java excel csv apache-poi
Rectangle 27 0

In order to to import this file into Excel, you would need to break it up and place the data on multiple sheets. This is not possible the straight import method you been using. The best you can do would be to read the CSV file with ADO into a Recordset object and then output the Recordset on to the individual sheets while specifying the number of records to be output.

Overall, this will be a fairly slow process. Why are you trying to display this in Excel? Something like Access maybe a better place to store the data (or even keep it in a CSV) and then connect to it from Excel for pivot tables and/or other analysis.

I need to make summary reports in excel of the imported text files, which include 1) name of columns, 2) their type (Numeric or Character), 3) Min & Max length, 4) Min & Max values (Numeric or Character) and 5)#Missing.

Would importing it into Access work for you? Once the data is in a table, you could either do what you need to directly in Access, or can connect to it from Excel.

Importing big text/csv file into excel using vba - Stack Overflow

vba excel-vba csv import