|
hello guys, I guess my topic explaings the questions that I have... how do I remove the commas after reading a CSV file into a streamreader.
I tried using string.Split(',') but I can't assign this to a string variable because this is an array i suppose.
I'm not even sure If I should even be concerned with removing the commas from the CSV file because the goal is to read all the raw data (separeted by commas into the memory) and eventually save it to a sql server database.
Can someone please shed some light over this. I'd appreciate it!
Tina
|
|
|
|
|
the Replace method is what you need.
Why would you want to remove the commas, when they tell you where the colums are ? How can you save them to a database, if you've taken the rows and turned them into one string ? The split method is probably best, you get a string array, with each element being a column.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
Removing all commas in the string is easy:
str = str.Replace(",", string.Empty);
However, that is not what you want to do. You need the commas so that you know where one value ends and the next one begins.
If no values in the line contains commas, you can use the Split method to get the values:
string[] values = line.Split(',');
However, as CSV file may be more complicated than that. A value can be enclosed in quotation marks, and then it can contain commas or even line breaks. Example:
Id, Info, Date<br />
1, Some text, 2008-12-14<br />
2, "Some text,<br />
containing commas,<br />
and line breaks.", 2008-12-15<br />
3, "More text, mind you.", 2008-12-16
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thank you Christian and Guffa, I appreciate your quick response. You guys are right, replace will work just fine but I don't need to remove those commas that separate each value.
Now as Guff mentioned above, the CSV file could have various formats...well the one I'm dealing with looks something like the following:
343243,23423432,34234234,2343423,23434234, Name, Address, Phone, Security Level, Coverage, RTX, 123 main st, 600-333-1234, Medium, Full
where the first 5 numbers just needs to be ignored (unfortunately they are part of the CSV file and we can't ask them to remove these prior to the file coming into our folder) the second 5 words will actually become the columns headers in a database table, and the remaining five values are the actual data that is corresponding to each of these columns.
Now as I said I was able to write a simple console app that reads and displays all these values in the console window using streamreader...the challange would be to write all this stuff to a SQL server 2005 database. I mean how do i grab just the values that I want and dump them in the right spot in the tabele under the corresponding column.
Guys, can you help me out here. I appreciate your answers in advance 
Tina
|
|
|
|
|
If you know that the values are not quoted, you can simply split each line:
string[] col = line.Split(',');
In the array you can ignore index 0 to 4 as they are the first five columns. Index 5 to 9 are the field names, and index 10 to 14 are the values. You can create an sql query from the field names like this:
string query = "insert into SomeTable (["+col[5]+"],["+col[6]+"],["+col[7]+"],["+col[8]+"],["+col[9]+"]) values (@Col,@Col2,@Col3,@Col4,@Col5)";
Create parameters with the names @Col1 to @Col5 with the values from the index 10 to 14 in the array.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks for the response Guffa. Here are a few more things I should've mentioned about the data. No the values are not quoted, and secondly I have a lot of data in that file.
here's a sample again:
343243,23423432,34234234,2343423,23434234, Name, Address, Phone, Security Level, Coverage, RTX, 123 main st, 600-333-1234, Medium, Full, RTP, 456 Main st, 777-123-8900, High, Full and so forth.
where the first 5 numbers just needs to be ignored (unfortunately they are part of the CSV file and we can't ask them to remove these prior to the file coming into our folder) the second 5 words will actually become the columns headers in a database table, and the rest is the actual data that is corresponding to each of these columns and will go under separate rows.
Now as you showed me above, I see that col[5] through col[9] will work fine in order to identify the columns accurately since we have a limited number of them, and the rest of the comma separated values in the file are data values. So in other words i could pottentially have 10,000 of those data values (rows) under these column headers...which would mean I can't pass every value myself explicitly (it has to be passed automatically somehow). Can you perhaps help me with that? SHould i create a loop or something to add the values to the parameter. A sample code snippet would help tremendously!
Thank you once again.
Tina
|
|
|
|
|
Wow, who though up that twisted use of a comma separated value file? Each line should normally contain the same number of columns, and the records would be put vertically in the file, not horisontally...
Yes, you need to use a loop to get each set of values from the line:
for (int i = 10; i < col.Length; i += 5) {
' here col[i], col[i+1] ... col[i+4] contains the five values.
}
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hell dear and thanks for the speedy response!
I guess I can see that you are using i+=5 instead of i++ because I told you that there might be 5 column headers and then the data would be repeated after every 5 values? Is that why you have increments of 5?
Secondly, in regards to the line
Guffa wrote: here col[i], col[i+1] ... col[i+4] contains the five values
well even if we use i based syntax instead of actual numbers...we are still doing it manually...in other words I'd still have to provide values (@Col,@Col2,@Col3,@Col4,@Col5)"; myself, which incase of tens of thousands of line, may not be practical! I'm sure thats not what you are suggesting. I think I'm missing a piece here sorry  Can you please elaborate where am I wrong here?
Thanks
Tina
|
|
|
|
|
Tina P wrote: I guess I can see that you are using i+=5 instead of i++ because I told you that there might be 5 column headers and then the data would be repeated after every 5 values? Is that why you have increments of 5?
Exactly.
Tina P wrote: well even if we use i based syntax instead of actual numbers...we are still doing it manually...in other words I'd still have to provide values (@Col,@Col2,@Col3,@Col4,@Col5)"; myself, which incase of tens of thousands of line, may not be practical!
The names of the parameters has nothing to do with the index of the columns where you get the values. In fact you should name the parameters something more useful, like @Name,@Address,@Phone,@SecurityLevel,@Coverage. For the @Name parameter you use the value in col[i], for the @Address parameter you use the value in col[i+1], and so on.
Create the command object and the five parameter objects outside the loop. That way you only have to assign the values to the paramters and execut the command inside the loop.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks a lot Guffa, I appreciate you explaining it to me in such a great manner
I think I'm getting it. I wrote this quickly, do you think it make sense?
SqlConnection conn = new SqlConnection("blah blah");
string query = "insert into ClientTable ([" + col[5] + "],[" + col[6] + "],[" + col[7] + "],[" + col[8] + "],[" + col[9] + "]) values (@Name,@Address,@Phone,@SecurityLevel,@Coverage)";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@Name";
param.ParameterName = "@Address";
param.ParameterName = "@Phone";
param.ParameterName = "@SecurityLevel";
param.ParameterName = "@Coverage";
for (int i = 10; i < col.Length; i += 5)
{
param.Value = col[i];
param.Value = col[i+1];
param.Value = col[i+2];
param.Value = col[i+3];
param.Value = col[i+4];
cmd.Parameters.Add(param);
cmd.ExecuteReader();
}
|
|
|
|
|
You can call the Prepare method on the command after you have added the parameters, that can reduce the number of round trips to the database.
Use the ExecuteNonQuery method to execute the command when you don't want any result back.
SqlConnection conn = new SqlConnection("blah blah");
string query = "insert into ClientTable ([" + col[5] + "],[" + col[6] + "],[" + col[7] + "],[" + col[8] + "],[" + col[9] + "]) values (@Name,@Address,@Phone,@SecurityLevel,@Coverage)";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.VarChar, 100);
SqlParameter parameterAddress = new SqlParameter("@Address", SqlDbType.VarChar, 100);
SqlParameter parameterPhone = new SqlParameter("@Phone", SqlDbType.VarChar, 50);
SqlParameter parameterSecurity = new SqlParameter("@SecurityLevel", SqlDbType.VarChar, 50);
SqlParameter parameterCoverage = new SqlParameter("@Coverage", SqlDbType.VarChar, 50);
cmd.Parameters.Add(parameterName);
cmd.Parameters.Add(parameterAddress);
cmd.Parameters.Add(parameterPhone);
cmd.Parameters.Add(parameterSecurity);
cmd.Parameters.Add(parameterCoverage);
cmd.Prepare();
for (int i = 10; i < col.Length; i += 5) {
parameterName.Value = col[i];
parameterAddress.Value = col[i+1];
parameterPhone.Value = col[i+2];
parameterSecurity.Value = col[i+3];
parameterCoverage.Value = col[i+4];
cmd.ExecuteNonQuery();
}
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks a lot Guffa, I did it slightly differently but same idea.
cmd.Parameters.Add("@DATE", SqlDbType.DateTime);
for (int i = 0; i < col.Length; i += 11)
{
cmd.Parameters["@DOW"].Value = col[i+1];
etc.
cmd.ExecuteNonQuery();
}
A slighly tangential question...I notice that the column name in the table in SQL server has spaces in them
lile Fuel Location, Pin Code etc. Now it looks like in C# (or even sql server if I try to do a query without
using [] square brackets around the name, it doesnt recognize it...the question is having said that do i need
these square brackets around the Values in the query and also the place where i"m setting the value of the parameter
Is this valid?
"insert into [dbo].[MyTable] ( Name,[PIN CODE],[SERVICE CENTER]) values (@Name,@[PIN CODE],@[SERVICE CENTER])
and are the following lines valid?
cmd.Parameters.Add("@[SERVICE CENTER]", SqlDbType.NVarChar, 50);
cmd.Parameters["@[Description]"].Value = col[i+2];
p.s. my concern is the use of these brackets when the actual field in a table has space in it?
Am i correct here?
Thank in advance, you have been great!
Tina
|
|
|
|
|
Tina P wrote: the question is having said that do i need
these square brackets around the Values in the query and also the place where i"m setting the value of the parameter
You can't use square brackets around parameter names, so the parameter names can not contain spaces.
Even field names without spaces in them may need square brackets around them if they are reserved keywords, like Name.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Thanks again Guffa, yeah i renamed them to simple one word with no spaces.
Now I'm running into a bigger issue though...i feel pretty embarassed but it is what it is, I just don't have enough experience in development.
So right now for development purposes I have modified that file i'm reading the data from so that it has same number of columns with the headers in the third line.
Evantually the file will look something like this:
Blah Blah, More blah, etc
Blah Blah, More blah, etc
Blah Blah, More blah, etc
These lines above needs to be ignored. I had to change the looping structure a little bit because it was throwing an array index out of bound exception.
Here is what i have now:
for (int i = 0; i < 11; i ++)
{
while ((line = sr.ReadLine()) != null)
{
string[] col = line.Split(',');
cmd.Parameters["@Name"].Value = col[i];
cmd.Parameters["@location"].Value = col[i + 1];
cmd.Parameters["@pincode"].Value = DateTime.Now.Date;
cmd.Parameters["@Date"].Value = col[i + 3];
.
.
.
.
cmd.Parameters["@eta"].Value = col[i + 10];
cmd.ExecuteNonQuery();
}
}
As you notice in the for loop, i had to hard code the range because col array hasn't been created at this time...this may not be a good idea moving forward!
Secondly, I'm doing single increments (i=i+1) as you can see because the data is going to be in it separate column right next to each other.
Lastly, i notice the code above seems to be ignoring the very first line in the line...what do you think is deriving that in the for loop?
What is your take on this, this seems to be inserting the values in the data base ok? But I don;t think the for and while loops logic is correct? Can you please elaborate on this?
Thank you so much.
Tina
|
|
|
|
|
Oh btw, i forgot to mention...sr is an instance of the streamreader class. Thanks
Tina
|
|
|
|
|
Remove the outer loop, you have no use for it as there is only one record per line. If there were several records per line, you would have to switch the loops so that the loop reading the lines was the outer loop and the loop traversing the columns was the inner loop.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hey how is everyone doing today? This is a ASP VBS question, I wasnt sure if I should post in here or in the visual basic forum so I apologize if this is the wrong place for this question.
I am trying to create a asp page that will take the text from an input and for each letter change it into a specified image. I am creating a generator so I can make a ton of animated letter images and they will be displayed instead of regular text depending on what the user enters.
Here is what I have tried already. The problem is, it replaces the c's in the "b" <img src string so it wont display the "b" image.
I know this is because it replaces the B with the img src and then the C replaces everything in it, what I want to know how can I get it to just replace what was inputed and not what gets replaced after it is submitted you know? haha that may sound confusing if you dont know what Im saying I will try to better explain.
Thanks for the help.
<%
sGlitter = Request.Form("text")
Function Glitter(sGlitter)
sGlitter = Replace(sGlitter, "b", "<img src=http://www.icecandyeffects.com/glitter2/pink1/b.gif>")
sGlitter = Replace(sGlitter, "c", "<img src=http://www.icecandyeffects.com/glitter2/pink1/c.gif>")
Glitter = sGlitter
End Function
Response.Write Glitter(sGlitter)
%>
|
|
|
|
|
icecandy wrote: question, I wasnt sure if I should post in here or in the visual basic forum so I apologize if this is the wrong place for this question.
Neither. Outdated and dead web technologies belong in the web development forum.
Why on earth would you use ASP ? Do you hate yourself ? ASP was a nightmare.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
So does each letter have a unique image then? I would build your image string separately and not use Replace. I'm not a VB coder so here's some pseudo code:
<br />
sGlitter = Request.Form("text")<br />
imageString = ""<br />
<br />
For Each Letter ltr in sGlitter<br />
imageString = imageString & "<img src=http://www.icecandyeffects.com/glitter2/pink1/" & ltr & ".gif>"<br />
End For<br />
Sorry for the inexact code, but you get the idea???
|
|
|
|
|
I am dynamically displaying thumbnail images from a folder but under each image, I have a checkbox -this is so the user can click a checkbox for each image he/she wants to delete and then click the submit button. However, I know I can get the values of checkboxes checked (true/false) but I need the filename of the image above it so I know which image to delete. I tried assigning the filename to the id of each checkbox but I get the error:
"The ID property of a control can only be set using the ID attribute in the tag and a simple value."
My code is as follows:
<form id="form1" runat="server">
<div>
<asp:DataList ID="dlImages" runat="server" forecolor="#000000" backcolor="#ffffff" cellpadding="3" gridlines="none" width="50%">
<itemstyle font-name="tahoma,arial,sans-serif" font-size="12" backcolor="#ffffff" />
<alternatingitemstyle font-name="tahoma,arial,sans-serif" font-size="2" backcolor="#ffff00" />
<itemtemplate>
<img src="uploaded/<%# Container.DataItem %>" border="0" />
<asp:CheckBox ID="<%# Container.DataItem %>" Text="<%# Container.DataItem %>" Name="<%# Container.DataItem %>" runat="server" />
</itemtemplate>
</asp:DataList>
<input id="btnDelete" type="button" value="Delete" OnServerClick="deletePics" runat="server" />
<asp:Button ID="btnCancel" runat="server" onclick="btn_Cancel_Click"
Text="Back to Vehicles" CausesValidation="False" />
</div>
</form>
and I am binding an ArrayList of filenames on Page_Load.
Any ideas would be much appreciated.
Thanks
Lorna
|
|
|
|
|
Yes, the id can't be that complex. If your images come from a DB, use the id of the DB item.
One way or the other, you need to come up with a list of simpler values that map to your images, and use those.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
Thanks but I'm just navigating through a directory and adding each filename to an ArrayList, I then bind the list to the DataList so somehow need to get the filename. I tried assigning the filename to the Name property using Name="<%# Container.DataItem %>" but when I wiew the source I get: name="dlImages$ctl09$cb3"??
So any ideas would be appreciated.
|
|
|
|
|
Are the filenames unique ? If you use Path.GetFileName, will that be unique enough ?
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
Try Giving Id a Fixed Value Like chk1 ,and bound name and text with arraylist
|
|
|
|
|
Hi All,
I'm having a problem with a gridview that I am dynamically creating with my code behind. I have a multiline textbox for users to input data that is then displayed in the gridview. When the gridview databinds with the table, it is not displaying the new lines. I've found a lot of articles online showing how to fix this by setting HTMLEncode = "false", however because I'm not using any bound fields I don't think that applies for this situation. I'm thinking I might be able to resolve this by catching the text being read in from the file during the databind and doing a Text.Replace(Environment.NewLine, "
") which would display them correctly in the cells without actually modifying the saved text in my saved file on the server.
GridView gv1 = new GridView();
gv1.Width = 600;
gv1.DataSource = Table1;
gv1.DataBind();
gv1.HeaderRow.Height = 50;
gv1.HeaderRow.BackColor = System.Drawing.Color.Black;
gv1.HeaderRow.ForeColor = System.Drawing.Color.White;
gv1.RowStyle.Height = 100;
Can someone give me a quick example of how I could wire this up in the code behind?
Thanks!
"You're damned if you do, and you're damned if you dont" - Bart Simpson
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
