|
|
You're in the
sql server dts
group:It's me again... Excel and SQL Server issue
sql server dts:
and started working on "prettying" up the excel file. Not anything too drastic, just validating cells and the like. I just updated all my Excel files and ran the package. Now the DTS package is importing 499 records for each Excel file. Each file only has 39 test records, and there are 3 of these files. So before there was only 117 records imported, now there are 1497 records imported. I have tried removing all validations from the Excel file, but it keeps doing it. Anyone know what is going on? Thanks, Drew
Ok... I figured out the problem, I selected all the cells of the Excel file
down to Row 919, then hit Format Cells and hit General. I saved the file and then tried executing the DTS packge. This time it inserted 918 rows (keep in mind the first row are column names). So, how can I keep the Transform from inserting NULL values? Like if the whole record is NULL, then skip it... Thanks, Drew [quoted text, click to view] "Drew" <drew.laing@NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message news:emLpI0gYFHA.3380@TK2MSFTNGP10.phx.gbl... > My DTS package was working great yesterday, so I closed Enterprise Manager > and started working on "prettying" up the excel file. Not anything too > drastic, just validating cells and the like. > > I just updated all my Excel files and ran the package. Now the DTS > package is importing 499 records for each Excel file. Each file only has > 39 test records, and there are 3 of these files. So before there was only > 117 records imported, now there are 1497 records imported. > > I have tried removing all validations from the Excel file, but it keeps > doing it. > > Anyone know what is going on? > > Thanks, > Drew >
Hi Drew,
[quoted text, click to view] "Drew" wrote: > Ok... I figured out the problem, I selected all the cells of the > Excel file down to Row 919, then hit Format Cells and hit General. I > saved the file and then tried executing the DTS packge. This time it > inserted 918 rows (keep in mind the first row are column names). > > So, how can I keep the Transform from inserting NULL values? Like if > the whole record is NULL, then skip it... the Excel provider can behave a little bit strange ;-) The import continues as long as there are rows with values. But unfortunately sometimes formating is interpreted as data, resulting in import of NULL values. The only way to avoid this, regardless of formatting, is by specifying a SELECT statement for the excel data where you define a WHERE clause which column should not be NULL. Just select the query option for the source of the transformation and use the Tablename shown in table dropdown above. HTH Helge
Also
Helge has noted the Excel driver is quirky well what about this. If the Driver cannot determine the correct data type by sampling rows it will enter null values for the columns it deems to have broken the datatype. So in this case your data could be perfectly valid but the driver will NULL it because it got confused. Excel Inserts Null Values (http://www.sqldts.com/default.aspx?254) Allan [quoted text, click to view] "Helge C. Rutz" <news050424@nurfuerspam.de> wrote in message
news:news050424@nurfuerspam.de: > Hi Drew, > > "Drew" wrote: > > Ok... I figured out the problem, I selected all the cells of the > > Excel file down to Row 919, then hit Format Cells and hit General. I > > saved the file and then tried executing the DTS packge. This time it > > inserted 918 rows (keep in mind the first row are column names). > > > > So, how can I keep the Transform from inserting NULL values? Like if > > the whole record is NULL, then skip it... > > the Excel provider can behave a little bit strange ;-) > The import continues as long as there are rows with values. But unfortunately sometimes formating is interpreted as data, resulting > in import of NULL values. The only way to avoid this, regardless of formatting, is by specifying a SELECT statement for the excel > data where you define a WHERE clause which column should not be NULL. > Just select the query option for the source of the transformation and use the Tablename shown in table dropdown above. > > HTH > Helge
Hi Allan and Drew,
[quoted text, click to view] "Allan Mitchell" wrote: > Also > > Helge has noted the Excel driver is quirky well what about this. > > If the Driver cannot determine the correct data type by sampling rows > it will enter null values for the columns it deems to have broken the > datatype. So in this case your data could be perfectly valid but the > driver will NULL it because it got confused. > > > Excel Inserts Null Values > (http://www.sqldts.com/default.aspx?254) ok, lets talk about the second big problem with this jet providers, especially the excel provider. The article above gives a good understanding of the problem, but there are some more things to say ;-) Excel uses a variant datatype which allows the change of column datatype row by row, and caused by the auto type guess of excel this happens all the time. The biggest problem I found is, that if you haven't specified the datatype for a column, you can't change it afterwards without rekeying all the values that excel stored with the wrong format. The new version show them most time with a small green triangle - but the work is still the same. So always specify the format before you enter data. When you use the IMEX Option you must force the provider to identify the right datatype. The TypeGuessRows value can be between 0 and 16, where 0 will force the provider to scan the hole table. This would be easy if your files are all small. But this option is for all imports on this computer, and an excel import of a very large file with the rows being scaned twice, because of this option, meens bad performance. On the other hand, when the provider only find empty fields, it defaults to a text type. But do you want all unsure types being imported as text? 1. So to avoid all this trouble you should use excel templates with the right formating where ever possible. 2. If you have non text columns with scarce values, you should insert a dummy row at the beginning to force the right type detection. 3. csv files can also be an alternativ for excel nativ import. I think thats all for the moment, time for bed now ;-) Helge
I tried the IMEX=1 trick, to no avail. I will try the suggested query
method tommorow at work. I'll post back for future searchers... Thanks again for all your help. I searched about my problem, but only found the IMEX=1 method, and when it didn't work, I thought I would ask... Thanks, Drew [quoted text, click to view] Allan Mitchell wrote:
> Also > > Helge has noted the Excel driver is quirky well what about this. > > If the Driver cannot determine the correct data type by sampling rows it > will enter null values for the columns it deems to have broken the > datatype. So in this case your data could be perfectly valid but the > driver will NULL it because it got confused. > > > Excel Inserts Null Values > (http://www.sqldts.com/default.aspx?254) > > > Allan > > > "Helge C. Rutz" <news050424@nurfuerspam.de> wrote in message > news:news050424@nurfuerspam.de: > >> Hi Drew, >> >> "Drew" wrote: >> > Ok... I figured out the problem, I selected all the cells of the >> > Excel file down to Row 919, then hit Format Cells and hit General. I >> > saved the file and then tried executing the DTS packge. This time it >> > inserted 918 rows (keep in mind the first row are column names). >> > >> > So, how can I keep the Transform from inserting NULL values? Like if >> > the whole record is NULL, then skip it... >> >> the Excel provider can behave a little bit strange ;-) >> The import continues as long as there are rows with values. But >> unfortunately sometimes formating is interpreted as data, resulting >> in import of NULL values. The only way to avoid this, regardless of >> formatting, is by specifying a SELECT statement for the excel >> data where you define a WHERE clause which column should not be NULL. >> Just select the query option for the source of the transformation and >> use the Tablename shown in table dropdown above. >> >> HTH >> Helge >
Hi Drew,
[quoted text, click to view] "Drew" wrote: >I tried the IMEX=1 trick, to no avail. I will try the suggested query >method tommorow at work. I'll post back for future searchers... > Thanks again for all your help. I searched about my problem, but only > found the IMEX=1 method, and when it didn't work, I thought I would > ask... Thanks, the IMEX=1 trick won't help you here - that's the reason I haven't described it in the first reply - thats the solution for the second big problem ;-) When you have text files, you can easily stop the import at EOF, but with excel this is differen, because the provider must identify if there are still more rows with data. The problem arise from different type formatting in one column, because excel stores this different type information with the cell, causing the provider to interpret this cell as not empty. Try this: 1. open a new excel sheet. 2. insert some text in the first to cells/rows 3. goto cell 100 and format it with a different datatype like text or number 4. define a transform task with this source and import Now you should have imported 99 rows where 98 are NULL. So when you can't control the formatting of the excel sheet you should expect the worst and avoid this problem with a dedicated select statement.. @Allan: What about adding this information to your excel article? Helge
Got it working again now, using this statement, Thanks for the help again!
SELECT PONum, PODate, Vendor, Description, Amount, CCCObjectCode, Carryover, Complete, Credit, PriorCO FROM [CreditCardLog$] WHERE (PONum IS NOT NULL) Thanks, Drew [quoted text, click to view] "Helge C. Rutz" <news050424@nurfuerspam.de> wrote in message news:3fo46aF8o13vU1@individual.net... > Hi Drew, > > "Drew" wrote: >>I tried the IMEX=1 trick, to no avail. I will try the suggested query >>method tommorow at work. I'll post back for future searchers... >> Thanks again for all your help. I searched about my problem, but only >> found the IMEX=1 method, and when it didn't work, I thought I would >> ask... Thanks, > > the IMEX=1 trick won't help you here - that's the reason I haven't > described it in the first reply - thats the solution for the second big > problem ;-) > When you have text files, you can easily stop the import at EOF, but with > excel this is differen, because the provider must identify if there are > still more rows with data. > The problem arise from different type formatting in one column, because > excel stores this different type information with the cell, causing the > provider to interpret this cell as not empty. > > Try this: > 1. open a new excel sheet. > 2. insert some text in the first to cells/rows > 3. goto cell 100 and format it with a different datatype like text or > number > 4. define a transform task with this source and import > Now you should have imported 99 rows where 98 are NULL. > > So when you can't control the formatting of the excel sheet you should > expect the worst and avoid this problem with a dedicated select > statement.. > > @Allan: What about adding this information to your excel article? > > Helge >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |