Trying to update/create SQL Table via SQL query with data from CSV File

Question

I am trying to insert data from a csv file into my SQL database. I originally wanted to do so using powershell but since id like to make Power Automate do this task later down the road, i have opted to use a SQL query to update the table. However i keep getting this error " Msg 213, Level 16, State 1, Line 13 Column name or number of supplied values does not match table definition."
I am very much a SQL Noob and have gotten completely stuck. Any recommendation or tips are welcome. Please help.

Also i know i can use the wizard in SSMS but like i said i want to run a power automate flow to execute a sql query and update the SQL table.
below is my code:
drop table SQLSRV1.dbo.NewUsers;

create table SQLSRV1.dbo.NewUsers(
full_name varchar(20),
username varchar(20),
job_role varchar(20),
manager varchar(20),
email varchar(20),
user_password varchar(20));

insert into SQLSRV1.dbo.NewUsers
select * from
openrowset (BULK 'C:\CSV\NewUsers.csv',
formatfile = 'C:\CSV\SQLCSVImport.txt',
FIRSTROW=2,
FORMAT='CSV'
) as tmp

--truncate table dbo.NewUsers
--select * from dbo.NewUsers

---

Content in file SQLCSVImport.txt :
9.0
3
1 SQLCHAR 0 20 "," 1 full_name SQL_LATIN1_General_Cp1_CI_AI
2 SQLCHAR 0 20 "," 2 username SQL_LATIN1_General_Cp1_CI_AI
3 SQLCHAR 0 20 "," 3 job_role SQL_LATIN1_General_Cp1_CI_AI
4 SQLCHAR 0 20 "," 4 manager SQL_LATIN1_General_Cp1_CI_AI
5 SQLCHAR 0 20 "," 5 email SQL_LATIN1_General_Cp1_CI_AI
6 SQLCHAR 0 20 "\r\n" 6 user_password SQL_LATIN1_General_Cp1_CI_AI

If it helps this the video i pretty much copied from: https://www.youtube.com/watch?v=M54YhpBv_cc

Answer 1

Try replacing 3 with 6 in the second line of SQLCSVImport.txt.

Answer 2

Please try the following solution.

It is based on BULK INSERT statement.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.tbl;

CREATE TABLE dbo.tbl 
(
   full_name varchar(20),
   username varchar(20),
   job_role varchar(20),
   manager varchar(20),
   email varchar(20),
   user_password varchar(20)
);

BULK INSERT dbo.tbl 
FROM 'C:\CSV\NewUsers.csv'
WITH  
(
   DATAFILETYPE    = 'char', --widechar',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n',
   CODEPAGE = '65001'
);

SELECT * FROM dbo.tbl;

Answer 3

The first thing you should do is run the select without the INSERT to see what you get back.

select * from
openrowset (BULK 'C:\CSV\NewUsers.csv',
formatfile = 'C:\CSV\SQLCSVImport.txt',
FIRSTROW=2,
FORMAT='CSV'
) as tmp

Answer 4

Hi @SqlNuub234 ，

Msg 213, Level 16, State 1, Line 13 Column name or number of supplied values does not match table definition

The number of values you provide does not match the number of columns in the table

To resolve this, according to this article: How to Import and Export SQL Server data to an Excel file
Open excel file for which is planned to store the data from SQL Server table and enter the column names which will represent the column names from the SQLSRV1.dbo.NewUsers table, then try to execute the code again

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Cannot bulk load because the file "C:\CSV\SQLCSVImport.txt" is incomplete or could not be read. Operating system error code (null).

A general remark: errors messages from BCP/BULK INSERT/OPENROWSET(BULK) are often very obscure and by no means obvious. This error at least says "incomplete" which apparently was true in a way. The same situation with BCP results in something about I/O error.

If you would have more problems with the bulk load tools in the future, you may be interested in this article on my web site: https://www.sommarskog.se/bulkload.html.