Sql-server – Syncing a SQL Server table with a CSV file

csvdata synchronizationsql serverssis

I have recently started a project with sensors that add data to a single CSV file as data the data. I would like to have this data stored in a SQL Server database in a relatively real-time manner. I know that CSV files can be imported but I am looking for a method that will sync data between the file and the database such that data automatically gets imported to the database as it is added to the CSV file.

I have looked around and found this post which suggests using SSIS to accomplish this. Having never used SSIS, I am not sure if it is the best method or if it even meets my requirements. Does someone have a good suggestion for how I can go about doing this and/or good resources for how to do this basic task in SSIS.

If it is relevant, I am using Microsoft SQL Server 2014 Standard edition on a Windows 7 desktop.

Best Answer

I have a partial answer - according to Microsoft's documentation it should work but I have not tested it. As I understand it you have a CSV file which is regularly updated, and you want the data to be available in a SQL Server 2014 database.

SQL Server 2012 onwards supports FILETABLE - this is a special type of table that maps onto folders and files in the file system. Each row in the filetable corresponds to an actual file, and updates to the physical files are reflected by updates to the rows in the filetable, and vice versa. See https://msdn.microsoft.com/en-us/library/ff929144(v=sql.120).aspx that describes FILETABLE. This Microsoft article further states that external to SQL Server, the filetable appears as a file share with files and directories. Another article https://technet.microsoft.com/en-us/library/gg492083(v=sql.110).aspx states you can use Windows explorer, or a command prompt to move and copy files into the filetable.

So assuming this works, you could create a SQL Server filetable that maps to the CSV file you want to load. You still have the problem of extracting the data from the filetable into a usable form in SQL Server. To do this, create a database view that retrieves the row from the filetable, converts it to a varchar(max), then uses a SQL Server split function with APPLY to split the string at line boundaries, then at column boundaries. A split function is discussed here https://stackoverflow.com/questions/20512855/split-function-by-comma-in-sql-server-2008 . SQL Server 2016 has an inbuilt string split function, although you are not using that version.

The result should be you have a database view, which gets the current state of the CSV file from the filetable (file system) and converts it to tabular form. By using the view you should always have an up to date version of the data from the CSV file.

DIsclaimer: As stated I have not tested this, so feel free to amend or delete if it's known to be incorrect.

Related Solutions

Sql-server – Doing a comparison on two csv files with no Primary Key

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by

SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;

Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, given the lack of a key on the staging table (and hopefully not on the real one - it's not clear from your question), deleting rows based on this comparison can be painful, especially with so many rows.

You can drop the staging table once you have finished.

(As far as I see, this approach will work in any RDBMS.)

Sql-server – Import errors importing CSV to SQL Server table

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes). Save your package to a file then open it in SQL Server Data Tools. Go into the data flow and open the source. In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in general). Modify it for the columns you are worried about.

All that being said you are probably(99.999%) not seeing a truncation because the column is to big to fit in a varchar(max). What is probably happening is that SSIS "thinks" your column is shorter than it is. If you go into the connection manager for your CSV file you can modify the "width" of your input columns in the advanced tab.

Best Answer

Related Solutions

Sql-server – Doing a comparison on two csv files with no Primary Key

Sql-server – Import errors importing CSV to SQL Server table

Related Question