Importing Text File that is vertically oriented

[ghadley_00]

Hi,

I have a MS access database into which I need to import a text file
that is oriented vertically (fields are listed from top to bottom with
the value for each field appearing to the right of the field name (with
a "|" as a delimiter)). The text file will only have 1 record's data.
I would ideally like to setup a linked table to import this data - does
anyone know of a way I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
gh********@yahoo.com

[salad]

gh********@yahoo.com wrote:

Hi,

I have a MS access database into which I need to import a text file
that is oriented vertically (fields are listed from top to bottom with
the value for each field appearing to the right of the field name (with
a "|" as a delimiter)). The text file will only have 1 record's data.
I would ideally like to setup a linked table to import this data - does
anyone know of a way I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
gh********@yahoo.com

Well, you should know what fields are associated with a line number or
some other way to identify the field. Let's say you have a table called
Table1. ID (autonumber) is the first column which would have an index
of 0, and the rest is of text file matches the first 10 fields. Some
code like the following would work

Private Sub ReadFile(strFile As String)

Close #1
Open strFile For Input As #1 'open the text file

Dim intCnt As Integer
Dim strLine As String
Dim rst As Recordset
set rst = Currentdb.Openrecordset("Table1",dbopendynaset)
rst.AddNew

Do While Not EOF(1) ' Loop until end of text file.
Line Input #1, strLine ' assign text file line to variable
intCnt = intCnt = 1 ' increment index counter
rst(intCnt) = strLine ' update with value from text file
Loop

rst.Update
rst.Close
set rst = Nothing

Close #1
End Sub

Now, strLine could contain a date, a number, or text. Depending on the
field type, you may need to use CLng, CInt, CDate, etc to convert the
value from a string to number or date when updating the field.

[Bob Quintal]

gh********@yahoo.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

Hi,

I have a MS access database into which I need to import a text
file that is oriented vertically (fields are listed from top
to bottom with the value for each field appearing to the right
of the field name (with a "|" as a delimiter)). The text file
will only have 1 record's data. I would ideally like to setup
a linked table to import this data - does anyone know of a way
I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
gh********@yahoo.com

Create a temporary table with two fields, one for the name and
the other for the value. Then you can create your properly
structured table using each of the datanames in the table, and
either write a crosstab query to pivot the data horizontally and
use that as the source for an append query or you can write some
visual basic code that manipulates the data and sets the value
in the final table.

so (off the top of my head, and needing support code)

rsDesti.addnew
do until rsSource.EOF
rsDesti.fields(rsSource!dataname) = rsSource!DataValue
loop
rsDesti.update
If you need help with any of this, post again.

--
Bob Quintal

PA is y I've altered my email address.

[Tom Becker]

On Sat, 31 Dec 2005 16:05:29 GMT, salad <oi*@vinegar.com> wrote:

gh********@yahoo.com wrote:

Hi,

I have a MS access database into which I need to import a text file
that is oriented vertically (fields are listed from top to bottom with
the value for each field appearing to the right of the field name (with
a "|" as a delimiter)). The text file will only have 1 record's data.
I would ideally like to setup a linked table to import this data - does
anyone know of a way I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
gh********@yahoo.com

Well, you should know what fields are associated with a line number or
some other way to identify the field. Let's say you have a table called
Table1. ID (autonumber) is the first column which would have an index
of 0, and the rest is of text file matches the first 10 fields. Some
code like the following would work

Private Sub ReadFile(strFile As String)

Close #1
Open strFile For Input As #1 'open the text file

Dim intCnt As Integer
Dim strLine As String
Dim rst As Recordset
set rst = Currentdb.Openrecordset("Table1",dbopendynaset)
rst.AddNew

Do While Not EOF(1) ' Loop until end of text file.
Line Input #1, strLine ' assign text file line to variable
intCnt = intCnt = 1 ' increment index counter

intCnt = intCnt + 1 ' minor typo
rst(intCnt) = strLine ' update with value from text file
Loop

rst.Update
rst.Close
set rst = Nothing

Close #1
End Sub

Now, strLine could contain a date, a number, or text. Depending on the
field type, you may need to use CLng, CInt, CDate, etc to convert the
value from a string to number or date when updating the field.