| Author |
Topic |
|
carrieh
Starting Member
9 Posts |
 Posted - 2006-04-14 : 15:22:56
|
| Hi all,Please, I need some help understanding what I need to do.I'm working with text files and they're too much for Access to handle.The logical conclusion is to use something more robust like SQL.I'm having trouble understanding how it would all fit together, and I'm looking for guidance.First of all, what do you think is a logical approach to this problem: I get 6 .txt files delivered to our webserver via ftp. Every night there's an update, so they overwrite. I need to be able to display that data on the web page and I thought I could use Access to do it. Well, it won't work, so then there's SQL.In my reading today, I find out that SQL isn't an environment like Access, it's a language. I'm assuming that means I can't just import all of this data into an SQL db, get it on the webserver, and then start running queries against it like I can in Access, right?If one of you more experienced users has any ideas, please share. How can I use SQL to search the data in these .txt files? Included with each text file is a .dic file containing field names, data types, and length. What is the best approach. Step by step would be wonderful as I am very new to this. I have only ever worked with SQL queries and Access db.Thank you for any help you can offer.Carrie |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-14 : 15:31:53
|
| Create your SQL Server tables according to the specifications in your .dic files. Use DTS (import/export tool available in SQL Server) to import your data from the files into the SQL tables. Write a web application to read the data from the SQL tables. ASP.NET is a popular choice for this. If you could post your .dic files and some sample data, we can better help you with this.Tara Kizeraka tduggan |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-14 : 15:33:45
|
| SQL is a language but you need a database engine to run it. As you're posting here I assume you want to use sql server (use the express or msde version if you want it free).You would import the text files into tables in the database then query these tables using SQL (same as you do with access probably).Have a look at http://www.nigelrivett.net/SQLTsql/ImportTextFiles.htmlfor the import.If you want to do the ftp from sql server seehttp://www.nigelrivett.net/FTP/s_ftp_GetFile.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
carrieh
Starting Member
9 Posts |
Posted - 2006-04-14 : 15:48:01
|
| Thank you so much for your replies and your offers to help.Ok, I follow the part about importing the data from the text files, and using the specs in the .dic file to define data. I've even pulled some files off of the webserver to play around with it a little bit on our SBS 2000 Server with SQL Server 2000.Then I get confused. After I import all the data into SQL (assuming that I get through that with no problems) what do I do with the SQL db? Do I import it to the webserver? Most everything I've been reading says I have to 'connect' to the SQL db, but I don't know where to put it. I also read that I need a 'front end' and SQL will be the 'back end'. I guess I thought (hoped) it worked like Access, just better, and I could upload it and start the querying.I also hope to have it on our webserver, because that's where the .txt files get delivered, and it would save me from downloading all of the data, importing it, and then uploading it again.Thanks, and forgive me for being so slow about this. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-14 : 16:53:23
|
| What do you want to do with it?If you are displaying static pages then you can produce them from sql server using bcp or something like that.If you want the users to interact then you will probably have to write an application.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
carrieh
Starting Member
9 Posts |
Posted - 2006-04-14 : 22:24:45
|
| I'd like to create search forms on our website for users who'd like to search area mls listings (that's what's in the .txt files), so it will be dynamic.What do I do after I import the .txt files into the SQL db? Can I just import it and start running queries, like Access? That's where I'm confused. Once the .txt files are imported, what should I do next?Thanks,Carrie |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-14 : 22:31:29
|
| You will need to write an application to interact with the users.You could use access for that (as it is a front end and database - not very good at either but can be used as a front end to sql server) but it's more usual to use asp or a .net language.In this language you can access the database usually by calling stored procedures but you could embed the sql in the application if you must.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
carrieh
Starting Member
9 Posts |
Posted - 2006-04-15 : 14:33:01
|
| If I can just use asp to interact with the db, then that would be great. So I can just import the SQL db to the webserver? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-15 : 14:58:35
|
quote:
Originally posted by carrieh...So I can just import the SQL db to the webserver?
You aren't quite getting the concept of what SQL Server is yet.With SQL Server, your application does not directly access the data. It passes SQL queries to SQL Server. SQL Server processes the query, and sends the results back to your ASP application.SQL Server is a program that runs as a service and processes requests. Your application interacts with the SQL Server program, not dirctly with the database.With Access, the Access application directly accesses the physical database file and does all the work of procerssing data. If multiple people are useing the same Access database, Access runs on each persons computer and directly queries and updates the physical database file. This is the main reason that Access is not suitable for large numbers of users.This means that your web server will have to interact with a server running the SQL database engine. For reasons I won't go into now, it is better to have SQL Server installed on a server by itself.CODO ERGO SUM |
|
|
|
carrieh
Starting Member
9 Posts |
Posted - 2006-04-16 : 21:13:19
|
| Thank you, I am beginning to understand.There is a hosting plan available at godaddy.com that says it has SQL Server 2000. Would it then be logical to assume that I can host just the db there and write the queries into a different website?I hope you read the initial reason for my wanting to use a SQL db at the beginning of this thread.Our company receives text files via ftp that contain data that I will need to import into the SQL db. Do you think it would be feasible to receive these files on the same webserver that the SQL db will reside on? I would of course have to figure out a way to import all of that data into the SQL db, whether it be through some sort of scripting or web application. The .txt files contain thousands of records. Has anybody ever done that sort of thing that could describe the process to me? - or at least let me know that I'm on the right track? More specifically, it's idx realtor data that I'm dealing with. If anybody's ever done a setup before I'd love to hear from you.Thanks for all of the replies so far. Keep them coming. |
|
|
|
|
help understand logic of moving from Access to SQL