Hi Chris,
This is about the right place. Here goes.
1) A file like yours can't be imported using Access's normal import
routine because the meaning of its lines depends not just on the data
they contain but on the order they're in. The choice is between
pre-processing at the textfile level so that each line becomes a
self-contained record that can be imported into a relational database,
and writing Access VBA code that reads the file line by line and does
what's necessary to append them to a usable table.
The VBScript at the end of this message should do the preprocessing for
you, maybe with a little modification. It looks out for the distinctive
string that marks the beginning of a record (from your sample case, a
line beginning "| 010: name :") and stores the record identifier that
makes up the remainder of the line (e.g. "program 1"). Then it outputs
the remaining lines in the record, prefacing each with the identifier,
e.g.
program 1:020: description: blah blah blah
program 1:030: date : 11/11/11
program 1:040: other_info : blah blah blah
another program:020: description: blah blah blah
...
The result is a ":"-delimited text file that Access can import into
fields such as ProgramName, OptionNumber, OptionName, OptionValue
2) It sounds as if your "programs" have varying numbers and names of
"options". If so, it's not a good idea to import it into a table with 50
or more fields numbered 010, 020 etc.: if you do, you're effectively
storing data (the line number/step number) in the field names, and some
kinds of querying become difficult or impossible. Better to leave it as
a tall narrow table with one record per option per program: it's always
possible to use a query to present the "spreadsheet" view of the data
when needed (or even to create a wide table if you absolutely have to).
I don't know that this is the right place but it's worth a shot. I am
developing a DW app using a access database and ASP. I have been provided a
text file for the database. The problem is that it needs to be separate
twice so a simple import into access or SQL server will not work. There are
100s of "programs" and each program has up to 50 options. It is "|" pipe
delimitated first, then separated by fields.
The file looks something like this :
| 010: name : program 1
020: description : blah blah blah
030: date : 11/11/11
040: other_info : blah blah blah
| 010: name : another program
020: description : blah blah blah
030: date : 11/11/11
040: other_info : blah blah blah
The file is about 20Mb and updates all the time so I need an easy way to
import this into access to end up with one table, where the field names are
010, 020, 030 etc. and each record is it's own "program".
any ideas ? thanks in advance.
'CODE STARTS
'Prepend.vbs
'VBScript
'Call from commandline as
' cscript Prepend.vbs GroupTag InFile OutFile
'where GroupTag is the string that begins a group.
'E.g. if the data is
'
'Group # 1
'First Line
'Second Line
'Group # 2
'First Line
'Second Line
'Third Line
'
'use a command line like
' cscript Prepend.vbs "Group # " Infile.txt Outfile.txt
'to get the output
'1,First Line
'1,Second Line
'2,First Line
'2,Second Line
'2,Third Line
Option Explicit
Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strTest 'As String
Dim strTag 'As String
Dim DELIM 'As String
DELIM = ":" 'String to separate the "group" data
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab
Set fso = CreateObject("Scripting.FileSystemObject")
strTest = WScript.Arguments(0)
Set fIn = fso.OpenTextFile(WScript.Arguments(1))
Set fOut = fso.CreateTextFile(WScript.Arguments(2))
Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
If Left(strLine, Len(strTest)) = strTest Then
'this line starts a group so store its value
strTag = Mid(strLine, Len(strTest)+1)
Else
fOut.Write strTag & DELIM & StrLine & Chr(13) & Chr(10)
End If
Loop
fIn.Close
fOut.Close
'END OF CODE
