Question about normalization

I have a question about normalization
basically I have an excel file with the following columns that my boss wants to store in a small database.

Brand Name, Retail Store, Location Info

Each Retail store may have one or more address. Each Retail store handles one or more brands Some brands may NOT be available in some Locations

So basically I created the following entities.

Brand (BrandID, Brand) Company (CompanyID, Name, Type (retail, manufacturer, etc)) Cmp_Location (LocationID, Address info..., CompanyID) Location_Brand (LocationID, BrandID)

What do you guys think?

You've laid out a decent start.

I wonder if, however, it might be advantageous to Consider that a Brand may have multiple Products. Perhaps a BrandProduct table will prove useful. (And then of course, there may eventually be a need for a BrandProductsDetail table.)

The Retail Stores carry Products, and they may not carry ALL Products for a Brand.

As a minor point, I would name the Locations table something like: CompanyLocations -it will sort following the Company table in the event your project continues to grow and develop a need for more tables.

(And with large retail operations, Brand is just a sub-component of a Supplier. One Supplier may control multiple Brands.)

If you are using SQL 2005. refer to Books Online about the use of schemas. You could have a schema for Suppliers, one for Retailers, etc. That would make it both easier to use Table names that are meaningful without being complex AND keep them located together. Consider

Suppliers.Companys Suppliers.Products Suppliers.ProductDetails Suppliers.Locations Retailers.Companys Retailers.Locations Retailers.Products

Question about Exporting to Excel and Caching

If you export a report to Excel and then the data changes and you export it
again you will bring up your last export and it will not reflect the new
data. Is their away to turn this caching off?It seems like it is downloading a cached copy from the server, I think. Any
sugegstions or ideas?
"Cooper" wrote:
> If you export a report to Excel and then the data changes and you export it
> again you will bring up your last export and it will not reflect the new
> data. Is their away to turn this caching off?

Question about exporting to Excel

Hi,

I have a report with a table that has a few columns that have visibility property set to hidden and other visible columns. I would like to export the report to Excel but I need to be able to export the hidden columns as well and unhide that from Excel file generated if needed. Is this possible to do or only the visible columns can be exported?

Thank you.

Yes, toggle state is preserved and exported as Excel outlines. So you should be able to export the hidden columns and unhide them through Excel outlines. Note there are some Excel limitations on outlines, e.g. the maximum number of levels for nexted outlines is 7, and if the toggle item is not in the previous or next row/column of the toggled item, outline is disabled.|||

Hi I am having trouble with the toggle item functionality being exported to excel. Could you please expand on the point you made about the toggle item functionality being disabled "if the toggle item is not in the previous or next row/column of the toggled item" or even better provide a pointer to some documentation on this.

Thanks in advance Tony

|||

Thanks - i have figured out what i was doing wrong - the problem seems to be that i had rows that were not part of the toggled group. rows like a detail row and group footer rows ocne i removed these (they were initially there but i just made them not visible) the toggle functionality worked perfectly

cheers Tony

Question about exporting to Excel

Hi,

I have a report with a table that has a few columns that have visibility property set to hidden and other visible columns. I would like to export the report to Excel but I need to be able to export the hidden columns as well and unhide that from Excel file generated if needed. Is this possible to do or only the visible columns can be exported?

Thank you.

Yes, toggle state is preserved and exported as Excel outlines. So you should be able to export the hidden columns and unhide them through Excel outlines. Note there are some Excel limitations on outlines, e.g. the maximum number of levels for nexted outlines is 7, and if the toggle item is not in the previous or next row/column of the toggled item, outline is disabled.|||

Hi I am having trouble with the toggle item functionality being exported to excel. Could you please expand on the point you made about the toggle item functionality being disabled "if the toggle item is not in the previous or next row/column of the toggled item" or even better provide a pointer to some documentation on this.

Thanks in advance Tony

|||

Thanks - i have figured out what i was doing wrong - the problem seems to be that i had rows that were not part of the toggled group. rows like a detail row and group footer rows ocne i removed these (they were initially there but i just made them not visible) the toggle functionality worked perfectly

cheers Tony

Question about Excel source - how to modify columns?

Hi,

I have a package that uses an Excel file source. There appears to be no place to modify the column data types as you can with a flat file manager. As such, the source columns do not match the columns in the database.

I believe I must be overlooking something here.

Can someone please tell me how I can modify the Excel column datatypes?

Thanks

nevermind

found it under "advanced editor", "input and output" properties

