ASP.NET

Re: How do I remove the commas after reading a CSV file into a streamreader

15-Dec-08 19:41

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 Smile | :)

Tina

Guffa15-Dec-08 21:15

Re: How do I remove the commas after reading a CSV file into a streamreader

15-Dec-08 21:15

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.

Tina P16-Dec-08 13:40

Re: How do I remove the commas after reading a CSV file into a streamreader

16-Dec-08 13:40

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

Guffa16-Dec-08 14:29

Re: How do I remove the commas after reading a CSV file into a streamreader

16-Dec-08 14:29

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.

Tina P16-Dec-08 14:56

Re: How do I remove the commas after reading a CSV file into a streamreader

16-Dec-08 14:56

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 Frown | :(

Can you please elaborate where am I wrong here?

Thanks
Tina

Guffa16-Dec-08 15:09

Re: How do I remove the commas after reading a CSV file into a streamreader

16-Dec-08 15:09

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.

Tina P16-Dec-08 16:08

Re: How do I remove the commas after reading a CSV file into a streamreader

16-Dec-08 16:08

Thanks a lot Guffa, I appreciate you explaining it to me in such a great manner Smile | :)

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();

                       }

Guffa17-Dec-08 5:09

Re: How do I remove the commas after reading a CSV file into a streamreader

17-Dec-08 5:09

Yes, but you need to create five SqlParameter objects, and add them to the command outside the loop. Specify the data type and maximum size for each parameter.

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.

Tina P17-Dec-08 7:30

Re: How do I remove the commas after reading a CSV file into a streamreader

17-Dec-08 7:30

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

Guffa17-Dec-08 11:52

Re: How do I remove the commas after reading a CSV file into a streamreader

17-Dec-08 11:52

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.

Tina P17-Dec-08 14:10

Re: How do I remove the commas after reading a CSV file into a streamreader

17-Dec-08 14:10

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

Tina P17-Dec-08 19:37

Re: How do I remove the commas after reading a CSV file into a streamreader

17-Dec-08 19:37

Oh btw, i forgot to mention...sr is an instance of the streamreader class. Thanks

Tina

Guffa26-Dec-08 7:51

HELP! using Replace function to replace letters with images

26-Dec-08 7:51

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.

icecandy15-Dec-08 12:36

icecandy

15-Dec-08 12:36

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)
%>

Re: HELP! using Replace function to replace letters with images

Christian Graus15-Dec-08 13:19

Re: HELP! using Replace function to replace letters with images

15-Dec-08 13:19

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.

treputt15-Dec-08 14:17

treputt

15-Dec-08 14:17

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???

Assigning and retrieving values from checkboxes which are dynamically created.

Member 340288615-Dec-08 10:12

Member 3402886

15-Dec-08 10:12

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

Re: Assigning and retrieving values from checkboxes which are dynamically created.

Christian Graus15-Dec-08 10:24

Re: Assigning and retrieving values from checkboxes which are dynamically created.

15-Dec-08 10:24

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.

Member 340288615-Dec-08 11:16

Member 3402886

15-Dec-08 11:16

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.

Re: Assigning and retrieving values from checkboxes which are dynamically created.

Christian Graus15-Dec-08 11:41

Re: Assigning and retrieving values from checkboxes which are dynamically created.

15-Dec-08 11:41

Are the filenames unique ? If you use Path.GetFileName, will that be unique enough ?

Christian Graus

Driven to the arms of OSX by Vista.

Nishant Singh15-Dec-08 17:22

Nishant Singh

15-Dec-08 17:22

Try Giving Id a Fixed Value Like chk1 ,and bound name and text with arraylist

GridView databinding event handler

compninja2515-Dec-08 8:37

compninja25

15-Dec-08 8:37

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

Re: GridView databinding event handler

Christian Graus15-Dec-08 15:19