I'm attempting to load files into a SQL Server database. I have familiarity with using bulk import and generating format files using BCP to reorganize columns. However, I'm attempting to do 2 new things that I've never tried before.
I've been given scores of separate CSVs to load into a database, individual files each representing a day over the course of a few years. I'd like to be able to use a utility to load them, but they have a varying number of columns (and of course, are separate files!). I was thinking about creating a loading stored procedure, and iterating over the files using PowerShell, but even then I can't come up with a good way to handle the varying number of columns.
The columns are consistent, it's just that the files have had columns added to them over the years. I'd like to be able to import the files with less columns, having the non-existent columns come in as null values. Does BCP support this?
I'd rather not have to figure out when each column was added and write different stored procedures/scripts for each group of dates (that's the only solution I can come up with!). Any suggestions?
Thanks!
The only way I can think of to handle this is with procedural coding. You could write either a console app, or an SSIS package with a script task. Either way, you would need to loop through the files, and inside the loop, examine the contents to see which columns are missing, and then import the file supplying some hard-coded value for any columns that are missing.
BCP can handle files with missing columns by using a format file, but it can't do it dynamically (not knowing in advance which columns are missing).