Format imported text in excel c#

Question

Hy everyone i d like to ask some help for the next one .

i want to loadt a txt file into excel in c# with the workbooks.opentext method ! it runs well but in the text data there is some numbers wich contains "," character and thats the main separator of the whole text let me show u

'1200000','29,8','DUPAREC PAPÍRGYŰJTŐ ÉS FELD. KFT','

and the problem is in the second column .

Is there anyone who have any idea how to avoid this problem? my code is the next one

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
int[,] fieldInfo = new int[4, 2] { { 1, 2 }, { 2, 4 }, { 3, 2 }, { 4, 2 } };
object oFieldInfo = fieldInfo;
xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value;
xlApp.Workbooks.OpenText(
    filepath,Excel.XlPlatform.xlWindows,
    1,
    Excel.XlTextParsingType.xlDelimited,
    Excel.XlTextQualifier.xlTextQualifierNone,
    false,
    false,
    false,
    true,
    false,
    false,
    missing,
    oFieldInfo,
    missing,
    missing, 
    missing, 
    missing, 
    missing 
        );

Best regards !

Smith!

A bit unclear on what you are asking. Do you want to know how to avoid the , in numbers or how to export it text excel with that field? — Kami
– Kami, Commented Sep 16, 2013 at 12:31
i want to know how to "skipp" that character couse when the programm runs over there will be an extra collumn becouse of the split by comma, which one is not needed . i just want the columns wich is between the '' characters. — balage90
– balage90, Commented Sep 16, 2013 at 13:01

marc_s · Accepted Answer · 2017-01-24 18:13:28Z

1

As your character to qualify a text is ' (single quote) you have to pass Excel.XlTextQualifier.xlTextQualifierSingleQuote as parameter.

See the Excel developer reference for more information.

The code below should make your import work.

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
int[,] fieldInfo = new int[4, 2] { { 1, 2 }, { 2, 4 }, { 3, 2 }, { 4, 2 } };
object oFieldInfo = fieldInfo;
xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value;
xlApp.Workbooks.OpenText(
    filepath,Excel.XlPlatform.xlWindows,
    1,
    Excel.XlTextParsingType.xlDelimited,
    Excel.XlTextQualifier.xlTextQualifierSingleQuote,
    false,
    false,
    false,
    true,
    false,
    false,
    missing,
    oFieldInfo,
    missing,
    missing, 
    missing, 
    missing, 
    missing 
        );

Because you defined the second column to be date time formatted in your fieldInfo array '29,8' will be displayed as 29. August in Excel.

You should replace {...,{2,4},...} with {...,{2,1},...} to let Excel parse the number as a number.

edited Jan 24, 2017 at 18:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 16, 2013 at 13:19

user1064248

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

4 Comments

balage90 Over a year ago

the second column somhow changes into date type after the process:S '29,8' that one and in exel it seems 29.agust do u have any ide why?

user1064248 Over a year ago

Added solution for not parsing the way you want in my answer.

user1064248 Over a year ago

You are welcome. Please don't forget to mark the question as answered.

user1064248 Over a year ago

xlWorkBook.SaveAs...

Collectives™ on Stack Overflow

Format imported text in excel c#

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related