Help with an import to SQL

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?

--
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

Help with aggregate query - Cant get it right!

Hi,

I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.

Nothing is normalized – everything is repeated in every record. The fields are:

customerNumber
Invoice_number
PO_number
Qty
Description
Line_number
Line_total
Freight
Tax
Invoice_date

So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines

I am writing a query to show the following

Order number, Invoice total, Date

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber

This works great - for each invoice I get the invoice number, InvoiceTotal, and Date

Then I was asked to add the PO Number – this is where I can't get it right.

When I added "PO_number" to the query, I got two lines for each invoice

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice,PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No,PO_number

Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)

Thanks

Hello my friend,

I see the problem. You want to get a field value without it causing other data to repeat. The solution is to use a function to get the one value and put this in the query like so: -

CREATE FUNCTION fn_GetPONumberByInvoice
(
@.InvoiceNumber AS VARCHAR(50) -- if it is an INT or whatever then change this line
)
RETURNS VARCHAR(80)

AS

BEGIN
DECLARE @.PONumber AS VARCHAR(80)

SET @.PONumber = (SELECT TOP 1 PO_Number FROM Invoices WHERE Invoice_Number = @.InvoiceNumber)

RETURN @.PONumber
END

Then in your query use the following: -

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, thanks for your reply.

I am not well versed in SQL, so I have a question:

How can I use the query you wrote to create a view? I did not mention that earlier (sorry), but the objective is to create a view with header information for each invoice (invoice number, date, total amount and PO number)

Hope you can help.

Again, thanks.

|||

Hello again my friend,

You should be able to do it like this: -

CREATE VIEW vw_Invoices

AS

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, THANK YOU.

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:
> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> > SQL Server 2005 Standard Edition.
> >
> > Using wizard, no problem to use SQL to import from ODBC data source. Saved
> > the DTS job to an SSIS package, unable to use with agent scheduler.
> >
> > Error is DTS_E_PRODUCTLEVELTOLOW.
> >
> > Please help why is this happening?
>
>

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?
Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?
|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:

> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
>
>

Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this?
Any help would be appreciated.
Thanks
Vishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this?
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help required in import Data into sql 2005 from excel 4.0

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.
Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


Any ideas about why is this happening?
UmerI have the exact error. Please someone help.