[2010?] Excel split/corrections

Sorry, I just reverted back to what I originally had.

What I originally did was replace my validate and reader with yours, not sure if that was supposed to be done.

I uploaded my source if you don't mind taking a look at it. Thats all that I have, thanks for your time.

The problem is that if you replace the , characters in the text from the file you no longer have a field seperator so there will be no way to tell which data belongs in which field or as you refer to it cell.

You would need to break the file down into fields then replace the unwanted characters within the fields, check for missing field data then rebuild the row and write it to the new file in the desired format.

The other problem is that if commas can appear in the data as well as being field seperators reading the data and deciding which commas to remove can be rather tricky.

One easy way to get around most of these issues is to open the file with Excel and save it as a TAB delimited text file then read the entire file into memory, use replace as needed to replace unwanted characters, then split to place the rows into an array and then loop through the array splitting each row into fields so you can check the data. If the data is to be saved you then rebuild the row in the desired output format and write to a file. When the number of rows written reach the desired number you would change to the next file name and continue until all the rows have been processed.

My mistake, I don't need to replace commas since all the CSV files I work with will not have them as thats what splits the raw data into separate cells (since you already know this) I was just using it as a example of what I need help with.

See my problem is, I spend hours day in and day out going through CSV files using the replace command in excel and then scrolling through thousands of rows deleting them, I was hoping to take my split tool and add these two features into it doing all my hard work.

It would be great too if I did get that far in the process of adding those features have some kind of log file generate of what rows were deleted since I deal with medical records all day but that is just something I thought of as a 3rd feature. I am unfortunately not too skilled with .net let alone any other languages either. I am still very new a starting school, I was really hoping for some assistance, only if someone had the time to spare though.

Ok Then if you do not need to replace the commas it gets a lot easier.

Something like this will allow you to read an entire file, replace characters then break it down into rows then fields, check the data and optionally write to output file.

Code:

---

        Dim SR As New System.IO.StreamReader("c:\temp\test.csv")
        Dim FileData As String = SR.ReadToEnd
        SR.Close()
        FileData = FileData.Replace(Chr(10), "")
        FileData = FileData.Replace(Chr(34), "")
        FileData = FileData.Replace("'", "")
        Dim FileRows() As String = FileData.Split(Chr(13))
        Dim FileRow() As String
        Dim BlankFlag As Boolean = False
        For x As Integer = 0 To FileRows.Length - 1
            FileRow = FileRows(x).Split(",")
            For y As Integer = 0 To FileRow.Length - 1
                If FileRow(y) = "" Then
                    BlankFlag = True
                    Exit For
                End If
            Next
            If BlankFlag = False Then
                'write filerows(x) to output file
            Else
                'write the row to the log file
            End If
            BlankFlag = False
        Next

---

btw the code above is only for example of how it may be done. You will need to add code to actually write to the output and log files where I have commented on the matter.

So I tried using what you came up with, I am still only able to split the file and it isn't giving me any kind of error yet it's still not replacing or removing rows :\

I have no idea how you tried to use it but the code does do the replace without issue. There is no code there to write any data to file, you must add that. Rather than just pasting it in and hoping it works you should read it and especially take note of where I added the comments about writing the data.

Oh I didn't try pasting it in lol
Just took your idea's and tried using them but didn't get any results.

Well, NOT showing what you tried isn't going to help you...

Well I know I completely messed it up so I didn't post it. I honestly cant figure out what to do at this point even with suggestions. I just need to probably drop this and learn a bit more to be honest.