+ Reply to Thread
Results 1 to 112 of 112

Code to remove blank rows

  1. #1
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Code to remove blank rows

    Good afternoon all

    I have removed the unnecessary files.

    Original Post:


    Attached are 11 files ( 24th June 19.z01.zip to .z11.zip " remove .zip ext to show the .z?? ext, to reassemble
    the whole the file marked as 24th_June_19.zip is the final one and reassemble into a 61+ Mb CSV file.

    What I am chasing is some code that will look at each row at cell "AH" and if Cell AH is blank remove
    that row, I have other code to run once that is sorted.

    The "Device_Retail.csv" file is over 220Mb and something like 20mil rows, so I cannot load it into excel.


    More of the zip files below they will not fit here.
    Last edited by AliGW; 06-28-2019 at 01:05 AM.
    One I have learnt over the last few months, " You are NEVER too old to learn ".
    Sometimes I forget to say " Thank You " for your assistance, so here I say " A BIG Thank You. "

  2. #2
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Part 2

    Good afternoon all

    I have removed the unnecessary files.
    Last edited by Potholes; 06-26-2019 at 06:46 AM.

  3. #3
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Part 3

    Good afternoon all

    I have removed the unnecessary files.
    Last edited by Potholes; 06-26-2019 at 06:47 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    I looked at the first file and it's full of gobbledegook. Not sure what it's supposed to contain.

    Do all the files have the same layout? If so, you could use PowerQuery to combine them and to get rid of your empty rows by filtering.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good afternoon all

    I have removed the unnecessary files.

    Original Post:



    Good Morning Ali

    You need to download all the files remove the ext .Zip on the end so you end up with .z01 etc
    through to 11 then unpack them all, this is the to get a big CSV file out there.


    You will end up with a file " Device_Details_TEST.csv that will be 61 ++Mb

    Peter
    Last edited by AliGW; 06-28-2019 at 01:06 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Yes, I know, Peter, but as I said, if I change the first file's extension to .csv and open it, it is full of gibberish.

    To be truthful, nobody is going to download all 11 files and reassemble them. There should be no need. All you ever need to provide is a SMALL representative sample of your data.

    Have you tried loading the data from the composite file into PowerQuery and filtering out the blank rows in column AH?

  7. #7
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good afternoon all

    I have removed the unnecessary files.

    Original Post:



    Hi

    I am still not up to speed as to how to use PQ as yet.

    I started by " Data/New Query/From File/From CSV/"Device_Details.csv"/import/Load to arrow/Then a load to Box/I selected Table,
    Existing worksheet, Cell $A$2,/ this brought up the side panel " Worksheet Query "/ with the counter going up also the Error count
    going up until it Fails the circle on the screen still trying to load, it loads 1.157,987 rows loaded/469,301 errors / followed a box in the
    screen say file is too big to load.

    If I am doing something wrong please advise.

    Pete
    Last edited by AliGW; 06-28-2019 at 01:07 AM.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question

    Hi !

    • I can't understand if your source data file is a text .csv why a word file is attached ?‼

    • As I'm very not sure Excel is the best tool for that but what is the final result file : a workbook, a text csv ?

  9. #9
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Mark L

    The doc sheet is displaying the error I am getting when I am trying to learn " Power Query ", and also
    trying to load a .CSV file that is 213Mil lines which is too many for the standard Excel program, hence
    Iv'e been told to use the power query.

    Peter

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Quote Originally Posted by Potholes View Post
    Hi Marc L
    Without answer as I'm not a mind reader any chance to help …

  11. #11
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    What is you knowledge of Power Query please. ???

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    First, we must clearly know what you are expecting for ‼

    Your thread title is about removing blank rows, too easy even for a beginner …
    But what a surprise once the thread is opened, the source file is not a workbook and it's very not blank rows to remove ‼
    So at this point when it's not very accurate some helpers directly move to the next thread …

    As it is very not difficult to join a reduced source csv file, just download Notepad++ for example
    if only really a text csv file is your source file, the first question I asked for …
    And we have no idea about your final file so we won't guess so we expect a complete crystal clear description of your need
    (what is the header of the data to check if blank for example) with a reduced source csv file …

    Again, if the final file is a csv file, according to the huge source file without any link with Excel so it's very not the best tool
    but why not as Excel if often the swiss knife of losts …
    Yes PowerQuery is a way like VBA is another one like any faster language like C, Python, …

  13. #13
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good afternoon all

    I have removed the unnecessary files.
    Last edited by Potholes; 06-26-2019 at 06:49 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    If you provide a SMALL representative sample workbook that isn't full of gibberish, I will walk you through the PQ steps to eliminate the blank rows (although it is as easy as loading the data and filtering out blanks, as I said before).

    If the entire file will not load into PQ, then break it down (as you have done here) and load each section into PQ separately. You can then eliminate the blank rows and then reassemble the resulting tables.

    As it stands I have no file to work with for the reasons already stated.

  15. #15
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Back the truck up a little.

    Once you have "imported" and filtered out the blanks, from your 200 million rows... What then? What are you trying to DO with the data next?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  16. #16
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good afternoon all

    I have removed the unnecessary files.

    Original Ppost:

    Hi MARK L and thank you for your response.


    I have a CSV file that is 200++ Million rows, in some of these rows there is a column that has some blank cells,
    in the particular column it contains a lot of Amateur Radio Call signs which is what I am trying to extract, also
    in this column are call signs that I don't need, I have code to extract what I need and sort them in chronological
    order.

    So what I am trying to achieve is to import this big file into Power Query ( Which Olly has advised maybe possible),
    NOW I have not been able to learn PQ in the short period that I have been introduced to, I have picked up some
    basic steps but I still need guidance on how to complete the integration of the 2.

    I have not heard of Notepad++ so I am not aware of it's functions.


    If someone was keen enough I could give them the link as to where I get the FILES associated with my primary one.

    peter
    Last edited by AliGW; 06-28-2019 at 01:08 AM.

  17. #17
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    You haven't answered my question, really. You're applying a set of filters to your source data... but THEN WHAT? What do you intend to do with the output?

  18. #18
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Once I have extracted my required data save that file as Device_Details_2.csv

    I will import that one along with the other 2 files into excel, and build a front end to assemble all information to a call sign as entered for the search.

    Hi all

    I have to get some sleep I will be back Tomorrow

    Nite Nite
    Last edited by Potholes; 06-25-2019 at 09:44 AM.

  19. #19
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    No need to do the intermediate step of saving as a CSV. Use the query without loading to a table, as a data source to blend with your "other 2 files", and build the front end around.

    I'd love you to write a clear specification of your mysterious projects - I always feel like a blind man pulling an elephant's tail, reading your threads, feeling like we're misguidedly offering advice on one specific issue, but missing the bigger picture and so not necessarily advising you well.

    Start with your true source data (not files you've already amended in any way), describe the business logic around the processing you need, describe the required output and interaction. Keep it simple and logical.

    I think I understand that you have some source data of devices / callsigns, and you want to be able to process this data so that you can query details for a single callsign / group of devices. But we really need a clear spec.

  20. #20
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Greetings all

    I have been through all of my files (.CSV) and cut them down so they can be uploaded here.

    I have created a excel sheet as guide as to what my ultimate aim is, it is in the form of a flowchart (Of sorts).

    I have zipped up the ( 5) .CSV files and uploaded plus " My Front End.xlsx".

    The client file ( Due to Australian Privacy Laws to which I have signed, Not to disclose), I have had to remove
    all names and address except for my own, and in the cells I have placed letters of the alphabet.

    Any not clear please feel free to contact me.

    Peter
    Attached Files Attached Files
    Last edited by Potholes; 06-26-2019 at 05:47 AM.

  21. #21
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Now we're getting somewhere. I can see the relationships between the source data files.

    Q: Why two client files? What's the difference between client.csv and client2.csv ?


    To confirm what your "Front End" looks like: you want to enter / choose a single call sign, and display the associated values for:

    • Authorisation Date
    • Licence No
    • Licence Type
    • Licence Category
    • Date Issued
    • Date of Effect
    • Date of Expiry
    • Licence Status
    • Licencee
    • Street
    • Suburb
    • State
    • Postcode
    • Client Type


    Is that correct?
    Last edited by Olly; 06-26-2019 at 05:21 AM.

  22. #22
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    How was my Analysis of my project. ??
    Last edited by Potholes; 06-26-2019 at 05:37 AM.

  23. #23
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Here's one simple approach.

    Create the following queries.

    Clients (connection only, do NOT load to data model):
    Please Login or Register  to view this content.

    Client Type (connection only, do NOT load to data model):
    Please Login or Register  to view this content.

    Devices (connection only, do NOT load to data model):
    Please Login or Register  to view this content.

    Licences (connection only, do NOT load to data model):
    Please Login or Register  to view this content.

    All Data (connection only, load to data model):
    Please Login or Register  to view this content.


    Create a measure in your data model (via PowerPivot, in the All Data table):
    Please Login or Register  to view this content.
    Now you can create a pivot table, with CALL_SIGN in Filters, all the other required fields in Rows. Apply a filter to row field LICENCE_NO:
    Please Login or Register  to view this content.
    We need this filter so that data is only returned if one callsign is selected - otherwise ALL rows will try to be returned.

    Format pivot table to suit.

    Does this get you close to what you were after?
    Attached Files Attached Files

  24. #24
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Olly

    Thank you for all of that It is going to keep me busy for awhile,

    How is the " Device_Details.csv ", handle the 200Mil + Rows. ??

    What version Excel did you use please. ???
    Last edited by Potholes; 06-26-2019 at 06:28 AM.

  25. #25
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Olly

    If I am not about after tomorrow I have to go in for my Bowel Cancer scans.

    Many Thanks for all you and Ali have done for me of recent.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Good luck at the hospital tomorrow, Peter.

    Post back here if you get unstuck.

  27. #27
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Hope all goes well at the hospital, mate.

    We can deal with 200+ million rows, once we have everything else working okay.... It probably makes sense to have the large Devices table separate from licence / client data, in the data model, rather than merging all rows in Power Query - but one step at a time. Let's make sure you can get this working as is, first, and understand what it's doing.

    Am working with Excel 2016 just now - should work equally well on 2019 / 365.

  28. #28
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Looking at your data more closely, it seems each callsign may have multiple licences, but each licence has only one client. Does this logic persist throughout your real data, or is it a characteristic only of your sample data? If this is the case in your real data, then not only do you have 200M+ rows of device data, you must also have 200M+ rows of Licence and Client data - is this correct?

    What is the source of the data - it must come from some form of database, and I suspect that spitting out to CSV files and querying those, is not the most efficient way of getting to this information. Would be much quicker and simpler to pass a parameterised query to the database, and retrive only the rows you are really interested in, each time, instead of dealing with 200M+ row flat files.

  29. #29
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi All

    Only the " Device_Details.csv " is the file with the Bloody large row count.

    The full " clients.csv " imported OK, but that is as far as I got tonight.

    I we liken the fact that you have only one drivers license, however you can own hundreds
    of Vehicles each with their own vehicle registration plate.

    In my case my client No is 64901, I am licensed to hold a call sign VK4OD that is my personal use
    I also own a Radio repeater station which has 2 different frequencies, VK4RLW, now under that
    call sign my 2 frequencies have separate Device listing, hence the need to exclude any call signs
    eg VK4RLW and only pickup VK4OD. In the " License_Status " tells me if my 64901 is currently
    registered for the next 12 months etc.

    I tried importing the " Device_Details.csv " 200++ lines and we got nowhere.

    One suggestion was to use Notepad++ and to do it in 1mll blocks.

    I not used it yet so I am not too sure how to get around it.

    73s

    Peter
    Last edited by Potholes; 06-26-2019 at 09:29 AM.

  30. #30
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Okay, so callsigns : licences is many : many.

    Pop the large Device Details.csv file somewhere I can get to it (e.g. OneDrive or Dropbox) and send me a link, and I'll have a play.

  31. #31
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi all

    To those concerned please advise if you were able to get the file as requested.

    Peter

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    By "those concerned", I presume you mean Olly?

  33. #33
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good Morning

    I didn't think I was on your talking point.

    Upon his request, Olly has access to my Google drive for that massive big file to play with.

    I can add you if you wish just need a email to put in the share list. ( Sent via PM)

    Pete

  34. #34
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    I've downloaded your 212Mb CSV file.

    It's only 1,178,646 rows, and after filtering out blank callsigns, it's a piddly little 264,662. The queries I gave you earlier in this thread handled it NO problem at all.

    What's the problem?!

  35. #35
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    I didn't think I was on your talking point.
    What on Earth dies that mean? I asked you to stop sending me visitor messages prompting me to look at your threads. I did not say that I would not help you.

    No, I don’t need to see the file. Olly is your man for this, but I shall be interested in the solution.
    Last edited by AliGW; 06-27-2019 at 04:32 PM.

  36. #36
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Posting cancelled see post
    Last edited by Potholes; 06-28-2019 at 06:13 AM.

  37. #37
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Code to remove blank rows

    Quote Originally Posted by Potholes View Post
    most editor's can only handle up to 1100 rows, even Notepad +++
    Totaly wrong ‼ Or unclear, whatever …
    As I edit your original 63 Mb file with Notepad++ (and with other one) the reason why I advised to use it
    and you even used it to create a reduced file …

  38. #38
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Find out why my attached screen shot is why I am not seeing at least 15k of call signs, and not 990+ rows
    As you scroll down, the other rows should appear. What you are seeing is merely a preview (see bottom right of the PQ window where it tells you this) - PQ is very efficient in this way and will not load more than it needs in the PQ editor window. However, it will perform the query on all rows. Did you try to close and load?

    EDIT: Peter - please do NOT post-edit out details from earlier posts in your threads. It makes a complete nonsense of the thread to anyone reading it for the first time. We refer to this unofficially as thread vandalism - don't do it again, please. I have reinstated your messages.
    Last edited by AliGW; 06-28-2019 at 01:11 AM.

  39. #39
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Mark L

    My sincere apologies I missed a zero on the end of that statement, Yes I am playing more and more with Notepad++
    With the 200 Mb file I am dragging so many lines out and save as a page.

    Pete

  40. #40
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Quote Originally Posted by AliGW View Post
    As you scroll down, the other rows should appear. What you are seeing is merely a preview (see bottom right of the PQ window where it tells you this) - PQ is very efficient in this way and will not load more than it needs in the PQ editor window. However, it will perform the query on all rows. Did you try to close and load?

    EDIT: Peter - please do NOT post-edit out details from earlier posts in your threads. It makes a complete nonsense of the thread to anyone reading it for the first time. We refer to this unofficially as thread vandalism - don't do it again, please. I have reinstated your messages.
    Hi Ali

    Please advise which sheet number you are referring to, if needed I will remove it, as I had not expected you or Olly
    to look again as I is close to the weekend and you may or many not be around. Sorry if I have offended anyone, it
    was not my intention.

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Sheet number???

    I am referring to your screenshot in post #36 and the comment I quoted from that thread.

  42. #42
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Pete: we've established that Power Query CAN handle, quite easily, your large CSV file. Stop mucking around manually editing stuff in Notepad++. That's a waste of time.

    So, the real question is how to apply the filter you want on the callsigns - including only VKxyy? (x = 0-9, y = A-Z), excluding VKxRyy. Is that correct?
    Last edited by Olly; 06-28-2019 at 02:57 AM.

  43. #43
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Try this update to the Devices query:

    Please Login or Register  to view this content.
    I've deliberately left the code verbose, to make it as clear as possible what it's doing.

    Now with the final grouping step (to remove duplicates), we are returning 15,279 rows.

    Edit: AliGW is quite right that only the first 1000 rows are previewed, by default, in the Power Query editor. To check the number of rows actually returned, you can use something like:

    Please Login or Register  to view this content.
    Last edited by Olly; 06-28-2019 at 03:39 AM.

  44. #44
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Another update: the query in post#43 missed the requirement to keep callsigns VKxRy - so try:

    Please Login or Register  to view this content.
    This returns 15,429 rows.

  45. #45
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Code to remove blank rows

    Quote Originally Posted by Potholes View Post
    Yes I am playing more and more with Notepad++
    I just advised for Notepad++ as it's a free tool in order to reduced the source file for an attachment here.
    Yes Notepad++ can execute some regular expressions ' to filter / edit ' data but it's easier under Power Query …

  46. #46
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    This entry removed

    Assesing new codes

    Original Post:

    Good evening one and all

    Today I used Notepad++ to breakdown a 220Mb CSV file and loaded each section in to 4 separate sheets in a XLSM file.

    What I would like some code to look down column "AH" and remove the following call signs from the description below,


    They range from " VK0 to 9" and consist of 2 or 4 letter's eg: VK_AA through to VK_ZZZ and or VK_FAAA to VK_FZZZ BUT
    excluding any call sign that will have VK_RAA to VK_RZZ, in this case also the maybe a number of call sign's like
    VK_RA to VK_RZ these are the only allowed 2 letters including the R this is the exception with the first letter
    being " R " otherwise they are allocated to the Amateur Radio Repeaters eg VK_RAA to VK_RZZ
    The above underscore's represent any number between 0 to 9.


    I think here I just need some VBA and able to nominate which sheet to action on..
    Last edited by AliGW; 06-28-2019 at 08:59 AM.

  47. #47
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Hello? Hello? Is this thing on?

  48. #48
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,518

    Re: Code to remove blank rows

    Quote Originally Posted by Olly View Post
    Hello? Hello? Is this thing on?
    That is too funny

  49. #49
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi guys

    I'm Here !!!!

    I didn't check any mail until late tonight, hence the removal of a previous posting.

    FYI I had previously changed the Device code but not the others (DOHHH), I have
    shown my current code below for the device, however I am still not running 100%
    if I enter any other call sign than mine it jucks a spack,

    As an aside there are many call signs that have "SJA " in the block, they are
    for the St Johns Ambulance, which we have nothing to do with them.

    After running the new code I still don't get 12k ++ available call signs.

    Please Login or Register  to view this content.

    Pete
    Attached Files Attached Files

  50. #50
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Column STATUS not found error tells me you're not using the same licence.csv file as the one you shared, and that the file you are trying to use has a different column structure.

    YOU HAVE TO KEEP FILE STRUCTURES THE SAME!!! If you change the layout of a source file, the queries will fail. It's simple. I can only write queries for the file structures you share. STOP CHANGING THINGS.

    Your second screenshot shows the default 1000 row preview in the Power Query Editor - as both AliGW and I previously explained. The total number of rows returned aren't displayed in the editor, only when you load the query. I showed you a query to count the rows returned. It's like you're not reading what I post, at all.

    Honestly, Pete - start listening. You're making this stuff FAR harder than it needs to be.

    Now, try the linked file - simply enter your path to your source data in the Input sheet (C:\ACMA Excel\28 th June 19 ?) then refresh all. If that doesn't work, then it can ONLY be because you've changed the structure of one of the source files.

    https://excel.solutions/potholes-fe-v1-2/

  51. #51
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Olly

    Thank you for that file

    Here is the file structure as received from Canberra, if anything has changed then it is not of my doing
    this is as todays download from Canberra.

    Please Login or Register  to view this content.
    Pete

  52. #52
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    So when you Refresh All in the file I just sent...?

  53. #53
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    What will stop the Name from appearing in Column C please.

  54. #54
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Wait. One small logical step at a time. What happens when you update the file path, then click Data > Refresh All?

  55. #55
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Peter - for the second time, please do NOT post-edit your posts to remove information. Leave it as it was originally. If you need to change information, do so in a new post to the thread.

  56. #56
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi

    I did that because I had to ask much the same question but in a different manner, so
    I didn't want to confuse anybody that may assist outside of Olly or you.

  57. #57
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    If you need to ask a different question, do so in a new post to the thread. Do NOT remove details from posts yourself.

  58. #58
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    OK lesson learnt, thank you

  59. #59
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Please answer Olly’s question in post #54.

  60. #60
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Olly

    When I run it the program shows my data OK,however if I run a query on VK4BB ( who I know very well ) this is the response

    Please Login or Register  to view this content.

  61. #61
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Message to all
    I am off to the sack now cu tomorrow

  62. #62
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Quote Originally Posted by Potholes View Post
    Olly

    When I run it the program shows my data OK,however if I run a query on VK4BB ( who I know very well ) this is the response

    Please Login or Register  to view this content.
    Yes.

    Is that not what you expected / wanted?!

    Did you update the path on the input sheet, and click Data > Refresh All ?

  63. #63
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Olly

    Yes to all your questions, still only my data OK, I looked within the
    client.csv to ensure all data structure OK...

    I was hoping once I updated the ALL paths manually tried the program,( No Joy )
    the reset the path to the download file it made no difference, it is now reset to
    look for the path on the input sheet.

    I have zipped & uploaded Client.csv up to the Google drive and your clearance to
    access & download, if you need to check the file structure.

    Pete
    Last edited by Potholes; 06-28-2019 at 09:26 PM.

  64. #64
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi all

    Until I can catch up with Olly to sort out some coding hassles not associated with this particular csv a problem, I would
    ask if anyone can look at some code for me please.

    I have a program in there are some Power Query codes in " Device _Details.csv " in a column "Call_Sign" I would like to filter out
    some unwanted data.

    They will be " VK_SJA " the underscore represent any digit between 0-9

    I could drop these lines within the coding.


    Please Login or Register  to view this content.
    OOpps my bad forgot the code....

    Peter
    Last edited by Potholes; 06-29-2019 at 04:21 AM.

  65. #65
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    We can't look at it unless you post it here.

  66. #66
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Good Morning Olly

    May I PM you off away from this topic, please.

    Pete

  67. #67
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Peter - can you please keep ALL of your queries here on the forum? Please start a new thread for a new query. As I have reminded you before, everything here should remain public for the benefit of all. Thanks.

  68. #68
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Ali

    I wanted to talk to Olly about some training methods that could be introduced.

  69. #69
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Why not discuss them here? The discussion may well be helpful to others, and there may be others who have pertinent advice.

    The private messaging system is really for messages that NEED to be private, not for discussions, which should take place publicly.

    You seem determined to keep bending the rules to suit yourself.

  70. #70
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    I don't know why but you seem hellbent on chastising me anytime I put something up here
    that may be away from the listed topic.

    As I have been advised not to PM people, but when I wish to discuss something away from topic
    that SHOULD NOT be disclosed here then it should go PM, in my case tonight it is something totally
    away from this forum, while I do have a email address for him I will not use it unless it is OK to do so.

    While I could venture to another forum for my projects I firmly believe in staying loyal to those that
    have helped educating me.


    Peter

  71. #71
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Peter - I am not hell-bent on chastising you.

    If you wish to PM someone about something PRIVATE, then you can do so without asking permission - it is up to them whether or not they respond. If someone has asked you NOT to PM them, or has deliberately turned off the PM function in their profile, then clearly you should respect their wishes.

    There have, unfortunately, been many times when you have just got things wrong here, the latest being the other day when you post-edited a whole load of posts and rendered an entire thread almost unintelligible. I am not going to ignore these issues, nor should I as a moderator. I am sorry if that means you feel hard done by. Perhaps a little more care and thought on your part might avoid these issues altogether, and then everybody would be happy.

    If you look more widely into my actions here as a moderator, you will find that I treat everybody fairly.

  72. #72
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Okay, to filter out call signs containing SJA, you just need another line in the Devices query:

    Please Login or Register  to view this content.
    This was easily done, using the Power Query editor - no code was hand written. Animation of the process to add this step: https://excel.solutions/filter_sja/

    Now, to your question about callsign VK4BB - when I refresh all, using your most recent clients.csv file, I get all the details of Mr Lloyd...

  73. #73
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Please, try to keep each thread on topic, otherwise it gets really really hard to follow, for everyone. This thread is about using power query to import, filter and combine several data sources. We're nearly finished.

  74. #74
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Olly

    I added that mod to the device OK in that dept

    However I am still not getting anybody other than myself, I just noticed that the " Licenses "
    has a exclamation mark visible and this is showing.

    Please Login or Register  to view this content.
    Is there a problem with the code below by chance.??

    Please Login or Register  to view this content.
    Any Idea what is up ???? Apart from the sky...

    Pete (
    Last edited by Potholes; 07-01-2019 at 06:17 AM.

  75. #75
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Yup! The clue is in the error message ...

    Check that the column exists - is there a trailing space in the name causing it not to be found?

  76. #76
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Ali you replied before I remembered to add the code line I suspect.

  77. #77
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    It's not the code line I'm referring to - it's the source file. That's what you need to check: PQ clearly thinks that the Status column no longer exists. Either that or you have changed the column name somewhere earlier in the code. Let's have a look at the workbook.

  78. #78
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    In the License file we have 2 columns with the word " Status " in them, these are
    columns J and K

    Please Login or Register  to view this content.

  79. #79
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Let's have a look at the workbook, otherwise it's just guesswork.

  80. #80
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    I Have NOT changed any file structure I learnt that lesson, WYSIWIG

  81. #81
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    OK - suit yourself.

  82. #82
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Ali please have a look at post 60 from Olly

  83. #83
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    In the files I have, callsign VK4BB is found on row 932,402 of file device_details.csv, and has licence_no 125671/1.
    This licence number is found on row 1,751 of file licence.csv, and has client_no 69443.
    This client number is found on row 2,355 of file client.csv, returning details for your friend in Jimboomba...

    The queries work as intended with the original licence.csv file you uploaded. As Ali says, the error message indicates that the structure of the licence.csv file has changed, and it no longer contains a column named "STATUS".

    Did you make a change to the licence.csv file?

    Can you upload the licence.csv file you are trying to query, please (complete, unedited, exactly as it is in the folder you're using), please. Or better still, point us at the actual source of the data?

  84. #84
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Quote Originally Posted by Potholes View Post
    Ali please have a look at post 60 from Olly
    That post was by you. What's your point?

    Please see post #83 by Olly, who seems to concur with the assessment I made that you don't like.

  85. #85
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Olly and all, as requested, Please use and then kill this post

    It is a ZIP file, the actual Licence.csv is over 20Mb
    Last edited by AliGW; 07-01-2019 at 06:52 AM.

  86. #86
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    What do you mean by "kill" this post? Why?

    I can't see a licence.csv file on that page ...
    Last edited by AliGW; 07-01-2019 at 06:51 AM.

  87. #87
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Privacy concerns from my point

  88. #88
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    I've removed the link until we establish what you mean. If this is a licensed file that should not be shared publicly, then you cannot share it with us, not even privately via PM.

    I suggest that you open the file yourself, copy the headers to a new Excel sheet and share that here - JUST the headers. Don't do anything else to the file.

  89. #89
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Have you downloaded the ZIP file.??

  90. #90
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Please see post #88.

  91. #91
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    The original version of licences.csv which you shared with us had just 9 columns.

    The version of licences.csv from the online zip source has 15 columns.

    This is a significant change.

    However, now we have (finally, 85 posts into the thread) got the true source data, not a manually amended interpretation of it, we can build some queries directly against it. 5 minutes, and I'll post code which gets everything directly from your web source...

    The terms of use of this (open access) data are made perfectly clear on the download site originally linked. It's perfectly fine to share and use.

  92. #92
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi

    I get a monthly DVD sent to me and when I applied years ago there was privacy statement.

  93. #93
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    The terms of use of this (open access) data are made perfectly clear on the download site originally linked. It's perfectly fine to share and use.
    Thanks for that, Olly.

    Here's the link: https://www.acma.gov.au/Industry/Spe...s-licence-data

  94. #94
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    When I WIN $20Mil tomorrow night I will have to make a point of going for a visit to GB either pre or Post Brexit.

  95. #95
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows



    Do I need to employ a body guard?

  96. #96
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Depends if you are nice to my XYL and myself in the FTF meet.

    Hey but I have to win it yet, lately always the Bridesmaid and never the Bride.

  97. #97
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    XYL? FTF? You've lost me ...

  98. #98
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Okay - here's a reworked file, to look directly at the source data:

    https://excel.solutions/potholes-fe-v2-0/

    In the Input sheet, you can enter either the URL to the file on the acma.gov.au server, or you can point at a locally downloaded file (which may be quicker, depending on your network speed).

    The queries reference the appropriate files within the zip archive, and join the data as before. There's a new "Unzip" query in play here.

    For reference, here are the queries.

    Unzip:
    Please Login or Register  to view this content.
    (from Mark White: http://sql10.blogspot.com/2016/06/re...erquery-m.html)


    ZipFiles:
    Please Login or Register  to view this content.

    Clients:
    Please Login or Register  to view this content.

    Client Type:
    Please Login or Register  to view this content.

    Devices:
    Please Login or Register  to view this content.

    Licences:
    Please Login or Register  to view this content.

    All Data (load to data model):
    Please Login or Register  to view this content.

  99. #99
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    THESE are Amateur Radio terms Like L = London, A = Apple and so on.
    Well XYL means Ex Young Lady, FTF Face to Face..

    Ohhh 73s is another international term " Best Wishes", and to ladies we say 88s =Hugs and Kisses.

  100. #100
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    And now, I am intrigued about why you'd want to do this in Excel, when there's a perfectly good lookup / search function on the website: https://web.acma.gov.au/rrl/register_search.main_page

  101. #101
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    and to ladies we say 88s =Hugs and Kisses
    I'll settle for a 73, thanks!

  102. #102
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi Olly

    I am having trouble with the 2.0vr.

    I have everything sitting in " C:\ACMA Excel "

    And All of the queries are showing the Yellow Asterisk, I set the INPUT to " C:\ACMA Excel "



    Please Login or Register  to view this content.
    Is what I get when I just move the mouse over any of the side queries ..

    PS

    When I told it to use the on board ZIP file is through a hissy fit and said not enough memory, so I am using the UNZipped files all in the same dir as the xlsx.
    Last edited by Potholes; 07-01-2019 at 08:42 AM.

  103. #103
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Peter - the clue is in the error message (again):

    Expression.Error: The path 'c:\ACMA Excel' is a folder path. A file path is expected.
    Try this:

    C:\ACMA Excel\2.0vr

  104. #104
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Not enough memory? Blimey. Okay. That's a shame... Much more robust to look directly at the source data. Buy some more RAM.

    Anyway - the only issue with file version 1.3 is that you previously removed some columns from licence.csv before sharing with us - so I wrote a query which expected 9 columns, and there are actually 15. So the following line in the Licenses query needs to change:

    Please Login or Register  to view this content.
    Here's a v1.4 file, which looks at unzipped files in the specified folder, with the corrected Licences query: https://excel.solutions/potholes-fe-v1-4/

    Two points, then:

    1. When supplying source data, don't make ANY changes to it at all. None. Even if you think it will make it easier to explain, or simple to work with, it just makes it more complicated.

    2. Why are you doing this, then there's a perfectly adequate search facility online, which returns all the data you're after (and more) ?


    EDIT: for clarity, V1.4 needs a path to a folder in the input box. V2.0 requires a path to a .zip file in the input box (which may also be a url for a .zip file).
    Last edited by Olly; 07-01-2019 at 08:57 AM.

  105. #105
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Ali
    This is the full path.

    C:\ACMA Excel\Potholes-FE-v2.0.xlsx

    Now when I try to refresh it says. " Expression Error There weren't enough elements in the enumeration to complete the operation.


    WE CROSSED PATHS with our posts.
    Last edited by Potholes; 07-01-2019 at 09:05 AM.

  106. #106
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Please see Olly's added EDIT in post #104.

  107. #107
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Olly

    This is the path for all files, I tried with and without the file ext, made no difference.

    C:\ACMA Excel\Potholes-FE-v1.4.xlsx

    It is 23:30 here and I am mentally exhausted, CU tomorrow all being well if it is not sorted out in the meantime.

    73s all
    P

  108. #108
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    That's a path to a FILE, Pete. In v1.4, enter the path to the FOLDER: C:\ACMA Excel

  109. #109
    Forum Contributor Potholes's Avatar
    Join Date
    11-25-2011
    Location
    Brisbane
    MS-Off Ver
    Office 2021
    Posts
    774

    Re: Code to remove blank rows

    Hi All

    " Houston We Have a Problem ",


    Finally we HAVE LIFT OFF


    Even though I have previously set the address as C:\ACMA Excel " and it would not play the game
    over night it decided to play the game.

    I don't know how I can ever repay you all for your help and guidance.

    Now you can stop taking your depression pills, as I will go quiet for a while.


    73s to you all

    Peter
    Last edited by Potholes; 07-01-2019 at 06:11 PM.

  110. #110
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    Great news, Pete - way to go.

  111. #111
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Code to remove blank rows

    Glad we got there!

    Now, do you understand how the queries work?

  112. #112
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Code to remove blank rows

    No - see Pete's new thread!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code Optimization - Copy cells from one sheet to another - remove blank rows
    By snuffnchess in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-15-2019, 07:52 AM
  2. VBA code remove blank rows from all tables
    By AlmaD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2018, 08:03 AM
  3. [SOLVED] Update VBA code - remove blank rows in newly created sheets
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2016, 03:17 PM
  4. [SOLVED] Update existing VBA code - Remove blank rows
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2016, 10:41 AM
  5. [SOLVED] Remove blank rows that aren't really blank (contain unused formulas)
    By painterartist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2014, 02:01 PM
  6. VBA code to remove rows in case of blank cells
    By GrahamRoss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2011, 12:02 PM
  7. How to remove blank rows, then insert blank row conditionally, PLUS error proof
    By GTS115 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2009, 11:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1