473,287 Members | 1,582 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Importing text to Access, How to?

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

Nov 13 '05 #1
7 2095
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(intcounter)=strline
rs.update
next intCounter

Nov 13 '05 #2
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.openrecordset("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(intCounter)
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(intCounter) = strText(intCounter)
Next intCounter
'save the record
rs.Update
Loop

rs.Close
Set rs = Nothing
End Sub

Nov 13 '05 #3
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...

Nov 13 '05 #4

On 21-Jun-2005, ni**********@webinfo.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.txt") 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 =---
Nov 13 '05 #5


co******@aol.com wrote:
On 21-Jun-2005, ni**********@webinfo.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.txt") 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>

Nov 13 '05 #6
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!

Nov 13 '05 #7
pi********@hotmail.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.

Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for...
1
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have...
1
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
1
by: Alan | last post by:
OK this is a weird one. I've got an import routine going whereby name and address data is pulled into a table from a csv file. I'm having strange results when importing postcode/zip data into...
2
by: denisel | last post by:
Hi, We will be conducting surveys through SurveyMonkey online and will be importing the answers by tab delimited or comma delimited file into access. I was wondering if there is specific way to...
2
by: Debbiedo | last post by:
I have a text file that I am importing into an Access table that was generatred from data exported from a Word file. Several (about 20-30) fields are from check boxes on the Word form. These fields...
1
by: Kosmos | last post by:
Hey guys, I'm fairly familiar with access by now, but I've never worked with importing text into an access database. I have some fairly large text files (lets say, for example, a folder of 20 text...
1
by: aconti74 | last post by:
Hello I am new to vba programming/coding. I am writing a program that goes through a directory of text files and imports them into the database. The problem is eventually the database gets to big...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.