What is the format for a flat file using a SQL BCP generated Format file?

Question

My understanding of using BCP format files and BULK INSERT is that:

1. CREATE the table in SQL
2. Execute BCP with the proper options to create the Format File
3. Provide the data you want to import in fixed column format
4. Use BULK INSERT with the Format File and your data input file.

My data is coming from a very old non-relational database.

My question is: how do you format the flat fixed column input file when it has a mixture of Char, Int, String and Float fields? Is there some special character to place between fields? For instance we have one field type that can have: " ","U "," P" or "UP"...

Answer 1

I found the answer from someone else's post: Here is a link to MS documentation on creating format files. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/non-xml-format-files-sql-server?view=sql-server-ver15

Use BCP to generate the Format file, edit it to make all fields SQLCHAR, specify any field terminator between the double-quotes ("\t" for tab), use "\r\m" as the input record terminator.