Export SQL Query to fixed width text file

[scottdg]

I am trying to take an existing SSIS package that imports an Excel file, transforms the data and exports as a fixed width text file and move it to a Windows application. I am able to import the file to SQL Server but I am not sure how to export the data as a fixed width file. I was able to use the following code to export to a tab delimited file but it must be a fixed width file for our vendor to read. I'm not sure if the code below can achieve this with a little modification or if a completely different approach is required. I would also be willing to do this in SQL server if anyone has a resource on how to do it using t-SQL. Thanks.

using (SqlCommand cmd = new SqlCommand("SELECT Col1, Col2, Col3, Col4, Col5, Col6 FROM myTable"))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    cmd.Connection = myDB;
                    adapter.SelectCommand = cmd;
                    using (DataTable mAdj = new DataTable())
                    {
                        adapter.Fill(mAdj);
                        string str = string.Empty;
                        str += "\r\n";
                        foreach (DataRow row in mAdj.Rows)
                        {
                            foreach (DataColumn column in mAdj.Columns)
                            {
                                str += row[column.ColumnName].ToString() + "\t\t";
                            }
                            str += "\r\n";
                        }
                        StreamWriter file = new StreamWriter(@"\\..\Export_files\myFile.txt");
                        file.WriteLine(str.ToString());
                        file.Close();
                    }
                }
            }

 

[jmcilhinney]

If it's fixed-width then you should not be using tabs. That's the opposite of fixed-width. You should use string interpolation or call String.Format and specify the width of each filed, e.g.

var lines = new List<string>();

foreach (var row in myDataTable.AsEnumerable())
{
    lines.Add($"{row[0]:10}{row[1]:-15}{row[2]:20}");
    // or
    lines.Add(String.Format("{0:10}{1:-15}{2:20}", row[0], row[1], row[2]));
}

File.WriteAllLines(filePath, lines);

The number after the colon in each case is the field width. If I remember correctly, a positive number means left-aligned and a negative number means right-aligned, but it might be the other way around.

By the way, given that you are simply reading each row and processing it then never touching it again, you ought to be using a data reader rather than a DataTable and a data adapter.