Is it possible to Change String Delimiter without using an interface Spec?

[Andibevan]

Hi All,

I have build the following simple function to import data into a table but
the data has a ' as the string delimiter. Is there any way I can specify
the string delimiter without using an interface spec?

I want to be able to distribute just the code on its own to keep it as
simple as possible for the users.

Thanks

Andi

Function fncBatchImport(strSRCpath As String, strTableName As String) As
Boolean
Dim strSRCdir As String, strSRCfile As String
'Reference the library Microsoft ActiveX Data Objects 2.1 (or higher).
On Local Error GoTo ImportError
'DoCmd.Hourglass True

DoCmd.TransferText acImportDelim, , strTableName, strSRCpath, True
ImportEnd:
'DoCmd.Hourglass False
fncBatchImport = tableExists(strTableName)
Exit Function

ImportError:
MsgBox Err.Description
' DoCmd.Hourglass False
End Function

 

[John Nurick]

Hi Andi,

If you want to use TransferText the only way to specify a non-standard
delimiter is by using an import specification or a schema.ini file.

Probably the best thing to do is to include code in your routine to handle
it, e.g.
- create the appropriate schema.ini, then use TransferText, then delete the
schema.ini
or
- read the file a line at a time, parsing each line into fields and
appending the resulting record to the table

 

[Andibevan]

Thanks John,

Thanks also for the assistance you provided on my previous query relating to
transfertext. I had been looking at numerous highly complicated methods and
your suggestion was far simpler.

Andi

 

[Andibevan]

I have another problem - I have a field that is a combination of date and
time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy

 

[Klatuu]

If the format of your date/time field is always as shown, then here is a
function that should be able to translate that for you.

Function TextToDate(ByVal strTheDate As String) As Date
Dim avarSplit As Variant
Dim dtmDatePart As Date
Dim dtmTimePart As Date

'Remove the leading dash if it is there
If InStr(strTheDate, "-") = 1 Then
strTheDate = Right(strTheDate, Len(strTheDate) - 1)
End If
'Makes all delimiters the same
strTheDate = Replace(strTheDate, ".", "-")
'Creates an array of all the elements
avarSplit = Split(strTheDate, "-")
'Create the date part
dtmDatePart = DateSerial(avarSplit(0), avarSplit(1), avarSplit(2))
'Create the time part
dtmTimePart = TimeSerial(avarSplit(3), avarSplit(4), avarSplit(5))
'Add up and return
TextToDate = dtmDatePart + dtmTimePart
End Function

 

[John Nurick]

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and TimeSerial() t
assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

 

[Andibevan]

Klatuu - thanks for the code but I am a bit of a newbie to this and am not
sure how I would apply this to my data?

Would you essentially import the data into the table and then step through
all data entries in the relevent field performing something like:

Var_Date = TextToDate(Var_Date)

Ta

Andi

 

[Andibevan]

Thanks John - can you recommend a good tutorial on using a calculated field
in an update - sorry for being a pain but I am new to all this. I have got
a good knowledge of using VBA with excel though.

Thanks

Andi

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and TimeSerial() t
assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

I have another problem - I have a field that is a combination of date and
time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy

 

[John Nurick]

VBA doesn't come into it unless you want to automate the whole process
for repetitive work. Normally I'd just do something like this:

1) Use File|Get External Data|Link to access the text file as a linked
table. Your non-standard date & time field (let's call it DT) should
then be linked as a text field.

2) Create a table with the field names and types you actually want
(mostly these will be the same as in the linked table, but DT will be a
date/time field, not a text field.

3) Create an append query that gets data from the linked table and
appends it to the new table, using calculated field with an expression
like the one I gave you.

See the help topics "Create a calculated field in a query", "Create an
append query" and "Create an update query". It's also worth checking out
Jeff Conrad's pages at
http://home.bendbroadband.com/conradsystems/accessjunkie.html : he has a
wonderful collection of links to Access information.

Thanks John - can you recommend a good tutorial on using a calculated field
in an update - sorry for being a pain but I am new to all this. I have got
a good knowledge of using VBA with excel though.

Thanks

Andi

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and TimeSerial() t
assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

I have another problem - I have a field that is a combination of date and
time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy

 

[Andibevan]

John,

I was actually trying to create a fully automated process as the data is not
live data and is a manually triggered dump out of a configuration management
system. As a result it SEEMED that trying to automate the whole thing would
be logical, but this is probably due to my lack of experience. I think that
your approach seems much simpler and provides much more flexibility.

Are the help topics you suggested in the Access VBA help? I can't track
down any pages with the exact titles - were they just a good guess? (I have
found titles that are similar?)

Ta

Andi

VBA doesn't come into it unless you want to automate the whole process
for repetitive work. Normally I'd just do something like this:

1) Use File|Get External Data|Link to access the text file as a linked
table. Your non-standard date & time field (let's call it DT) should
then be linked as a text field.

2) Create a table with the field names and types you actually want
(mostly these will be the same as in the linked table, but DT will be a
date/time field, not a text field.

3) Create an append query that gets data from the linked table and
appends it to the new table, using calculated field with an expression
like the one I gave you.

See the help topics "Create a calculated field in a query", "Create an
append query" and "Create an update query". It's also worth checking out
Jeff Conrad's pages at
http://home.bendbroadband.com/conradsystems/accessjunkie.html : he has a
wonderful collection of links to Access information.

