Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2019, 08:02 AM
ruggb ruggb is offline macro construction Windows 10 macro construction Office 2016
Novice
macro construction
 
Join Date: Aug 2010
Posts: 28
ruggb is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-21-2019, 08:19 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline macro construction Windows 7 64bit macro construction Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 05-21-2019, 08:35 AM
ruggb ruggb is offline macro construction Windows 10 macro construction Office 2016
Novice
macro construction
 
Join Date: Aug 2010
Posts: 28
ruggb is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-21-2019, 03:38 PM
Lugh's Avatar
Lugh Lugh is offline macro construction Windows 10 macro construction Office 2016
Competent Performer
 
Join Date: May 2019
Location: USA
Posts: 137
Lugh is on a distinguished road
Default

Quote:
Originally Posted by ruggb View Post
it has records with fields separated by , and CRs.
I need to remove the CR field separations and leave the CR record separations.

There is nothing unique that I can key on.
Does the CR record separator also have a comma?

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.
Reply With Quote
  #5  
Old 05-21-2019, 05:24 PM
ruggb ruggb is offline macro construction Windows 10 macro construction Office 2016
Novice
macro construction
 
Join Date: Aug 2010
Posts: 28
ruggb is on a distinguished road
Default

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

=================
Reply With Quote
  #6  
Old 05-21-2019, 07:26 PM
Alansidman's Avatar
Alansidman Alansidman is offline macro construction Windows 10 macro construction Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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.
Reply With Quote
  #7  
Old 05-21-2019, 09:18 PM
ruggb ruggb is offline macro construction Windows 10 macro construction Office 2016
Novice
macro construction
 
Join Date: Aug 2010
Posts: 28
ruggb is on a distinguished road
Default

well i was going to do that but it wasn't immediately obvious how one would attach a file. now i found it.
Attached Files
File Type: txt Food_Lion_US_V.csv.txt (93.7 KB, 8 views)
Reply With Quote
  #8  
Old 05-21-2019, 11:36 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline macro construction Windows 7 64bit macro construction Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #9  
Old 05-23-2019, 03:11 PM
Lugh's Avatar
Lugh Lugh is offline macro construction Windows 10 macro construction Office 2016
Competent Performer
 
Join Date: May 2019
Location: USA
Posts: 137
Lugh is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 05-23-2019, 05:31 PM
Alansidman's Avatar
Alansidman Alansidman is offline macro construction Windows 10 macro construction Office 2019
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
Default

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"
The file is attached.
Attached Files
File Type: xlsx Book3.xlsx (76.6 KB, 5 views)
Reply With Quote
Reply

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
macro construction Construction Project Presentation manich1 PowerPoint 3 04-17-2011 08:29 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft