How to insert a csv file if it contains multiple empty rows ( each column of that row is empty). I want either Bulk insert move to another row if it encounter empty row or it should insert that row with null values.
I think you can handle this using data flow task, that if a column value is empty then it can convert the value to NULL. th-cam.com/video/k7qqrtSY09o/w-d-xo.html
Awesome worked great!!! I was getting some errors while trying to import flat file feature due to large number of records (> 500k). The only thing I had to modify here was the file path location. Since my sql server instance was present on a remote sever, I had to use a shared folder path.
Hello sir i want to perform bulk insertion but i am using SQLBindParameter and then SQLBulkOperations for executing but it's giving me error.. I tried finding you on linkdin but not found.. Will you please help me out?
Hi Priyanka, I don't have much experience working with SQLBindParameter and then SQLBulkOperations, 99% of the time I use SSIS to import data from CSV\flat files to sql server table. If you are getting an error then try to google it or you can ask chatgpt as well to see if that can help or you can post your question on stackoverflow.com they give very legitimate answers and they are very quick.
@@learnssis actually I google it I implemented various ways also, I tried chatgpt but error still arrives.. And also I tried this csv part but it's not taking my folder path accurately.. I am getting error there it's unable to find file. Can we connect and then I will show you?? Maybe you will get to know.. What I am doing wrong
@@priyankabachhav5315 Actually I don't have much experience with it thus not sure if I can help you with it, please share the screenshot of the error message and code to aqil33@gmail.com. It might be related to permission issue. Make sure that your sql services owner should have full access on the folder and file that you are trying to import.
Frankly speaking I never used bulk insert query with Azure, I mostly tried with On-Premises instance. I saw some examples online where they use C# to read data from a flat file insert the data to Azure sql server stackoverflow.com/questions/46004119/increase-sql-bulk-copy-speed-on-azure stackoverflow.com/questions/51686757/bulkcopy-100-million-rows-of-data-to-azure-sql-server-using-c
Hey i am getting error. I am using mysql workbench 10:40:18 BULK INSERT tripdata FROM 'D:\Sample Data\Raw data for Capital Bikeshare\202004.csv' WITH (FORMAT = 'CSV' , FIRSTROW=2 , FIELDTERMINATOR = ',' , ROWTERMINATOR = '0x0a') Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BULK INSERT tripdata FROM 'D:\Sample Data\Raw data for Capital Bikeshare\202004.' at line 1 0.047 sec
You can try this query LOAD DATA INFILE 'D:/Files/Sellers.CSV' INTO TABLE sellers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 1 ROWS; I will make a video on this very soon.
@@learnssis That also not working Because I am working in MYSQL work bench. My CSV File local in my local computer when I try to upload I getting Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.015 sec
@@kannann1492 Check this video th-cam.com/video/bG08P9nNLcQ/w-d-xo.html And the query that I shared with you, I tested it in mysql workbench and it is working fine, I will try to make a video on this today.
nice tutorial ...!! I have diffferent CSV file and throwing me error of (Bulk load data conversion error (type mismatch or invalid character for the specified codepage)) could u please suggest
@@nareshMerugu The below code that I got from google it works for importing multiple csv files, I tested it with 2 files and it worked. Provide your folder path at this line SET @path = 'D:\Files\' And the provide your sql table name at this line set @sql = 'BULK INSERT YourTableName FROM ''' + @path + stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; go --BULK INSERT MULTIPLE FILES From a Folder drop table if exists ALLFILENAMES --a table to loop thru filenames drop table ALLFILENAMES CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255)) --some variables declare @filename varchar(255), @path varchar(255), @sql varchar(8000), @cmd varchar(1000) --get the list of files to process: SET @path = 'D:\Files\' SET @cmd = 'dir ' + @path + '*.csv /b' INSERT INTO ALLFILENAMES(WHICHFILE) EXEC Master..xp_cmdShell @cmd UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null --cursor loop declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%' open c1 fetch next from c1 into @path,@filename While @@fetch_status -1 begin --bulk insert won't take a variable name, so make a sql and execute it instead: set @sql = 'BULK INSERT YourTableName FROM ''' + @path + @filename + ''' ' + ' WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = '' '', FIRSTROW = 2 ) ' print @sql exec (@sql) fetch next from c1 into @path,@filename end close c1 deallocate c1
I have not done this kind of thing as I did not get a chance to work on SSRS reports. Also I don't have SSRS installed on my machine at the moment. Sorry for this.
Sorry I don't have any information about woocommerce store. I have a video below on loading data from xml file to sql server table using sql query th-cam.com/video/w05oX0obgp4/w-d-xo.html This is how we can import xml file to sql server table using ssis th-cam.com/video/9vWYLGsaXJc/w-d-xo.html And this the method how we can export data from sql server to xml using ssis th-cam.com/video/NuT3CpiUSrM/w-d-xo.html
It works for simple cases, but don't even think to mess with CSVs and Microsoft SQL server for data that might contain quotes or newlines - it just will break in one way or another. SQL server is not able nor export nor import ISO standard compatible CSV files.
@@learnssis Unfortunately, if using SSIS to export to CSV files, it will have the same problems with data that has quotes and newlines. There are two great articles about these issues; I cannot link them directly (youtube is aggressive against links lately), but you can find them by names: 'Comedy Limited' with SQL Server Extracting Data from (Azure) SQL Server Huge Tables in RFC 4180-Compliant CSV Files Really amusing and sad stories.
Hi Ravi, you won't be able to pass the file dynamically to Bulk insert task. I have shown other options how you can move multiple sql tables from one server to another How to transfer multiple sql tables from one server to another th-cam.com/video/YXeEZsGmJrI/w-d-xo.html How to dynamically move multiple sql tables from one server to another th-cam.com/video/-GEShPKmlTw/w-d-xo.html
![[Watch Party] RoV Pro League 2024 Winter | ( Bacon Time Vs PSG )](http://i.ytimg.com/vi/zM25REvnJW4/mqdefault.jpg)
Great tutorial, opening it in a textpad is something other tutorials skip. 10/10
Thank you Bone Apple Tea.
in an interview they have asked this question and i have struggled a lot to write this simple query. Thank you so much for you help on this Bro
No problem. You are most welcome Keerthi.
Thank you for sharing this, it a tip now
getting an error - Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'format'.
How to insert a csv file if it contains multiple empty rows ( each column of that row is empty). I want either Bulk insert move to another row if it encounter empty row or it should insert that row with null values.
I think you can handle this using data flow task, that if a column value is empty then it can convert the value to NULL.
th-cam.com/video/k7qqrtSY09o/w-d-xo.html
Great, simple explanation - thanks for this.
Thank you Peter.
Awesome worked great!!!
I was getting some errors while trying to import flat file feature due to large number of records (> 500k).
The only thing I had to modify here was the file path location. Since my sql server instance was present on a remote sever, I had to use a shared folder path.
Nice to hear Ameet that it worked for you.
Hello sir i want to perform bulk insertion but i am using SQLBindParameter and then SQLBulkOperations for executing but it's giving me error.. I tried finding you on linkdin but not found.. Will you please help me out?
Hi Priyanka, I don't have much experience working with SQLBindParameter and then SQLBulkOperations, 99% of the time I use SSIS to import data from CSV\flat files to sql server table. If you are getting an error then try to google it or you can ask chatgpt as well to see if that can help or you can post your question on stackoverflow.com they give very legitimate answers and they are very quick.
@@learnssis actually I google it I implemented various ways also, I tried chatgpt but error still arrives.. And also I tried this csv part but it's not taking my folder path accurately.. I am getting error there it's unable to find file. Can we connect and then I will show you?? Maybe you will get to know.. What I am doing wrong
@@priyankabachhav5315 Actually I don't have much experience with it thus not sure if I can help you with it, please share the screenshot of the error message and code to aqil33@gmail.com. It might be related to permission issue. Make sure that your sql services owner should have full access on the folder and file that you are trying to import.
thank you so much sir.
You are most welcome.
Is using Azure SQL Server, then file needs to be stored on Blog Storage, otherwise it will error out.
Frankly speaking I never used bulk insert query with Azure, I mostly tried with On-Premises instance.
I saw some examples online where they use C# to read data from a flat file insert the data to Azure sql server
stackoverflow.com/questions/46004119/increase-sql-bulk-copy-speed-on-azure
stackoverflow.com/questions/51686757/bulkcopy-100-million-rows-of-data-to-azure-sql-server-using-c
Hey i am getting error. I am using mysql workbench
10:40:18 BULK INSERT tripdata FROM 'D:\Sample Data\Raw data for Capital Bikeshare\202004.csv' WITH (FORMAT = 'CSV' , FIRSTROW=2 , FIELDTERMINATOR = ',' , ROWTERMINATOR = '0x0a')
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BULK INSERT tripdata FROM 'D:\Sample Data\Raw data for Capital Bikeshare\202004.' at line 1 0.047 sec
You can try this query
LOAD DATA INFILE 'D:/Files/Sellers.CSV' INTO TABLE sellers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS;
I will make a video on this very soon.
@@learnssis That also not working Because I am working in MYSQL work bench. My CSV File local in my local computer when I try to upload I getting
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.015 sec
@@kannann1492 Check this video
th-cam.com/video/bG08P9nNLcQ/w-d-xo.html
And the query that I shared with you, I tested it in mysql workbench and it is working fine, I will try to make a video on this today.
@@kannann1492 you can watch the video here how to import a csv file into mysql using a bulk insert query
th-cam.com/video/HlnmXv_kCL0/w-d-xo.html
nice tutorial ...!! I have diffferent CSV file and throwing me error of (Bulk load data conversion error (type mismatch or invalid character for the specified codepage)) could u please suggest
I would suggest you to try using Data Flow task and see if you are able to load it from there.
Thanks Abdul .but I have csv file from kaggle but having difficulties to load data from these csv file into my SQL table using SSIS
Try using data flow task and if that fails then paste the error here I will try to help
th-cam.com/video/aMhwCepVpwE/w-d-xo.html
Sir,we have import the multiple CSV files in one goo is it possible??
I think you can try using cursor and pass one file name at a time to the import sql query and see how it works.
@@learnssis tq for ur reply...but I tried same as but one column doesn't show the data it's showing only data retrieved successfully
How it is possible sir ?
@@nareshMeruguCan you ask chatgpt to write the sql query for you ? It can write the sql query for you, just give him instruction.
@@nareshMerugu
The below code that I got from google it works for importing multiple csv files, I tested it with 2 files and it worked.
Provide your folder path at this line
SET @path = 'D:\Files\'
And the provide your sql table name at this line
set @sql = 'BULK INSERT YourTableName FROM ''' + @path +
stackoverflow.com/questions/16076309/import-multiple-csv-files-to-sql-server-from-a-folder
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
go
--BULK INSERT MULTIPLE FILES From a Folder
drop table if exists ALLFILENAMES
--a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
SET @path = 'D:\Files\'
SET @cmd = 'dir ' + @path + '*.csv /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT YourTableName FROM ''' + @path + @filename + ''' '
+ ' WITH (
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''
'',
FIRSTROW = 2
) '
print @sql
exec (@sql)
fetch next from c1 into @path,@filename
end
close c1
deallocate c1
Can we bulk insert delta table like this from blob storage?
I have never tried that, you can try it out.
whats your current salary... in hand LPA?
Great video and series. Can you do a video on how to run a SSRS report with a parameter from SSIS and get the report and move to a folder?
I have not done this kind of thing as I did not get a chance to work on SSRS reports. Also I don't have SSRS installed on my machine at the moment. Sorry for this.
Do you know which tables contains the products in a woocommerce store?also do you have video for XML ?Thanks and you got a sub
Sorry I don't have any information about woocommerce store. I have a video below on loading data from xml file to sql server table using sql query
th-cam.com/video/w05oX0obgp4/w-d-xo.html
This is how we can import xml file to sql server table using ssis
th-cam.com/video/9vWYLGsaXJc/w-d-xo.html
And this the method how we can export data from sql server to xml using ssis
th-cam.com/video/NuT3CpiUSrM/w-d-xo.html
It works for simple cases, but don't even think to mess with CSVs and Microsoft SQL server for data that might contain quotes or newlines - it just will break in one way or another. SQL server is not able nor export nor import ISO standard compatible CSV files.
For complex cases, try to use SSIS which is built for large data processing.
@@learnssis Unfortunately, if using SSIS to export to CSV files, it will have the same problems with data that has quotes and newlines. There are two great articles about these issues; I cannot link them directly (youtube is aggressive against links lately), but you can find them by names:
'Comedy Limited' with SQL Server
Extracting Data from (Azure) SQL Server Huge Tables in RFC 4180-Compliant CSV Files
Really amusing and sad stories.
@@camelCased Correct. We can use Fixed width files to export data to flat files if data is huge and contains double quotes or newline characters.
how to pass a file to import dynamically?
Hi Ravi, you won't be able to pass the file dynamically to Bulk insert task. I have shown other options how you can move multiple sql tables from one server to another
How to transfer multiple sql tables from one server to another
th-cam.com/video/YXeEZsGmJrI/w-d-xo.html
How to dynamically move multiple sql tables from one server to another
th-cam.com/video/-GEShPKmlTw/w-d-xo.html
How to load multiple Excel files into SQL server by using bulk insert
How to do this in PL/SQL
If you want to do this in MySQL then you take a look at this video
th-cam.com/video/HlnmXv_kCL0/w-d-xo.html
@@learnssis Error starting at line : 6 in command -
FIELDS TERMINATED BY ','
Error report -
Unknown Command
Not working
@@Deepratan27 what is your source and destination and on which platform are you executing this query ?