Convert Dat File into different layout format and into CSV Format

Hi Guys,

Really struggling on this one as it's so far out of my excel knowledge it's untrue.

I've got this file attached.

Sheet one is the .Dat file that i've converted into excel format using delimited by +.
Sheet two is the output style I've created by using simple vlookups

What I want to be able to do is set up a VBA excel file that takes files in a folder within .Dat format and then automatically convert them into the sheet 2 format and then save as a .csv file in the same folder.

Is this possible?

Many thanks for any help

No, formatting is not a feature of csv files.

You can save that sheet as an xlsx file with formulas converted to text. Or, save sheet2 as a csv file less the formatting.

Hi Kenneth,

Thanks for the reply. I'd need the second sheet saved as the CSV file with the formulas converted.

The VBA to convert that sheet from formulas to values is fairly straight forward i'm guessing, I can do that.
It's just the automated process of taking the dat file and placing it into the excel sheet in sheet one that i'm having trouble with.

While I could do it first, I fear that the DAT file import might need some tweaking first. Can you attach a short simple obfuscated DAT file so I can compare to your Sheet1?

Would the DAT files be in one parent folder or could they be in subfolders as well?

They'd just be within one parent file.

I'm attempting to upload the file but keep getting an "invalid file" error. Could it be they don't allow the file type to be uploaded?