Importing a folder of many csv files and append file names to field


#1

I’m trying to import 100,000 text message lines that derive from 2,000 “conversation CSV files” exported from iExplorer; yet I must append the filename of each file to each row as the filename is the “key conversation with field”. BTW: The only solution I’m aware is using Excel for Windows “Power Query” add-on function. It allows you do exactly what I want; i.e.: to merge all CSV files in a folder into one file and append the filename of each file to a column – yet this Excel add-on is not available on Excel Mac and I would prefer a Panorama solution. Thank you for any insight on how to accomplish this in Panorama.


#2

If you are willing to do a bit of programming this is certainly possible. This code assumes that the first field in the database is the one that will contain the file name. (Please note that I have not actually tested this code, so it may contain typos.)

local csvFolder
choosefolderdialog csvFolder
if csvFolder="" return endif // user pressed cancel
let filenames = filecatalog(csvFolder,".csv")
looparray filenames,cr(),filename
    importtext fileload(filename),"rearrange",filename+tab()+import()
endloop

The code starts by displaying a dialog that asks what folder you want to import, or you could hard code the folder path if you want.

Then it uses the filecatalog( function to generate a list of all of the csv files in that folder.

Next it uses the looparray statement to iterate over each csv file.

For each csv file it uses the importtext statement to import and append the text lines. The “rearrange” option is used to shift the imported text over by one field, and to put the filename in the first field.

The text is actually read into memory with the fileload( function.


#3

Jim,

Very cool! The code provided handled the filename append and looped through the folder of 2,000 CSV files creating nearly 100,000 records, yet it inserted all of the data from the (5) existing columns/fields into the second column/field (separated by “,”) – is there a way to have the columns import into separate fields?

Best; Elton


#4

It should do that automatically. It looks at the first line to see if it contains any tabs, if it does, it uses tabs as the separator, otherwise it will use commas. Is there any chance that there is a tab in the first line of imported data?

Oh, I see the problem – my rearrange code is putting a tab into the data. So this should fix it.

importtext fileload(filename),"rearrange",filename+","+import()

#5

Jim,

I apologize for the difficulty, yet that code change now places all data in the first column – any insight on how to fix would be appreciated. Best; Elton


#6

It’s tricky writing code when you can’t test it (since I don’t have your data). Here’s another version you can try – this code converts the text from comma to tab delimited, then imports it.

importtext csvtotsv(fileload(filename)),"rearrange",filename+tab()+import()

#7

Jim,

Very coo!; that will focus me on Panorama X! …not something I could do in Panorama 6x, FileMaker, Helix, Terminal, etc. (not good with Shell or AS).

It is however, wrapping/overflowing some records over several lines; possibly records with “,”; here are (3) sample CSV files that demonstrates the situation. – is there a way to avoid such?

Best; Elton


#8

It looks like csvtotsv( does not ignore commas within quotes, so you are getting a tab where it should be left a comma.


#9

Some of your data contains line breaks. That should be ok since the data is quoted. I did a test simply importing the text files into a new database and that worked ok. Ok, the problem is that the csvtotsv format removes the quotes, since the whole idea is that the tsv format is simple and doesn’t require complicated parsing.

I’m not sure if the csvtotsv( function will work inside the importtext statement, but if it does, this may fix the problem.

importtext fileload(filename),"rearrange",filename+tab()+csvtotsv(import())

Or, you might need this:

importtext fileload(filename),"rearrange",filename+tab()+replace(csvtotsv(import()),lf(),vtab())

#10

Jim,

Each work perfectly; thank you very much for your help. Best; Elton