#1
|
|||
|
|||
macro construction
Disclaimer: I may have used a macro 10 yrs ago and I need another now. I have a csv file I am opening in excel. It is actually now in an xlsm file. it has records with fields separated by , and CRs. I need to remove the CR field separations and leave the CR record separations. I can't do a replace in Word because it will also replace all the record separators. There is nothing unique that I can key on. I think a macro will work, but how do I get it to repeat to the end of the records. This is what I recorded for the first Record. Range("A2").Select <<< start Selection.Cut Range("B2").Select Selection.Cut Range("A3").Select Selection.Cut Rows("2:3").Select Selection.Delete Shift:=xlUp Range("A3").Select <<< start again now it needs to repeat to the end of the records. thanks |
#2
|
||||
|
||||
Please wrap your code with code tags. Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
I would if I knew what they were.
I copied the above from the macro editor. I didn't think the name and end stmt were relavent. |
#4
|
||||
|
||||
Quote:
Can you attach the file here? If not, can you post a small dummy representation of what you have—say 3-4 fields, 3-4 records? Or better still paste a screenshot of same from Excel. |
#5
|
|||
|
|||
it is a GPS POI file. Here are a few records.
=============== -75.507446,35.334573,Food Lion,41934 NC Hwy 12, Avon, NC 27915,,, (252) 995-4488,,,, -75.05776,38.423954,Food Lion,11801 Coastal Hwy, Ocean City MD 21842,,,, (410) 524-9039,,,, -75.111927,38.335172,Food Lion,9936 Stephen Decatur Hwy., Ocean City MD 21842,,,, (410) 213-0166,,,, -75.615798,35.93571,Food Lion,5200 S Croatan Hwy, Nags Head, NC 27959,,, (252) 441-4118,,,, -75.166172,38.375451,Food Lion,11007 Manklin Creed Rd., Berlin MD 21811,,,, (410) 208-1256,,,, -75.643492,35.985347,Food Lion,2515 S. Croatan Hwy., Nags Head, NC 27959,,, (252) 449-8852,,,, -75.211056,38.333535,Food Lion,10138 Old Ocean City Blvd, Berlin MD 21811,,,, (410) 629-1576,,,, -75.673453,36.0321492,Food Lion,1720 N. Croatan Hwy., Kill Devil Hills, NC 27948,,, (252) 480-1016,,,, -75.231763,38.458848,Food Lion,32 N Dupont Hwy, Selbyville DE 19975,,,, (302) 436-9557,,,, -75.722765,36.1,Food Lion,5543 N. Croatan Hwy, Southern Shores NC 27949,,,, (252) 261-3205,,,, -75.293593,38.581377,Food Lion,28544 Dupont Blvd. Unit #8, Millsboro DE 19966,,,, (302) 934-8880,,,, ================= |
#6
|
||||
|
||||
If you attach a file instead of a picture, then we can see all the hidden features like carriage returns, tabs, etc. We can also determine cell apportionment. Then we can determine a viable solution. What you have presented does not allow any of that.
|
#7
|
|||
|
|||
well i was going to do that but it wasn't immediately obvious how one would attach a file. now i found it.
|
#8
|
||||
|
||||
To add code tags select your code and click the #button.
Code tags preserve formatting and make code easier to copy
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
||||
|
||||
Hmm, I get an 'Invalid file' error trying to upload a CSV.
Ruggb, use 'Open with' to open your TXT file in Excel. Tell it it's 'Comma delimited' and to 'Treat multiple delimiters as one', and you'll end up with 3 rows per entry: Row 1 to Street address; Row 2 City & State; Row 3 Phone # Does that get you towards where you want to be? |
#10
|
||||
|
||||
Using Power Query which is available to you since you indicate you are using 2016. It is on the Data Tab and shown as Get and Transform.
Here is the Mcode Code:
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\alans\Desktop\Food_Lion_US_V.csv.txt"), null, null, 1252)}), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Column1"), #"Filled Up" = Table.FillUp(#"Pivoted Column",{"2", "1"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([0] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"0", "Location"}, {"1", "City"}, {"2", "Phone"}}) in #"Renamed Columns" |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Construction Schedule Rain Days | kpayne | Project | 0 | 04-19-2019 08:56 AM |
Call up times in Construction program | Crawf | Project | 1 | 03-06-2019 01:29 PM |
MS Project 2003 - Commercial Construction template | Rory Todd | Project | 1 | 05-30-2016 04:27 PM |
New heights of construction! | Ulodesk | Chitchat | 0 | 04-27-2015 09:59 AM |
Construction Project Presentation | manich1 | PowerPoint | 3 | 04-17-2011 08:29 PM |