Importing Text Files

[telecomjim]

The text file I am importing has a field that contains a place and a
telephone number. I would like to split the data so that the text and number
are exported to different fields within my database. I am running Access 2000.

 

[Ken Snell [MVP]]

Import the data "as is" to a temporary table, then you can use an append
query to copy the data (including parsing the place and telephone number
into different fields) via an append query.

Or import the data "as is" to your permanent table, then run an update query
to parse the values and put them in the correct fields.

 

[Klatuu]

Here is a way you can do that:
First, create a table to import your text file into. Structure it like the
text file coming in. Then create two append or update queries (depending on
whether you are adding records or updating existing records. One query would
be to update the table where you want to put the Place and one to update the
table where you want to put the Phone Number. Then to do the whole process,
you could create a Macro or write some VBA code that will do the following:
1. Delete the data from the table you import to to clear out previous data.
2. Run your TransferText to put the data in you table.
3. Run the first query to update the Places
4. Rin the second query to update the phone numbers.

 

[telecomjim]

Thanks

Import the data "as is" to a temporary table, then you can use an append
query to copy the data (including parsing the place and telephone number
into different fields) via an append query.

Or import the data "as is" to your permanent table, then run an update query
to parse the values and put them in the correct fields.

 

[telecomjim]

Thanks

Here is a way you can do that:
First, create a table to import your text file into. Structure it like the
text file coming in. Then create two append or update queries (depending on
whether you are adding records or updating existing records. One query would
be to update the table where you want to put the Place and one to update the
table where you want to put the Phone Number. Then to do the whole process,
you could create a Macro or write some VBA code that will do the following:
1. Delete the data from the table you import to to clear out previous data.
2. Run your TransferText to put the data in you table.
3. Run the first query to update the Places
4. Rin the second query to update the phone numbers.

 

[telecomjim]

How would you structure the query?

Here is a way you can do that:
First, create a table to import your text file into. Structure it like the
text file coming in. Then create two append or update queries (depending on
whether you are adding records or updating existing records. One query would
be to update the table where you want to put the Place and one to update the
table where you want to put the Phone Number. Then to do the whole process,
you could create a Macro or write some VBA code that will do the following:
1. Delete the data from the table you import to to clear out previous data.
2. Run your TransferText to put the data in you table.
3. Run the first query to update the Places
4. Rin the second query to update the phone numbers.

 

[Klatuu]

Without knowing the layout of the imcomming data, I would not be able to give
you a good answer. The basics are, create a table with fields that match the
text data coming in. Define the data types to match the data types of the
fields in the text file. Then, in your append query (I am guessing, since I
don't know if you are updating existing records in the destination tables or
adding new records) use the table you imported into as the source table and
the fields from the source you want to include in the target table.

 

[telecomjim]

When you create the append query to move the data to the target table what
function do you use to make sure that data ends up in the right fields of the
target table.

 

[Klatuu]

There are no functions to use. Use the query builder.
1. Create a new query in design mode.
2. Select the table for the source data. This will be the table you
imported the text from.
3. On the menu bar select Query then select Append Query. This will pop up a
dialog box where you can select the name of the table you want to append the
data to (the destination table).
4. Select the fields from the source table you want to put into the
destination table
5. In the Append To row of the query builder, you can drop down to select
the field you want the data to put in.

 

[telecomjim]

When I run my query I still get all of the original data (place and tel no)
going into the new field which is only for telephone numbers.

 

[Klatuu]

Oh, I did not fully understand before. Now I see that you have two vaules in
one field and you want to split the two values into two fields in the
destination table. The trick here is knowing how the two values look in the
original field.
Question, how many characters in the Place and how many in the Phone Number?
Is it text or numeric coming in? What order are the values in? Place & Phone
or Phone & Place?
Just for expample purposes, lets assume it is Place and Phone and Place is 5
characters and phone is 10 characters. So to put the data in the Place field
in the destination table it would be Left(combinedfield, 5) and for the Phone
it would be Right(Phone, 10). If place is a variable number of characters,
this takes a little more work because Jet (where the queries are executed)
does not understand the Len function, so you would have to write a function
in a standard module to do the work and use that in the query. Let me know
what the format is, and I can give more detail.
My apologies for not quite understanding the first time.

 

[telecomjim]

Some Sample Data

Wolverhamptn01902 31XXX1 ,
Glasgow 0141 XXX 2221,
Peterborough 01733 416XXX ,
Ireland Rep 353XXX46816 ,

I would particularly like to seperate the phone number element of the data.
For security the X represents a digit.

 

[Klatuu]

Here is a function that will seperate the two. It returns one or the other
depending on the second argument (See Comments).
Use this in your query, one occurance for each of the two fields. So
instead for the field name for the source table, create an expression Like:
ExpPlace: =SplitPlaceAndName([MyTable]![MyField], True)
ExpPhone: =SplitPlaceAndName([MyTable]![MyField], False)

Function SplitPlaceAndPhone(ByVal strPlaceAndPhone As String, blnPlace) As
String
'Seperates the Place and Phone Number
'Returns either the Place or the Phone Number
'If blnPlace is True, the Place is returned
'If blnPlace is False, the Phone Number is Returned
'If strPlaceAndPhone is Null or "" then "" is returned.
'Dave Hargis 10/20/05

Dim lngX As Long

If IsNull(strPlaceAndPhone) Or strPlaceAndPhone = "" Then
SplitPlaceAndPhone = vbNullString
Exit Function
End If
For lngX = 1 To Len(strPlaceAndPhone)
If IsNumeric(Mid(strPlaceAndPhone, lngX, 1)) Then
Exit For
End If
Next lngX
If blnPlace Then
SplitPlaceAndPhone = Trim(Left(strPlaceAndPhone,
Len(strPlaceAndPhone) - lngX))
Else
SplitPlaceAndPhone = Trim(Right(strPlaceAndPhone, lngX))
End If
End Function