Tools & techniques for one-off consolidation of multiple 'similar' CSV files?

[Craig Newman]

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 at 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 at lists.runrev.com <mailto:use-livecode at 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 at lists.runrev.com <mailto:use-livecode at 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 at 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 at 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 at lists.runrev.com <mailto:use-livecode at 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>