How can I Import data from one text file to multiple tables?

[dash]

Thanks a lot John,
Yeah It is going to be a regular job, I would prefer to do it using
VBA.
Could you be please be more specific about the Access - VBA coding
part, as its been some time I have done something in it.

I would really appreciate if you can write the code for me (how to
write the recordsets)
Thanks a lot
Dash




Dash,
If this is a one-off task, I'd use text file tools to split the file
into three simple files, one for each table, that can be imported in
the
usual way. Assuming that the input file is laid out like this:


Table1 Record1
Table2 Record1
Table3 Record1
Table1 Record2
Table2 Record2
Table2 Record2
...


I'd use Perl from the Windows command prompt, substituting the actual
names and paths of the files you want.


perl -ne"print if ($. % 3) == 1" INPUT.txt > TABLE1.txt
perl -ne"print if ($. % 3) == 2" INPUT.txt > TABLE2.txt
perl -ne"print if ($. % 3) == 0" INPUT.txt > TABLE3.txt


Perl uses $. for the line number of the input, so
$. % 3
is "line number modulo 3", which cycles 1,2,0,1,2,0 from the first line

on. If Perl isn't installed on your computer you can download it free
from www.activestate.com


It's possible to write code to do the same thing in just about any
other
programming or scripting language. If this import was going to be a
regular task, I'd do it differently, writing Access VBA code that opens

three recordsets, one for each table, and then reads the file a line at

a time. The first line would be parsed and appended to the first table,

then the second and third to their tables, and then back to line 4 and
the first table again.

 

[John Nurick]

This is air code but should give the general idea. I'm assuming your
three tables are called XXX, YYY and ZZZ, and that it's a simple
tab-delimited file.


Dim dbD As DAO.Database
Dim rsXXX As DAO.Recordset
Dim rsYYY As DAO.Recordset
Dim rsZZZ As DAO.Recordset
Dim lngFN As Long
Dim strFN As String
Dim strLine As String
Dim arFields As Variant
Dim j As Long

'Open file
strFN = "C:\Folder\File.txt"
lngFN = FreeFile()
Open strFN For Input As #lngFN

'Open recordsets
Set dbD = CurrentDB()
Set rsXXX = dbD.OpenRecordset("XXX")
Set rsYYY = dbD.OpenRecordset("YYY")
Set rsZZZ = dbD.OpenRecordset("ZZZ")

'Loop to read the file
Do Until EOF(1) ' (Note 1)

'Read first line of group of 3
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))
With rsXXX
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 to .Fields.Count
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'Now a similar block of code to read
'the second line and append it to YYY
...

'And the third line
...

Loop 'back to read the first line of the next group

'Close recordsets and file
rsXXX.Close
rsYYY.Close
rsZZZ.Close
Close #lngFN

NOTES
(1) This assumes that the number of lines in the text file is guaranteed
to be a multiple of 3.
(2) The Split() function is fine for a simple delimited file, but won't
suffice for a csv file or a fixed-width file: these will need custom
code.
(3) You may want to adjust field values here, for instance to replace
zero width strings in the text file with Nulls in the Access tables.

 

[dash]

Hi again,
Its working John, I implemented the code on 2 tables(MS-ACCESS) and one
small text file with 2 records of each table...it works great.

But I am facing one problem in my actual project...every thing runs
great except that a funny character exists after every record in my
text file, because of which compiler assume that it has reached EOF and
gives error.
A "square" looking symbol is there after every record in the text file
irrespective of records of different tables ( that means after every
one record no matter from which table it is........this symbol exists)

please note I tried to copy that EOF square symbol here but no good.

Now, I tried deleting that symbol and entering(carriage return) on the
same place so that the new record will start on new line and my program
works fine.
But the text file is too big since it contains more than 65000 records
of each table...its impossible to do it by hand.

Please help me, How can i replace <enter> or <carriage return> in place
of that EOF square symbol.

Or another way is to incorporate some validation in VBA so that the
compiler will recognize the EOF square symbol and reads the next
record.

I hope I have explained the problem well....pl write back if its
unclear or if you have any questions.


