Link to home
Start Free TrialLog in
Avatar of psilli1
psilli1

asked on

Importing a fixed length text file

I am looking for a method of importing a fixed length text file into an access table.

I am using one at the present but it is far to slow as the text file has approximatley 150000 lines.

I am currently using

iFile = FreeFile
Open App.Path & "\" & current_file For Random As #iFile Len = 208

Do Until EOF(iFile) = True
      Get #iFile, , MyLayout  
     
      If Len(Trim(MyLayout.Field1)) = 0 Then
           GoTo Do_Loop  'to handle end of file
      End If

Dim full_descript As String
full_descript = Replace(MyLayout.Field5, "'", " ")
final_descript = Replace(full_descript, "&", " AND ")

insert_item = "INSERT INTO data_table(TAPETYPE, EFF_DATE, PART_NO,SUP_C_NO,DESCRIP,ABBR_PART,FRAN_CODE,UOI,P_LIST_NO,N_RETAIL_P,VAT_CODE,BULK_QTY1,BLK_RETCD1,BULK_QTY2,BLK_RETCD2,BULK_QTY3,BLK_RETCD3,DISC_CDE,EX_SUR_VAL,PRCE_APPCD,STOCK_CDE,SUPER_CDE,SUPER_PNO,SUPER_QTY,SUPER_PRICE,DATE_NLA,SUPER_DCD1,RETPRICEEU,RETSUPEREU,EXSUREU) VALUES ('" _
& MyLayout.Field1 & "','" & MyLayout.Field2 & "','" _
& MyLayout.Field3 & "','" & MyLayout.Field4 & "','" _
& final_descript & "','" & MyLayout.Field6 & "','" _
& MyLayout.Field7 & "','" & MyLayout.Field8 & "','" _
& MyLayout.Field9 & "','" & MyLayout.Field10 & "','" _
& MyLayout.Field11 & "','" & MyLayout.Field12 & "','" _
& MyLayout.Field13 & "','" & MyLayout.Field14 & "','" _
& MyLayout.Field15 & "','" & MyLayout.Field16 & "','" _
& MyLayout.Field17 & "','" & MyLayout.Field18 & "','" _
& MyLayout.Field19 & "','" & MyLayout.Field20 & "','" _
& MyLayout.Field21 & "','" & MyLayout.Field22 & "','" _
& MyLayout.Field23 & "','" & MyLayout.Field24 & "','" _
& MyLayout.Field25 & "','" & MyLayout.Field26 & "','" _
& MyLayout.Field27 & "','" & MyLayout.Field28 & "','" _
& MyLayout.Field29 & "','" & MyLayout.Field30 & "')"


MyDatabase.Execute (insert_item)

Do_Loop:
  Loop  'move to the next line

Hope someone can help!
Avatar of q2eddie
q2eddie
Flag of United States of America image

Hi, psilli1.

I do have a simple method of importing a csv into access/foxpro table.

#Questions
1. Is this Q related to your other Q "Reading Text to DBF"?
2. In addition to being fixed-length, is the text file also comma-delimited?
3. Has the text ISAM driver been registered on your machine?

#Comments
I am testing the import of a 7MB, 25300 record csv file into both an access database and a dbase table now.  I'll post when I'm done.

Bye. -e2
1.you should not be going at the database while you have your text file open, and instead of using do loop and reading file line by line use this code to store the entire file to a single varialbe-its quicker strfilecontents = StrConv(InputB(LOF(FileNum), FileNum), vbUnicode)

2.Use stored procedures when you do actually go at the database. There are much quicker. Make sure your tables are indexed, and a primary key is set. Make sure to close all recordsets when not in use.
Avatar of psilli1
psilli1

ASKER

#Questions
1. Is this Q related to your other Q "Reading Text to DBF"?

Yeah but I have solved most of the problems now it is just the getting the text into access bit left.


2. In addition to being fixed-length, is the text file also comma-delimited?

No,  just a fixed length file.


3. Has the text ISAM driver been registered on your machine?

I assume so!!!!


#Comments
I am testing the import of a 7MB, 25300 record csv file into both an access database and a dbase table
now.  I'll post when I'm done.

The files I am using are in the region of 28MB to give you an idea

Thanks
Avatar of psilli1

ASKER

1.you should not be going at the database while you have your text file open, and instead of using do
loop and reading file line by line use this code to store the entire file to a single varialbe-its quicker
strfilecontents = StrConv(InputB(LOF(FileNum), FileNum), vbUnicode)

Not tried this yet but surely it is the innsert statements that are taking the time???
Are you using ADO? ODBC driver?
Avatar of hes
You can also use a schema.ini file. The schema file has to be in the same directory as the text file.
The schema itself contains te name of the text file and delimeter descriptions. Example name of text
file is move.txt

