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,A BBR_PART,F RAN_CODE,U OI,P_LIST_ NO,N_RETAI L_P,VAT_CO DE,BULK_QT Y1,BLK_RET CD1,BULK_Q TY2,BLK_RE TCD2,BULK_ QTY3,BLK_R ETCD3,DISC _CDE,EX_SU R_VAL,PRCE _APPCD,STO CK_CDE,SUP ER_CDE,SUP ER_PNO,SUP ER_QTY,SUP ER_PRICE,D ATE_NLA,SU PER_DCD1,R ETPRICEEU, 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!
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))
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,A
& 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!
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.
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.
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
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
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???
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
Not tried this yet but surely it is the innsert statements that are taking the time???
Are you using ADO? ODBC driver?
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;tab le=MOVE.TX T")
SQLStmt = "SELECT * INTO [MOVE2] IN '" & CurrDir & "\MOVE.MDB' "
SQLStmt = SQLStmt & "FROM MOVE.TXT"
'Err.Number = 0
DB2.Execute SQLStmt, dbFailOnError
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;tab
SQLStmt = "SELECT * INTO [MOVE2] IN '" & CurrDir & "\MOVE.MDB' "
SQLStmt = SQLStmt & "FROM MOVE.TXT"
'Err.Number = 0
DB2.Execute SQLStmt, dbFailOnError
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
g2eddie,
Can this go straight from the text file to a DBF without the need for access?
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\temp or.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\te mpor.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
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\temp
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\te
' Create the mdb file and table.
myDB.Execute "select * into fixedtest " & _
"FROM [Text;DATABASE=c:\temp\;].
' Append into an existing table inside an existing mdb file.
myDB.Execute "insert into fixedtest select * FROM [Text;DATABASE=c:\temp\;].
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\te mpor.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
>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\te
'
' 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\].
"FROM [Text;DATABASE=c:\temp\;].
'
' Close and delete the mdb file.
' We no longer need it.
'
myDB.Close
Kill "c:\temp\tempor.mdb"
End Sub
Bye. -e2
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
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
ASKER
Done it using DBase III,
Still taking approx 5 minutes but a massive improvement on 30.
Still taking approx 5 minutes but a massive improvement on 30.
ASKER
Thanks,
Works Great!!!
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
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
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