0

In my project, I want to automatically import data from the constantly updated csv format to my SQL database. My main goal is that the relevant line can be automatically deleted after the csv format file I get.

Alpay Toprak
  • 1
  • Yes, you can. (If user that runs an app has appropriate permissions of course). What code have you written to achieve your goal? – ettudagny Dec 22 '20 at 10:38
  • DECLARE @cmd NVARCHAR(MAX) = 'xp_cmdshell ''del "C:\root\sfd_devtracker\' + deletefile + '"'''; EXEC (cmd) I tried to delete by this way but my main problem is I want to delete it after all CSV rows insert into the SQL Table – Alpay Toprak Dec 22 '20 at 10:49

1 Answers1

1

There is no direct connection between a random CSV file on your computer and an SQL database, they are separately stored copies of the data. You are safe to delete it.

If you need to automatically ingest a CSV file in to a database consider skipping the CSV file entirely and use the SQL INSERT command to insert new rows from your program.

Or construct a program to use a command line tool like watch for Linux and update the database accordingly when the CSV file is updated.

Aidan
  • 115
  • 2
  • 11
  • I want to automatically insert rows from CSV file to SQL table and I want to give some delay to insert process and then if there are not any row, SQL delete this CSV file. Is it possible? – Alpay Toprak Dec 22 '20 at 10:51
  • Also I wanna ask one more question can you make an comparison between SQL and CSV file because my server can be offline(when my server is online,still CSV file get data from HMI panel) and I want to insert these files also. – Alpay Toprak Dec 22 '20 at 10:53
  • SQL is not able to delete the file, it is a database structure, you would need a to create a program to do it automatically. – Aidan Dec 22 '20 at 11:00
  • I do not know what a HMI panel is, an SQL server runs like a program, so long as you run the program, the SQL server works, if you stop it or turn off your computer, it does not work. A CSV file is just a way of storing information. Its a flat file, like a word or text document, if you open your CSV file in notepad you will be able to see the data as comma separated values. – Aidan Dec 22 '20 at 11:02
  • Thanks a lot, I insert CSV file into SQL database but how I can do it automatically, here is my code.... I USE CSVTEST GO IF OBJECT_ID('DenCSV2') IS NOT NULL DROP TABLE DenCSV2 GO CREATE TABLE DenCSV2 ( [Tarih] VARCHAR(50), [Zaman] VARCHAR(50), [Milisaniye] VARCHAR(50), [LW-0] VARCHAR(50), [LW-1] VARCHAR(50), [LW-2] VARCHAR(50), [LW-3] VARCHAR(50) ) GO BULK INSERT DenCSV2 FROM 'C:\Users\Alpay Toprak\Desktop\Tolkar-Makina\IP_192.168.0.6\datalog\Makine-1\20201222.csv' WITH ( FIRSTROW=2, MAXERRORS=0, FIELDTERMINATOR = '","', ROWTERMINATOR = '\r' ) GO SELECT*FROM DenCSV2 – Alpay Toprak Dec 22 '20 at 11:10
  • Basically can I make a comparison between SQL and CSV file for finding uninserted rows – Alpay Toprak Dec 22 '20 at 11:39
  • Yes, but you will need to construct a program, it does not come out of the box. https://stackoverflow.com/questions/53603035/compare-mysql-with-csv-and-find-differences – Aidan Dec 22 '20 at 11:45
  • Thanks again, I will try as this. I think if I will compare timestamp values it would be enough – Alpay Toprak Dec 22 '20 at 12:24