nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Import multible CSV files in a folder and stack them

Stats_Like_Jazz

Hi All,

I have an issue with my CSV files, which have a weird separator (",") that I can't figure out how to get JMP to import automatically.

I aim to be able to pick a folder containing multiple CSV files, with the same structure and column names, import all data, and stack them.

I have attached a CSV file that looks like my file.

Hope you can help me

cheers, Christian

CSV with wierd sep.csv

Accepted answers

All comments

hogi

even more than that, the quotation marks come as 1, 2 and 3:

Maybe the best solution is to fix the file export : )

Another solution: use "Notepad ++" / Replace in Files:

to get rid of the quotation marks.

hogi

In JMP,
Text to Columns is THE tool to handle such bunches of separators:
it doesn't care if it's " or "","" or """
it treats multiple separators as a single one (!)
This can be very harmful: Text To Columns: handle missing data correctly
... but often it's exactly what we need, like here .

So,

a) load the files via MFI with the option "Row per Line"

b) and use Text to Columns with Delimiters = ,"

  dts = Multiple File Import(
	<<Set Folder( "C:\Users\xyz\Downloads\csv\" ),
	<<Set Import Mode( "Row Per Line" ),
	<<Set Stack Mode( "Table Per File" )
) << Import Data;

mydt = New Table();

For Each( {dt}, dts,
	dt << Text to Columns( columns( :Text ), Delimiters( "\!"", "," ) );
	dt << Delete Columns( :Text );

// get the heading, make numeric columns numeric
	dt << Move up;
);

mydt << concatenate( dts, append to first table );

For Each( dt, dts, Close( dt, noSave ) );

jthi

You also seem to have "" at the end each row and first column doesn't have the "," format but rather ,".

I'm not sure if this will work for all of your files but I would handle them as text, cleanup them into "proper" csv and then open that from memory into JMP table

Names Default To Here(1);

filedir = "$DOWNLOADS\New folder (2)\";


csvfiles = {};

For Each({filename}, Files In Directory(filedir),
	If(Ends With(filename, ".csv"),
		Insert Into(csvfiles, filedir || filename);
	);
);

dt_res = Empty();

For Each({csvfile}, csvfiles,
	txt = Load Text File(csvfile);
	lines = Words(txt, "\!N");
	lines_fixed = Transform Each({line}, lines,
		items = Concat items(Words(line, "\!","), ",");
	);
	csv_txt = Concat Items(lines_fixed, "\!N");
	
	dt = Open(Char to Blob(csv_txt), "text", Invisible);
	dt << Set Name(Word(-1, csvfile, "\/"));
	If(Is Empty(dt_res),
		dt_res = dt;
	,
		dt_res << Concatenate(
			dt,
			"Append to first table";
		);
		Close(dt, no save);
	);
);

dt_res << Bring Window To Front;

hogi

Ah, right, words()uses the same trick as Text to Columns() .

hogi

Digging deeper:

the main issue is the

in the MFI settings.

JMP is happy about the first and the last quotation mark - and reads the whole line of text as one column ( keeping every second quotation mark ?!?):

edit: first and last quotation mark? non, it's more complicated - see below!

As a quick solution, one can change the CSV Quote setting to another character:

<<Set CSV Quote( "#" ),

and use the quotation mark itself as a separator:

This allows JMP to read the files - but:

different to Words() and Text To Columns(), MFI treats every (single) separator as a separator
with the idea: missing data = missing data, no skipped column!

Thanks MFI, for most of the files, this makes sense : - )

Not here - so, after the import, the user has to delete the empty columns.

Please note: Importing the file with an empty CSV quote setting, doesn't work.

<<Set CSV Quote( "" ),

JMP will automatically use a quotation mark as CSV Quote and load the whole file as a single column.

hogi

for more fun with MFI and CSV quotes

use the attached file

input: JMP:

Once a hypothesis has been set up that fits rows 1–N, it is easy to find a black swan for row N+1 ...

CSV with weird sep_EXTREME.csv