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

15 views (last 30 days)
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

Answers (2)

Jan
Jan on 28 Mar 2019
Edited: Jan on 28 Mar 2019
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

Harsimran Singh
Harsimran Singh on 3 May 2021
use this link, it works perfectly for me:
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

Categories

Find more on Cell Arrays in Help Center and File Exchange

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!