crribs.com

At work, a main component of my job involves daily maintenance of 200 or so scheduled vbscript’s that maintain servers, generate daily data feeds, process images, transfer files, and do the bulk of our ETL processing (retrieval, normalization, etc.).  Because many of the imports consist of relational files that we need to combine into one, file- an internal schema we use in an attempt to standardize loading into the db- we use the Microsoft text driver to query the text files (with the text driver, you can query delimited text files with a subset of SQL, so you can join multiple files together and aggregate relational data).

The text driver requires a file named “schema.ini” to be placed within the directory holding the files you want to query.  As you guessed, the schema.ini specifies the schema of the file.  You set values like character set, if the first row has headers or not, and the length/type of the headers.  But you are SOL if you have a .csv file with more than 255 columns.  I haven’t found any really good solutions yet (actually, I’ve used some pretty terrible ones).  I have actually resorted to firing off a DTS package in the middle of a script that trims off unneeded columns and outputs a new file, with less than 255 columns.  Short of looping through the whole .csv, splitting every line by the delimiter, and reassembling a new file based on the resulting array, I can’t even really think of any good solutions.  One promising lead is the free component for working with .csv files from Chilkat (http://www.example-code.com/vbscript/csv_read.asp – it can be downloaded for free on this page- the download link is the little peach-colored down arrow icon about two inches from the top of the page).

I’m thinking, though, that it may be time to ditch vbscript in favor of Powershell, which has an ‘import-csv’ commandlet.  It takes a .csv file in memory, and creates an object for every header item.  It works really well, in my limited testing.