Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I was wondering how we could ingest CSV files located on a Red Hat Linux server into SQL Server database tables.

I know we can write a stored procedure/bulk insert to read the files that are located on the same Windows server as SQL Server and update the database, but not sure how to do it when the files are present on a Linux server.

Any help would be greatly appreciated.

share|improve this question
1  
make file accessible on a network share (samba) or just copy them over? –  TZHX Aug 5 '13 at 9:52
    
There are many options depending on what kind of infrastructure you already have in place and if this is a reoccurring task. I'm going to assume this is a one-off thing and agree with @TZHX. –  MetaFight Aug 5 '13 at 10:34
    
Thanks for your kind replies. This is going to be a reoccuring task. Can you please point me to some ideas on how to do it again and again? –  user1345260 Aug 5 '13 at 10:36
    
This seems like a self answering question. If you have a csv file on a linux machine that you want to input into an sql server, then write a small program that reads the csv file and sends the data to the sql server. If this is recurring, set it up in cron. Or is there something left out of the question? –  GrandmasterB Aug 5 '13 at 16:17

1 Answer 1

The key for connecting to a MS SQL Server from arbitrary places is ODBC.

You are looking for some language that can handle reading a csv easily, and inserting the data back across ODBC.

There are several choices for ODBC. If you are a Java shop, one can go and get the jdbc for SQL Server and connect to the database that way. Have that .jar be part of your class path and connect away. This should probably be the option if you have people on high dictating technologies (java is a fairly safe one and you can always find or hire a java programmer).

Me? I'm a perl guy at heart. Crunching one data file and exporting it into another is a classic perl solution domain.

WIth perl, one would get DBI (docs) for the interface to a database layer, and then DBD::CSV (tutorial) to read the data from the csv file as if it was a database itself, and then DBD::ODBC to write out to an ODBC connection. Installing perl modules through CPAN is fairly easy (though I do admit that I say that after having done it many, many times).

This could be done with Text::CSV instead. However, there is a certain elegance in the DBD approach of writing a program to read from one arbitrary database (do any appropriate transforms on the data) and write to another arbitrary database. If done right, and you find yourself wishing to copy data from another database at some point, it becomes a fairly minor change to the code (install the appropriate DBD driver for the database and read the data.


Taking a completely different approach that bends more into the sysadmin world than the programmer domain, one could move the data from the linux machine to the windows machine on a regular basis.

  • You could put the files on a samba share and mount them from windows.
  • Similar option to above, you could make the file available on a web server and then fetch and process it on windows.
  • With the appropriate packages, one could mount a windows share on linux (something along the line of mount -t cifs -o guest,uid=client_user,gid=users //192.168.1.100/share /path_to/mount or //192.168.44.100/share /path_to/mount cifs guest,_netdev 0 0 in /etc/fstab)
  • Instead of sharing linux to windows, or windows to linux, NAS (network attached storage) is an easy way to have a single server (not linux or windows) that serves files to each.
  • Set up an ftp server on linux and have a scheduled job to pull the file from windows.
  • Have an automated job on linux (cron) ftp the file to windows (this assumes you've set up an ftp server on some windows machine).
  • Create a web app on windows that can insert a csv file into a database, call this web app via curl from windows.

These options take a greater degree of synchronization between the machines. Making the file available requires that both machines can access each other. This may be difficult if one is say, in the dmz and another is not. Having two sets of programs to do the work on different machines (copy file, process file) requires that clocks be synced reasonably and multiple sets of scheduled processes be able to run.

share|improve this answer
    
Hi Michael, Thanks a lot for your detailed answer. We use Python as a Development Language. Do you've any ideas on how to do it using Python? Also, I think it's best to mount a Windows Share on the LINUX drive and then pick the CSV files from that shared folder. Is there some documentation that I can go through on the same. –  user1345260 Aug 5 '13 at 15:35
    
From python to odbc, one uses pyodbc. import csv should give you all of the processing of files that you need. Mounting cifs on linux isn't difficult if you have the appropriate configuration. You might find it a better long term solution to go with some network attached storage (NAS) as a universal drive space for transferring files. Depending on what you want, they aren't that expensive. –  MichaelT Aug 5 '13 at 16:16
    
Michael - I do have an NFS Shared Drive. I can put all the CSV files into it. Should I be mounting this on my Windows Server for MSSQL and make my Stored Procedure pick up from that mount? Or should I be creating a Windows Share on the NFS. I'm sorry, I'm completely new to this. –  user1345260 Aug 5 '13 at 16:18
    
@user1345260 NFS is one file sharing protocol used by the unix world, you might be able to mount it (I'm really not a windows guy and couldn't say how to do it). CIFS is another that is used in the windows world. I believe it would be easier to mount a cifs share on linux. That said, it is far easier to have a NAS device that stores and shares the files with multiple protocols to make it easy for both. This approach, though moves out of the programming world and into the sysadmin world (thats another stack exchange depending on the system). –  MichaelT Aug 5 '13 at 16:23
    
Thanks. I will create a ticket in the Server Fault. I appreciate your help –  user1345260 Aug 5 '13 at 16:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.