[MOVE.TXT]
ColNameHeader=False
CharacterSet=ANSI
Format=FixedLength
MaxScanRows=0
Col1=ORDER_NO Char Width 6
Col2=UPC DOUBLE Width 11
Col3=DESC Char Width 29
Col4=QTY DOUBLE Width 9

Then use something like this

DBEngine.IniPath = CurrDir + "\SCHEMA.INI"
Set WS = DBEngine.Workspaces(0)
Set DB1 = WS.OpenDatabase(CurrDir + "\MOVE.MDB", False, False)
Set DB2 = WS.OpenDatabase(CurrDir, False, False, _
              "TEXT;Database=CurrDir;table=MOVE.TXT")
 
 
  SQLStmt = "SELECT * INTO [MOVE2] IN '" & CurrDir & "\MOVE.MDB' "
  SQLStmt = SQLStmt & "FROM MOVE.TXT"
  'Err.Number = 0
  DB2.Execute SQLStmt, dbFailOnError
Avatar of psilli1

ASKER

DAO
..Don't think you can speed it up much..reading whole file at once might give a small advantage, but if file becomes too large then the machine will grind to a halt, eventually it will crash out of memory.
ASKER CERTIFIED SOLUTION
Avatar of q2eddie
q2eddie
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of psilli1

ASKER

g2eddie,

Can this go straight from the text file to a DBF without the need for access?
Oops.

1. It should be txt2dbf not csv2dbf.  Won't make any difference though.

2. If you are importing into an existing mdb file, then use the following line instead of CreateDatabase.

Set myDB = OpenDatabase("c:\temp\tempor.mdb")

3. If you are appending into an existing table inside an existing mdb file, then you will need to change the execute statement.

<-------------- Sample Code Begins -------------->
Sub txt2mdb()

Dim myDB As Database
On Error Resume Next
Kill "c:\temp\tempor.mdb"
Set myDB = CreateDatabase("c:\temp\tempor.mdb", dbLangGeneral)

' Create the mdb file and table.
myDB.Execute "select * into fixedtest " & _
                "FROM [Text;DATABASE=c:\temp\;].[fixedlen.txt]"

' Append into an existing table inside an existing mdb file.
myDB.Execute "insert into fixedtest select * FROM [Text;DATABASE=c:\temp\;].[fixedlen.txt]"

myDB.Close

End Sub
<-------------- Sample Code Ends -------------->

Results...
The fixedtest table ends up with 304,068 records.
It took 97.97266 seconds to create the mdb file.
It took 103.3672 seconds to append 152,034 records into that table.
(I am using the same fixed-length text file.)

Bye. -e2
Hi, again.

>Can this go straight from the text file to a DBF without
>the need for access?
The method that I am using needs a "workspace" which is why I am using the CreateDatabase function.  (I know that I am probably misusing that term.)  However, it does not have to directly use access to import a text file into a dbf.

Let's look at the text-to-dbf code for a little bit.

Sub txt2dbf()

'
' Uses Microsoft DAO 3.51 Object Library.
'

Dim myDB As Database
On Error Resume Next
Kill "c:\temp\tempor.mdb"

'
' Here, I am creating a temporary mdb file.
'

Set myDB = CreateDatabase("c:\temp\tempor.mdb", dbLangGeneral)

'
' I am executing an SQL command that ...
' a. selects records from the text file
' b. creates a new table called fixedlen.dbf
' c. inserts those records into the new table
'
' Notice that I am not accessing the access mdb file in any way.
'

myDB.Execute "select * into [FoxPro 3.0;DATABASE=d:\testdbf\].[fixedlen] " & _
               "FROM [Text;DATABASE=c:\temp\;].[fixedlen.txt]"

'
' Close and delete the mdb file.
' We no longer need it.
'

myDB.Close
Kill "c:\temp\tempor.mdb"

End Sub

Bye. -e2
Avatar of psilli1

ASKER

Getting a cannot find installable ISAM error
Hmmm...

Either change the "FoxPro 3.0" to "dBase III" or re-register your text ISAM driver.

#Re-registering your text ISAM driver
If you are using Win9x, then ...
a. Open a DOS box.
b. Go to windows\system
c. dir mstext*.dll
You will probably get two files: mstext35.dll and mstext40.dll.

d. type regsvr32 mstext35.dll
(I had to do this on my WinNT PC recently to get my text code to work.)

If you are using WinNT, then ...
same thing but go to c:\winnt\system32 instead.

Bye. -e2
Avatar of psilli1

ASKER

Done it using DBase III,

Still taking approx 5 minutes but a massive improvement on 30.
Avatar of psilli1

ASKER

Thanks,

Works Great!!!
Hi, again.

FOLLOWUP:
>Done it using DBase III,...
I tried this on another machine and found that DBase III will work where FoxPro 3.0 will not.  Hmmm... I believe that DBase III is probably one of the inherent formats of Jet were as FoxPro 3.0 may not be.

Bye. -e2