|||

Well, I'm having a difficult time changing the datatypes on my excel columns.

The "input and output properties" allow you to change the datatypes of the "output columns", but if I try to modify the "external columns", I get an error that the source column doesn't match or something to that effect. If I just try to change the length, it gives me other errors. So it seems I can't really change these?

So I left all the "external" columns as unicode strings with the original length, and changed the "output columns" to regular strings and the floats to numerics.

But in the end, this has solved nothing because I'm still getting truncation errors. So obviously changing the "output columns" isn't enough.

I really don't think I'm doing this right! Need help.

|||Sadie,
Use a derived column or a data conversion component to change your data types. Let the Excel manager and Excel Source do their jobs. The output data type is dictated by the driver and the data in the Excel sheet.|||You need to use a derived column or data conversion transform to change the data types. Excel data is stored as unicode, and it is a conversion step to change it to ANSI. Same thing for the floats.|||

Ok, duh

makes sense... just never worked with an excel spreadsheet before

Question about deleting a spreadsheet in excel using DTS

Dear all,

i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!

Kam2Use a combination of Drop/Create Table statements within a SQL-Task before pumping data to excel.

Note the type of quote marks!!

E.g.:

DROP table `tb_Excel_Output`
GO
CREATE TABLE `tb_Excel_Output` (
`Col1` VarChar (7) ,
`Col2` VarChar (2) ,
`Col3` VarChar (3) )

kbk|||your method rocks ~!
thx for you help i'm just an newbie in DTS :D|||When I try to drop an Excel table using this method, I'm receiving a "Microsoft Jet Database Engine" error stating that I'm using invalid syntax in the drop statement.

The only SQL I'm using is:
DROP table 'x'
GO

I'm using the connection that I've created for the Excel spreadsheet.

Is there something I'm missing?

Thanks,
Steve

Question about data import in MS-SQL server

Dear All,

I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?

Thanks for your kind attention.

BennyBenny (cs_benny@.hotmail.com) writes:
> I am performing a data import on the SQL server. Due to fact
> that I use the excel file as a source. Some of cells in excel are
> actually empty, they become NULL fields after importing into the SQL
> server. Actually I want these fields are empty string instead of NULL.
> Does SQL server has any approach to make these fields to be empty
> string instead of NULL when importing?? Or is there any store
> procedure exist for converting the fields to empty string?

Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> Benny (cs_benny@.hotmail.com) writes:
> > I am performing a data import on the SQL server. Due to fact
> > that I use the excel file as a source. Some of cells in excel are
> > actually empty, they become NULL fields after importing into the SQL
> > server. Actually I want these fields are empty string instead of NULL.
> > Does SQL server has any approach to make these fields to be empty
> > string instead of NULL when importing?? Or is there any store
> > procedure exist for converting the fields to empty string?
> Since I don't know how you import the data, I can't really say what
> you could do in that end.
> Once the data is in SQL Server, you can say:
> UPDATE tbl
> SET col = ''
> WHERE col IS NULL|||Benny (cs_benny@.hotmail.com) writes:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?

Sorry, I don't use DTS so I don't know. But I want to point out the
necessity of providing people full information about what you are
doing.

If you don't get any replies, consider asking in
microsoft.public.sqlserver.dts. (Available on msnews.microsoft.com if your
ISP does not have it.)

Also check out the FAQ on http://www.sqldts.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||With DTS, you can transform the data before it gets imported. That's the
'T' in 'DTS'. When moving an Excel spreadsheet to SQL Server, there is a
place in DTS where you select the worksheet from the Excel file. At this
location, there is a Transform button. You can do simple
transformations, or you can use VB code to do more complex
transformations.

HTH,
Brain

Benny wrote:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> > Benny (cs_benny@.hotmail.com) writes:
> > > I am performing a data import on the SQL server. Due to fact
> > > that I use the excel file as a source. Some of cells in excel are
> > > actually empty, they become NULL fields after importing into the SQL
> > > server. Actually I want these fields are empty string instead of NULL.
> > > Does SQL server has any approach to make these fields to be empty
> > > string instead of NULL when importing?? Or is there any store
> > > procedure exist for converting the fields to empty string?
> > Since I don't know how you import the data, I can't really say what
> > you could do in that end.
> > Once the data is in SQL Server, you can say:
> > UPDATE tbl
> > SET col = ''
> > WHERE col IS NULL

--
================================================== =================

Brian Peasland
dba@.remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"