Bulk Import into SQL server Table

Question

Can you please help me with the syntax to bulk import this data:

startIpNum,endIpNum,country,region,city,postalCode,latitude,longitude,dmaCode,areaCode
1.0.0.0,1.7.255.255,"AU","","","",-27.0000,133.0000,,
1.9.0.0,1.9.255.255,"MY","","","",2.5000,112.5000,,
1.10.10.0,1.10.10.255,"AU","","","",-27.0000,133.0000,,
1.11.0.0,1.11.255.255,"KR","","","",37.0000,127.5000,,
1.12.0.0,1.15.255.255,"CN","","","",35.0000,105.0000,,
1.16.0.0,1.19.255.255,"KR","","","",37.0000,127.5000,,
1.21.0.0,1.21.255.255,"JP","","","",36.0000,138.0000,,
1.22.0.0,1.23.255.255,"IN","","","",20.0000,77.0000,,
1.24.0.0,1.31.255.255,"CN","","","",35.0000,105.0000,,
1.33.0.0,1.33.255.255,"JP","","","",36.0000,138.0000,,

I am using this :

set quoted_identifier OFF
drop table #y
drop table #x
DECLARE @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100), 
@TableName varchar(128),@query varchar(1000),@max1 int,@count1 int,@filename varchar(100),@fieldTerminator VARCHAR(100),@RowTerminator VARCHAR(100)

SELECT @servername ='SQL',
@DatabaseName ='Test',
 @filepath ='C:\test',
  @pattern ='*.*', 
@TableName ='WeeklyImport'
,@fieldTerminator='''","'''
,@RowTerminator='''"\n"'''
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x 
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" 
                WITH ( FIELDTERMINATOR = ' +@fieldterminator + ',ROWTERMINATOR = ' + @rowterminator + ')'
print @query
exec (@query)
end

drop table #y

Answer 1

Do you have access to the SQL Import/Export tool in SQL Server?

For me, it's at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe". There's other SO pages on this, including http://stackoverflow.com/questions/177860/questions-about-exporting-and-importing-flatfiles-txt-csv-in-sql-server-2005

Answer 2

I tried it out the following format file...

...against the first 3 columns of your csv file:

The query goes as follow:

You now only need to add all the other columns and specify their format inside the format file. You can use the REPLACE function if you need to modify some values before inserting them.