MY code is as follows:


Dim dbD As DAO.Database

Dim rsAccount_Balance As DAO.Recordset
Dim rsAccount_Balance_Count As DAO.Recordset
Dim rsAllocation As DAO.Recordset
Dim rsAllocation_Count As DAO.Recordset
Dim rsCitistreet_Demog As DAO.Recordset
Dim rsCitistreet_History As DAO.Recordset


Dim lngFN As Long
Dim strFN As String
Dim strLine As String
Dim arFields As Variant
Dim j As Long


'Open file
strFN = "C:\Documents and Settings\d_b\My
Documents\Database\demo.txt"
lngFN = FreeFile()
Open strFN For Input As #lngFN


'Open recordsets
Set dbD = CurrentDb()
Set rsAccount_Balance = dbD.OpenRecordset("Account_Balance")
Set rsAccount_Balance_Count =
dbD.OpenRecordset("Account_Balance_Count")
Set rsAllocation = dbD.OpenRecordset("Allocation")
Set rsAllocation_Count = dbD.OpenRecordset("Allocation_Count")
Set rsCitistreet_Demog = dbD.OpenRecordset("Citistreet_Demog")
Set rsCitistreet_History = dbD.OpenRecordset("Citistreet_History")


'Loop to read the file
Do Until EOF(1) ' (Note 1)

'Read first line of group of 5
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsCitistreet_Demog
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1 'John pl note here it should be
-1'
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------2-----------------------
'Read first line of group of 3
Line Input #1, strLine ' HERE IS WHERE THE COMPILER GIVES ERROR...
'AS IT THINKS THAT IT HAS ALREADY REACHED THE EOF....AS THE SQUARE
SYMBOL EXISTS
'AFTER THE FIRST RECORD

'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAccount_Balance
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------3-----------------------
'Read first line of group of 3
Line Input #3, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAllocation
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With





Thanks
Dash

 

[John Nurick]

First question: when you say there's a square box at the end of the
record, do you mean it appears at the end of every third line in the
text file or at the end of every line?

Second, we need to find out just what those square characters are.
Assuming your text is in English or another Western language, the most
straightforward way is to use a hex editor.

If you don't already have one, there are many available for download,
e.g. Hexedit from http://www.expertcomsoft.com/ in both free and
paid-for versions. Just open the text file in the hex editor, compare
the "text" view in one pane with the hexadecimal bytes displayed in the
other pane, and work out which hex bytes correspond to the square boxes.

(You may find that the text appears alternating with null bytes; e.g.
"The cat" appears as ".T.h.e. c.a.t"; this means it's a Unicode file
with two bytes per character, so there'll be two hex characters for each
square box. )

(If you're an old DOS hand, you can also get a hexadecimal view of the
file by opening a Windows command prompt and using DEBUG).

Please post back showing exactly what sequence of bytes appears at the
end of each line of the text file, and at the end of each record (i.e.
each third line) if it's different. After that it should be easy to
modify the import routine.

I'm going to be travelling for a few days, so may not be able to get
back to you. But I'm sure someone else will help.

Hi again,
Its working John, I implemented the code on 2 tables(MS-ACCESS) and one
small text file with 2 records of each table...it works great.

But I am facing one problem in my actual project...every thing runs
great except that a funny character exists after every record in my
text file, because of which compiler assume that it has reached EOF and
gives error.
A "square" looking symbol is there after every record in the text file
irrespective of records of different tables ( that means after every
one record no matter from which table it is........this symbol exists)

please note I tried to copy that EOF square symbol here but no good.

Now, I tried deleting that symbol and entering(carriage return) on the
same place so that the new record will start on new line and my program
works fine.
But the text file is too big since it contains more than 65000 records
of each table...its impossible to do it by hand.

Please help me, How can i replace <enter> or <carriage return> in place
of that EOF square symbol.

Or another way is to incorporate some validation in VBA so that the
compiler will recognize the EOF square symbol and reads the next
record.

I hope I have explained the problem well....pl write back if its
unclear or if you have any questions.


MY code is as follows:


