Importing a large amount of data as 1 line string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to import files into access that maybe up to 4 meg in size. The file
is one continuous string and I will be breaking down the data in access to 80
column lines. The data is mixed text and numeric so I need the data type as
text. I know there is a limit as far as memo or text field types. Thanks
 
Gary G said:
I need to import files into access that maybe up to 4 meg in size. The file
is one continuous string and I will be breaking down the data in access to
80
column lines. The data is mixed text and numeric so I need the data type
as
text. I know there is a limit as far as memo or text field types. Thanks
Click to expand...


There doesn't seem to be a question here.
Yes, you can store the whole 4 meg file in a single record in a memo field.
Could you store it in 80 columns? In only one row? Depends on how you
structure the columns. Memo fields are handled differently to text fields
and although you could have up to 1GB per record if stored in a memo field,
if you tried to have 80 columns each with 100 characters in it, the record
could not be saved as there is a 2000 characters per record limit (excluding
memo and ole objects).
 
Sorry. The question is how do I import a 4 meg file. When I setup my tabe as
1 memo field and then try to import, I get an error message that says One or
more rows of data un your file contains too many characters to import. The
maximum characters per row is 65000. Not sure what to do next. Thanks
 
Gary G said:
Sorry. The question is how do I import a 4 meg file. When I setup my tabe
as
1 memo field and then try to import, I get an error message that says One
or
more rows of data un your file contains too many characters to import. The
maximum characters per row is 65000. Not sure what to do next. Thanks
Click to expand...


If you want to get 4MB into the table, you will have to do it
programatically. Have a look at the following which gives an example of
this:
http://support.microsoft.com/?kbid=210486
 
Hi Gary,

There's a limit - in the thousands, not millions - on the number of
characters per line that the Access/Jet text import routines will
accept. You'll need to use VBA's file I/O statements to read the data
from the file, and then parse it and append it to a table.

The VBA function at
http://www.j.nurick.dial.pipex.com/Code/VBA/FileContents.htm will read
the contents of a file into a VBA string. It should work with a 4MB
file, but this will depend on the available virtual memory and on what
you do with the string. Or you can use the VBA Open statement to open
the file and read it bit by bit, either as a binary file or with
fixed-width records.

If Perl's installed on your system, this one-liner will insert a
linebreak every 80 characters and leave the original file as a backup.
Would that turn your file into an ordinary fixed-width file that
Access's built-in import facilities can handle?
perl -ibak -pe"s/(.{1,80})/\1\n/g" "D:\folder\filename.txt"




On Thu, 19 Jan 2006 00:36:04 -0800, Gary G <Gary
 
This subroutine will read a text file that is a single string into a table
breaking it at 80 characters for each record.
'---------------------------
Sub ReadLongTextFile()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim MyRecord As String
Dim i As Integer

Set dbs = CurrentDb
' Open sample file for binary access.
Open "c:\GetTest.txt" For Binary As #1
MyRecord = String(80, " ")
Set rst = dbs.OpenRecordset("CharacterTable")
Do While Not EOF(1)
Get #1, , MyRecord ' Read 80 char.
rst.AddNew
rst!Char80 = MyRecord
rst.Update
Loop
Close #1 ' Close file.
End Sub
'---------------------------

This assumes your file is "c:\GetTest.txt", your table is called
"CharacterTable", and your field in the table is called "Char80". Change
these values to match your database. It also requires a Reference to DAO to
be set.
 
Back
Top