This project has moved. For the latest updates, please go here.

Workbooks.Open Method for csv file with delimiter

Jul 26, 2013 at 4:26 PM
Edited Jul 26, 2013 at 4:27 PM
Hey,

i tried to open a csv file with Excel (using the workbooks.open method in C#).
The csv file has tabulators as delimiter.
The problem is, that Excel writes the complete Text in the first collum instead of using the delimiter.

If i save the csv file as txt and then use the workbooks.open method with tabulator as delimiter, it works fine.

Someone knows how to fix the problem?

Thanks in advance,
Stephan
Jul 29, 2013 at 3:53 PM
Hi,

with Visual Basic this code works fine:
                With oSheet.QueryTables.Add(Connection:="TEXT;" & strInFile, Destination:=oRng)
                    .Name = strTabName
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .RefreshStyle = ExcelEnums.XlCellInsertionMode.xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .TextFilePromptOnRefresh = False
                    .TextFilePlatform = CType(1252, ExcelEnums.XlPlatform)
                    .TextFileStartRow = 1
                    .TextFileParseType = ExcelEnums.XlTextParsingType.xlDelimited
                    .TextFileTextQualifier = ExcelEnums.XlTextQualifier.xlTextQualifierNone
                    .TextFileConsecutiveDelimiter = False
                    .TextFileTabDelimiter = False
                    .TextFileSemicolonDelimiter = True
                    .TextFileCommaDelimiter = False
                    .TextFileSpaceDelimiter = False
                    .TextFileColumnDataTypes = FieldInfo 'Range with Columdescription (Int)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh(BackgroundQuery:=False)
                End With
I just try to transfer my projekt to C# and my problem is same.

This code should work, but it doesn't :(
                    object with_0 = oSheet.QueryTables.Add("TEXT;" + strInFile, oRng);
                    with_0.Name = strTabName;
                    with_0.FieldNames = true;
                    with_0.RowNumbers = false;
                    with_0.FillAdjacentFormulas = false;
                    with_0.PreserveFormatting = true;
                    with_0.RefreshOnFileOpen = false;
                    with_0.RefreshStyle = ExcelEnums.XlCellInsertionMode.xlInsertDeleteCells;
                    with_0.SavePassword = false;
                    with_0.SaveData = true;
                    with_0.AdjustColumnWidth = true;
                    with_0.RefreshPeriod = 0;
                    with_0.TextFilePromptOnRefresh = false;
                    with_0.TextFilePlatform = (ExcelEnums.XlPlatform)(1252);
                    with_0.TextFileStartRow = 1;
                    with_0.TextFileParseType = ExcelEnums.XlTextParsingType.xlDelimited;
                    with_0.TextFileTextQualifier = ExcelEnums.XlTextQualifier.xlTextQualifierNone;
                    with_0.TextFileConsecutiveDelimiter = false;
                    with_0.TextFileTabDelimiter = false;
                    with_0.TextFileSemicolonDelimiter = true;
                    with_0.TextFileCommaDelimiter = false;
                    with_0.TextFileSpaceDelimiter = false;
                    with_0.TextFileColumnDataTypes = FieldInfo;
                    with_0.TextFileTrailingMinusNumbers = true;
                    with_0.Refresh(false);
The error:
Fehler 32 'NetOffice.ExcelApi._Worksheet.QueryTables.get': Der Operator oder Accessor kann nicht explizit aufgerufen werden. csv2xcel.cs 464 71 MyProjectname

Haro
Coordinator
Jul 29, 2013 at 11:19 PM
the occurs in the first line?
this line:
object with_0 = workSheet.QueryTables.Add("TEXT;" + "", null);
works fine for me. but you can't use 'object' in c# of course, you have to use Excel.QueryTable or the dynamic keyword.

Sebastian
Jul 30, 2013 at 4:36 PM
This "howto" from microsoft:
TEXT

I solved my problem yesterday with:
var _with1 = oSheet.QueryTables.Add(connection: "TEXT;" + modGlobalVar.glStrCVSFullPath, destination: oRng);
_with1.Name = strSheetName;
_with1.FieldNames = true;
_with1.RowNumbers = false;
_with1.FillAdjacentFormulas = false;
_with1.PreserveFormatting = true;
_with1.RefreshOnFileOpen = false;
_with1.RefreshStyle = NetOffice.ExcelApi.Enums.XlCellInsertionMode.xlInsertDeleteCells;
_with1.SavePassword = false;
_with1.SaveData = true;
_with1.AdjustColumnWidth = true;
_with1.RefreshPeriod = 0;
_with1.TextFilePromptOnRefresh = false;
_with1.TextFilePlatform = (ExcelEnums.XlPlatform)1252;
_with1.TextFileStartRow = 1;
_with1.TextFileParseType = NetOffice.ExcelApi.Enums.XlTextParsingType.xlDelimited;
_with1.TextFileTextQualifier = NetOffice.ExcelApi.Enums.XlTextQualifier.xlTextQualifierNone;
_with1.TextFileConsecutiveDelimiter = false;
_with1.TextFileTabDelimiter = false;
_with1.TextFileSemicolonDelimiter = true;
_with1.TextFileCommaDelimiter = false;
_with1.TextFileSpaceDelimiter = false;
_with1.TextFileColumnDataTypes = arFieldinfo;
_with1.TextFileTrailingMinusNumbers = true;
_with1.Refresh(backgroundQuery: false);
arFieldinfo is an array like this:
int[,] arFieldinfo = { { 1, 2 }, { 2, 2 }, { 3, 2 }, { 4, 1 } };
Quote from the site above:
Optional XlColumnDataType. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of theXlColumnDataType constants specifying how the column is parsed.

xlGeneralFormat General (1)
xlTextFormat Text (2)
xlMDYFormat MDY date (3)
xlDMYFormat DMY date (4)
xlYMDFormat YMD date (5)
xlMYDFormat MYD date (6)
xlDYMFormat DYM date (7)
xlYDMFormat YDM date (8)
xlEMDFormat EMD date (10)
xlSkipColumn Skip Column (9)
The data in parentheses correspond to the numerical value of the data type (public enum XlColumnDataType).

Haro