Insert .csv file into SQL Server table

Question

Background:

I have a table named dbo.table, within my database Data. I then have a .csv file named test.csv I would like to push this test.csv data into my dbo.table within my Data database. How would I go about doing this?

  dbo.table

  State    Customer
  Ga       John Doe


  test.csv

  State    Customer 
  NY       Carolyn King

Desired Output:

State    Customer
Ga       John Doe
NY       Carolyn King

I wish to insert test.csv data into the dbo.table. I am currently doing a Bulk Insert:

BULK INSERT dbo.table
FROM 'C:\test.csv'
WITH  
     (
   FIRSTROW = 2,
   rowterminator='\n',
   fieldterminator=',',
   tablock
     );

However, how would I perform this with a regular Insert query?

Could I do:

  INSERT INTO dbo.table SELECT a.* FROM
  OPENROWSET 
          (BULK N'D:\data.csv', FORMATFILE =
           'C:\test.csv', CODEPAGE = '65001');

I am unsure of the CODEPAGE....

Ok, I know there is a way to do a regular INSERT, but am unsure about proper way of going about it. Thanks — Lynn, Sep 24 '20 at 21:46
Have you looked at `OPENROWSET`? https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15 — SS_DBA, Sep 24 '20 at 22:48

score 1 · Answer 1 · answered Sep 25 '20 at 02:04

You can directly import a csv into a table in SSMS

https://support.discountasp.net/kb/a1179/how-to-import-a-csv-file-into-a-database-using-sql-server-management-studio.aspx

or you can generate INSERT statements from excel like below and run them

Generate sql insert script from excel worksheet

how about that! there are online tools as well http://beautifytools.com/csv-to-sql-converter.php

Insert .csv file into SQL Server table

1 Answers1