Click here to Skip to main content
15,887,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,
I've got a Problem with importing a CSV file to Excel via C#.
I've got 2 different types of CSV files, the first one is delimitered by commas and the import works perfectly fine, the second type is delimitered by tabstops and is not working. I can't find any mistake so I hope you are going to find a solution:

Code for Importing comma file:
C#
// Create new Excel-Application and initialize the Worksheet and the Range //
                Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Worksheet xSheet;
                Microsoft.Office.Interop.Excel.Range xRange;

                // Import CSV and split at Comma //
                xl.Workbooks.OpenText(tbDS.Text,
                   false, 3,
                   Excel.XlTextParsingType.xlDelimited,
                   Excel.XlTextQualifier.xlTextQualifierNone,
                   Type.Missing, Type.Missing, Type.Missing, true,
                   Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing);


Code for Importing tab file:
C#
string file = Path.GetDirectoryName(tbDS.Text) + "\\Measurements\\" + report + ".csv";
            if (File.Exists(file))
            {
              // Create new Excel-Application and initialize the Worksheet and the Range //
              Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();

              Microsoft.Office.Interop.Excel.Worksheet xSheet;
              Microsoft.Office.Interop.Excel.Range xRange;

              // Import CSV and split at Tab-Stop //
              xl.Workbooks.OpenText(file,
                 false, 3,
                 Excel.XlTextParsingType.xlDelimited,
                 Excel.XlTextQualifier.xlTextQualifierNone,
                 Type.Missing, true, Type.Missing, Type.Missing, 
                 Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, 
                 Type.Missing, Type.Missing, Type.Missing);


First one splits the CSV perfectly fine, second one does not split the CSV File

Sample CSV:
comma:
Testplan, Load, RFV1, 1HAR1, RFV2, 1HAR2, RR, LRU, LRL, TUZ(pp), TUZ(har), Total grade, date + time,
434, 6326, 59, 19, 0, 0, 0.249, 1.108, 0.66, 17.3, 0, 1,  7. 05. 2014 08:54:09,
434, 6334, 129, 95, 0, 0, 0.352, 1.449, 0.931, 27.8, 0, 4,  7. 05. 2014 08:54:25,
434, 6301, 41, 23, 0, 0, 0.129, 1.032, 0.631, 14.5, 0, 1,  7. 05. 2014 08:54:40,
434, 6317, 76, 34, 0, 0, 0.248, 1.011, 0.774, 18, 0, 1,  7. 05. 2014 08:54:57,


tab:
RKS1	RKS2	LKS1	LKS2	HS	SSO	SSU
6	0	2	0	-47	0	-44
6	0	2	0	-49	1	-48
6	0	2	0	-50	2	-54
6	0	2	0	-51	3	-60
6	0	2	0	-52	5	-66
6	0	1	0	-54	5	-70



Thank you in Advance
Posted
Updated 8-Jul-14 4:29am
v2
Comments
George Jonsson 8-Jul-14 10:07am    
I don't see that you set the delimiter character anywhere.
If the default is comma it won't work for tab.
PZero1992 8-Jul-14 10:09am    
it should be the "true" between the "Type.Missing" isn't it?
George Jonsson 8-Jul-14 10:23am    
To me it looks like you have the same code for both CSV and TSV.
George Jonsson 8-Jul-14 10:32am    
I saw wrong, the code was just difficult to read.

1 solution

I'd suggest to use ADO.NET[^] instead od Excel OpenText method.

Please, refer these articles:
Accessing Microsoft Office Data from .NET Applications[^]
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET[^]
ADO.NET Managed Providers and DataSet...[^]
Read Text File (txt, csv, log, tab, fixed length)[^]
Much ADO About Text Files[^] - treat it as additional information.

I suspect that comma in your system has special meaning (for example decimal separator). That's why OpenText method failed. Please, see my past answer to find out how to go around it: Read Text File Specific Columns[^] - VB.NET, but this language is similar to C#.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900