Import multible CSV files in a folder and stack them

[#1]

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 

[#2]

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.

[#3]

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 ) );  

[#4]

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;

[#5]

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

[#6]

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.

[#7]

 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 ...

[#8]

0. if there are characters in fron of the quotation mark, its no CSV quote.

1. if the first character of the cell is quotation mark, it's a CSV quote -> scan to find the closing quotation mark
2. if on your way there are two adjacent quotation marks, treat them as a single quotation mark
   
3. if on your way you find a comma, it's a comma
4. if on your way you find a newline, it's a newline (!!)
   actually, the content of the cell is:
   
5. any other interesting characters?  ...
6. if you find a closing quotation mark, the quote is complete
7. search for a delimiter or a line break.
   any character you find: just add it to the quote.
8. here: a single quotation mark is a singe quotation mark
9.          2 quotation marks are 2 quotation marks (compare #2)
10. if you find a delimiter or line break, this is the end of the cell (compare #4)
11. With a space in front of the quote, the quote is no CSV quote.
    (subset of  #0)
    It took me a while to understand

     
    from the previous post, this is why I added it as a separate case.
    
    

csv file:                                JMP:

                  

[#9]

In light of the new findings, the data table in the original post now looks more familiar:

It resembles a data table that, in the first step ('V1'), was exported with quotation marks added to the numeric and character columns (just the first column didn't get the additional quotation marks:

Surprisingly:

V1 can be opened in JMP and Excel via drag and drop. Both programs automatically 'unpack' the CSV quotes and load the table in the correct format. So, how do you get from V1 to   CSV with wierd sep.csv ?

What works:
step2: open V1 as tab-separated table (instead of a CSV file with CSV quotes)

step3: save the table as csv (in this step [in JMP and Excel], the additional quotation marks at the start and end of each row are added automatically and single quotation marks are replaced by 2x quotation marks)

The reverse process should be straightforward:
- Just open CSV with wierd sep.csv in JMP or Excel. Both programs remove the enclosing quotation marks automatically, and double quotation marks are converted back to single ones: This is how it looks in Excel:

exactly like what we want:

So, let's save the data table as a csv - and load it again : )
in JMP, we can disable the options:

]

Unfortunately, the CSV encoding is enabled by default. On the one hand, it's reasonable — saving the file without CSV quotes would change the data structure significantly!

But actually, that's what we wanted.

So, the only way (?) is to copy the entries from JMP or Excel to a text editor and save v1.txt from there.
The text editor doesn't know about csv quotes and saves the text as it is.

[#10]

@hogi @jthi  Thank you so much for your answers and effort here! It proved even worse than I imagined!

I will have to spend some time digging through your answers, but I wanted to thank you right away  

cheers, Christian