C# Save Text File to Excel

Question

I have a text file that is comma delimited. some numbers are representing Codes or Credit Card numbers and they should show in the Excel as TEXT and should not be treated as numbers (left Zero is important )

here is my code:

m_objBooks.OpenText(
                    Filename: _fileInfo.FullName,
                    Origin: Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                    StartRow: 1,
                    DataType: Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
                    TextQualifier: Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote,
                    ConsecutiveDelimiter: false,
                    Tab: false,
                    Semicolon: false,
                    Comma: true,
                    Space: false,
                    Other: false,
                    OtherChar: m_objOpt,
                    FieldInfo: m_objOpt,
                    TextVisualLayout: m_objOpt,
                    DecimalSeparator: m_objOpt,
                    ThousandsSeparator: m_objOpt,
                    TrailingMinusNumbers: m_objOpt,
                    Local: m_objOpt
                    );

                m_objBook = m_objExcel.ActiveWorkbook;

                // Save the text file in the typical workbook format and quit Excel.
                m_objBook.SaveAs(excelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
                m_objExcel.Quit();

Although the Code and Credit Card fields has the Double Quotes around them, the generated excel file still treats them as numbers by removing the Left Zero and stuff like that. why?

m_objOpt represent the Missing parameter

Yousi
– Yousi

2013-11-18 08:06:19 +00:00
Commented Nov 18, 2013 at 8:06 — Yousi
– Yousi, Commented Nov 18, 2013 at 8:06

Ragesh Puthiyedath Raju · Accepted Answer · 2013-11-18 08:33:15Z

2

I already fight with this problem over a month. I found there is many problem using this method.

Finally i got a great example in the CodeProject website.

Please look at this below example.

A Very Easy to Use Excel XML Import-Export Library

I hope it helpfull.

answered Nov 18, 2013 at 8:33

Ragesh Puthiyedath Raju

3,93514 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yousi Over a year ago

well, i don't want to use another library. I need to use the Excel interop

Yousi · Accepted Answer · 2013-11-18 10:38:35Z

I managed to solve this problem by passing the correct FieldInfo object.

in my example, I passed a Missing object to the FieldInfo parameter.

instead of that, I created an array object and passed it to the FieldInfo parameter.

Here is a sample on how to create the FieldInfo object:

int[,] _fieldInfo = new int[8,2]  // 8 = number of columns , 2 = number of properties (column number and column type (General = 1 , Text = 2 .. check: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext.aspx )

//fill the array:
_fieldInfo[0,0] = 1; //column number
_fieldInfo[0,1] = 2; //column type (2 = text)

_fieldInfo[1,0] = 2;
_fieldInfo[1,1] = 2;

_fieldInfo[2,0] = 3;
_fieldInfo[2,1] = 2;

after that, I just pass the _fieldInfo to the method and all is done. Of course, you can use this technique to create the _fieldInfo dynamically using loops.

Collectives™ on Stack Overflow

C# Save Text File to Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related