Solved

Importing a fixed length text file

Posted on 2001-06-06
17
1,200 Views
Last Modified: 2008-03-17
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!
0
Comment
Question by:psilli1
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 7

Expert Comment

by:q2eddie
ID: 6159745
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
0
 
LVL 3

Expert Comment

by:Maxim10553
ID: 6159757
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.
0
 
LVL 1

Author Comment

by:psilli1
ID: 6159834
#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
0
 
LVL 1

Author Comment

by:psilli1
ID: 6159850
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???
0
 
LVL 3

Expert Comment

by:Maxim10553
ID: 6160021
Are you using ADO? ODBC driver?
0
 
LVL 20

Expert Comment

by:hes
ID: 6160034
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
0
 
LVL 1

Author Comment

by:psilli1
ID: 6160035
DAO
0
 
LVL 18

Expert Comment

by:deighton
ID: 6160060
..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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Accepted Solution

by:
q2eddie earned 200 total points
ID: 6160088
Hi, again.

#Experiment
Here is what I did.
a. Created a fixed-length text file with 152,034 records and 26 fields (integers, floats, and chars).
b. Created a schema.ini file and generated its contents.
(Note: the text file does NOT have a header row.)
c. Ran my VB code to import text file into both an Access database and a FoxPro table.

<----------- Schema.INI Begins ---------->
[fixedlen.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=UNQBILL Integer Width 11
Col2=UNQCUST Integer Width 11
Col3=MONTH Integer Width 11
Col4=YEAR Integer Width 11
Col5=UNQDAY Integer Width 11
Col6=UNQRATE Integer Width 11
Col7=UNQCYCLE Integer Width 11
Col8=STARTDATE Char Width 19
Col9=STOPDATE Char Width 19
Col10=CREATEDATE Char Width 19
Col11=BILLVER Integer Width 11
Col12=DATAVER Integer Width 11
Col13=CURMOKWORG Float Width 20
Col14=TOTALKWHOR Float Width 20
Col15=TOTALKWH Float Width 20
Col16=ACTUALKW Float Width 20
Col17=ANNUALKW Float Width 20
Col18=BILLINGKW Float Width 20
Col19=MULTKW Float Width 20
Col20=MULTKWH Float Width 20
Col21=MULTKWHCAL Float Width 20
Col22=READINGKW Float Width 20
Col23=READINGKWH Float Width 20
Col24=TOTALPRETA Float Width 20
Col25=TAXDUE Float Width 20
Col26=TOTALDUE Float Width 20
<----------- Schema.INI Ends ---------->

<----------- 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)

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

myDB.Close

End Sub
'
'
Sub csv2dbf()

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

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

myDB.Close

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

Results...
My PC: WinNT 4.0/P II/350 Mhz/256 MB RAM
Text File -> DBF in  89.4179 seconds
Text File -> MDB in 101.4375 seconds

Bye. -e2
0
 
LVL 1

Author Comment

by:psilli1
ID: 6160107
g2eddie,

Can this go straight from the text file to a DBF without the need for access?
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6160174
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
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6160218
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
0
 
LVL 1

Author Comment

by:psilli1
ID: 6160368
Getting a cannot find installable ISAM error
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6160423
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
0
 
LVL 1

Author Comment

by:psilli1
ID: 6160579
Done it using DBase III,

Still taking approx 5 minutes but a massive improvement on 30.
0
 
LVL 1

Author Comment

by:psilli1
ID: 6166782
Thanks,

Works Great!!!
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6168723
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now