Importing selected data from a text file into an MS Access table. 1

Giffy · Jul 28, 2002

I have a text file with four summary headers and one detail block of data labelled "DELIVERY DATA". These are separated by open lines.

This text file is downloaded from the mainframe in exactly the same format every time.

Please help me with a working sample of VBA code to automate the selection and importing of the "DELIVERY DATA" block of text only into an MS Access table.

I have browsed all the threads in this regard without any satisfaction. Is anyone willing to supply me with the specific code for the text file as outlined below.

The text file is called import.txt and an abbreviated version looks like this:

UNIT SUMMARY,UNIT,BUNDLES,TONS
,041,17, 507.100
,040,10, 234.200
,042,1, 29.250
,*,43, 1287.450
,053,15, 421.350
,014,40, 866.000

SEASON SUMMARY,SEASON,,TONS FIBRE,TONS MOISTURE,TONS CANE
, 2002,, 903.409, 4391.032,6310.200

MONTHLY SUMMARY,SEASON,MONTH,FIBRE,MOISTURE,TONS
, 2002, 2, 216.460, 968.095, 1391.100
, 2002, 3, 215.195, 1019.123, 1456.750
, 2002, 4, 206.244, 1032.628, 1493.300
, 2002, 5, 265.509, 1371.186, 1969.050

WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS
, 2002, 8, 67.121, 290.745, 419.200
, 2002, 9, 149.339, 677.350, 971.900
, 2002, 10, 136.685, 648.193, 925.300
, 2002, 11, 78.510, 370.929, 531.450
, 2002, 17, 54.913, 280.592, 405.300
, 2002, 18, 151.331, 752.036, 1088.000
, 2002, 19, 139.831, 733.469, 1045.700
, 2002, 20, 125.678, 637.718, 923.350

DELIVERY DATA,SEASON,WEEK,DATE,VOUCHER,TONS
,2002,8,18-04-2002 12:22:52,B170485, 27.300
,2002,8,18-04-2002 17:22:05,B170786, 28.250
,2002,8,19-04-2002 01:36:21,B170787, 29.300
,2002,8,19-04-2002 03:00:21,B170788, 29.900
,2002,8,19-04-2002 05:30:26,B170789, 28.300
,2002,8,19-04-2002 13:13:52,B170790, 28.400
,2002,8,19-04-2002 16:23:44,B170791, 28.250

Thanks in anticipation,

Howard

GrantDB · Jul 29, 2002

I would probably read the text file, and then work with each bit of information. Am I right in thinking that you want to insert each block into a different table?

This code is pretty rough, but it shoul;d give you an idea of how to gather the data

I hope it helps

Grant

