Insert from flat file into SQL Server Table but with different number of Columns and order ?

Question

Hello, is it possible to Insert data from a flat file that has say 5 columns but target SQL Server Table has only 3 matching Columns (with correct data types) but in different order?

Following example:

Source flat personlist.csv file has 5 Column headers are: [name], [gender], [age], [city], [country]

But SQL Server target Tables has 3 Columns created as:

create table dbo.personlist (
[age] int,
[name] varchar(20),
[gender] varchar(10)
);

Just the 3 Columns in SQL Server target Table is needed and the other 2 sets of data [city], [country] from flat file can be excluded.

Thanks in advance.

Answer 1

Yes, you can do this with a format file. It would look something like this:

   9.0  
   5  
   1 SQLCHAR "" "" "," 1 col1 ""  
   2 SQLCHAR "" "" "," 0 "" ""  
   3 SQLCHAR "" "" "," 2 col1 ""  
   4 SQLCHAR "" "" "," 0 "" ""  
   5 SQLCHAR "" "" "\r\n" 3 col3 ""  

In this example fields 1, 3 and 5 are imported.

For more information on format files, see this article on my web site: https://www.sommarskog.se/bulkload.html

Answer 2

Thanks for all of your helpful replies, much appreciated.