Reading In And Performing Calculations To Columns Of A CSV File
Apr 25, 2012
I haven't used VB in 2 years and have forgetten way too much, but I still understand quite a bit. I just can't synthesize it.
Anyways, here is what I am struggling to do.
I have a CSV file as formatted in the screenshot at this link:[URL]
Number of rows can change but the number of columns won't.
I need to:
1:read in a csv file
2:take the second column save the first and last number as a variables for computations (these numbers will be the smallest and largest numbers, respectively if that makes it easier to program)
3:put into the first column of a new array the following computations based on the 2nd column from the csv file computation=((array value-first number)/(last number-first number)*100)
4:add to first column the same computations again but +100 i.e. if the first row had a cell that had computed to 1 then the next number would be 101
5:add to first column again the same computations again but with an addition +100 (+200 total) this should cause the column to have numbers between 0 and 300
6:write column 3 of array to 2nd column of the new array
7:write column 4 of array to the end of 2nd column of the new array
8:write column 5 of array to the end of 2nd column of the new array
9:delete excess array space
10:write new array to txt file with delimiter of " " (a tab) with the same name as the previous file
I have been looking at this for 6 hours now and although I figured out how to read in a csv file, I was not able to figure out how to call up the individual values for computations.
I won't complain if you give me code but what I really want is an understanding of what I need to do to accomplish this.
View 1 Replies
ADVERTISEMENT
May 24, 2010
I hope this is the right forum. I've been developing something for a while now, and I've hit some walls with the direction I was going. I'm not an expert in .Net, so I was hoping maybe there's a nice solution that I had no idea of its existance.
A little bit of background: I'm building a relatively large scale calculation component which used to be in a spreadsheet. It composes of about 1300 values, about 150 which are passed in to the component, and the remaining 1150 are calculated based on these inputs. However, these values can be in multiple rows, as in there will be multiple rows of these 1300 values. Aggregate calculations need to be performed on these rows on a regular basis. Right now I'm using a dataset to perform this since it supports multi-row calculations nicely, but it has its limitations. It doesn't support some of the mathematical functions that I need it to perform (such as exponential) and performing a calculation like a weighted average is a hassle because you need to add a new field for it. Furthermore it doesn't let you use custom variables in your expressions, so if you want to create an on/off switch for something you need to create a single row table and set up a relationship to it. Also I'm getting tired of the dataset designer being riddled with bugs, and it's obvious to me that it was not meant for calculations of this magnitude. Not being able to see the data type of the field in the designer is a minus too (you have to bring up the property window on that field). I looked in to just creating classes to perform this, but the problem is that it needs to be relatively dynamic, and the calculations need to refresh based on user inputs. The best way I can think of doing this is setting up the 1150 calculated variables as properties so that they will be calculated automatically. But, I'm afraid that this is going to fire off thousands of calculations each time any variable is accessed, which is going to be a rather significant performance hit.
So, my question is whether anyone knows of a good structure or component that I could use to achieve my goals. I apologize if I didn't do a very good job explaining my situation, so I'll try to elaborate on any of my explanations if anything is unclear.
View 5 Replies
Sep 1, 2011
im usinf Gembox to read Excel files.I'm copying the fields to a datatable, so i have to add the columns to the datatable first.
Therefor im using this code:
For i As Integer = 0 To objWorksheet.Columns.Count - 1
objDataTable.Columns.Add(i, GetType(ExcelCell))
Next
But objWorksheet.Columns.Count is 0 even if theres data in 4 columns.
View 1 Replies
May 17, 2010
how to read from the first column but I need to read from both of them. I am using full row select which I need on there.Here is the code I am using to get it for the first column.
Dim I As Integer
For I = 0 To ListView1.SelectedItems.Count - 1
MsgBox(ListView1.SelectedItems(I).Text)
Next
View 2 Replies
Aug 12, 2009
I am reading XLS files, via JET provider, using the following
Dim conString As String = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + fileName + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""
cn = New OleDbConnection(conString)
cn.Open()
da = New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
da.Fill(dt)
The problem I am facing now is that some of these excel files have hidden columns: These columns are meant to be ignored. However, the code above fills my datatable with ALL columns (hidden and nothidden). Is there any "Extended Property" that I could add to the connection string, to ignore hidden columns?
View 2 Replies
Sep 27, 2009
Reading Excel in .NET - some columns are read in as null
View 10 Replies
Sep 5, 2011
In my application I read data (using data binding) from an Excel spreadsheet into a DataGridView. This works, but the data columns contain text fields (strings). I would like to set the data type for some columns to "Date" and Decimal. I cant do this on the DataGridView (error: cant change properties for data-bound stuff) and I cant figure out how to do it on the dataset or data-table that I bind to the DataGridView.The following is the code I use to read and bind the data:
Using con As OleDbConnection = New OleDbConnection(cs)
'
'open the OleDbConnection
[code]....
The commented out code (below the 'Todo: comment) causes a additional table, but I want to change the table with the data.
View 2 Replies
Apr 22, 2011
I am trying to access a txt flie in my project. This file contains many records that I wish to retrieve individually to make further calculations with.My first problem is how to connect to the file in my project, I have no clue howw to do this, have tried many different ways, but still no success.I also wist to have a textbox that I can type the (airport name) so that the streamreader will search for that record, similar to a google type search.
View 11 Replies
Jun 16, 2011
I found a great way to get the equations of calculations from application to Microsoft word file with MimeTex here-[URL]
But unfortunelly the code is only for Microsoft Visual Studio Solution under the Eq2ImgWinForms folder, but I use on my application Visual Basic language (Visual Basic 2008 Express Edition).
how could I use the same thing on my application with Visual Basic?
View 2 Replies
Oct 21, 2010
I am using Streamreader to read a text file containing data such as the following:
IN;SC;PU;PU;SP1;LT;VS10
IN;SC;PU;PU;SP1;LT;VS10
PW5;PU4179,27448;PD4179,29951;PD3244,29951;PD3244,30286;PD5494,30286;PD5494,29951;PD4555,29951;PD4555,27448;PD4179,27448;
The final goal is to use the numbers next to the PU and PD characters to perform specific calculations.
Essentially, I think the code needs to do the following:
1. Read each block of characters ending in a ";"
2. If the block commences with anything other than PU or PD, discard it
3. Extract the numbers in such a way that calculations can be performed. The following is an example:
x y
PU4179,27448;
PD4179,29951;
[Code]....
Essentially, there will be hundreds of these rows and I will need to sum up the deltas so that a specific formula can be applied.
I have tried doing this a number of ways but each seem very inefficient (using text boxes to store/swap data and creating additinal text files are 2 methods I have tried) and so I'm looking for some direction.
View 3 Replies
Jan 2, 2010
I am working on a project in VB 2008 and need it to do this:Read first line from text file (using Openfile)Enter line into textbox on formDo some other codeThen Read second linefrom text fileEnter line into same text boxand loop until we have gone through text fileI am not sure how to read line by line from text file then enter it in textbox. I can open the Openfile and get the filename and everything, but I just am not sure how to read from it or enter that line into the textbox.Here is what I have, its not much but its a start:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim FileReader As StreamReader
[code]....
View 4 Replies
May 20, 2009
I've trying to do an inner join select statement where I select two fields from a table, and than all the records of a field in a second table that have the same id as the first table.The code looks as follow:
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Public Sub openDB()
[code]....
I get a an exception: "invalid bracketing of name [Vegetables Descriptions.DescID] if I take it out to make it look as follow I get a "Join expression not supported"
da = New OleDbDataAdapter("SELECT [Vegetables Descriptions.Task], [Vegetables Descriptions.Description], [TasksOcc.When] FROM [Vegetables Descriptions] INNER JOIN [TasksOcc] ON [DescID] = [DescID] WHERE [Vegetables Descriptions.VegeID] = vegeID", conn)
I tried folowing examples from the net but where unsuccessful.
View 3 Replies
May 4, 2009
I need to write data to a file, preferably in binary format, but I am unaccustomed with the concept. Where's the easiest place to get the basics? I could come here with a specific need, but I'm at the point right now where I am more willing to work within the confines of keeping it simple.
Here's what I know:
1. how to open a new file
2. how to specify the record length
3. how to close the file
Some specific questions:
Does the record length have to be constant throughout the file?
Can I read the nth record without reading the whole file?
View 8 Replies
Nov 11, 2010
I would like to perform an audit as part of a unit test that uses reflection to verify some assumptions, the basic-psuedo code for this would be as follows:
[Code]...
View 2 Replies
Jun 30, 2011
I know about VSTO/VSTA, and I also know that some VBA functionality (with .NET syntax of course) can be done in .NET. There is definitely a learning curve going from VBA to .NET without VSTO. My question though is, can everything that is done in VBA somehow be recreated in .NET without VSTO?
The reason is because I am converting my VBA application into .NET for several reasons: I want to take advantage of OO, my class libraries will be better available for use, and for fun. I wouldn't be too happy if I got so far in only to learn of some limitations.
View 1 Replies
Jun 21, 2009
If I have code like this (C# or VB.Net does not matter)
Dim e As IEnumerable(Of XElement) = _
From s In _dataSourceSettings.Elements _
Where s.Attribute("name").Value = toInsert.Attribute("name").Value
If e.Count > 0 Then
e.Remove()
End If
How do I get now new _dataSourceSettings without element that I removed?
View 1 Replies
Feb 4, 2011
I am writing a INSERT Statement which will INSERT data into a Access 97 DB.Everytime i run the Statement from my VB.NET Code i always get Error in INSERT SyntaxI have narrowed it down to one DB Field - INSERT INTO TEMP (Size) VALUES ('" & varSize & "') - I have taken all the other variable out.
Is the fieldname "Size" a reserved name or anything in Access 97 and is there a way round this so my whole statement will work including the Size part?
I have some old VB6 code which was written by someone else who is doing the same insert but instead of INSERT they have created a VB6 RecSet and set all the values then used RecSet.Update to insert the row of data. Is there a VB.NET version of RecSet and Update.
View 2 Replies
Jul 26, 2010
I am writing a program that rolls those crazy many-sided dice for use in fantasy games, and I have come to a point in my work where I am completely stumped. I am hoping that some of the more seasoned VB programmers on these forums might be able to point me in the right direction.
If this explanation gets a bit lengthy, please forgive me; I tend to be long winded. The feature I am trying to implement is that every roll result can have one or more "damage types" associated with it, such as "Fire" or "Acid". If I rolled a dice with "Fire" as the type, and the resulting dice roll was a 4, this would result in an output string of "4[Fire]". If there are more than one type, they are separated by commas like "4[Fire,Acid]".
The problem is when performing math while keeping track of these types. For addition and subtraction, there is little problem, "4[Fire]" added to "4[Acid]" is simple "8(4[Fire], 4[Acid])", while "8[Cold]" minus "4[Fire]" will leave "4[Cold]". Still with me? The problem comes into play when we multiply or divide rolls with types together. If we multiply "4[Fire]" and "4[Acid]", we get "16[Fire,Acid]" which is different than either "Fire" or "Acid" alone. Similarly, if we divide that "16[Fire,Acid]" by "4[Fire]", we are left with "4[Acid]", but if we divide by "4[Cold]" instead we will have "4[Fire,Acid]"
So how do I go about keeping track of this? Some of the dice strings my parser can handle might have dozens of damage types that might need to be figured into the total results. The result should display the total for the entire calculation, with results per damage type tracked separately. Currently I am using code that parses the string data above so that every type name is stored in an array with a integer variable associated with it for the total of that damage type. So, for the string "4[Fire,Ice]" I am storing the info in a variable that holds the 4 and an array that holds "Fire" and "Ice".
View 17 Replies
Oct 16, 2009
i'm trying to set a discount to a product between two dates / times.Is it possible to achieve it?Are there any examples
View 3 Replies
Feb 3, 2011
I have a program that has the option to create a restore point. My problem is that when you click on it to do so, the program just locks up until it's finished creating the restore point. I have put a separate form on the program and it has a Marquee Progress Bar Control which I downloaded, so the idea is.
User clicks "Create Restore Point"
frmRestore.show
Marquee Progress Bar goes on and on while the restore point is created
Restore point is done
frmRestore.hide
The thing is, when you click create restore point, the form shows and the program just locks up, onces the restore point is created the form hides again. Could this problem be solved with a Background worker? To be honest I'm not completely sure what it does, I mucked around with the controls but can't seem to find anything useful.
View 3 Replies
Nov 23, 2011
I have a program that has the option to create a restore point...My problem is that when you click on it to do so, the program just locks up until it's finished creating the restore point.
View 13 Replies
Jan 28, 2011
I would like to know about timer_tick function.I have one form(form1) with 2 buttons : button1 and save respectively. There are picturebox and a timer as well Upon form1 show, I would like to use ALT + printscreen to perform button1 click. Having said that, when button1 click, it will perform Clipboard.ContainsImage() which is in timer_click. How to ensure that I can use ALT + Printscreen and able to capture the image?
Initially this function will be activated when user press printscreen button and it will show the image captured. However I have amended to add a button to perform the same action (ALT + Printscreen) ---> perform button click..and capture screen:
[Code]...
View 5 Replies
Aug 20, 2009
I am trying to multiply timespan by an integer....
Private Sub wtothrs0_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles wtothrs0.MouseClick
Dim temp As TimeSpan = TimeSpan.Parse(ttothrs0.Text) + TimeSpan.Parse(wetothrs0.Text)
wtothrs0.Text = temp.ToString
[code]....
i gives the error mes "Input string was not in a correct format."
View 14 Replies
Mar 28, 2011
Can I perform an action when the X (CLOSE BUTTON) will be press?ex.If he pressed the X button then a msgbox will prompt and will verify his action of closing the application.
View 1 Replies
Mar 11, 2012
I have created tool which performs batch processing on files that are selected. The sub routine I have defined in the code, now I want to implement a way through which I can select actions that I want to perform and then perform the actions. For example the list has multiple actions, like Open, Close, SaveAs, Resize, Color Correct etc. I should be able to select what all I want to do on each file and then perform this actions. Someone suggested me using delegates for this, but I cant use that since all my functions have different parameters. Ither option I had is to use a nested IfElse or Switch to determine which action is selected and call function according to it, but thats to heavy I think, is there any optimized a better way to do this.???
View 2 Replies
Jul 12, 2011
I am pretty new to using LINQ and have been trying it out querying my strongly typed DataTable.
I have managed to perform a 'Select' ok but was wondering if someone can point me in the right direction to do Insert and Updates. I'm not sure if LINQ is best doing an insert at all?
My Select code is as follows:
Dim results = From myRow In _dt.AsEnumerable() _
Where myRow.Language_key = Lang And _
myRow.Section_key = SectionKey And _
[Code]....
View 1 Replies
Apr 11, 2009
im using an open source backup tool and im going to add extra functions. [Code] All in all im wondering how i can incorporate "sleep" into sleeping until the selected time, and then performing the backup.
View 1 Replies
Sep 19, 2010
I want to be able to able to read the dimensions of a TIF image without loading the entire file.
PS Using Visual Basic 2008 on Vista64.
View 11 Replies
Mar 27, 2012
I have an asynchronous task that is executing a function. The function does not contains any looping statements, but instead it is executing series of sql commands on sql server. Now, i have a button on my window that can cancel these sql operations. In other words, cancel the whole asynchronous task.
I know that this technique will require CancellationTokenSource and CancellationToken to cancel the task, but i've seen many examples on the internet and all of them are showing that the function that task is executing contains loop statements in which they are checking for IsCancellationRequested boolean property. But in my case, it is not so. My function does not have any loop statements in which i can do a check on this boolean property.
View 3 Replies
Sep 20, 2009
I have made an countdown app that is working in a normal way (added picture of it). Now when I select a number in the numeric box and click start, it starts to count down from that many minutes, but what I want is the number I put in the box *3. So if I put in 3 (points) the counter will count down 9 minutes. If it is possible I want a counter at the bottom every 3 minutes the value of the 000 at the bottom of the image multiply by 1 (+1 for every 3 mins timer1 runs).
View 8 Replies
