Importing text to Access, How to?

[nizar.jouini]

Hello.

I have long text file whitch is formatted like this:

nextrow4

asdf

asdf

asdf

nextrow3

bsdf

bsdf

bsdf

nextrow6

cdsf

cdsf

cdsf

So the file should look like this after importing

nextrow4 | asdf | asdf | asdf
nextrow3 | bsdf | bsdf | bsdf
nextrow6 | csdf | csdf | csdf

So nextrow+"some number" or just nextrow tells to start a new row. The
linechange is indicating column change.

I'we been trying all kinds of additional software to import the
textfile but none of them did work the way I needed.

Every help will be respected, thanks!

- Nisse

[pietlinden]

use a loop when you read through the file...

for intCounter = 0 to 3
if intCounter=0 then
rs.AddNew
end if
strLine = LineInput....
rs.fields(intco unter)=strline
rs.update
next intCounter

[pietlinden]

Okay, got it to work. (The help files in Access seem to suck more with
every release! Pretty soon they won't even bother with one, I think!)

Here's the code... and no, AFAIK, there is no way to do it with a
macro.
Except one problem that my code does not deal with... the blank lines.
I guess you could test for null...

Option Compare Database
Option Explicit

Public Sub ImportTextfile( ByVal strFile As String)
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim intFileNo As Integer
Dim strText(0 To 3) As String 'holds the lines of text
Dim intCounter As Integer

Set db = CurrentDb
Set rs = db.openrecordse t("Table1", dbOpenTable, dbappendonly)

intFileNo = FreeFile
Open strFile For Input As intFileNo
Do Until EOF(intFileNo)
'read the block of 4 values into an array
For intCounter = 0 To 3
Line Input #1, strText(intCoun ter)
Next intCounter
'write the array to a record
rs.AddNew
'--sorry, the fields collection in DAO is zero-based, so I'm
counting 0...3
For intCounter = 0 To 3
rs.Fields(intCo unter) = strText(intCoun ter)
Next intCounter
'save the record
rs.Update
Loop

rs.Close
Set rs = Nothing
End Sub

[pietlinden]

oh, you're going to have to modify this a little if you have blank
lines between each value... probably just stuff your blank line into a
variable and test it for null or zero-length... But if your extra
carriage returns were just to make it more readable, this should at
least get you started...

[corepaul]

On 21-Jun-2005, ni**********@we binfo.fi wrote:

Hello.

I have long text file whitch is formatted like this:

nextrow4

asdf

asdf

asdf

nextrow3

bsdf

bsdf

bsdf

nextrow6

cdsf

cdsf

cdsf

So the file should look like this after importing

nextrow4 | asdf | asdf | asdf
nextrow3 | bsdf | bsdf | bsdf
nextrow6 | csdf | csdf | csdf

So nextrow+"some number" or just nextrow tells to start a new row. The
linechange is indicating column change.

I'we been trying all kinds of additional software to import the
textfile but none of them did work the way I needed.

Every help will be respected, thanks!

- Nisse

You didn't specify much about the text file you are reading. From the way
you formatted the data on screen, I am going to assume that each piece of
text is followed by a carriage return and that a carriage return is on each
of the blank lines between text lines.

Dim tmpStr As String
Dim inputStr() As string ' dimension as needed to handle all entries in
your "long" text file
Dim i As Integer ' index to current row
Dim x As Integer

Open (strPath & "filename.t xt") For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, tmpStr ' Input (instead of Line Input) may work
depending on delimiters in text file
x = InStr(1, tmpStr, "nextrow", vbTextCompare)
If x > 0 Then
' input contains text "nextrow"
i = i + 1
inputStr(i) = tmpStr
Else
inputStr(i) = inputStr(i) & " " & tmpStr " add more spaces or perhaps
a Tab character to get columns right
End If
Loop
Close #1
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Chuck Grimsby]

co******@aol.co m wrote:

On 21-Jun-2005, ni**********@we binfo.fi wrote:

Hello.

I have long text file whitch is formatted like this:

nextrow4

asdf

asdf

asdf

nextrow3

bsdf

bsdf

bsdf

nextrow6

cdsf

cdsf

cdsf

So the file should look like this after importing

nextrow4 | asdf | asdf | asdf
nextrow3 | bsdf | bsdf | bsdf
nextrow6 | csdf | csdf | csdf

So nextrow+"some number" or just nextrow tells to start a new row. The
linechange is indicating column change.

I'we been trying all kinds of additional software to import the
textfile but none of them did work the way I needed.

Every help will be respected, thanks!

- Nisse

You didn't specify much about the text file you are reading. From the way
you formatted the data on screen, I am going to assume that each piece of
text is followed by a carriage return and that a carriage return is on each
of the blank lines between text lines.

Dim tmpStr As String
Dim inputStr() As string ' dimension as needed to handle all entries in
your "long" text file
Dim i As Integer ' index to current row
Dim x As Integer

Open (strPath & "filename.t xt") For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, tmpStr ' Input (instead of Line Input) may work
depending on delimiters in text file
x = InStr(1, tmpStr, "nextrow", vbTextCompare)
If x > 0 Then
' input contains text "nextrow"
i = i + 1
inputStr(i) = tmpStr
Else
inputStr(i) = inputStr(i) & " " & tmpStr " add more spaces or perhaps
a Tab character to get columns right
End If
Loop
Close #1

Of you could grab my TextFile class from
<http://www.mvps.org/access/modules/mdl0057.htm> and tell it to ignore
blank lines.....

<Grin>

[pietlinden]

Hmm... yeah, I forgot about that, Chuck. I think I used to have it on
my computer, but that one's in storage now...

Is there any way to do the rest of it, besides reading into an array or
structure and then writing to the database? Normally, one can't say
"write four records into one"... (Hey, I'm still a relative novice...)

I like that idea... I'll have to test it out. Thanks!

[Chuck Grimsby]

pi********@hotm ail.com wrote:

Hmm... yeah, I forgot about that, Chuck. I think I used to have it on
my computer, but that one's in storage now...
Is there any way to do the rest of it, besides reading into an array or
structure and then writing to the database? Normally, one can't say
"write four records into one"... (Hey, I'm still a relative novice...)

Personally, I wouldn't bother with the recordset. Just build a SQL
INSERT INTO string from the data you read in and then execute it,
changing the SQL String and repeating as needed. This is actually a
great way to handle situations where you have to write to multiple
tables with bits and peices of the data from the external file.

By the way, when importing from an external file of some kind, I'll
often use the line number of the file that "trigered" the insert(s) as
a "unique identifer" for the record. When things go wrong for some
reason, you have a nice way to "trace back" the information to the
source data.