Upload text files stored on FTP to Database

H

Hogrod

Expert Member
Joined
Mar 24, 2005
Messages
1,869
Reaction score
6
Hi,

I have a 250mb text delimited file on a remote FTP server that has been uploaded there recently. I want to now transfer the data in the text file to a remote SQL database.
Do you know a way to do this?

The scenario is I have a website that has a large data file uploaded to it every so often. Once the file is uploaded via FTP, I then want the data in the text file to be put into a table in the MSSQL database on the web server.

I would imagine I'd need some kind of script (vb or c#.net) to do this
 
Option 1 :
Most databases have some way to execute SQL files. Well at least all the one's I've worked and played with on Linux (Oracle, PostgreSQL and MySQL).
All you need to do is format the data so that it's in an SQL statement (typically an insert).
If it's comma delimited then it's a piece of cake to write a script to format it.
Python's text handling functions would do the trick nicely.
This is the ultra simple route.

Option 2 :
Write a loader that inserts the data into the DB directly from the text file.
You can use Python for this as well.
The nice thing about this approach is that you can add a lot of error checking and handle things gracefully. Things like constraints and key violations.

You mentioned vb or c#.net which I suppose will work too (I have no experience with them since I don't mess with proprietary MS stuff).
Python is Open Source and cross platform. Pick which ever tool you're most comfortable with.

Stop press!
Did you mean you want to store the FILE into the DB or just the text contents into the DB?
If you want to store the file into the DB (single row) then you'll be wanting to look at BLOBs (Binary Large Objects).
In that case it would be better to store the path to the file and not the file itself.
If you want to store the records contained in the text file into a table (lots of records) then ignore my BLOB comment.
 
Last edited:
You should be able to use import or link commands to load the file directly off the FTP server, provided the database has access to the site.
 
Why not use DTS or SSIS if you are running MSSQL ?
 
Why not use DTS or SSIS if you are running MSSQL ?
Click to expand...

Yea, on MS SQL Server, you would typcially go into Enterprise Manager/Sql Server Management Studio , click on a database -> Tasks -> Import Data , and then you browse to the text file etc etc....
 
The problem is the fact that I want to transfer data from a remote FTP server to a remote SQL database. This means you should not think along the lines of using Enterprise Manger, or DTS. These options are not viable, therefore I'd like to know if there is a way to do this without the use of these tools?

The answer from JayT is along what I'm after but could you provide more detail on how to achieve this? I don't understand the use of import or link commands?
 
Again just to clarify I want to transfer data inside a text file located on a remote web server to a remote sql database.
 
The GET file from FTP is exactly what I was after. Thanks very much. If anyone has other other good examples then post them here as well.
Thanks
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X