Dim dbD As DAO.Database

Dim rsAccount_Balance As DAO.Recordset
Dim rsAccount_Balance_Count As DAO.Recordset
Dim rsAllocation As DAO.Recordset
Dim rsAllocation_Count As DAO.Recordset
Dim rsCitistreet_Demog As DAO.Recordset
Dim rsCitistreet_History As DAO.Recordset


Dim lngFN As Long
Dim strFN As String
Dim strLine As String
Dim arFields As Variant
Dim j As Long


'Open file
strFN = "C:\Documents and Settings\d_b\My
Documents\Database\demo.txt"
lngFN = FreeFile()
Open strFN For Input As #lngFN


'Open recordsets
Set dbD = CurrentDb()
Set rsAccount_Balance = dbD.OpenRecordset("Account_Balance")
Set rsAccount_Balance_Count =
dbD.OpenRecordset("Account_Balance_Count")
Set rsAllocation = dbD.OpenRecordset("Allocation")
Set rsAllocation_Count = dbD.OpenRecordset("Allocation_Count")
Set rsCitistreet_Demog = dbD.OpenRecordset("Citistreet_Demog")
Set rsCitistreet_History = dbD.OpenRecordset("Citistreet_History")


'Loop to read the file
Do Until EOF(1) ' (Note 1)

'Read first line of group of 5
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsCitistreet_Demog
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1 'John pl note here it should be
-1'
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------2-----------------------
'Read first line of group of 3
Line Input #1, strLine ' HERE IS WHERE THE COMPILER GIVES ERROR...
'AS IT THINKS THAT IT HAS ALREADY REACHED THE EOF....AS THE SQUARE
SYMBOL EXISTS
'AFTER THE FIRST RECORD

'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAccount_Balance
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------3-----------------------
'Read first line of group of 3
Line Input #3, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAllocation
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With





Thanks
Dash

 

[dash]

HI JOHN,
Thanks for your earlier reply.

first Question's answer:

The file contains records of 5 tables in one text file,in this order

record 1 of table 1,(square appears here)
record 1 of table 2,(square appears here)
record 1 of table 3,(square appears here)
record 1 of table 4,(square appears here)
record 1 of table 5,(square appears here)
record 2 of table 1, (square appears here)
record 2 of table 2,(square appears here)...

and so on....

the square box appears at the end of every record as indicated above


SECONDLY: I tried downloading the hex editor but was not
successful...Please tell me how can i send you the original text file
which contains "square symbol"...so that you can have a look at the
square symbol....i was trying to attach the text file from here...but
there is no option available here to attach any files....pl let me know
if its ok to forward you the text file to your personal address.

Lastly I am pasting the whole routine here........

============================================ROutine starts
here=================
Sub Main()
Dim dbD As DAO.Database

Dim rsAccount_Balance As DAO.Recordset
Dim rsAccount_Balance_Count As DAO.Recordset
Dim rsAllocation As DAO.Recordset
Dim rsAllocation_Count As DAO.Recordset
Dim rsCitistreet_Demog As DAO.Recordset
Dim rsCitistreet_History As DAO.Recordset


Dim lngFN As Long
Dim strFN As String
Dim strLine As String
Dim arFields As Variant
Dim j As Long


'Open file
strFN = "C:\Documents and Settings\dash_bibhuti\My
Documents\Database\demo.txt"
lngFN = FreeFile()
Open strFN For Input As #lngFN


'Open recordsets
Set dbD = CurrentDb()
Set rsAccount_Balance = dbD.OpenRecordset("Account_Balance")
Set rsAccount_Balance_Count =
dbD.OpenRecordset("Account_Balance_Count")
Set rsAllocation = dbD.OpenRecordset("Allocation")
Set rsAllocation_Count = dbD.OpenRecordset("Allocation_Count")
Set rsCitistreet_Demog = dbD.OpenRecordset("Citistreet_Demog")
Set rsCitistreet_History = dbD.OpenRecordset("Citistreet_History")


'Loop to read the file
Do Until EOF(1) ' (Note 1)

