VBA to handle .csv date fields

[cgmojoco]

Hi there -

I need VBA to search through column headers for the word

date

.

If the column header contains date, I need the column of data to be set to this custom format:
YYYY-MM-DD hh:mm:ss

Then, the column should have each of its cells wrapped in quotes (to preserve the format on .csv save).

Next column, rinse, repeat until all cells in columns with

date

have had their format changed and have had an extra layer of protection from Excel changing the format again, by having them be wrapped in quotes.

Thanks in advance!

 

[Joe4]

Then, the column should have each of its cells wrapped in quotes (to preserve the format on .csv save).

I don't think this part should be necessary. When you save an Excel file to a CSV file, it will save the data in whatever format you have it in in Excel. So it shouldn't change it all.
The only reason usually to put it in quotes is if you have a character in your data that is the same as your delimiter. But there are no commas in the "YYYY-MM-DD hh:mm:ss" format. So that shouldn't be necessary.

Where many people get confused is if they try to use Excel to view the contents of their CSV file after it is created. NEVER USE EXCEL TO VERIFY THE CONTENTS OF A CSV FILE!!!
The reason is because Excel will automatically apply its own conversions to the data when you open the CSV file in Excel.
If you TRULY want to view the contents of a CSV file, use a Text Editor program like NotePad to view it. Then you can see the data exactly as it appears in the CSV file.
I think you will see that the format of your date fields will appear exactly as it did before you saved it from Excel.

In order to change the format of each column depending on whether the word "date" appears in row 1, try this code:

Sub MyFormatColumns()

    Dim lc As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Find last column in row 1 (header row) with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns
    For c = 1 To lc
'       See if the word "date" appears in header row
        If InStr(LCase(Cells(1, c)), "date") > 0 Then
'           Format column
            Columns(c).NumberFormat = "yyyy-mm-dd hh:mm:ss"
        End If
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[cgmojoco]

I don't think this part should be necessary. When you save an Excel file to a CSV file, it will save the data in whatever format you have it in in Excel. So it shouldn't change it all.
The only reason usually to put it in quotes is if you have a character in your data that is the same as your delimiter. But there are no commas in the "YYYY-MM-DD hh:mm:ss" format. So that shouldn't be necessary.

Where many people get confused is if they try to use Excel to view the contents of their CSV file after it is created. NEVER USE EXCEL TO VERIFY THE CONTENTS OF A CSV FILE!!!
The reason is because Excel will automatically apply its own conversions to the data when you open the CSV file in Excel.
If you TRULY want to view the contents of a CSV file, use a Text Editor program like NotePad to view it. Then you can see the data exactly as it appears in the CSV file.
I think you will see that the format of your date fields will appear exactly as it did before you saved it from Excel.

In order to change the format of each column depending on whether the word "date" appears in row 1, try this code:

Sub MyFormatColumns()

    Dim lc As Long
    Dim c As Long
  
    Application.ScreenUpdating = False
  
'   Find last column in row 1 (header row) with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
  
'   Loop through all columns
    For c = 1 To lc
'       See if the word "date" appears in header row
        If InStr(LCase(Cells(1, c)), "date") > 0 Then
'           Format column
            Columns(c).NumberFormat = "yyyy-mm-dd hh:mm:ss"
        End If
    Next c
  
    Application.ScreenUpdating = True
  
End Sub

Great, thanks, this is the next best thing when you one must open a .csv with Excel because the data must be manipulated in a spreadsheet and then saved again and there are to many columns to "import" using excel import tool (it won't even show that many columns, at least on MacOS.
I appreciate your guidance and the code, everything working as expected.