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

Question

hi

i am trying to convert a .csv file to .xls file.

and i am able to do it . but the problem is when it changes to .xls file the comma seperated values are displaying in a single cell like

vijay,50,60,hyd

suri,68,93,bng

can any body please tell how to split it by comma and i want the data in a different cells

Shailendra Kumar · Answer

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 Swinton · Answer

Hi replacing the separator with a tab character (	) 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 .

Jumaila Shahzad · Answer

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 Lange · Answer

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 Lange · Answer

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 + 	 + 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; txtExcel.Text += 
; 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 + 	 + strUsername + 	 + strDisplayName + 	 + strTelephone + 	 + strEmail + 	 + strOS + 	 + strOSVersion + 	 + strOSServicePack + 	 + strCPU + 	 + strCPUSpeed + 	 + strRAM + 	 + strIPAddress + 	 + strGFX + 	 + strReportDate + 	 + strLastBootDate; } progressBar1.Increment(1); txtExcel.Text += 
; } 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 NamePCN16290 UsernameDomain\Username Report Date2008/12/17 11:10:58 AM Last Boot Date2008/12/17 07:16:49 AM [Operating System] Operating SystemMicrosoft Windows XP Professional Version5.1.2600 Service Pack 2.0 IP Address10.16.25.140.0.0.0 [CPU] CPU NameIntel(R) Core(TM)2 CPU T7200 @ 2.00GHz Speed1995 CPU NameIntel(R) Core(TM)2 CPU T7200 @ 2.00GHz Speed1994 [Memory] Physical Memory2048 [Graphics] Bits Per PixelRefresh RateVideo Mode 32601920 by 1200 pixels True Color 60 Hertz Adapter CompatibilityAdapter DAC TypeAdapter RAMDescriptionDriver DateDriver VersionNameVideo Processor MicrosoftInternal DAC(400MHz)524288mbConfigMgr Remote Control Driver20070626020000.000000-0004.00.5824.0000ConfigMgr Remote Control DriverATI MOBILITY FireGL V5200 (0x71C4) ATI Technologies Inc.Internal DAC(400MHz)524288mbATI MOBILITY FireGL V5200 20080103113252.000000-0006.14.10.6755ATI MOBILITY FireGL V5200 ATI MOBILITY FireGL V5200 (0x71C4)

Riaan de Lange · Answer

Split the comma data into variables then build new line of text and add + 	 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

Yogita · Answer

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

Alan · Answer

Try the code in this thread instead: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=245749&SiteID=1