Hi. I rarely deal with the csv monster, but every time I have had to it all boiled down to the fact that a comma is a terrible character to use as a delimiter of ANY kind.
Ideally it would be possible to simply replace every comma with a tab. The returns remain untouched. If that works, you are very lucky. Have you tried it? Craig > On Apr 4, 2022, at 1:37 PM, Bob Sneidar via use-livecode > <use-livecode@lists.runrev.com> wrote: > > Does all the data need to be in a single table? > > Sent from my iPhone > >> On Apr 4, 2022, at 10:31, Mike Kerner via use-livecode >> <use-livecode@lists.runrev.com <mailto:use-livecode@lists.runrev.com>> wrote: >> >> keith, >> are all the files structured the same way? are they all gathered in the >> same place? >> LC's big strength, IMHO, is text handling, so you're right in its >> wheelhouse. >> for the simplest example, let's assume that all the files have all the same >> column layout and they're all in the same folder. >> in that case, you would >> * grab the list of files in the folder >> * exclude files that shouldn't be included - maybe filter everything >> that doesn't have a .csv suffix. you could do this during the loop, below, >> or ahead of time. >> * iterate through the files by reading each one, and appending the contents >> to a variable/container (if you do this, don't forget to make sure that >> when you append each file, the last line ends with a line delimiter) >> * create a new file >> * save the variable/container to the file >> >> slightly more complicated: the layouts aren't the same >> you can either: >> * rearrange the columns upon reading the file or >> * use something like an sqlite db and create a record for each row, >> assigning the column in each record based on the column name in the csv >> file, or >> * use a LC array, using the column names in the file as the keys of the >> array. >> * create a new file >> * output the result of whichever of the three solutions you chose to the >> new file. >> >> both the easy and less-easy scenarios should take, i'm guessing, somewhere >> between twenty and fifty lines of code, and are easy to implement. if you >> have a couple hundred files, i think that the LC solution would be much >> faster and easier to write, test, and run than the drag-and-drop solution. >> >>> On Mon, Apr 4, 2022 at 1:04 PM Keith Clarke via use-livecode < >>> use-livecode@lists.runrev.com <mailto:use-livecode@lists.runrev.com>> wrote: >>> >>> Hi folks, >>> I need to consolidate a couple of hundred CSV files of varying sizes >>> (dozens to hundreds of rows) and column structures (some shared columns but >>> many unique), into a single superset dataset that comprises all data from >>> all files. >>> >>> There are too many files and columns to attempt a manual process, so in >>> I’ve been trying to achieve this with LiveCode - by iterating through the >>> files to parse the column header rows into into row template and column >>> mapping arrays used to build the combined table. However, I'm struggling to >>> both compile the superset and render the results. >>> >>> I feel I may be designing a very complicated solution for a problem that >>> has probably been solved before. I wonder if I’m missing a simpler method - >>> with LiveCode or perhaps with Excel or ‘drag & drop’ into some kind of >>> self-organising database GUI for SQLite, etc? >>> >>> Thanks in advance for any ideas. >>> Best, >>> Keith >>> _______________________________________________ >>> use-livecode mailing list >>> use-livecode@lists.runrev.com >>> Please visit this url to subscribe, unsubscribe and manage your >>> subscription preferences: >>> http://lists.runrev.com/mailman/listinfo/use-livecode >>> >> >> >> -- >> On the first day, God created the heavens and the Earth >> On the second day, God created the oceans. >> On the third day, God put the animals on hold for a few hours, >> and did a little diving. >> And God said, "This is good." >> _______________________________________________ >> use-livecode mailing list >> use-livecode@lists.runrev.com >> Please visit this url to subscribe, unsubscribe and manage your subscription >> preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com <mailto:use-livecode@lists.runrev.com> > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > <http://lists.runrev.com/mailman/listinfo/use-livecode> _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode