Importing Text File that is vertically oriented

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
ghadley_00@yahoo.com

re: Importing Text File that is vertically oriented

ghadley_00@yahoo.com wrote:[color=blue]
> 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
> ghadley_00@yahoo.com
>[/color]

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.

re: Importing Text File that is vertically oriented

ghadley_00@yahoo.com wrote in
news:1136042461.470019.234440@g14g2000cwa.googlegr oups.com:
[color=blue]
> 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
> ghadley_00@yahoo.com
>[/color]
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.

re: Importing Text File that is vertically oriented

On Sat, 31 Dec 2005 16:05:29 GMT, salad <oil@vinegar.com> wrote:
[color=blue]
>ghadley_00@yahoo.com wrote:[color=green]
>> 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
>> ghadley_00@yahoo.com
>>[/color]
>
>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[/color]

intCnt = intCnt + 1 ' minor typo
[color=blue]
> 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.[/color]