|
I am using DTS to import data from an Excel Spreadsheet. One column, which is typed as VARCHAR(100), will import numbers but when the data in that column is formated like:
HMPG:051022:RSK:IDIGO2SPI9S-WWD_JJTXWE1G7UOTXCFZLQ:04252007:102844PM
OR
013970:MKENDE6548:MARTHA
I am getting a NULL imported in it's place.
Any ideas?
Thanks,
Bryan
|
|
|
|
|
I believe this may be an issue with your Jet Driver for Excel. What happens is that it decides within 8 records by default what type the column is. There is a registry change that will need to be made in order to overcome this, if it is the same cause that I am describing.
Solution from the link follows[^]
onedaywhen wrote: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.
ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:
Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
I had made the registry edits but had forgot the IMEX=1...THANKS!!!!
It's always the simple things that get you!
Note to anyone else having this problem:
When you are using DTS you can change the IMEX=1 by going into Disconnected Edit and adding it to the Value of the Extended Properties under your source connection.
Thanks,
Bryan
|
|
|
|
|
I am attempting to improve a current VB.NET 2.0 program that originated in VB6 with disconnected recordsets and arrays by replacing the loops that look for matches with a DataTable.Find call. This seems to work all well and good except that the current and old program both use some time of in memory storage until it is time to write the output to a file.
The in memory storage was a disconnected recordset and is now...you guessed it...a datatable. I noticed that the program memory usage with the recordset stayed around 20 K while the program with the datatable seems to increase with each record dealt with so when the program has read in 500 K records it is using 700 K memory. Why is this and is there a way for me to minimize the datatable memory impact? I want to avoid using the VB6 style.
One tidbit - I currently do not have the option to utilize a table in SQL for this.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Are you really only keeping a dataTable or do you have a dataset as well? The dataset has a lot more overhead. Here is another question. If you don't need the datatable functions then why not use a dataview? That should be more light weight and should allow you to do what you need to do. You can start with a datatable and then keep the view with dataTable.Defaultview.
Ben
|
|
|
|
|
I build the blank datatable in the beginning and add to it so I need that functionality. As for the dataviews, do they allow for finding rows?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Yes, there is a RowFilter property. When you set this property the only rows you will see are the ones that meet that filter criteria. If you want to see all the rows again set the rowfilter to "".
Hope that helps.
Ben
|
|
|
|
|
So if I want to hold 1 million records (made up number) in memory which would utilize less memory, the datatable or the dataview?
What is it about the disconnected recordset that allows it to store the exact same amount of records (1 million (made up number)) and utilizes 2.5% of the memory that the datatable utilizes?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.
Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.
Ben
|
|
|
|
|
kubben wrote: Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.
Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.
Alright, let me say that I have appreciated your help so far, I never thought to use a DataView for this purpose. The speed was reduced by 30 minutes so it is 46% faster that it was before BUT the memory usage is still unreal. The reason I say unreal is because I watched it with the disconnected recordset and it never rose much above 20,000 KB. This process now uses 850,000 KB at the late end of the process. Do you have any additional ideas of what to do to lower that other than using the disconnected recordset?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Unfortunately no. Microsoft just added a lot of features to DataSet and DataTable, that makes them big. I suppose they figure memory is cheap. By the way, have you seen the RAM memory prices on NewEgg, sorry but I was blown away the other day. Ok, back on task. Using the dataview is the best you are going to get with those structures.
Ben
|
|
|
|
|
kubben wrote: Unfortunately no. Microsoft just added a lot of features to DataSet and DataTable, that makes them big. I suppose they figure memory is cheap. By the way, have you seen the RAM memory prices on NewEgg, sorry but I was blown away the other day. Ok, back on task. Using the dataview is the best you are going to get with those structures.
Is there anything .NETificationally wrong with using the disconnected recordset to save on memory usage? The .FindRows has really cut down on the total time it takes to run so that is great because the old way uses arrays and loops until it finds a match but MAN the memory usage...and no more RAM is not the solution. 
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Personally, I wouldn't do it, but then again I don't totaly understand your whole problem and solution. There may be other options. If you create a class that meets the columns you need and if you are using .net 2.0 you could have a generic collection of that class. I would imagine that would take up less memory.
Hope that helps.
Ben
|
|
|
|
|
Hi chaps,
I currently use Data Tables / Rows for my OR Mapping, but it's a bit weak.
I've browsed a whole stack of OR tools in wikipedia, but i'm not sure which one to go with?. Could anyone give me any recomendations or their preferences?
I do have a few requirements:
Handles Nullable Types
Doesn't generated stored procs
Ideally, i just want a collection of objects that relate to each other in code, that have support classes with standard CRUD functionality but can be customised. Additionaly, the ability to specify custom functions for validation.
It's been something that has been perplexing me for a long time now.
Any advice appreciated
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
DevExpress Persistent Objects (XPO).
Castle ActiveRecord.
I've used both and they are both good - although they do require a bit of a mind-shift.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SELECT pag_id, 3 + 5 AS expr1, CASE WHEN isnull(expr1 + , 0) <> 0 THEN pag_expire ELSE 10 END AS expr2
FROM dbo.cms_page_loc
query a valid one on sql server , its work
fine in MS access.
please ignore the logic of the query, i just want to know how to validate
an evaluated expansion i.e(expr1)
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
expr1 does not exist yet. It is part of the metadata of the result set. To evaluate it in the following column definition you need to either repeat the expression that created expr1 or to evaluate it in a subquery so that it is passed up to the main query.
|
|
|
|
|
Dear All,
This is related to IList concept in .net 2.0,
I have Ilist property i.e CourseOptions,
There one storedProcedure That return result like this
ID VALue Text
1 Averages YES
2 Honor Rolls NO
4 Marking Period Totals YES
8 Class Ranks NO
16 Transcripts YES
32 PRC YES
64 Attendance NO
128 Print On Schedule NO
256 Print On Matrix NO
512 Offered Course NO
1024 Nat'l. Hon. Soc. NO
2048 No Marks NO
8192 Junior Hon. Soc. NO
I Want to get all the values of Text column.
These columns i need to bound to IList of CourseOptions I can doit
Pls help me in this
Thanks N Advance
|
|
|
|
|
shakeela wrote: please help me in this very urgent
I offer help. I don't priorities because someone said their problem is urgent. To each of us our own problems are always the most urgent. If your problem is truely urgent them I'm sure you wouldn't mind paying for a support call. However, you are here, so it obviously isn't that urgent.
You will also have to make your request clearer. I don't really understand what you want to do. Are you looking for SQL code or are you looking for how to get the data in the .NET application? Or do you already have the data in the .NET application and need to coerce it into an object that implements IList?
|
|
|
|
|
Hello,
After filling a DataTable with the DataAdapter i get no information regarding which column is the unique key one.
Basically it sets all of the column to false on the unique property of the column even if it's not so on the DataBase.
Also i go through hell to retrieve the Sql Data type of the column in the DataTable.
Anyone has an answer to that?
thanks,
Leo
|
|
|
|
|
You dont know how to do it?
all of us here allready knows.
|
|
|
|
|
Hi guys, I've got this problem, hope someone can help me out.
I've written and application and it works fine on my computer and i can connect to SQL through a Text file
connection string in the Text box : Data Source= NameOfMyDataSource;Initial Catalog= DataBaseName ;Integrated Security=True
As I said, it works fine on my computer but NOT on other computers !!!
I installed my application on another computer and also installed SQL server, then attached my database and it works fine, but when i run my progran and enter ID and Pass get this error !
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
A friend from here suggested:
By default, SQL Server blocks remote access. To get round this, log into SQL Server Configuration Manager and make sure that you have enabled Named Pipes. Then, go into the Surface Area Configuration Tool and make sure it's set for Local and/or remote connections for Named Pipes. Now, restart the SQL Server instance.
I did that but still get the same error !!! what should I do ???
Thanx
|
|
|
|
|
I am guessing that your servername is incorrect. On the machine that it is not working on, try ping the server name. If you are using something like localhost that for sure would not work on remote systems. If you can't ping the sql server, then you might have a name resolution issue which would be solved with a DNS entry on your network.
Hope that helps.
Ben
|
|
|
|
|
Hey, how can I fix this resolution issue thing ???
Mr.K
|
|
|
|
|
Can you ping the ip address or sql server name?
Ben
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
