Search code examples
sql-serverbcp

Is it possible to generate both an FMT and a table from a CSV file?

So I need a way to import CSVs that vary in column names, column order, and number of columns. They will always be CSV and of course comma-delimited.

Is it possible to generate both FMT and a temp table creation script of a CSV file?

From what I can gather, you need one or the other. For example, you need the table to generate the FMT file using the bcp utility. And you need the FMT file to dynamically build a create script for a table.

Solution

  • Using just SQL and to dynamically load files text files there is no quick way to do this. I see one option:

    1. Get the data into SQL Server as a single column (bcp it in or use t-sql and openrowset to load, SSIS, etc...). Be sure to include in this table a second column that is an identity (I'll call it "row_nbr"). You will need this to find the first row to get column names from the header in the file.
    2. Parse the first record "where row_nbr = 1" to get the header record. You will need a string parse function (find online, or create your own) to substring out each column name.
    3. Build dynamic SQL statement to create a new table with the parsed out number of fields you just found. Must calculate lengths and use a generic "varchar" data type since you wont know how to type the data. Use column names found above.
    4. Once you have a table created with the correct number of adequately sized columns, you can create the format file.

    I assumed, in my answer, that you are comfortable with doing all these things, just shared the logical flow at a high level. I can add more if you need more detail.