A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

getting data from a text file



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 11:50 PM posted to microsoft.public.access
StuJol
external usenet poster
 
Posts: 122
Default getting data from a text file

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}

  #2  
Old May 28th, 2010, 01:04 AM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default getting data from a text file

One way is to put data in table named [Text_Table] with field [Text_Data].
Create a query like this but continue to add the table to the design grid for
the other output fields.

SELECT
Replace(Mid([Text_Table].[Text_Data],InStr([Text_Table].[Text_Data],"NAME=")+7),'"','')
AS USER_ALARM,
Mid([Text_Table_1].[Text_Data],InStr([Text_Table_1].[Text_Data],"user=")+7,InStr([Text_Table_1].[Text_Data],"
time=")-10) AS [User],
Mid([Text_Table_1].[Text_Data],InStr([Text_Table_1].[Text_Data],"/*
")+4,InStr([Text_Table_1].[Text_Data],'"
*/')-InStr([Text_Table_1].[Text_Data],"/* ")-4) AS Alarm_Date
FROM Text_Table AS Text_Table_1, Text_Table
WHERE (((Text_Table_1.Auto)=[Text_Table].[Auto]+1));

--
Build a little, test a little.


"stujol" wrote:

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}

  #3  
Old May 28th, 2010, 01:05 AM posted to microsoft.public.access
Dorian
external usenet poster
 
Posts: 542
Default getting data from a text file

Your file is in the wrong format for automatically importing to Access.
You need to get your file into a format like a spreadsheet where you have
the field names going across and the value for each field below the heading.
Each field should either have a fixed width or you can have the fields
separated by commas or some other delimiter.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"stujol" wrote:

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}

  #4  
Old May 28th, 2010, 04:24 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default getting data from a text file

Another way I think will be easier is to open the file using Word.
Replace all =" with a tab -- =" with ^t
Replace all returns with tab -- ^p with ^t
Replace all { tab with return -- {^t with ^p

This puts all data for a record in a single line of text. Then it is
easier to parse.

--
Build a little, test a little.


"stujol" wrote:

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}

  #5  
Old May 29th, 2010, 01:33 AM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default getting data from a text file

I errored here --
Replace all { tab with return -- {^t with ^p

it should read --
Replace all } tab with return -- }^t with ^p

--
Build a little, test a little.


"KARL DEWEY" wrote:

Another way I think will be easier is to open the file using Word.
Replace all =" with a tab -- =" with ^t
Replace all returns with tab -- ^p with ^t
Replace all { tab with return -- {^t with ^p

This puts all data for a record in a single line of text. Then it is
easier to parse.

--
Build a little, test a little.


"stujol" wrote:

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}

  #6  
Old May 29th, 2010, 02:50 AM posted to microsoft.public.access
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default getting data from a text file

Dorian wrote:
Your file is in the wrong format for automatically importing to Access.
You need to get your file into a format like a spreadsheet where you have
the field names going across and the value for each field below the heading.
Each field should either have a fixed width or you can have the fields
separated by commas or some other delimiter.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table so

[quoted text clipped - 25 lines]
WAVE_FILE=""
}


No, it just means he can't just attach the text file and query it directly.
He can open it in code, look for "DESCRIPTION=", strip out the junk, and
write the value to a table...

Private Sub ParseTextFile(ByVal strFile As String)
Dim intFileNo As Integer
Dim strTextLine As String

intFileNo = FreeFile
Open strFile For Input As #intFileNo

Do While Not EOF(intFileNo) ' Loop until end of file.
Line Input #1, strTextLine ' Read line into variable.
strTextLine = Trim$(strTextLine)
'intCounter = intCounter + 1
If Left$(strTextLine, 12) = "DESCRIPTION=" Then
' PROCESS THE TEXT FILE HERE.
strTextLine = Mid$(strTextLine, 14)
strTextLine = Left$(strTextLine, Len(strTextLine) - 1)

' write the record to your recordset here...
Debug.Print strTextLine
End If
Loop

Close #intFileNo

End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

  #7  
Old June 1st, 2010, 06:05 PM posted to microsoft.public.access
Miladka Novotna
external usenet poster
 
Posts: 1
Default getting data from a text file


"stujol" píše v diskusním příspěvku
...
i have an access 2003 database. i have a text file which has loads of
instances of the below text. i want to be able to this data into a table
so
for every instance of the word 'description', plance the description text
into the description field. Any help would be great... many thanks...

USER_ALARM NAME="_OAR ALARM"
user="PCSD_TEAM" time=1177594986/* "26-Apr-2007 14:43:06" */
{
DESCRIPTION="Operator Action Request"
ALARM_WORD="OAR"
MESSAGE="%P1 %P2"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1="OAR/MSG1"
DEFAULT_PARAM2="OAR/MSG1"
WAVE_FILE=""
}
USER_ALARM NAME="_OAR_HORN"
user="PCSD_TEAM" time=1140541360/* "21-Feb-2006 17:02:40" */
{
DESCRIPTION="OAR Time Out Alarm"
ALARM_WORD="_OAR_HORN"
MESSAGE="OAR Time Out"
CATEGORY="PROCESS"
SUMMARY_NO=7
DEFAULT_PARAM1=""
DEFAULT_PARAM2=""
WAVE_FILE=""
}


__________ Informace od ESET Smart Security, verze databaze 4628
(20091122) __________

Tuto zpravu proveril ESET Smart Security.

http://www.eset.cz






__________ Informace od ESET Smart Security, verze databaze 4628 (20091122) __________

Tuto zpravu proveril ESET Smart Security.

http://www.eset.cz




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Š2004-2024 OfficeFrustration.
The comments are property of their posters.