Import multiple csv files

Panorama X
1

How can I import hundreds of csv files and ignore the first line which contains field names? After importing the data I would also want to move all the imported files to an archive folder. This process will be continuous so I would want to automate as much of this as possible so there would be no need for a human to manually do any of the steps.

2

Here is somehing I quickly threw together that should be a starting point for what you need. Note that i have not tested this so test it first on some non important files.

local theCSVfiles
theCSVfiles=listfiles("~/Downloads/CSVfiles",".csv")
if theCSVfiles = ""
    stop
endif
looparray theCSVfiles,¶,element,item
    importtext arraylefttrim(fileload("~/Downloads/CSVfiles/"+element),1, ¶)
    filerename "~/Downloads/CSVfiles/"+element, "~/Documents/CSVArchives/"+element
endloop

This obviously would assume your new .csv files are in a “CSVfiles” folder in your Downloads folder and are then moved to the “CSVArchives” folder of your Documents folder after importing. The arraylefttrim( function should remove the first line from the csv files.

3

Hi Gary,

I gave it a try and the only error generated is “arrayrange(function: Array parameter must be text.”

4

The result of the fileload( function has a binary datatype. You need to insert a binarytotext( function between the fileload( and the arraylefttrim(

importtext arraylefttrim(binaytotext(fileload("~/Downloads/CSVfiles/"+element)),1, ¶)
5

Worked wonderfully

Thank you Gary and Dave!

6

Actually I have run into another anomally. The import is breaking after a certain field. Using Garys wonderful “Reveal Invisible Text” solution the files have a cr() and lf() for its record delimiter. How can I modify the arraylefttrim( function to accommodate?

7

In Panorama X you can have multiple separator characters for use with various array functions and statements. Change the separator from ¶ to cr()+lf().

8

This works if all the files are consistently using crlf, but it will break if they aren’t. To handle all possibilities you could insert lftocr( into the formula and continue to use carriage return as your separator.

importtext arraylefttrim(lftocr(binaytotext(fileload("~/Downloads/CSVfiles/"+element))),1, ¶)

That would convert both line feed and crlf line endings to carriage returns.