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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|