Importing CSV into SQL Server via powershell, but only the matching columns? -

i file particular vendor every week. once in while process changes, , wind getting fields, in middle of row. fortunately, proper header, , don't appear remove existing fields. unfortunately, nobody ever knows why or how changed. after changing several times, decided clever.

so, i'd do: query sql server, columns target view/table, select fields exist in both, table.

my current code (below) doesn't - import-csv, "select fields know good", insert table. works, yes, slow crap. , dumb, since have hardcode fields know good. i'd more extensible process.

additionally, i'd stream in - existing process crawls (15 minutes 50mb file), because has load entire file, run through select & convert, insert. works.

i played high-performance techniques importing csv sql server using powershell, kept running "i don't know powershell enough fix" issues.

here's have far. ultra-simplified, though make simpler building view on top of conversion "that first field firstfield". in advance!

in sql server:

create database my_database go use my_database go create table my_target_table (firstfield varchar(100), storeid int,state varchar(3)) 

now powershell script:

#import stuff need.  using these chad miller's datatable scripts.   #http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894/ #http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae/ . c:\scripts\out-datatable.ps1; . c:\scripts\invoke-sqlcmd2.ps1; . c:\scripts\write-datatable.ps1;  if (test-path variable:\my_import) { remove-variable my_import }  $importfilepath = "c:\test\my_import_file.txt"  #save below 3 lines "c:\test\my_import_file.txt" #that first field|store id|uselessfield|state #1|15|aaa|mi #12|4|ab|ri  $my_import = import-csv $importfilepath -delimiter "|"| ` select @{label="firstfield";expression={$_."that first field"}}, `        @{label="storeid";expression={$_."store id"}}, `        @{label="state";expression={$_."state"}} ` |out-datatable         write-datatable -serverinstance my_server -database my_database -tablename my_target_table -data $my_import 

i can't powershell don't know enough it, can using datatable not terribly efficient because has load entire dataset memory first, before releasing sql server. alone not scalable.

give want to:

1. select subset of fields in csv file
2. stream data in
3. be dynamic / flexible possible
4. import 50 mb in far less 15 minutes

you should able accomplish of using table-valued parameters , .net.

step 1 of getting tvp work create user-defined table-type (udtt). strongly-typed structure used transport data .net (and maybe powershell well) sql server. provides definition of table variable in stored procedure or ad hoc query receives data. udtts can have identity columns, check constraints, unique constraints, etc.

step 2 writing code in .net key component creating method returns ienumerable<sqldatarecord>. method passed parameter of sqlcommand maps user-defined table-type, rather passing in datatable udtt. while datatable stream out data sql server, same method create, custom method allows streaming data into app. means each row read, can send immediately, , read row. memory footprint single row.

the other nice thing approach since code reads file custom, can implement logic want regarding fields grab csv file. can connect sql server, columns in particular table, , map those. down-side, however, tvp (i.e. user-defined table-type / udtt) not dynamic , not changeable. should ok since doubt table definitions changing anyway. mapping each particular table, more mapping columns in table. need naming convention udtts makes easy correlate them associated target table. still have ability query db definition of udtt can dynamically build sqlmetadatacollection provides structure of sqldatarecord .net mapping udtt.

i have example code of of (all dynamic fetching of sqldatarecord definition) in following answer, here on s.o.:

how can insert 10 million records in shortest time possible?