Delete Rows Based On Condition Of 1 Column

    Hi,


    I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.


    Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.


    Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?


    Michal

    Re: Delete Rows Based On Condition Of 1 Column


    Hallo Dave,


    While those methods your link shows work they still require the file to be opened in excel and edited in excel. When I do that the formating is of the SCV file is changed.


    The file I wish to edit is a CSV file that is designed to be used in an analyser program. Whenever I change anything using excel and then save the file in CSV format the file becomes unusable by the analyser program resulting in incorect data display.


    When I do this edit manually in Wordpad file I can delete the rows (lines) and when I save that, the analyser program has no problems.


    So there is something that excel does with the CSV that must change the formating or end of line marker or the commas or something like that that makes it useless for further work.


    Michal

    Re: Delete Rows Based On Condition Of 1 Column


    I think you will find the problem is your "analyser program" not Excel saving as CSV. Excel text formats are used globally by millions without issue.

    Re: Delete Rows Based On Condition Of 1 Column


    Dave,


    The analyser program is a comercial software, not writen by me. I agree with you however that it is probably at fault but the fact that I can edit in wordpad and not compromise the file, means that it should be possible. Perhaps not in excel but using VBA and perhaps other methods of opening and editing the file.


    Michal

    Re: Delete Rows Based On Condition Of 1 Column


    Post small samples of before and after versions of the same file. I'm fairly sure I could make the file save correcly. You may not even have to import the file into Excel, but just use Excel to parse it and create a new one.
    Also include the criteria for deletion.
    p45cal

    Re: Delete Rows Based On Condition Of 1 Column


    HAllo,


    The file as originaly produced by the program (which is also has the analyser ) is called Test
    the file that I edited by deleting the second row of data is called test changed.


    The cell that I want to base the row delete on is AJ and the value = 5
    but I would like to be abel to later add other criteria in other cells.


    I should mention I opened the CSV in excel to delete the row and did this manualy, when I do this through my program the result is the same, from all indications the problem occurs when I open the file in excel.


    Thank you

    Re: Delete Rows Based On Condition Of 1 Column


    Michal


    As a test, take your csv file before you've opened it up in Excel, and using Windows Explorer, change the file extension from .csv to .txt (ie rename the file). Then open this up in Excel. You will get the Text Import Wizard and you want to specify Delimited using a comma and you want to specify all the columns as Text (not General). Click Finish.


    This will open the file up in Excel - navigate to the column you are interested in (AJ I think) and click in AJ1 and go Data>Autofilter. Use Autofilter to only show the lines you want to delete (ie those containing "5"), and select the lines and delete them. Go Data>Autofilter to turn off autofilter and do a SaveAs to save the file (remember to change the extension type back to csv).


    Open the file up in WordPad/Notepad and verify that the format remains how you want it to be.


    If this works, then the source of the problem is Excel's interpretation of delimited csv fields using its native General number format.


    Richard

    Re: Delete Rows Based On Condition Of 1 Column


    Richard,


    I have tried the suggested method and this time the analyser program crashed all together. Normaly the problem is that the error is in the display of the results. By that I mean that results are not compiled under 1 criteria but all listed separatele so if the ocurance is say 4 then under normal operation there may be 5 instances of 4 and so the analysis is 4 5 times when the error ocurs the 4 is listed as a single instance 5 different times.


    Anyway, it may not be commas but may be spaces. I must also say that the files look to me like they are identical, in as much I cant tell the differance between them. Perhaps excel changes a column to text where a number is expected? How would I be abel to test for that ?


    Perhaps this is the answer because using the wordpad I can delete the lines without afecting the structure.




    A bit of a break through.


    I took the files that I uploaded here aand compared them in word using the non printing character view. What I found is that there are charecters missing in quite a lot of the data.


    For instance the second cell in the scv contains a number designating a day of the week. In the unaltered file it is shown as
    format Cell 1 Date, Cell 2 Day of Week and it looks like this


    17/09/2007, 2,


    in the altered file it is shown as


    17/09/2007,2,


    in the non printing characters it is actually shown as ,..2, but the dots are not fulstops like here but rather in the middle of the line.


    I would think that that may be the problem the Analyser is expecting 3characters and instead its getting 1. This may cause the problems with the way it interprets data.


    Further more it seams that the spaces are only ditched from numbers any text still looks like it retains the extra spaces.


    The question then is how do I stop excel from ditching the spaces?


    Thank You for your help


    Michal

    Re: Delete Rows Based On Condition Of 1 Column


    Examining the file in notepad shows it to be comma delimited BUT each field also has a fixed length, padded out with spaces.


    The closest I got to replicating your file structure (by bringing the file into a sheet) was by changing the extension to .txt as parsnip suggested and opening it in Excel, choosing ',' as the delimiter (a comma), making sure all columns were imported as Text. Then I deleted a few rows and used Save As to save the file as a csv file, (the first csv option in the list).


    The saved file might be usable in your Analyser, but the differences included:
    1. Where your original file had no data beyond the MYCALC field it would have nothing. In the saved file there was a string of commas one per missing field datum.
    2. There was an extra field on most lines of the new file where there wasn't one on the original file. Further investigation reveals that your original file's header has an extra comma with no field heading.
    So I reckon only a 40% chance of it being readable and it would need verifying even if it were readable. However, this method would be the easiest for you to delete the lines you need to delete as you can use all the facilities available to you that a spreadsheet offers to help in that. Another difficulty encountered was writing a macro to automate the importing to a sheet; there are so many fields per record that Excel complained that the lines of code were too long or used "too many line continuations", it crashed Excel a couple of times. So I doubt I could do much to automate the process using this method.


    I had much better results with just using Excel to read the original file one line at a time and write that line to a new file if it didn't match your criterion. The resultant files were identical to the original, bar the skipped rows. Now to hope that your Analyser doesn't throw a wobbly if there are fewer lines than it expects in a file, otherwise I'm more than 99% sure it will be readable.


    Could you run this macro for me and test the resultant file in the Analyser? If it likes it, I/you can develop it further, principally to make it more user fiendly (oops, friendly). You'll have to change the lines with the file names to suit your folder structure and file names. The bit which skips the writing of the lines you want skipped is:

    Code
    If Val(Flds(35)) <> 5 Then


    p45cal

    Re: Delete Rows Based On Condition Of 1 Column


    Hallo P45cal,


    RESOLVED !!!!


    Thank you so much for your time, I have spent months on this problem but it was not until here that the real problem was diagnosed, and your solution is just perfect. The CSV file is a fickly thing with the commas and spaces and date problems that occurs when excel opens it and tries to deal with the content.


    I can easily place this into my program.


    All I have to do is then rename the original file and then name the newly created file as the original and the analysis program runs without any problems.


    Thank you for such an elegant and simple solution. It is fast as well. Perfect.


    Michal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login