Please try to bear with this SQL "newbie" here ;)
I am trying to figure out how to import the FCC ULS report results into a
database. I believe what I need is a JOIN statement. To see the data
for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt
(I chose this query because it's near where I live. If you want to run
your own search, the URL is
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)
(NOTE:
The FCC webpage with the query at
http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
what I am trying to do, but I'd like to keep just certain records locally
so that searches are faster and are not dependent on being on a computer
with an internet connection.)
I'll try to briefly explain what you are seeing.
The .txt file referenced above is a flat text file, pipe-delimited.
The very first field is a two-letter code denoting the table type. Some
examples are HD (Header), FR (Frequency), and EM (Emission Type).
The second field is a unique entity number.
The fourth field contains the call sign (WNDV619).
After that, the content varies significantly, based on the first field's
table type.
The FCC website lists the column headings for each table type. For
example, in a HD (Header) table, the first field is the table type, the
second is the entity number, the fourth field is the call sign, the
seventh field is some date, etc. The SQL statements (I believe there are
some syntax errors) to create all of those table types can be found at
http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql
Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
bin/wtb-datadump.pl
(This is so overwhelming...)
What I would like to do is (either in Access if possible--yeah,right--or
via a web-type query in .asp or .html) to be able to click on a call
sign, and have a new table appear with choices of table types, then to be
able to click on the table type, and have a new table appear with those
choices, etc.
Some entity numbers have more than one call sign. Some call signs have
more than one frequency (as in the example above), etc.
Is there an explanation of how to do this already? (What terminology
describes that which I am trying to accomplish? Linked tables?)
Note that with the number of table headers available (that first column,
2-letter code) there can be quite a few tables just on one entity number
and/or call sign.
Can anybody help?
--
NOTE: To reply, DELETE the obvious word in my e-mail address you need to
DELETE in order to reply.One option is to create a different format file for each table type
code. Then, using bulk insert or bcp, you can do multiple passes and
each time load a particular data file to its corresponding table.
You can also try to load the text file into a staging area. You may
then process each row in that table through a client app or from a
stored procedure.
As far as your other questions are concerned, you can check Books
Online and other documentation on primary keys, foreign keys,
normalization, etc.
TeleTech <tele_tech1212DELETE@.yahoo.com> wrote in message news:<Xns950087502A22Bteletech1212@.207.69.154.205>...
> Hi.
> Please try to bear with this SQL "newbie" here ;)
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt
> (I chose this query because it's near where I live. If you want to run
> your own search, the URL is
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp)
> (NOTE:
> The FCC webpage with the query at
> http://wireless2.fcc.gov/UlsApp/Uls...archLicense.jsp does just
> what I am trying to do, but I'd like to keep just certain records locally
> so that searches are faster and are not dependent on being on a computer
> with an internet connection.)
> I'll try to briefly explain what you are seeing.
> The .txt file referenced above is a flat text file, pipe-delimited.
> The very first field is a two-letter code denoting the table type. Some
> examples are HD (Header), FR (Frequency), and EM (Emission Type).
> The second field is a unique entity number.
> The fourth field contains the call sign (WNDV619).
> After that, the content varies significantly, based on the first field's
> table type.
> The FCC website lists the column headings for each table type. For
> example, in a HD (Header) table, the first field is the table type, the
> second is the entity number, the fourth field is the call sign, the
> seventh field is some date, etc. The SQL statements (I believe there are
> some syntax errors) to create all of those table types can be found at
> http://wireless.fcc.gov/uls/data/do...n/pa_ddef16.sql
> Further specs on the tables can be found at http://wireless.fcc.gov/cgi-
> bin/wtb-datadump.pl
> (This is so overwhelming...)
> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.
> Some entity numbers have more than one call sign. Some call signs have
> more than one frequency (as in the example above), etc.
> Is there an explanation of how to do this already? (What terminology
> describes that which I am trying to accomplish? Linked tables?)
> Note that with the number of table headers available (that first column,
> 2-letter code) there can be quite a few tables just on one entity number
> and/or call sign.
> Can anybody help?|||TeleTech (tele_tech1212DELETE@.yahoo.com) writes:
> I am trying to figure out how to import the FCC ULS report results into a
> database. I believe what I need is a JOIN statement. To see the data
> for yourself, visit http://s91748743.onlinehome.us/wndv619_example.txt
I'm afraid that importing this data is not entirely trivial matter.
The way I would do it would be to write a Perl script that read
one row at a time, splitted the data into columns, looked at the first
field to find the target table.
The reason I would use Perl is simply because this is the client language
I know best. You should be able to this in VBscript, C++ or whatever.
I don't know if DTS has some built-in support for this kind of file, but
I doubt. But since I don't know DTS, don't take it from me. The friendly
folks in microsoft.public.sqlserver.dts would know for sure.
> What I would like to do is (either in Access if possible--yeah,right--or
> via a web-type query in .asp or .html) to be able to click on a call
> sign, and have a new table appear with choices of table types, then to be
> able to click on the table type, and have a new table appear with those
> choices, etc.
I'm not really sure what sort of help you are asking for here, but it
sounds like something which is more apt for comp.databases.ms-access or
some web-programming newsgroup.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