'--------------------------------1-----------------------
'Read first line of group of 3
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsCitistreet_Demog
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------2-----------------------
'Read first line of group of 3
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAccount_Balance
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------3-----------------------
'Read first line of group of 3
Line Input #3, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAllocation
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------4-----------------------
'Read first line of group of 3
Line Input #1, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAllocation_Count
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'--------------------------------5-----------------------
'Read first line of group of 3
Line Input #4, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))

With rsAccount_Balance_Count
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 To .Fields.Count - 1
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With


Loop 'back to read the first line of the next group


'Close recordsets and file

rsAccount_Balance.Close
rsAccount_Balance_Count.Close
rsAllocation.Close
rsAllocation_Count.Close
rsCitistreet_Demog.Close
rsCitistreet_History.Close
Close #lngFN

End Sub
============================================ROutine ends
here=================

First question: when you say there's a square box at the end of the
record, do you mean it appears at the end of every third line in the
text file or at the end of every line?

Second, we need to find out just what those square characters are.
Assuming your text is in English or another Western language, the most
straightforward way is to use a hex editor.

If you don't already have one, there are many available for download,
e.g. Hexedit from http://www.expertcomsoft.com/ in both free and
paid-for versions. Just open the text file in the hex editor, compare
the "text" view in one pane with the hexadecimal bytes displayed in the
other pane, and work out which hex bytes correspond to the square boxes.

(You may find that the text appears alternating with null bytes; e.g.
"The cat" appears as ".T.h.e. c.a.t"; this means it's a Unicode file
with two bytes per character, so there'll be two hex characters for each
square box. )

(If you're an old DOS hand, you can also get a hexadecimal view of the
file by opening a Windows command prompt and using DEBUG).

Please post back showing exactly what sequence of bytes appears at the
end of each line of the text file, and at the end of each record (i.e.
each third line) if it's different. After that it should be easy to
modify the import routine.

I'm going to be travelling for a few days, so may not be able to get
back to you. But I'm sure someone else will help.

 

[dash]

Hi John,
I tried again running the hex edit software and this time it worked

In text, it shows the first record of first table as following
001203937.10.19280219.M.00000000000.68105.20040901.00000000.00.00.00000011912.20050630..

where in the hexadecimal values of the first record are as follows:
30 30 31 32......etc etc .......33 30 09 0A

where 0A represents the last dot and
09 represents the second last dot
please let me know if i can provide any other information.

Thanks again
Dash

 

[dash]

Hey John,
I am really sorry to mail you three times in less than 2 hours time.
but the good news is am all set with the problem.....
The solution is , I just used "Find all and Replace" option in Hexedit
and made every record to start on a new line and its working fine.

Thanks a lot
please let me know of you need any information about the same, i know
this is not one of the best approach, but it works fine and it does not
take lot fo time.

Dash

 

[dash]

Hi John,
It was working fine with 10-15 records in the text file.....but its
taking lot of time to find and replace in the actual text file which
contains more than 65000 records of 5 tables

I am replacing 09 0A with 09 0A 0D 0A so as to create one empty line
after every record....which is the trick that worked for the smaple
test run.

But Its taking hell lot of time.....PL suggest some better and faster
way to do it.

Thanks
Dash

 

[John Nurick]

Hi Dash,

We haven't actually discussed this yet, but I get the impression that
your data is tab-delimited, i.e. with a tab character - i.e. chr(9) -
between each field. Also, 09 0A is an unlikely combination to end the
line of a text file, while plain 0A is the standard newline in the
Unix/Linux world.

This makes me feel that your data file probably just has 0A between
lines, and that the 09 in "09 0A" is a field separator, i.e. that in the
lines you've looked at there's an empty last field.

If I'm right, you'll find that the file contains 0A characters that are
not preceded by 09. In that case, you don't need to replace 09 0A with
09 0A 0D 0A, but to replace 0A with 0D 0A. (I'd expect this to be a
fairly slow global replace operation in a hex editor because each
replacement means inserting bytes into the file.)

