|
I'm running into a bit of a snag here;
I'm loading an excel spreadsheet using the following code
Dim Ole_connection As System.Data.OleDb.OleDbConnection
Dim Ole_command As System.Data.OleDb.OleDbDataAdapter
Dim Ole_dataset As New System.Data.DataSet()
Ole_connection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & Str_filename & ";Extended Properties=Excel 8.0;")
Ole_command = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", Ole_connection)
Ole_dataset.Clear()
Ole_connection.Open()
Ole_command.Fill(Ole_dataset)
Ole_connection.Close()
I have no control over the data that I receive (the excel spreadsheet)
it's generated by a pbx system at midnight.
The problem is the spreadsheet is not as flat as I was lead to believe, the first sample data I was given was 9 column of data;
in the real version most of the spreadsheet is like that except for the first 7 rows of the spreadsheet which is branding and titles and one or two pieces of info that I do need (dates and a group name)
Is there any way for me to figuratively force the square peg into the round hole? forcing the dataset to be 9 columns large(this is never going to change) and just shoving the 7 first rows of un-flat data wherever it may fall?
|
|
|
|
|
Would using p/invoke to open the Excel sheet as a COM object work as an alternate option for you?
|
|
|
|
|
I'm not certain, never used that before, so I am googleing it as type this;
Maybe a bit of background on what I'm doing will help
Every day our phone system gives us an excel spreadsheet of over 2000 rows, other then the aforementioned un-flat data in the 7 lines, the sheet contains;
this wont show up properly here but
-A small header
Workgroup: WG-NBA-Montreal (continued)
Username: username (Active)
Agent Name: Testing, The fish
-Titles for every column:
" Logged-in Time Frame or Reason Unavailable""Logged-in Time or Time Unavailable" | Begin Time | End Time | Originated
Logged-in Time Frame 11/5/2007 12:04:17 11/5/2007 20:25:54 08:21:37 23
Pre-Shift Unavailable 11/5/2007 12:04:17 11/5/2007 12:08:30 00:04:13
Initializing Unavailable 11/5/2007 12:04:17 11/5/2007 12:04:17 00:00:00
Break Unavailable 11/5/2007 14:44:15 11/5/2007 14:44:15 00:00:00
-several more rows for one agent
- then the header portion for the next agent and row of data for him, and so on and so forth.
This is from the sample I was given so the data is fake. but the format is right
anyway what my application does is load the sheet into a table and then I parse for each agent the various states they are in during the day and build a report that says when they are late, when they take breaks that are too short or too long based on bounds that are configured.
right now this is done manually so I'm just automating the process to shave a few hours of aggravation for the person who has to read those 2000 lines
everything was working fine with the sample data, but once those 7 lines of header came in everything got scrapped and most of what was there before is now null.
|
|
|
|
|
Since you will be opening the Excel file not as a recordset, but as a spreadsheet, you will be able to write your parser anyway you want, so I imagine your requirement should not be so difficult to implement.
Hope you got the information you needed on p/invoke?
|
|
|
|
|
It may just be my Google skills that are sub par but all I'm finding is books for sale and how to insert a spreadsheet as an object in a form.
|
|
|
|
|
I guess I should have also mentioned that you can find this as Microsoft Office Interop too... sorry 
Does this help? http://www.codeproject.com/KB/office/office_automation.aspx[^]
I know it talks about exporting to Excel (and is in C#), but the code to open an Excel file is present and a few of the Excel cell and range operations are also demonstrated...
There are also a few other Interop articles on CP, though I haven't actually read through those.
Hope this helps 
|
|
|
|
|
how do you delete empty elements ("") from an 1 D array? I have mixture of numbers (0-24) and "" characters. I want to strip out all the elements with the " char.
Thanks
EM
|
|
|
|
|
You can't delete any elements from an array. You can't change the size of an array once it's created.
You can create a list and add the items to it, like:
Dim result As New List(Of String)()
For Each s As String in someArray
If s.Length > 0 Then result.Add(s)
Next
Experience is the sum of all the mistakes you have done.
|
|
|
|
|
I have a form with an imagecontrol (2 icons) and 2 listviews. I have loaded file names and icons into the first listview control. I have set the tag property of the item in the first listview to the directory path of the file.
I need to allow the user to pick the files he/she wants from the first listview and add them to the second listview via a button. When I use the code below the icon does not appear. Furthermore I have to use the .Text property of the NewItem variable in order to add it to the second listview. My goal here is to be able to add the item to the second listview and I also need to add the tag property of the item in focus from the first lisview to the tag property of the item in the second listview.
Once the files have all been added to the second listview I load them into an array via another button. I am noticing that as I loop through the items in the secondlistview that although the variable X shows the files, both the name and tag properties are "".
Is this because I used the .TEXT property as stated above? How do I work around this?
Private Sub Button10_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
Dim NewItem As ListViewItem
NewItem = listView1.FocusedItem
NewItem.ImageIndex = 2
listView2.Items.Add(NewItem.Text)
NewItem.Tag = listView1.FocusedItem.Tag 'Transfer Tag of item in listview1 to item in listview2
listView2.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
End Sub
"***************************************************************************************
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
'Load selected files into array
Dim Y As Integer
Dim X As ListViewItem
Dim Counter As Double
Y = listView2.Items.Count
ReDim FileArray(Y-1, 1)
Counter = 0
For Each X In listView2.Items
FileArray(Counter, 0) = X.Name
FileArray(Counter, 1) = X.Tag
Counter = Counter + 1
Next
End Sub
|
|
|
|
|
To move items from one ListView to another, you don't have to create a new ListViewItem. Just remove it from one ListView and add it to the other:
For Each item As ListViewItem In SourceFilesListView.SelectedItems()
SourceFilesListView.Items.Remove(item)
SelectedFilesListView.Items.Add(item)
Next
|
|
|
|
|
Hello,
In pascal, it's possible to create a record of different data types, like:
coordinate = record <br />
x: integer; <br />
y: integer; <br />
end;
How could I create one like that in vb.net?
Thankful,
Vykintas
|
|
|
|
|
|
Thanks! Structure is just what I was looking for
Vykintas
|
|
|
|
|
Ro[CK]eT wrote: Thanks! Structure is just what I was looking for
Be careful. A structure is a value type. That means it is copied each time you assign it to something (like a parameter in a method call). If the structure is large this could become a real performance hit.
|
|
|
|
|
Good afternoon all
i have recently upgraded to SQL2005 from 2000, am using vb2003. when i develop am no longer using a wizard, especially on my database development.So now i have dropped a sql adapter on my form and follow the wizard as usual and let the adapter to generate the selected command, Updated command and deleted Command, but the adapter reports the Errors for all commands excluding the select, most of the time this will happen when the table has no primary key defined, but in my table, it is defined.
What could be the Problem.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
You cannot use any Update, Delete, or Insert commands on a DataAdapter that is tied to a result set with no Primary Key. Your table MUST have a primary key AND the SQL Select command you gave MUST return the primary key field in it's result set in order for the other commands to work.
|
|
|
|
|
hi Dave,
what you are saying its true, but my query is returning a Primary key. here is the code in my Adapter.
SELECT Primary_ID, Boundary_area, Attrib_Code, Lis_Key, Func_Key, Actual_Extent, Improved_Value, Site_Value, Purchase_price, Purchase_date, prop_id, confidence_score, fin_ls_prediction, new_lis_key, new_func_key, Tshwane_Prediction, matchflag, source, flagtype, Property_id FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key)
And the Following is my Structure of my table.
USE [Tshwane_Valuations]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Property_mass](
[Primary_ID] [int] NOT NULL,
[Property_id] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Boundary_area] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Attrib_Code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lis_Key] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Func_Key] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Roll_Remark] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_ADJ_Extent_1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_ADJ_Extent_2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Actual_Extent] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Improved_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Site_Value] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Density] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_Net_Usable_Extent] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_Total_Land_Factor] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_Points_1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[New_Points_2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Purchase_price] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Purchase_date] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[source] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[owner_type] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[prop_id] [float] NULL,
[matchflag] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[confidence_score] [float] NULL,
[Tshwane_Prediction] [float] NULL,
[fin_ls_prediction] [float] NULL,
[new_lis_key] [nvarchar](174) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[new_func_key] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[flagtype] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
[Locked] [bit] NULL,
[Lockedby] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Updatedby] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UpdateDT] [datetime] NULL,
CONSTRAINT [Property_mass$PrimaryKey] PRIMARY KEY CLUSTERED
(
[Primary_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF .
Thanks Dave
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Then you're going to have to post the exact error message you get and what the code setting up the DataAdapter looks like.
|
|
|
|
|
hi Dave here is my Error
http://nedsecurity.3host.biz/Adapter.htm[^]
thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
That's hardly a descriptive error, but...
The reason it fails is because of the parameterized WHERE clause. The SqlCommandBuilder cannot use the WHERE clause you've specified. You'll have to generate the remaining commands yourself, leaving out the parameterized WHERE clause and specifying just the Primary Key in the WHERE clause and it should work. It also doesn't work with SELECT commands that return multiple result sets and/or gets data from multiple tables at once.
This is why I don't use the Wizards to do my data layers.
|
|
|
|
|
Thanks, i need to learn doing things without a wizard, i have grown .
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Guys, I need an idea about implementing multithreading in vb.net.
Here's the scenario:
I've to bulk insert multiple (100's) of files into SQL Server. The list of files are stored in a table and there's an SSIS package that loops through the resultset and bulkloads each file. The downside to this approach is at any given time only one file is being loaded. I'd like to introduce parallelism into this approach. ( I can have multiple loops, but it breaks the workflow)
I redesigned the package to load multiple files into the db, but the structure is rigid. For example, if in a batch of 5 files loading simultaneously, if the first file were smaller and the rest very big, one connection would be idle until all have finished to exit out of the task.
To alleviate this, I thought of an approach that uses threading.
What I need:
An idea that uses a multithreading wherein there's a parent thread that loops through the list of files and assigns each file to child threads which do the bulk load. As soon as a child thread is done, it must notify the parent to give it the next file to load. The number if child threads must be user configurable.
Here are the questions:
1) Is the idea of any worth, if not, is there a better way of doing it?
2) I'm trying to use the ThreadPool class and callback delegates, is this correct? (I read from Marc's article that ThreadPool is not worth it's salt)
3) Would the Async callback method of the SQLCommand work?(I kinda figured that this is not feasible, since the callback delegate does not notify the success or failure of the query)
4) Any good articles that can goad me in the correct direction.
Requirement:
The VB.NET module must execute from within an SSIS Script task.
And it has to be in VB.NET, C# is not supported in SSIS scripts.
Hope I've been clear enough.
Thanks.
SG
Cause is effect concealed. Effect is cause revealed.
modified on Wednesday, January 02, 2008 9:16:45 AM
|
|
|
|
|
SimulationofSai wrote: I'd like to introduce parallelism into this approach.
First question: Why? What's the point of doing multiple threads? One of the biggest misconceptions about multithreading is that is make the process run faster. In general, no, it doesn't. Depending on where the processing bottlenecks are, you might see a small performance benefit, you might not.
|
|
|
|
|
Because in a server with multiple hard drives and multiple CPU's, and with the SQL Server data files located in different volumes, the I/O might get divided across different processors.
I particularly refer to I/O because the most intensive operation in a BULKLOAD is I/O.
Executing 5 BULK LOAD commands in a single SQL connection uses only one processor, but splitting it across multiple connections actually fan out to different processors and cut short the load time. I know that it can only run as fast as the fastest I/O thats taking place.
Dave Kreskowiak wrote: Depending on where the processing bottlenecks are, you might see a small performance benefit, you might not.
When I'm loading more than 250 files of 500MB each, I'd be willing to take any time gain thats available. A difference of 2 minutes gain for a couple of files might mean 2-4 hours gain for all my files.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Fair enough.
It's possible to do with threads, but I'd probably think about managing my own thread pool for this, keeping the number of threads down to the number of processors in the box. I'd even consider "numProcessors - 1" given the length of time this operation takes. This would be to keep the SQL server free to do its work for other clients.
Now, I'm no expert in SSIS for sure. I have precious little experience with it. (I think this question might be geared more for the SQL forum and not the VB.NET forum.) But, from what I've read, I'd probably create a package that works with it's own connection and file, then terminates. A second, "master" package would then launch your VB.NET code, which organizes the files to be imported, manages it's own thread pool, and launches instances of a class that executes the first SSIS package.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
