Converting .txt file to .csv, splitting files and adding rows

CamBF

New Member
Joined
Oct 19, 2023
Messages
16
Office Version
  1. 2016
Hi all,

Needing urgent help please to put together a VBA code that will do the following, assisting with speeding up workflow by removing time consuming manual editing.

Currently we export from our software a .txt file that contains data relevant to multiple points we have modeled. The data in the .txt file is separated by a comma, please refer to the attached screenshot. However it is not a .csv file format. Also if .txt file is opened in Excel all data is in column A only.

What we need is a VBA code that will open a window to locate the .txt file that contains this information.

Then once the file is selected through the window and open / ok is pressed to close the window, the VBA code will do the following:

1. Delete the top row of text in the selected file.

2. Search the data in the .txt file after the 4th comma in each row and group the data based on the text found, refer to the pink column in attached screenshot.

3. For each "group", for example all rows with "GRIDS", we need the code to create a .csv file in the same location as the selected .txt file, which contains only the rows with "GRIDS".

4. The code then needs to do the same for all other "groups" of text as per step 3 above. Essentially creating multiple separate .csv files for each "group". So looking at the attached screenshot there would be one .csv file for GRIDS, another .csv file for "STAGE 1 - SLAB POINTS" and so on. The file names will need to be the name of the "group".

5. In each .csv file created, a row of text will need to be added at the top. It will need to be: ID,X (m),Y (m),Z (m),Layer

6. Once all .csv files have been created, the original selected .txt file will need to be shifted into a new created folder in the same location, named "_superceded", if there is already a folder in the location named _superceded, no need for the code to try create a second folder, just move the .txt file into the existing folder.

Example .txt file can be downloaded here: Dropbox

Thanks guys :)
 

Attachments

  • Example.png
    Example.png
    120.9 KB · Views: 12

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top