How to extract specific column from multiple csv and store as different columns as output table ?

Hello All,

I am working on multiple csv analysis. I could come till extracting specified column from these multiple CSV and putting one after another. But what I want to do is, to create multiple columns in "Output" with header of the file name and values of specified paramter values.

So for example:

I have ABC1.csv & ABC2.csv , I want to extract column named "xyz" from both of them. With the following code, I could extract "xyz" column values from each of them and could be able to put those one after another.

But what I want is the "Output" table should have column headers "ABC1" & "ABC2" and under these headers, the respective "xyz" column values to be stored.

clearvars
close all
clc

%% mention the name of the parameter to extract
param='xyz'; %Change default prameter name with required parameter


%% select folder
dataFolder = uigetdir();
filePattern = fullfile(dataFolder, '*.csv');
list = dir(filePattern);

% Read each CSV file, extract rows and store them to 'Output'
Output = table();
for kk = 1:numel(list)
 data = readtable(fullfile(list(kk).folder, list(kk).name));
 VarNames =data.Properties.VariableNames; 
 ColIdx = find(strcmp(VarNames, param));
 Output = [Output; data(:,ColIdx)];
end

Answer 1

[Jan]

Output = table();
for kk = 1:numel(list)
    filename = list(kk).name;
    data     = readtable(fullfile(list(kk).folder, filename));
    try
        [~, varname] = fileparts(filename);  % remove the file extension
        Output.(varname) = data.(param);
    catch ME
        fprintf('Cannot find [%s] in file: %s\n  %s', ...
            param, filename, ME.message);
    end
end

[adi kul]

Not working.

Getting error:

Cannot find [xyz] in file:ABC1.csv
'ABC1' is not a valid variable name.>>

[adi kul]

still couldn't fix this :(

Answer 2

[Harsimran Singh]

use this link, it works perfectly for me:

https://www.mrexcel.com/board/threads/delete-first-2-rows-of-each-csv-file-in-a-folder-1-000-csv-files.890203/

Option Explicit

Sub FixCsvFiles()

Dim SelectFolder As String

Dim csvFiles As Variant

Dim csvWb As Workbook

Dim x As Integer

'browse for folder with csv files

On Error GoTo FixCsvFiles_Error

SelectFolder = GetFolder("c:\")

Application.ScreenUpdating = False

'Check user did not cancel folder selection

If SelectFolder = "" Then

MsgBox "No Folder Selected - Cannot continue", vbCritical

End

End If

SelectFolder = SelectFolder & "\"

csvFiles = Dir(SelectFolder & "*.csv")

Do While csvFiles <> ""

Set csvWb = Workbooks.Open(SelectFolder & csvFiles)

Rows("1:2").Delete

x = x + 1

csvWb.Close True

csvFiles = Dir

Loop

Application.ScreenUpdating = True

MsgBox "A total of " & CStr(x) & " files processed", vbInformation

On Error GoTo 0

Exit Sub

FixCsvFiles_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FixCsvFiles of Module2"

End Sub

Function GetFolder(strPath As String) As String

Dim fldr As FileDialog

Dim sItem As String

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

With fldr

.Title = "BROWSE TO FOLDER LOCATION WITH CSV FILES"

.AllowMultiSelect = False

.InitialFileName = strPath

If .Show <> -1 Then GoTo NextCode

sItem = .SelectedItems(1)

End With

NextCode:

GetFolder = sItem

Set fldr = Nothing

End Function