Dim MyString
Dim StringArray
Dim counter As Integer
Open "c:\temp\tempfile.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
'could use just input if you have the same number of fields on each line - stringone, stringtwo etc
Line Input #1, MyString ' Read data into two variables.
Debug.Print MyString ' Print full line to the Immediate window.
'some check to see what block is being dealt with
'could check for blank lines
'Print individual fields
StringArray = Split(MyString, ",&quot

this will print out the values
For counter = 0 To UBound(StringArray)
'print
Debug.Print trim(StringArray(counter))
Next counter
Loop
Close #1 ' Close file.

Giffy · Jul 29, 2002

Thanks Grant, You were right on the button-I do need to put the data into different tables.

I will try your code and let you know how I got it it to work.

Howard

Giffy · Aug 2, 2002

Hi Grant,

I need to search for the following block of data only and import it into an an MS Access table in this format:-

DELIVERY DATA,SEASON,WEEK,DATE,VOUCHER,TONS
,2002,8,18-04-2002 12:22:52,B170485, 27.300
,2002,8,18-04-2002 17:22:05,B170786, 28.250
,2002,8,19-04-2002 01:36:21,B170787, 29.300
,2002,8,19-04-2002 03:00:21,B170788, 29.900
,2002,8,19-04-2002 05:30:26,B170789, 28.300
,2002,8,19-04-2002 13:13:52,B170790, 28.400
,2002,8,19-04-2002 16:23:44,B170791, 28.250

DELIVERY DATA is the name of the range of data. This is what needs to be searched for.

The rest of the info as at the beginning of this thread, I do not need.

Can you show me me how to do this in VBA?

Hope you can

Regards

Howard

GrantDB · Aug 2, 2002

OK, although I was only going to give a few pointers, I ended up doing a demo.

Simply past this code on a form. Add a button (command1) and a list box (list1). then press the button. You will need to change the path - Open "c:\temp\tempfile.txt"

Hopefully is should be pretty obvious what it does, but:
Searches each line of your file

At it line it checks to see if it is
- a blank line, or
- a header (compares the line with the header array)
- if it is none of these it must be data (the variable thisLineType is checked to see if it is still empty)

The info is added to a list box for you to study.

Let me know how you get on with it

Grant
-------------

Option Explicit

Private Sub Command1_Click()
Dim MyString
Dim StringArray
Dim counter As Integer
Dim linecount As Integer
Dim HeaderArray(4) As String
'use to store the line type - blank, header, data
Dim thisLineType As String
HeaderArray(0) = "UNIT SUMMARY,UNIT,BUNDLES,TONS"
HeaderArray(1) = "SEASON SUMMARY,SEASON,,TONS FIBRE,TONS MOISTURE,TONS CANE"
HeaderArray(2) = "MONTHLY SUMMARY,SEASON,Month,FIBRE,MOISTURE,TONS"
HeaderArray(3) = "WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS"
HeaderArray(4) = "DELIVERY Data,SEASON,WEEK,Date,VOUCHER,TONS"

linecount = 0
Open "c:\temp\tempfile.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
'could use just input if you have the same number of fields on each line - stringone, stringtwo etc
thisLineType = ""
Line Input #1, MyString ' Read data into two variables.
linecount = linecount + 1
'just in case there is white space
MyString = Trim(MyString)
If MyString = "" Then
thisLineType = "Line " & linecount & ": blank"
List1.AddItem thisLineType
List1.AddItem "The text: " & MyString
Else
'check for header
For counter = 0 To UBound(HeaderArray)
If LCase(MyString) = LCase(HeaderArray(counter)) Then
thisLineType = "Line " & linecount & ": Header"
End If
Next counter
List1.AddItem thisLineType
List1.AddItem "The text: " & MyString

'if none of these thisLineType is empty, so must be data
If thisLineType = "" Then
thisLineType = "Line " & linecount & ": Data"
List1.AddItem thisLineType
List1.AddItem "The text: " & MyString

'Print individual fields
StringArray = Split(MyString, ",&quot

For counter = 0 To UBound(StringArray)
'print and strip out " char
List1.AddItem "Variable " & counter & ": " & Trim(Replace(StringArray(counter), Chr(34), "&quot

)
Next counter
End If
End If

Loop
Close #1 ' Close file.

End Sub

Giffy · Aug 3, 2002

Grant, Thanks for you precious time.

When I run the above code, Access gives the following error.

Compile error:

Method or Data member not found

Grant, this occurs at the code line

Code:

List1.AddItem thisLineType

.AddItem is highlighted and the help reveals the following suggestions:-

Method or data member not found (Error 461)

The collection, object, or user-defined type doesn't contain the referenced member. This error has the following causes and solutions:

You misspelled the object or member name.
Check the spelling of the names and check the Type statement or the object documentation to determine what the members are and the proper spelling of the object or member names.

You specified a collection index that's out of range.
Check the Count property to determine whether a collection member exists. Note that collection indexes begin at 1 rather than zero, so the Count property returns the highest possible index number.

I know that your code is correct so what reference or what data type needs to be added?

Or is there some other advise that you can give?

Many thanks

Howard

GrantDB · Aug 3, 2002

Oh, my mistake. I actually did the programming in Visual Baisc. In Access (VBA) the listbox is slightly differet, since it is aimed at database/bound controls. Since the reason behind the listbox was to help you see what is happening it isn't strictly necessary.

However, I have now changed the code (it also works better). Instead of the listbox add a textbox (make the width quite wide so you can see the data. The textbox will be used to view the data

I hope that works, sorry for being a bit stupid. If you need help actually entering the info into your tables then let me know, also me know if the code doesn't work properly

Grant

Private Sub Command1_Click()
Dim MyString
Dim StringArray
Dim counter As Integer
Dim linecount As Integer
Dim HeaderArray(4) As String
'use to store the line type - blank, header, data
Dim thisLineType As String
HeaderArray(0) = "UNIT SUMMARY,UNIT,BUNDLES,TONS"
HeaderArray(1) = "SEASON SUMMARY,SEASON,,TONS FIBRE,TONS MOISTURE,TONS CANE"
HeaderArray(2) = "MONTHLY SUMMARY,SEASON,Month,FIBRE,MOISTURE,TONS"
HeaderArray(3) = "WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS"
HeaderArray(4) = "DELIVERY Data,SEASON,WEEK,Date,VOUCHER,TONS"

linecount = 0
Open "c:\temp\tempfile.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
'could use just input if you have the same number of fields on each line - stringone, stringtwo etc
thisLineType = ""
Line Input #1, MyString ' Read data into two variables.
linecount = linecount + 1
'just in case there is white space
MyString = Trim(MyString)
If MyString = "" Then
thisLineType = "Line " & linecount & ": blank"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString
Else
'check for header
For counter = 0 To UBound(HeaderArray)
If LCase(MyString) = LCase(HeaderArray(counter)) Then
thisLineType = "Line " & linecount & ": Header"
End If
Next counter
If thisLineType <> "" Then
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'if none of these thisLineType is empty, so must be data
ElseIf thisLineType = "" Then
thisLineType = "Line " & linecount & ": Data"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'Print individual fields
StringArray = Split(MyString, ",&quot

For counter = 0 To UBound(StringArray)
'print and strip out " char
'list1.AddItem "Variable " & counter & ": " & Trim(Replace(StringArray(counter), Chr(34), "&quot

)
Text1.Value = Text1.Value & vbCrLf & "Variable " & counter & ": " & Trim(Replace(StringArray(counter), Chr(34), "&quot

)
Next counter
End If
End If

Loop
Close #1 ' Close file.

End Sub

Giffy · Aug 4, 2002

Grant, the code works properly and is most educational. It will help me to understand the next step. Thanks.

Can I take your offer up in imorting the DELIVERY DATA data range only into an Access table using VBA?

To refresh, the challenge that this text file (as at the beginning of the thread) poses, is that it has five ranges of data.

I only need the DELIVERY DATA range of data imported into the MS Access table.

Now, to import, I usually use the TransferText method. This is unable to do what I request above. Anyway, it's not about what I know right now, but what you know.

Please show me step by step how I can use VBA to import only the DELIVERY DATA range into an Access 2000 table (with first line as field names). The other data ranges and all blank lines must be ommited.

I hope that my terminology is correct

Thanks once again for spending time with me here.

Howard

GrantDB · Aug 4, 2002

OK, my code above get all ranges. It was able to do this because of the array that I defined at the beginning of the code, i.e

HeaderArray(0) = "UNIT SUMMARY,UNIT,BUNDLES,TONS"

You could in theory modify the code to only respond on a particular array, either by only having one HeaderArray, or responsing to a particular arrau number ie HeaderArray(4). personally I would use the second option as it will be more flexible in the future.

So, here is a another version:
Finds header array3
HeaderArray(1, 3) = "WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS"

Inserts data into access table using sql, the table is
HeaderArray(0, 3) = "Table4"

when an insert has occure, this header is marked as done
HeaderArrayDoneStatus(3) = True

I noticed that the table name is in the first field of your header array, so you will need to modify this code slightly, for example, at the moment it does this

INSERT INTO Table4 ([WEEKLY SUMMARY], [SEASON], [WEEK], [FIBRE], [MOISTURE], [TONS]) VALUES ('', '2002', '8', '67.121', '290.745', '419.200')

where: [WEEKLY SUMMARY], [SEASON], [WEEK], [FIBRE], [MOISTURE], [TONS]

are field names
and '', '2002', '8', '67.121', '290.745', '419.200'
is the corresponding data

Anyway, let me know how you get on with this

Grant

------------
Private Sub Command0_Click()
Call ImportGroupData(3)
End Sub
Function ImportGroupData(arrayNumberFind As Integer)
Dim MyString
Dim StringArray
Dim counter As Integer
Dim newCounter As Integer
Dim SQLFields As String
Dim linecount As Integer
'two dimesional array
Dim HeaderArray(1, 4) As String
'has the section been done?
Dim HeaderArrayDoneStatus(4) As Boolean
Dim doAction As Boolean
'use to store the line type - blank, header, data
Dim thisLineType As String

'table for data
HeaderArray(0, 0) = "Table1"
HeaderArray(0, 1) = "Table2"
HeaderArray(0, 2) = "Table3"
HeaderArray(0, 3) = "Table4"
HeaderArray(0, 4) = "Table5"

'actual data
HeaderArray(1, 0) = "UNIT SUMMARY,UNIT,BUNDLES,TONS"
HeaderArray(1, 1) = "SEASON SUMMARY,SEASON,,TONS FIBRE,TONS MOISTURE,TONS CANE"
HeaderArray(1, 2) = "MONTHLY SUMMARY,SEASON,Month,FIBRE,MOISTURE,TONS"
HeaderArray(1, 3) = "WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS"
HeaderArray(1, 4) = "DELIVERY Data,SEASON,WEEK,Date,VOUCHER,TONS"

HeaderArrayDoneStatus(0) = False
HeaderArrayDoneStatus(1) = False
HeaderArrayDoneStatus(2) = False
HeaderArrayDoneStatus(3) = False
HeaderArrayDoneStatus(4) = False

linecount = 0

Open "c:\temp\tempfile.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
'could use just input if you have the same number of fields on each line - stringone, stringtwo etc
thisLineType = ""
Line Input #1, MyString ' Read data into two variables.
linecount = linecount + 1
'just in case there is white space
MyString = Trim(MyString)
If MyString = "" Then
thisLineType = "Line " & linecount & ": blank"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'can reset the doAction as we know that blank lines are a separator
doAction = False
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString
Else
'check for header
'use ubound to find the total in array
For counter = 0 To UBound(HeaderArray, 2)
If LCase(MyString) = LCase(HeaderArray(1, counter)) Then
thisLineType = "Line " & linecount & ": Header"
If counter = arrayNumberFind And HeaderArrayDoneStatus(counter) = False Then
doAction = True
'marks this sections a being done
HeaderArrayDoneStatus(counter) = True
'need to make the fiedls SQL friendly
StringArray = Split((HeaderArray(1, counter)), ",&quot

For newCounter = 0 To UBound(StringArray)
If newCounter = 0 Then
SQLFields = "[" & StringArray(newCounter) & "]"
Else
SQLFields = SQLFields & ", [" & StringArray(newCounter) & "]"
End If
Next newCounter
End If
Text1.Value = Text1.Value & vbCrLf & doAction
End If
Next counter
If thisLineType <> "" Then
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'if none of these thisLineType is empty, so must be data
ElseIf thisLineType = "" Then
thisLineType = "Line " & linecount & ": Data"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'Print individual fields
If doAction = True Then
Text1.Value = Text1.Value & vbCrLf & "true"
StringArray = Split(MyString, ",&quot

Dim SQLValues As String
Dim thisField As String
SQLValues = ""
For counter = 0 To UBound(StringArray)
'print and strip out " char
thisField = Trim(Replace(StringArray(counter), Chr(34), "&quot

)
Text1.Value = Text1.Value & vbCrLf & "Variable " & counter & ": " & thisField
'list1.AddItem "Variable " & counter & ": " & Trim(Replace(StringArray(counter), Chr(34), "&quot

)
'make sql friendly
thisField = Replace(thisField, "'", "''&quot

If counter = 0 Then
SQLValues = "'" & thisField & "'"
Else
SQLValues = SQLValues & ", '" & thisField & "'"
End If
Next counter
'add by sql
Debug.Print "INSERT INTO " & HeaderArray(0, arrayNumberFind) & " (" & SQLFields & &quot

VALUES (" & SQLValues & &quot

"
CurrentDb.Execute "INSERT INTO " & HeaderArray(0, arrayNumberFind) & " (" & SQLFields & &quot

VALUES (" & SQLValues & &quot

"
End If
End If
End If

Loop
Close #1 ' Close file.

End Function

Giffy · Aug 6, 2002

Hi Grant,

I am getting on fine with this and see that it will be soon that I can use it.

As you noticed, the table name is in the first field of your header array i.e.

INSERT INTO Table4 ([WEEKLY SUMMARY], [SEASON], [WEEK], [FIBRE], [MOISTURE], [TONS]) VALUES ('', '2002', '8', '67.121', '290.745', '419.200').

1)Please help me to modify this code to use the text field [WEEKLY SUMMARY]to make the table named WEEKLY SUMMARY

2)If the table already exists then to import and insert the data only.

3)The field [WEEKLY SUMMARY] is empty as you can see. I hope that it is not too much of me to ask of you all this help. If not, please give me an idea of how I can ommit this field in the make table and insert code.

4) The VALUES statement returns an error so I used SELECT. This never returned an error but also never inserted the data. I think that this might be a syntax problem so will search through some of my working code to find how I did it. The Access help is not so helpful, niether the Access VBA handbook by Suzanne Novalis. What rescources do you recommend?

Regards

Howard

GrantDB · Aug 7, 2002

OK. This should do the trick. There are a couple of changes
1-the counters now begin at 1 not 0, as the first bit of the field array is the table name
2-the value for HeaderArray(0, x) is reset to the value of the first variable in the header split
3-the table name is checked to see if it exists (see the function)
4-if the table name exists it is inserted
5-need to add extra [] to the sql statement as your table names have spaces in them

Let me know if that works

Grant

Option Explicit

Private Sub Command0_Click()
Call ImportGroupData(3)
End Sub
Function ImportGroupData(arrayNumberFind As Integer)
Dim MyString
Dim StringArray
Dim counter As Integer
Dim newCounter As Integer
Dim SQLFields As String
Dim linecount As Integer
'two dimesional array
Dim HeaderArray(1, 4) As String
'has the section been done?
Dim HeaderArrayDoneStatus(4) As Boolean
Dim doAction As Boolean
'use to store the line type - blank, header, data
Dim thisLineType As String

'table for data
HeaderArray(0, 0) = "Table1"
HeaderArray(0, 1) = "Table2"
HeaderArray(0, 2) = "Table3"
HeaderArray(0, 3) = "Table4"
HeaderArray(0, 4) = "Table5"

'actual data
HeaderArray(1, 0) = "UNIT SUMMARY,UNIT,BUNDLES,TONS"
HeaderArray(1, 1) = "SEASON SUMMARY,SEASON,,TONS FIBRE,TONS MOISTURE,TONS CANE"
HeaderArray(1, 2) = "MONTHLY SUMMARY,SEASON,Month,FIBRE,MOISTURE,TONS"
HeaderArray(1, 3) = "WEEKLY SUMMARY,SEASON,WEEK,FIBRE,MOISTURE,TONS"
HeaderArray(1, 4) = "DELIVERY Data,SEASON,WEEK,Date,VOUCHER,TONS"

HeaderArrayDoneStatus(0) = False
HeaderArrayDoneStatus(1) = False
HeaderArrayDoneStatus(2) = False
HeaderArrayDoneStatus(3) = False
HeaderArrayDoneStatus(4) = False

linecount = 0

Open "c:\temp\tempfile.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
'could use just input if you have the same number of fields on each line - stringone, stringtwo etc
thisLineType = ""
Line Input #1, MyString ' Read data into two variables.
linecount = linecount + 1
'just in case there is white space
MyString = Trim(MyString)
If MyString = "" Then
thisLineType = "Line " & linecount & ": blank"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'can reset the doAction as we know that blank lines are a separator
doAction = False
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString
Else
'check for header
'use ubound to find the total in array
For counter = 0 To UBound(HeaderArray, 2)
If LCase(MyString) = LCase(HeaderArray(1, counter)) Then
thisLineType = "Line " & linecount & ": Header"
If counter = arrayNumberFind And HeaderArrayDoneStatus(counter) = False Then
doAction = True
'marks this sections a being done
HeaderArrayDoneStatus(counter) = True
'need to make the fiedls SQL friendly
StringArray = Split((HeaderArray(1, counter)), ",&quot

'reset table name
HeaderArray(0, arrayNumberFind) = Trim(Replace(StringArray(0), Chr(34), "&quot

)
'CHANGED FROM 0 to 1 as the first variable is TableName
For newCounter = 1 To UBound(StringArray)
If newCounter = 1 Then
SQLFields = "[" & StringArray(newCounter) & "]"
Else
SQLFields = SQLFields & ", [" & StringArray(newCounter) & "]"
End If
Next newCounter
End If
Text1.Value = Text1.Value & vbCrLf & doAction
End If
Next counter
If thisLineType <> "" Then
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'if none of these thisLineType is empty, so must be data
ElseIf thisLineType = "" Then
thisLineType = "Line " & linecount & ": Data"
Text1.Value = Text1.Value & vbCrLf & thisLineType
Text1.Value = Text1.Value & vbCrLf & "The text: " & MyString
'list1.AddItem thisLineType
'list1.AddItem "The text: " & MyString

'Print individual fields
If doAction = True Then
Text1.Value = Text1.Value & vbCrLf & "true"
StringArray = Split(MyString, ",&quot

Dim SQLValues As String
Dim thisField As String
SQLValues = ""
'CHANGED FROM 0 to 1 as the first variable is TableName

For counter = 1 To UBound(StringArray)
'print and strip out " char
thisField = Trim(Replace(StringArray(counter), Chr(34), "&quot

)
Text1.Value = Text1.Value & vbCrLf & "Variable " & counter & ": " & thisField
'list1.AddItem "Variable " & counter & ": " & Trim(Replace(StringArray(counter), Chr(34), "&quot

)
'make sql friendly
thisField = Replace(thisField, "'", "''&quot

If counter = 1 Then
SQLValues = "'" & thisField & "'"
Else
SQLValues = SQLValues & ", '" & thisField & "'"
End If
Next counter
'add by sql

If IsTable(HeaderArray(0, arrayNumberFind)) = True Then
Dim SQLStatement As String
SQLStatement = "INSERT INTO [" & HeaderArray(0, arrayNumberFind) & "] (" & SQLFields & &quot

VALUES (" & SQLValues & &quot

"
Debug.Print SQLStatement
CurrentDb.Execute SQLStatement
End If
End If
End If
End If

Loop
Close #1 ' Close file.

End Function

Function IsTable(TableName As String) As Boolean
' This function will check if a table exists and reutrn True if itdoes.
Dim Count As Integer
For Count = 0 To CurrentDb.TableDefs.Count - 1
If CurrentDb.TableDefs(Count).Name = TableName Then
IsTable = True
' Since we have have it, there is no reason to checkfurther.
Exit For
End If ' ThisDB(Count).Name = TableName
Next Count
End Function

Giffy · Aug 15, 2002

Hi Grant, thanks a million. I reckon I can take this project further now.

I still hope that you will be able to help in future.

Regards

Howard

jnicks · Aug 15, 2002

Hi,

I am glad you got a solution, there is a briefer apporach.

This situation is amenable to River text handling.

I took a copy of your data into table.txt and ran

Code:

type table.txt| note2lin| kepwithi all &quot;delivery data&quot;| line2not

which reduces the data to a standard comma separated values file (CSV) with a title
line.

[tt]
DELIVERY DATA,SEASON,WEEK,DATE,VOUCHER,TONS
,2002,8,18-04-2002 12:22:52,B170485, 27.300
,2002,8,18-04-2002 17:22:05,B170786, 28.250
,2002,8,19-04-2002 01:36:21,B170787, 29.300
,2002,8,19-04-2002 03:00:21,B170788, 29.900
,2002,8,19-04-2002 05:30:26,B170789, 28.300
,2002,8,19-04-2002 13:13:52,B170790, 28.400
,2002,8,19-04-2002 16:23:44,B170791, 28.250

[/tt]

And if you wish to have it line at a time,

Code:

type table.txt| note2lin| kepwithi all &quot;delivery data&quot;| line2not > datfil1
type datfil1| deltop 1| prepline &quot;Delivery Data&quot; $$lin

reduces it to

[tt]
Delivery Data ,2002,8,18-04-2002 12:22:52,B170485, 27.300
Delivery Data ,2002,8,18-04-2002 17:22:05,B170786, 28.250
Delivery Data ,2002,8,19-04-2002 01:36:21,B170787, 29.300
Delivery Data ,2002,8,19-04-2002 03:00:21,B170788, 29.900
Delivery Data ,2002,8,19-04-2002 05:30:26,B170789, 28.300
Delivery Data ,2002,8,19-04-2002 13:13:52,B170790, 28.400
Delivery Data ,2002,8,19-04-2002 16:23:44,B170791, 28.250
[/tt]

which is a lot less coding.

Giffy · Aug 18, 2002

Hi jnicks,

Such short code interests me hugely.

I am not aware of River text handling. How can I use it with VBA to import the data range "DELIVERY DATA" into an MS Access table?

Thanks for taking time and interest.

Regards

Howard

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Importing selected data from a text file into an MS Access table. 1

Giffy

Programmer

GrantDB

Programmer

Giffy

Programmer

Giffy

Programmer

GrantDB

Programmer

Giffy

Programmer

GrantDB

Programmer

Giffy

Programmer

GrantDB

Programmer

Giffy

Programmer

GrantDB

Programmer

Giffy

Programmer

jnicks

IS-IT--Management

Giffy

Programmer

Similar threads

Part and Inventory Search

Sponsor