pipe delimited table script

[cronid]

I have 61 pipe delimited files in a Windows 2000 folder. I need to write a script (VB?) to create 61 Access tables, which I have done manually in the past. Each file is different with a different number of rows and columns. It is not necessary that the columns be named anything but the default field 1, field 2, etc. I already have 61 queries for the rows count in each table, 61 queries for duplicates and for the tables 2 through 27 for the rows without a match in field 1 of the first table. I need to have a script create the tables, run those queries and post the results to an Excel spreadsheet each time the 61 files are overlayed.

Can VB do it? Does anyone have a sample script that does something similar?

Is there an alternative scripting language that will be better suited?

Thanks in advance,

Dennis Cronin cronid@aol.com

 

[Pat Hartman]

There is VB, VBA, and VB Script. Try not to use the word script when referring to VB and VBA code. Both VB and VBA can do what you are asking. I am not sure about VB Script though.

Pipe delimited is not the default so you would need to create an input spec and save it in order to specify the special delimiter. Once you do that (you only need to do it once, not 61 times), you can use the TransferText method or Action to import data from a file and append it to a table. Since you will be importing the same files repeatedly, you will need to run delete queries to empty the existing rows prior to appending new ones.

You can use TransferSpreadsheet to output a recordset to Excel.