If I'm wrong, and every single 0A is preceded by 09, you can just
replace 09 0A with 0D 0A. (This should be a fairly fast operation in a
hex editor because it doesn't involve changing the length of the file.)

Either way, if the file isn't more than a few megabytes you can do the
replacement with a few lines of VBA code added to the routine you
already have. The idea is to open the file, read it all into a variable,
use the VBA Replace() function, and write the result out to a temporary
file:

This is what I originally posted, with modifications. Still air code<g>:

Dim dbD As DAO.Database
Dim rsXXX As DAO.Recordset
Dim rsYYY As DAO.Recordset
Dim rsZZZ As DAO.Recordset
Dim lngFInput As Long
Dim strFInput As String
Dim lngFTemp As String
Dim strFTemp As String
Dim strLine As String
Dim arFields As Variant
Dim j As Long

'Open input file and temp file
strFInput = "C:\Folder\File.txt"
lngFInput = FreeFile()
Open strFInput For Input As #lngFInput
strFTemp = "C:\Folder\Temp.$$$"
lngFTemp = FreeFile()
Open strFTemp For Output As #lngFTemp

'Read input file, process line ends and write to temp file
'Slurp entire file, won't work for huge files
strLine = Input(LOF(lngFInput), #lngFInput)
'Replace 0A with 0D 0A
strLine = Replace(strLine, vbLF, vbCrLf)
'Change "vbLF" to "vbTab & vbLF" to replace 09 0A with 0D 0A
'Write to temp file
Print #lngFTemp, strLine
Close #lngFInput

Close #lngFTemp
#lngFTemp = FreeFile()
Open strFTemp For Input As #lngFTemp

'Open recordsets
Set dbD = CurrentDB()
Set rsXXX = dbD.OpenRecordset("XXX")
Set rsYYY = dbD.OpenRecordset("YYY")
Set rsZZZ = dbD.OpenRecordset("ZZZ")

'Loop to read the file
Do Until EOF(lngFTemp) ' (Note 1)

'Read first line of group of 3
Line Input #lngFTemp, strLine
'Split line into fields (Note 2)
arFields = Split(strLine, Chr(9))
With rsXXX
'Add new blank record
.AddNew

'Loop through the fields (Note 3)
For j = 0 to .Fields.Count
.Fields(j).Value = arFields(j)
Next j

.Update 'save the record
End With

'Now a similar block of code to read
'the second line and append it to YYY
...

'And the third line
...

Loop 'back to read the first line of the next group

'Close recordsets and file
rsXXX.Close
rsYYY.Close
rsZZZ.Close
Close #lngFTemp
Kill strFTemp

 

[dash]

Yeah John, You are right i.e 0A is between lines and 09 is the field
separator.

i.e
<Field1-Table1> 09 <Field2-Table1> 09 <Field3-Table1> 09 0A
<Field1-Table2> 09 <Field2-Table2> 09 <Field3-Table2> 09
<Field4-Table2> 09 0A
etc............etc

You are also right about the replacing 0A with 0D 0A in HEXEDIT
aplication.....but still it takes lot of time...as you said it has to
insert bytes in the text file.

The file size is 17 MB

Will it be faster if i can incorporate your code (VBA Replace
function)in my project instead of replacing 0A with 0D 0A in Hexedit?

I am still looking for a better approach, Let me know if you have some
idea.
thanks again
Dash

 

[John Nurick]

I would expect the replacement to run much faster using VBA the way I
suggested. Try it.

Yeah John, You are right i.e 0A is between lines and 09 is the field
separator.

i.e
<Field1-Table1> 09 <Field2-Table1> 09 <Field3-Table1> 09 0A
<Field1-Table2> 09 <Field2-Table2> 09 <Field3-Table2> 09
<Field4-Table2> 09 0A
etc............etc

You are also right about the replacing 0A with 0D 0A in HEXEDIT
aplication.....but still it takes lot of time...as you said it has to
insert bytes in the text file.

The file size is 17 MB

Will it be faster if i can incorporate your code (VBA Replace
function)in my project instead of replacing 0A with 0D 0A in Hexedit?

I am still looking for a better approach, Let me know if you have some
idea.
thanks again
Dash