converting .csv file to .xls file using C# code

Hi,
I would suggest you to load the csv file to datatable by reading it as a database for that you can use this link http://www.codeproject.com/Articles/11435/Importing-CSV-Data-and-saving-it-in-database
In it there is and option to load csv in datatable (without any loop, always try minimizing the loops coz you never know what size of file client will be using) and then it saves it to datatable . Now replace the code of saving it to database and call the source file attached here to do the rest of the thing (Don't remember from where I got this source code but yes its always been a part of my code )
copy and paste this code after importing the source file to your project
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();

obj.WriteDataTableToExcel(DTExcel, "Excel Report", SaveDialog.FileName + ".xlsx", "Details");

Regards

c# code

        string strIPAddress     = "";
        string strLastBootDate = "";
        string strReportDate    = "";
        string strComputerName = "";
        string strUsername      = "";
        string strDisplayName   = "";
        string strTelephone     = "";
        string strEmail         = "";
        string strOS            = "";
        string strOSVersion     = "";
        string strOSServicePack = "";
        string strCPU           = "";
        string strCPUSpeed      = "";
        string strRAM           = "";
        string strGFX           = "";
        string[] files;

        private void PrepareDataForExcel(string[] sfiles)
        {
            clearDataFields();
            string output = "";
            string username = "";

            progressBar1.Value = 0;
            progressBar1.Minimum = 0;
            progressBar1.Step = 1;

            if (lblAmount.Text != "None")
            {
                progressBar1.Maximum = sfiles.Length;
                string[] strArray;

                txtExcel.Clear();
                txtExcel.Text = "Computer Name" + "\t" + "Username" + "\t" + "Display Name" + "\t" + "Telephone Number" + "\t" + "Email Address" + "\t" + "Operating System" + "\t" + "OS Version" + "\t" + "OS Service Pack" + "\t" + "CPU" + "\t" + "CPU Speed" + "\t" + "Memory" + "\t" + "IP Address" + "\t" + "Graphics Card" + "\t" + "Report Date" + "\t" + "Last Boot Date";
                txtExcel.Text += "\r\n";

                char[] split = new Char[] { ',' };
                ArrayList al = new ArrayList();
                al.Clear();

                foreach (string filename in sfiles)
                {
                    al.Clear();
                    clearDataFields();
                    try
                    {
                        using (StreamReader sr = new StreamReader(filename))
                        {
                            while (sr.Peek() >= 0)
                            {
                                al.Add(sr.ReadLine());
                            }
                            foreach (string sentence in al)
                            {
                                strArray = sentence.Split(split);
                                if (strArray.Length > 1)
                                {
                                    switch (strArray[1])
                                    {
                                        case "Computer Name":
                                            strComputerName = strArray[2];
                                            break;

                                        case "Username":
                                            strUsername = strArray[2];
                                            if (cbWSDisplayName.Checked)
                                            {
                                                username = getUserName(strUsername);
                                                strDisplayName = adws.ADUserInfoDisplayName(username);
                                                strTelephone = adws.ADUserInfoTelephoneNumber(username); ;
                                                strEmail = adws.ADUserInfoEmailAddress(username);
                                            }
                                            else
                                            {
                                                strDisplayName = "";
                                                strTelephone = "";
                                                strEmail     = "";
                                            }

                                            break;

                                        case "Operating System":
                                            strOS = strArray[2];
                                            break;

                                        case "Version":
                                            strOSVersion = strArray[2];
                                            break;

                                        case "Speed":
                                            strCPUSpeed = strArray[2];
                                            break;

                                        case "Service Pack":
                                            strOSServicePack = strArray[2];
                                            break;

                                        case "CPU Name":
                                            strCPU = RemoveDuplicateWhiteSpace(strArray[2]);
                                            break;

                                        case "Physical Memory":
                                            strRAM = strArray[2];
                                            break;

                                        case "Intel Corporation":
                                            strGFX = strArray[4];
                                            break;

                                        case "" :
                                            strGFX = strArray[4];
                                            break;

                                        case "NVIDIA":
                                            strGFX = strArray[4];
                                            break;

                                        case "ATI Technologies Inc.":
                                            strGFX = strArray[4];
                                            break;

                                        case "(Standard display types)":
                                            strGFX = strArray[4];
                                            break;

                                        case "Report Date":
                                            strReportDate = strArray[2];
                                            break;

                                        case "Last Boot Date":
                                            strLastBootDate = strArray[2];
                                            break;

                                        case "IP Address":
                                            if (strArray[2] == "0.0.0.0")
                                            {
                                                if (strArray[3] == "0.0.0.0")
                                                {
                                                    if (strArray[4] == "0.0.0.0")
                                                    {
                                                    }
                                                    else strIPAddress = strArray[4];
                                                }
                                                else strIPAddress = strArray[3];
                                            }
                                            else strIPAddress = strArray[2];

                                            break;

                                        default: break;
                                    }
                                }
                            }
                            txtExcel.Text += strComputerName + "\t" + strUsername + "\t" + strDisplayName + "\t" + strTelephone + "\t" + strEmail + "\t" + strOS + "\t" + strOSVersion + "\t" + strOSServicePack + "\t" + strCPU + "\t" + strCPUSpeed + "\t" + strRAM + "\t" + strIPAddress + "\t" + strGFX + "\t" + strReportDate + "\t" + strLastBootDate;
                        }
                        progressBar1.Increment(1);
                        txtExcel.Text += "\r\n";
                    }
                    catch (IndexOutOfRangeException ioorex)
                    {
                        stslblErrors.Text = ioorex.Message.ToString() + output;
                    }
                    catch (Exception ex)
                    {
                        stslblErrors.Text = ex.Message.ToString() + output;
                    }
                }
            }
        }
        public string RemoveDuplicateWhiteSpace(string input)
        {
            return Regex.Replace(input, @"[\s]+", " ", RegexOptions.Singleline | RegexOptions.IgnoreCase);
        }

csv file:
[Computer Info]
,Computer Name,PCN16290
,Username,Domain\Username
,Report Date,2008/12/17 11:10:58 AM
,Last Boot Date,2008/12/17 07:16:49 AM
[Operating System]
,Operating System,Microsoft Windows XP Professional
,Version,5.1.2600
,Service Pack, 2.0
,IP Address,10.16.25.14,0.0.0.0,
[CPU]
,CPU Name,Intel(R) Core(TM)2 CPU         T7200 @ 2.00GHz
,Speed,1995
,CPU Name,Intel(R) Core(TM)2 CPU         T7200 @ 2.00GHz
,Speed,1994
[Memory]
,Physical Memory,2048
[Graphics]
,Bits Per Pixel,Refresh Rate,Video Mode
,32,60,1920 by 1200 pixels, True Color, 60 Hertz
,Adapter Compatibility,Adapter DAC Type,Adapter RAM,Description,Driver Date,Driver Version,Name,Video Processor
,Microsoft,Internal DAC(400MHz),524288mb,ConfigMgr Remote Control Driver,20070626020000.000000-000,4.00.5824.0000,ConfigMgr Remote Control Driver,ATI MOBILITY FireGL V5200 (0x71C4)
,ATI Technologies Inc.,Internal DAC(400MHz),524288mb,ATI MOBILITY FireGL V5200   ,20080103113252.000000-000,6.14.10.6755,ATI MOBILITY FireGL V5200   ,ATI MOBILITY FireGL V5200 (0x71C4)

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shailendra Kumar12y ago
Hi,
I would suggest you to load the csv file to datatable by reading it as a database for that you can use this link http://www.codeproject.com/Articles/11435/Importing-CSV-Data-and-saving-it-in-database
In it there is and option to load csv in datatable (without any loop, always try minimizing the loops coz you never know what size of file client will be using) and then it saves it to datatable . Now replace the code of saving it to database and call the source file attached here to do the rest of the thing (Don't remember from where I got this source code but yes its always been a part of my code )
copy and paste this code after importing the source file to your project
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();

obj.WriteDataTableToExcel(DTExcel, "Excel Report", SaveDialog.FileName + ".xlsx", "Details");

Regards
Constance Swinton12y ago
Hi, replacing the separator with a tab character (\t) worked for me, but unfortunately this results in throwing a nasty warning because Excel detects that file extension is different than the file's type.
If you want to convert the CSV file into a real XLS file (BIFF format) with C# then try this simple snipp code:

var file = ExcelFile.Load("Your file.csv", LoadOptions.CsvDefault);
file.Save("Your file.xls", SaveOptions.XlsDefault);

The code uses this C# dll for processing excel files.
13y ago
You can convert your csv file to xls formats by using this excel converter that uses .Net or C# language to convert your file to desired formats and not only that it also allows you to create , read and convert your excel file to many other formats.
Riaan de Lange17y ago
Output:

Computer Name Username Display Name Telephone Number Email Address Operating System OS Version OS Service Pack CPU CPU Speed Memory IP Address Graphics Card Report Date Last Boot Date
PCN16290 Domain\Username Microsoft Windows XP Professional 5.1.2600 2.0 Intel(R) Core(TM)2 CPU T7200 @ 2.00GHz 1994 2048 10.16.25.14 ATI MOBILITY FireGL V5200 2008/12/17 11:10:58 AM 2008/12/17 07:16:49 AM
Riaan de Lange17y ago
c# code

        string strIPAddress     = "";
        string strLastBootDate = "";
        string strReportDate    = "";
        string strComputerName = "";
        string strUsername      = "";
        string strDisplayName   = "";
        string strTelephone     = "";
        string strEmail         = "";
        string strOS            = "";
        string strOSVersion     = "";
        string strOSServicePack = "";
        string strCPU           = "";
        string strCPUSpeed      = "";
        string strRAM           = "";
        string strGFX           = "";
        string[] files;

        private void PrepareDataForExcel(string[] sfiles)
        {
            clearDataFields();
            string output = "";
            string username = "";

            progressBar1.Value = 0;
            progressBar1.Minimum = 0;
            progressBar1.Step = 1;

            if (lblAmount.Text != "None")
            {
                progressBar1.Maximum = sfiles.Length;
                string[] strArray;

                txtExcel.Clear();
                txtExcel.Text = "Computer Name" + "\t" + "Username" + "\t" + "Display Name" + "\t" + "Telephone Number" + "\t" + "Email Address" + "\t" + "Operating System" + "\t" + "OS Version" + "\t" + "OS Service Pack" + "\t" + "CPU" + "\t" + "CPU Speed" + "\t" + "Memory" + "\t" + "IP Address" + "\t" + "Graphics Card" + "\t" + "Report Date" + "\t" + "Last Boot Date";
                txtExcel.Text += "\r\n";

                char[] split = new Char[] { ',' };
                ArrayList al = new ArrayList();
                al.Clear();

                foreach (string filename in sfiles)
                {
                    al.Clear();
                    clearDataFields();
                    try
                    {
                        using (StreamReader sr = new StreamReader(filename))
                        {
                            while (sr.Peek() >= 0)
                            {
                                al.Add(sr.ReadLine());
                            }
                            foreach (string sentence in al)
                            {
                                strArray = sentence.Split(split);
                                if (strArray.Length > 1)
                                {
                                    switch (strArray[1])
                                    {
                                        case "Computer Name":
                                            strComputerName = strArray[2];
                                            break;

                                        case "Username":
                                            strUsername = strArray[2];
                                            if (cbWSDisplayName.Checked)
                                            {
                                                username = getUserName(strUsername);
                                                strDisplayName = adws.ADUserInfoDisplayName(username);
                                                strTelephone = adws.ADUserInfoTelephoneNumber(username); ;
                                                strEmail = adws.ADUserInfoEmailAddress(username);
                                            }
                                            else
                                            {
                                                strDisplayName = "";
                                                strTelephone = "";
                                                strEmail     = "";
                                            }

                                            break;

                                        case "Operating System":
                                            strOS = strArray[2];
                                            break;

                                        case "Version":
                                            strOSVersion = strArray[2];
                                            break;

                                        case "Speed":
                                            strCPUSpeed = strArray[2];
                                            break;

                                        case "Service Pack":
                                            strOSServicePack = strArray[2];
                                            break;

                                        case "CPU Name":
                                            strCPU = RemoveDuplicateWhiteSpace(strArray[2]);
                                            break;

                                        case "Physical Memory":
                                            strRAM = strArray[2];
                                            break;

                                        case "Intel Corporation":
                                            strGFX = strArray[4];
                                            break;

                                        case "" :
                                            strGFX = strArray[4];
                                            break;

                                        case "NVIDIA":
                                            strGFX = strArray[4];
                                            break;

                                        case "ATI Technologies Inc.":
                                            strGFX = strArray[4];
                                            break;

                                        case "(Standard display types)":
                                            strGFX = strArray[4];
                                            break;

                                        case "Report Date":
                                            strReportDate = strArray[2];
                                            break;

                                        case "Last Boot Date":
                                            strLastBootDate = strArray[2];
                                            break;

                                        case "IP Address":
                                            if (strArray[2] == "0.0.0.0")
                                            {
                                                if (strArray[3] == "0.0.0.0")
                                                {
                                                    if (strArray[4] == "0.0.0.0")
                                                    {
                                                    }
                                                    else strIPAddress = strArray[4];
                                                }
                                                else strIPAddress = strArray[3];
                                            }
                                            else strIPAddress = strArray[2];

                                            break;

                                        default: break;
                                    }
                                }
                            }
                            txtExcel.Text += strComputerName + "\t" + strUsername + "\t" + strDisplayName + "\t" + strTelephone + "\t" + strEmail + "\t" + strOS + "\t" + strOSVersion + "\t" + strOSServicePack + "\t" + strCPU + "\t" + strCPUSpeed + "\t" + strRAM + "\t" + strIPAddress + "\t" + strGFX + "\t" + strReportDate + "\t" + strLastBootDate;
                        }
                        progressBar1.Increment(1);
                        txtExcel.Text += "\r\n";
                    }
                    catch (IndexOutOfRangeException ioorex)
                    {
                        stslblErrors.Text = ioorex.Message.ToString() + output;
                    }
                    catch (Exception ex)
                    {
                        stslblErrors.Text = ex.Message.ToString() + output;
                    }
                }
            }
        }
        public string RemoveDuplicateWhiteSpace(string input)
        {
            return Regex.Replace(input, @"[\s]+", " ", RegexOptions.Singleline | RegexOptions.IgnoreCase);
        }

csv file:
[Computer Info]
,Computer Name,PCN16290
,Username,Domain\Username
,Report Date,2008/12/17 11:10:58 AM
,Last Boot Date,2008/12/17 07:16:49 AM
[Operating System]
,Operating System,Microsoft Windows XP Professional
,Version,5.1.2600
,Service Pack, 2.0
,IP Address,10.16.25.14,0.0.0.0,
[CPU]
,CPU Name,Intel(R) Core(TM)2 CPU         T7200 @ 2.00GHz
,Speed,1995
,CPU Name,Intel(R) Core(TM)2 CPU         T7200 @ 2.00GHz
,Speed,1994
[Memory]
,Physical Memory,2048
[Graphics]
,Bits Per Pixel,Refresh Rate,Video Mode
,32,60,1920 by 1200 pixels, True Color, 60 Hertz
,Adapter Compatibility,Adapter DAC Type,Adapter RAM,Description,Driver Date,Driver Version,Name,Video Processor
,Microsoft,Internal DAC(400MHz),524288mb,ConfigMgr Remote Control Driver,20070626020000.000000-000,4.00.5824.0000,ConfigMgr Remote Control Driver,ATI MOBILITY FireGL V5200 (0x71C4)
,ATI Technologies Inc.,Internal DAC(400MHz),524288mb,ATI MOBILITY FireGL V5200   ,20080103113252.000000-000,6.14.10.6755,ATI MOBILITY FireGL V5200   ,ATI MOBILITY FireGL V5200 (0x71C4)
Riaan de Lange17y ago
Split the comma data into variables
then build new line of text, and add + "\t" after each variable
when u copy and paste it into Excel, it will paste into columns, and not into one column - this is what i did with my csv2xls app

second fase is to write the output directly to excel file

for now, copy and paste - automate it in c# form with a click of a button

will post my code in a few hours - at work
Yogita17y ago
Hello,

I am trying to do the same thing .Could you please paste the code..

Thanks,

Yogita
Alan18y ago
Try the code in this thread instead:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=245749&SiteID=1