Create Tab Delimited CSV File with StreamWriter

jdtrent · January 21, 2021, 6:05pm

Hey there,

I’m creating a CSV file from data that might contain quote marks, so I want to tab delimit it.

Here’s my code:

// write programming batch to server file
string tab = "/t";
string[] headerList = {"PartNum", "Filename", "Qty", "RawMtl", "Material", "Gauge", "JobNum", "Date"}; 
string header = "";
string detail = "";

int numRecs = ttResults.Where(row => row.JobMtl_ProgrammingExported_c == true).Count();

if (numRecs > 0)
{
  string fileName = @"\\E10-TCFDEV02\Radan\" + "RadanBatch_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + callContextClient.CurrentUserId + ".csv";

  using(var sw = new System.IO.StreamWriter(fileName, false)) 
  {
    **header = string.Join(tab, headerList);** // inserts delimiters into list

    var ttResultQuery = ttResults.Where(row => row.JobMtl_ProgrammingExported_c == true);

    foreach (var ttResult in ttResultQuery)
    {
      string[] lineList = 
      {ttResult.JobHead_PartNum,
      ttResult.Part1_CutFileName_c,
      ttResult.JobMtl_RequiredQty.ToString(),
      ttResult.JobMtl_PartNum,
      ttResult.Part_MaterialType_c,
      ttResult.Part_Gauge_c.ToString(),
      ttResult.JobHead_PartNum,
      ttResult.JobOper_StartDate.Value.ToString("MM/dd/yyyy")};

      **string line = string.Join(tab,lineList);**

      detail += Environment.NewLine + line;
    }

    sw.Write(header + detail);
    sw.Close();

    this.PublishInfoMessage("File Created: " + fileName, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");  
  }

}
else
{
  this.PublishInfoMessage("No File Created.", Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", ""); 
}

But my output looks like this:

What’s the secret sauce for getting the tab character inserted?

Thanks,

Joe

Aaron_Moreng · January 21, 2021, 6:12pm

Can you try making the delimiter a literal string

Doug.C · January 21, 2021, 6:22pm

You can try changing the

string tab = "/t";

to

char tab = '\t';

jdtrent · January 21, 2021, 6:29pm

Hey Doug,

Dang it. Saw the backslash and changed it. Then got:

Now the joins don’t like the char type, expecting a string.

Still working.

Thanks,

Joe

Doug.C · January 21, 2021, 6:35pm

You changed the string to char? And changed from double quotes to single?

jdtrent · January 21, 2021, 6:39pm

Aaron,

Changing to:

string tab = @"\t";

Resulted in:

Aaron_Moreng · January 21, 2021, 6:41pm

Tricky Ol’ backslash vs forward

jdtrent · January 21, 2021, 6:43pm

Doug,

Here’s what I’m getting:

I haven’t tried manually creating the strings yet.

Joe

Aaron_Moreng · January 21, 2021, 6:48pm

I was mistaken about the @ symbol

Doug.C · January 21, 2021, 6:55pm

Sorry. I see the string.Join(Char, Object[]) is .NET5

jdtrent · January 21, 2021, 6:59pm

Doug,

I tried this:

And got this:

Wondering if StreamWriter is filtering them out.

Thanks for your help.

Joe

Doug.C · January 21, 2021, 7:00pm

Based on what @Aaron_Moreng posted, have you tried:

var tab = "\t";

EDIT

Actually I tried it with:

string tab="\t";

and it looked like it worked as well

jdtrent · January 21, 2021, 7:16pm

Yep, I’ve tried it that way. The tab just doesn’t seem to make it to the csv file.

Here’s the data we’re writing out. It looks like the tabs are in there okay.

Doug.C · January 21, 2021, 7:34pm

I don’t know if the NewLine is causing any issues.

Maybe try this?

// write programming batch to server file
string tab = "\t";
string[] headerList = {"PartNum", "Filename", "Qty", "RawMtl", "Material", "Gauge", "JobNum", "Date"}; 
string header = "";

int numRecs = ttResults.Where(row => row.JobMtl_ProgrammingExported_c == true).Count();

if (numRecs > 0)
{
  string fileName = @"\\E10-TCFDEV02\Radan\" + "RadanBatch_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + callContextClient.CurrentUserId + ".csv";

  using(var sw = new System.IO.StreamWriter(fileName, false)) 
  {
    header = string.Join(tab, headerList);

    sw.WriteLine(header); //WriteLine - header

    var ttResultQuery = ttResults.Where(row => row.JobMtl_ProgrammingExported_c == true);

    foreach (var ttResult in ttResultQuery)
    {
      string detail = "";
      string[] lineList = 
      {ttResult.JobHead_PartNum,
      ttResult.Part1_CutFileName_c,
      ttResult.JobMtl_RequiredQty.ToString(),
      ttResult.JobMtl_PartNum,
      ttResult.Part_MaterialType_c,
      ttResult.Part_Gauge_c.ToString(),
      ttResult.JobHead_PartNum,
      ttResult.JobOper_StartDate.Value.ToString("MM/dd/yyyy")};

      detail = string.Join(tab,lineList);

      sw.WriteLine(detail); //WriteLine - detail
    }
    // Removed sw.Write
    sw.Close();

    this.PublishInfoMessage("File Created: " + fileName, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", "");  
  }

}
else
{
  this.PublishInfoMessage("No File Created.", Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "", ""); 
}

jdtrent · January 21, 2021, 7:44pm

Same thing, sorry to say.

Joe

Doug.C · January 21, 2021, 7:47pm

Do you have Notepad++? Maybe see if there’s something in there you see rather than using Excel?

adaniell · January 21, 2021, 7:57pm

This might just be an issue with Excel parsing the file as a csv.
What happens if you just change the extension to .txt? This is Excel’s save as option extension for tab delimited files.
Also, you can try to add this as the first line in the CSV to tell Excel to use it as a delimiter for csv:
sep=\t

jdtrent · January 21, 2021, 8:07pm

All,

I don’t know why it wasn’t working, but it gave me success when I added the encoding:

using(var sw = new System.IO.StreamWriter(fileName, false, System.Text.Encoding.UTF32))

Default is UTF8.

All is well.

Thanks, folks.

Joe