How to turn a csv file into a 3D matrix?

Hik everyone and thanks in advance for your attention, I am a beginner MatLab user and I cannot fnd a way to manage some data I am working on.

I have a database (csv file) like this attacched below:

I would like to create a 3-D array (let's call it FLOW) which enables me to put as rows the reporter code, as columns the partner code, and as pages the commodity code (although the year is written, it is not relevant beacause every data refers to 1990). So that I can identify the flow of commodity "i" from country "x" to country "y" simply by writing:

FLOW(x,y,i)

I would like to know whether is this possible and also how to turn my csv file (attacched below) into the 3D array in an automatic way, so that I don't have to write manually the 3D matrix.

To help you better understand the problem, let's take as an example the same kind of data but for a database with fewer data:

I'd like to find some algorithm which automaticly creates from the csv file, this 3D matrix:

A=[0 0 a c; 0 0 e; g]

A(:,:,2)= [0 0 b d; 0 0 f h]

In this way I reach my goal, because when I write

A(1,3,1) matlab will answer "a" which is exactly the flow from country one to country 3 of commodity 1.

Thank you very much for the help

Roberta

Answer 1

[Image Analyst]

Try using readtable()

t = readtable('1990 Blocco note.txt')
% Extract numerical arrays
years = t.Year
reporterCode = t.ReporterCode

and so on.

The 4th column being either a string "TOTAL" or a number will be a problem though. What's going on with that???

[Roberta]

The 4th column indicates the commodity code: every number means a certain commodity (for example 312 stands for Apple, 313 for live cattle, and so on).

When the word TOTAL appear, it means that the number in the fifth columns represent the total flow of commodity from reporter country to partner country (e.g. in the 1st row of data there is the value of the total export from country 32 to country 8).

In my opinion this problem could be easily solve substituing a number to the word "TOTAL", in order to have only numerical data.

[Image Analyst]

If you could do that it would make it easier. Otherwise, I see a line by line reading with fgetl(), then check if the line has the word "TOTAL" in it, and calling textscan() two different ways depending on whether the word is or it not in there. You might have do to this if you're stuck with the file - like it was created not in MATLAB but some other program. Anyway, it's not that hard. Just look up help for fgetl() and see how they read in lines, then look up help for textscan().