Thanks John - can you recommend a good tutorial on using a calculated field
in an update - sorry for being a pain but I am new to all this. I have got
a good knowledge of using VBA with excel though.

Thanks

Andi

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and TimeSerial() t
assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

On Tue, 8 Nov 2005 17:48:44 -0000, "Andibevan"

I have another problem - I have a field that is a combination of date and
time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy

 

[John Nurick]

I was actually trying to create a fully automated process as the data is not
live data and is a manually triggered dump out of a configuration management
system. As a result it SEEMED that trying to automate the whole thing would
be logical, but this is probably due to my lack of experience. I think that
your approach seems much simpler and provides much more flexibility.

Certainly it can be automated, but starting by doing it "manually" means
you'll understand the processes involved. Once you've got it working
manually, automating it is just a matter of writing code to

1) Call
DoCmd.TransferText acLink
to create the linked table connected to your text file, using the same
table name each time even if the file name changes.

2) Execute the append query (which you will already have from the manual
operation) to move the data into the "permanent" table (which you will
already have) from the linked table (whose name doesn't change, so the
query doesn't need to change).

3) Delete the linked table ready for next time.

Are the help topics you suggested in the Access VBA help? I can't track
down any pages with the exact titles - were they just a good guess? (I have
found titles that are similar?)

They are exact titles, but all are in Access help rather than VBA help.

Ta

Andi

VBA doesn't come into it unless you want to automate the whole process
for repetitive work. Normally I'd just do something like this:

1) Use File|Get External Data|Link to access the text file as a linked
table. Your non-standard date & time field (let's call it DT) should
then be linked as a text field.

2) Create a table with the field names and types you actually want
(mostly these will be the same as in the linked table, but DT will be a
date/time field, not a text field.

3) Create an append query that gets data from the linked table and
appends it to the new table, using calculated field with an expression
like the one I gave you.

See the help topics "Create a calculated field in a query", "Create an
append query" and "Create an update query". It's also worth checking out
Jeff Conrad's pages at
http://home.bendbroadband.com/conradsystems/accessjunkie.html : he has a
wonderful collection of links to Access information.

Thanks John - can you recommend a good tutorial on using a calculated field
in an update - sorry for being a pain but I am new to all this. I have got
a good knowledge of using VBA with excel though.

Thanks

Andi

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and TimeSerial() t
assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

On Tue, 8 Nov 2005 17:48:44 -0000, "Andibevan"

I have another problem - I have a field that is a combination of date and
time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy

 

[Andibevan]

Thanks for all your help on this John - much appreciated.

I was actually trying to create a fully automated process as the data is not
live data and is a manually triggered dump out of a configuration management
system. As a result it SEEMED that trying to automate the whole thing would
be logical, but this is probably due to my lack of experience. I think that
your approach seems much simpler and provides much more flexibility.

Certainly it can be automated, but starting by doing it "manually" means
you'll understand the processes involved. Once you've got it working
manually, automating it is just a matter of writing code to

1) Call
DoCmd.TransferText acLink
to create the linked table connected to your text file, using the same
table name each time even if the file name changes.

2) Execute the append query (which you will already have from the manual
operation) to move the data into the "permanent" table (which you will
already have) from the linked table (whose name doesn't change, so the
query doesn't need to change).

3) Delete the linked table ready for next time.

Are the help topics you suggested in the Access VBA help? I can't track
down any pages with the exact titles - were they just a good guess? (I have
found titles that are similar?)

They are exact titles, but all are in Access help rather than VBA help.

Ta

Andi

VBA doesn't come into it unless you want to automate the whole process
for repetitive work. Normally I'd just do something like this:

1) Use File|Get External Data|Link to access the text file as a linked
table. Your non-standard date & time field (let's call it DT) should
then be linked as a text field.

2) Create a table with the field names and types you actually want
(mostly these will be the same as in the linked table, but DT will be a
date/time field, not a text field.

3) Create an append query that gets data from the linked table and
appends it to the new table, using calculated field with an expression
like the one I gave you.

See the help topics "Create a calculated field in a query", "Create an
append query" and "Create an update query". It's also worth checking out
Jeff Conrad's pages at
http://home.bendbroadband.com/conradsystems/accessjunkie.html : he has a
wonderful collection of links to Access information.

On Wed, 9 Nov 2005 12:30:59 -0000, "Andibevan"


Thanks John - can you recommend a good tutorial on using a calculated field
in an update - sorry for being a pain but I am new to all this. I

have
got

a good knowledge of using VBA with excel though.

Thanks

Andi

That's an easy one, provided there are always two digits for month, day,
hour, minute and second. Import the field as text, or access the text
file as a linked table, and then use a calculated field in an update or
append query to convert it to a date/time value.

The calculated field needs to use the Left() and Mid() functions to
extract the numbers from the string, and DateSerial() and

TimeSerial()
t

assemble them again, something like this:

TheDate: DateSerial(Left(S, 4), Mid(S, 6, 2), Mid(S, 9, 2))
+ TimeSerial(Mid(S, 12, 2), Mid(S, 15, 2), Right(S, 2))

Replace S with the name of the text field, e.g.
...DateSerial(Left([MyField], 4)...

On Tue, 8 Nov 2005 17:48:44 -0000, "Andibevan"

I have another problem - I have a field that is a combination of

date
and

time :- 2005-11-06-23.36.42

Any clues on what I need to do in order to convert this into a usable
format?

Thanks

Andy