insert missing columns

[chiguy99]

Hi all, I am in need of some help. I have a folder with a bunch of csv files. What I need is a macro that will open each csv look though a list of column names that I provide and if the column is not there insert the name of the missing one and insert "0" all the way to the bottom of the document. It needs to do this for all of the files in the folder.

Attached is an example, so in the file we have some columns labeled: time stamp, a, b, c, d.

And what I needed is columns: time stamp, a, b, c, d, e. The script will see that there is no column E, so it will add the title at the top and fill it all with 0 till end end of the data.

I had some help from someone but the script does not work, What I mean is that it does insert the columns but it will insert them even if the columns are already there. So it will double up on the columns. Do you know what the problem could be? Some of my my column names have spaces you think that could be it?? It also seems to be stuck in the loop it never stops.

example code

Sub InsertMissingColums()

Dim C As Long
Dim ColumnHeaders As Variant
Dim Filename As String
Dim Filepath As String
Dim R As Long
Dim Rng As Range
Dim Wkb As Workbook

ColumnHeaders = Array("time_stamp", "a", "b", "c", "d", "e")

Filepath = "C:\Documents and Settings\Desktop\csvimport\"

Filename = Dir(Filepath & "*.csv")

Do While Filename <> ""
Set Wkb = Workbooks.Open(Filepath & Filename)
Set Rng = ActiveSheet.UsedRange.Rows(1)
R = ActiveSheet.UsedRange.Rows.Count
For C = 0 To UBound(ColumnHeaders)
If Rng.Cells(1, C + 1) <> ColumnHeaders(C) Then
Rng.Cells(1, C + 1).EntireColumn.Insert
Rng.Cells(1, C + 1) = ColumnHeaders(C)
Rng.Cells(1, C + 1).Offset(1, 0).Resize(R - 1, 1).Value = 0
End If
Next C
Wkb.Close SaveChanges:=True
Loop

End Sub

[mdmackillop]

Can you list your actual column headers? There is no basic fault with the code.

[chiguy99]

this the what I use and I'll include a screen shot of what it does to the csv file.

Sub InsertMissingColums()

Dim C As Long
Dim ColumnHeaders As Variant
Dim Filename As String
Dim Filepath As String
Dim R As Long
Dim Rng As Range
Dim Wkb As Workbook

ColumnHeaders = Array("barometric_pressure (kPa)", "ambient_air_temperature (C)", "engine_coolant_temperature (C)", "engine_oil_temperature_1 (C)", "engine_fuel_rate (l/h)", "aftertreatment_1_fuel_rate (l/h)", "active_regeneration_start_flag (Raw Value)", "engine_speed (rpm)", "actual_engine_torque (Nm)", "wheel_based_vehicle_speed (kph)", "aftrtrtmnt_1_dsl_oxid_cat_intake_gas_temp_docit (deg C)", "aftrtrtmnt_1_dsl_partic_filter_intake_gas_temp_dpfit (deg C)", "aftrtrtmnt_1_dsl_partic_filter_outlet_gas_temp_dpfot (deg C)", "engine_intake_air_mass_flow_rate (kg/h)", "engine_intake_manifold__1_pressure (kPa)", "amber_warning_lamp_dm01_pgn_feca_spn624_sa__div0 (bit)", "dtc_events_this_trip_-_active (Raw Value)", "engine_throttle_valve_1_position (%)", "load_pf_can (g/l)", "aftertreatment_1_diesel_particulate_filter_differential_pressure (kPa)")

Filepath = "C:\F_factor_creator_I6\import\"

Filename = Dir(Filepath & "*.csv")

Do While Filename <> ""
Set Wkb = Workbooks.Open(Filepath & Filename)
Set Rng = ActiveSheet.UsedRange.Rows(1)
R = ActiveSheet.UsedRange.Rows.Count
For C = 0 To UBound(ColumnHeaders)
If Rng.Cells(1, C + 1) <> ColumnHeaders(C) Then
Rng.Cells(1, C + 1).EntireColumn.Insert
Rng.Cells(1, C + 1) = ColumnHeaders(C)
Rng.Cells(1, C + 1).Offset(1, 0).Resize(R - 1, 1).Value = 0
End If
Next C
Wkb.Close SaveChanges:=True
Loop

End Sub

as you can see it adds all of the variables at the beginning of the csv, even though they are all actually there in the document.