Question Import CSV in Excel

biv1962

New member
Joined
Sep 18, 2017
Messages
3
Programming Experience
5-10
Hi All,

I want to import a CSV file programmaticaly in Excel.
I've searched the internet and came up with this code:

xlWorkBooks.OpenText(@"C:\WOC\Example.CSV",
DataType: Excel.XlTextParsingType.xlDelimited,
TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
ConsecutiveDelimiter: false,
Semicolon: true);

But this is not working. Whatever I do a row is just a long string as if the Semicolon is not recognized.
I tried so many possible options but none is working.

Pls find in the attachment what I see when Excel is openend.

Your help is very much appreciated!

Best regards,
Bert
 

Attachments

  • CSV2XLS.PNG
    CSV2XLS.PNG
    8.6 KB · Views: 70
Do you have a 2 or 3 row sample of the raw csv file you can post?

Hi JuggaloBrotha,

Here are some lines:

00010;BORD MIDNIGHT BROWN PLAT 28CM;000000000061124660;529069; 1200,000;ST; 1,000;ST;8717522275930;20171024
00020;BORD MIDNIGHT BROWN DIEP 21CM;000000000061124690;529071; 1200,000;ST; 1,000;ST;8717522275893;20171024
00030;KOM MIDNIGHT BROWN 15CM;000000000061124700;529072; 1200,000;ST; 1,000;ST;8717522275879;20171024
 
Hi JuggaloBrotha,

Here are some lines:

00010;BORD MIDNIGHT BROWN PLAT 28CM;000000000061124660;529069; 1200,000;ST; 1,000;ST;8717522275930;20171024
00020;BORD MIDNIGHT BROWN DIEP 21CM;000000000061124690;529071; 1200,000;ST; 1,000;ST;8717522275893;20171024
00030;KOM MIDNIGHT BROWN 15CM;000000000061124700;529072; 1200,000;ST; 1,000;ST;8717522275879;20171024
Here's an example of parsing the file with each line going into an array that you can use to create a List<of typ> collection from or put right into the Excel file, however you choose.
string ParseFile(string FileName, string Delimiter, string Qualify)
{
    string Output = String.Empty;
    string NonTypableDelimiter = "?";
    
    // Make sure the file exists
    if (File.Exists(FileName))
    {
        // Make sure we have a delimiter
        if ((Delimiter.Length > 0))
        {
            StreamReader sr = null;
            try
            {
                sr = new StreamReader(FileName);
                string TrueDelimiter = Qualify + Delimiter + Qualify;
                string CurrLine = String.Empty;
                string[] Records;

                while (sr.Peek() > -1)
                {
                    //Read the line and trim the first and last qualify char from it
                    CurrLine = sr.ReadLine().TrimStart(Qualify.ToCharArray()).TrimEnd(Qualify.ToCharArray());
                    
                    //Convert the qualify char and delimiter into a non-typable character then split on it to get array
                    Records = CurrLine.Replace(TrueDelimiter, NonTypableDelimiter).Split(NonTypableDelimiter[0]);

                    foreach (string item in Records)
                    {
                        // Do Something
                        MessageBox.Show(item);
                    }
                }
            }
            catch (Exception ex)
            {
                // Pass the error message out
                Output = ex.Message;
            }
            finally
            {
                // Cleanup objects
                if (sr != null)
                {
                    sr.Close();
                    sr.Dispose();
                }
            }
        }
        else
        {
            //Error: no delimiter
            Output = "No delimiter";
        }
    }
    else
    {
        // Error no input file
        Output = "File {FileName} doesn\'t exist";
    }

    return Output;
}
Simply call it:
private void Button1_Click(object sender, EventArgs e)
{
    string ErrorMessage = this.ParseFile(FileTextBox.Text.Trim(), DelimiterTextBox.Text, QualifyTextBox.Text);
    
    if (ErrorMessage.Length > 0)
        MessageBox.Show(ErrorMessage, "Error Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
 
Thank you JuggaloBrotha!!!

I will try your solution.
I am little disappointed thow that there is no simple way to programmaticaly import a csv/txt file into Excel via Office Automation.


Thanks again.


 
It appears OpenText considers .csv files as special format that it parses uses own rules (comma separator). If you change extension to for example .txt it should work.

As alternative I also had success setting parameter Local:true, this uses the systems regional settings for List separator though, so it may not be suitable or reliable.
 
Back
Top Bottom