Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Apr 2007
    Posts
    14

    Unanswered: how to convert text files into access database...

    Hi,

    i have text files in the below format...which have been generated from pascal...

    [CLIENT]
    L1_CUSTOMER_REFERENCE=AbreyCatherine
    L1_TITLE=Ms
    L1_SURNAME=Abrey
    L1_FORENAME=Catherine
    L1_DOB=1 Jul 1957
    L2_DOB=9 Jul 1954
    L1_ADD1=165 Downhills Way
    L1_ADD2=Tottenham
    L1_ADD3=London
    L1_PCODE=N17 6AH
    Q_L1_SEX=F
    L1_CLCODE=CA1
    L1_MSTATUS=M
    L1_RELIGION=Christ.
    L1_HASPARTNER=Y
    L1_PARTNERNAME=Gian Paolo Caddeo
    L1_PHONEH=0181 889 3870
    L1_SALUTATION=Catherine
    L1_OCC=Management Consultant
    L1_EMP_STAT=S
    L1_HEALTH=G
    L1_SMOKE=Y
    .
    .
    .
    .

    now, i want to convert this into access detabase..where
    1. first part is the field name for the table and
    2. other part is the value for the relative field.

    kindly help me in to this..because i am just a tech support not have any good command over the generating queries...

    waiting for reply.

    thanks, om

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So there's no way you can get this in a delimited text file (csv)?
    If not, it is still technically possible, but will require a LOT more code...
    EDIT: Helloffalotmore!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Posts
    14
    Quote Originally Posted by georgev
    So there's no way you can get this in a delimited text file (csv)?
    If not, it is still technically possible, but will require a LOT more code...
    EDIT: Helloffalotmore!
    hi, thanks for your interest....but unfortunately.... not possible to convert into csv , otherwise i have done it the day i have received the files....

    and the data is placed here is the 1 tiny bit of the file...i have 15 different files with same format...and the specific file contains about 700 records in the same format...

    i hope this might help you...

    thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, that's a shame but...

    I've knocked up the following code as a starting point for you
    Code:
    'Declare the FSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Open the textfile for reading
    Set objFile = objFSO.OpenTextFile("C:\TestFile.txt")
    'Loop through the lines in the file
    Do While objFile.atendofstream = False
        strLine = objFile.ReadLine
        Debug.Print strLine
    Loop
    I can see you needing a massive set of if's to determine what to do with each line...

    HTH - GeorgeV
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    14
    Quote Originally Posted by georgev
    Well, that's a shame but...

    I've knocked up the following code as a starting point for you
    Code:
    'Declare the FSO
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Open the textfile for reading
    Set objFile = objFSO.OpenTextFile("C:\TestFile.txt")
    'Loop through the lines in the file
    Do While objFile.atendofstream = False
        strLine = objFile.ReadLine
        Debug.Print strLine
    Loop
    I can see you needing a massive set of if's to determine what to do with each line...
    HTH - GeorgeV
    IF YOU CAN PUT SOME MORE LIGHT ON IT RATHER THAN LEAVING IN THE AIR...FOR ME...BCZ I AM TOTALLY LAY MAN...IN CODDING ..I JUST UNDERSTAND THE LOGIC OF CODES....

    I AM UNABLE TO PRODUCE ON MY OWN.....

    SO, KINDLY HELP ME OUT...ITS GREAT INDEED TO ME...AT ALL..

    I KNOW ITS NOT EXPCTED FROM POSTER....BUT ITS UNFORTUNE FOR ME..

    REGARDS,
    OM

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Stop abusing that poor caps lock / shift key!
    Have you even tried running the code I posted? That is the hardest part of the process - I just gave you an answer to it!
    If you're not willing to even try, nor am I.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Posts
    14
    Quote Originally Posted by georgev
    Stop abusing that poor caps lock / shift key!
    Have you even tried running the code I posted? That is the hardest part of the process - I just gave you an answer to it!
    If you're not willing to even try, nor am I.
    i am really sorry....about previous reply....i did not noticed that....please.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well you quoted my response which included the code for the start of your solution. Go stick that code behind a button and change the filepath to the path of your textfile, then you will get the contents of the file shown line by lin in the immediate window.

    Come back when you're happy that you have that part working; and bring a better mood with you.
    EDIT: don't forget to click the button
    Here's an ever easier to use solution for illustration purposes
    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\TestFile.txt")
    strLine = ""
    Do While objFile.atendofstream = False
        strLine = strLine & objFile.ReadLine & chr(13)
    Loop
    MsgBox strLine
    Last edited by gvee; 04-23-07 at 10:37.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2007
    Posts
    14
    Quote Originally Posted by georgev
    Well you quoted my response which included the code for the start of your solution. Go stick that code behind a button and change the filepath to the path of your textfile, then you will get the contents of the file shown line by lin in the immediate window.

    Come back when you're happy that you have that part working; and bring a better mood with you.
    EDIT: don't forget to click the button
    Here's an ever easier to use solution for illustration purposes
    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\TestFile.txt")
    strLine = ""
    Do While objFile.atendofstream = False
        strLine = strLine & objFile.ReadLine & chr(13)
    Loop
    MsgBox strLine
    hi, that worked the way you told me to do....it ....
    now let me know what to do further..

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What did it do?
    Please read this: http://www.dbforums.com/showpost.php...84&postcount=3
    I can help you, not do this for you.
    Once you've told me what this code does (here's a clue: ReadLine) - can you think of how this is going to help you reach your goal?
    EDIT: Actually, I can do this for you - but not for free
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is nothing stopping you form editing the file
    inserting an appropriate separator... as the file contains text vaues it may make sense to use the $ symbol to separate fields, mind you if its for a church that may not be appropriate,.. choose another symbol such as #

    so
    [CLIENT]
    L1_CUSTOMER_REFERENCE=AbreyCatherine
    L1_TITLE=Ms
    L1_SURNAME=Abrey
    L1_FORENAME=Catherine
    L1_DOB=1 Jul 1957
    L2_DOB=9 Jul 1954
    L1_ADD1=165 Downhills Way
    L1_ADD2=Tottenham
    L1_ADD3=London
    L1_PCODE=N17 6AH
    Q_L1_SEX=F
    L1_CLCODE=CA1
    L1_MSTATUS=M
    L1_RELIGION=Christ.
    L1_HASPARTNER=Y
    L1_PARTNERNAME=Gian Paolo Caddeo
    L1_PHONEH=0181 889 3870
    L1_SALUTATION=Catherine
    L1_OCC=Management Consultant
    L1_EMP_STAT=S
    L1_HEALTH=G
    L1_SMOKE=Y

    becomes
    [CLIENT]
    L1_CUSTOMER_REFERENCE=AbreyCatherine$
    L1_TITLE=Ms$
    L1_SURNAME=Abrey$
    L1_FORENAME=Catherine$
    L1_DOB=1 Jul 1957$
    L2_DOB=9 Jul 1954$
    L1_ADD1=165 Downhills Way$
    L1_ADD2=Tottenham$
    L1_ADD3=London$
    L1_PCODE=N17 6AH$
    Q_L1_SEX=F$
    L1_CLCODE=CA1$
    L1_MSTATUS=M$
    L1_RELIGION=Christ.$
    L1_HASPARTNER=Y$
    L1_PARTNERNAME=Gian Paolo Caddeo$
    L1_PHONEH=0181 889 3870$
    L1_SALUTATION=Catherine$
    L1_OCC=Management Consultant$
    L1_EMP_STAT=S$
    L1_HEALTH=G$
    L1_SMOKE=Y$

    then do an edit to remove the L1_........

    eg L1_OCC=Management Consultant$ becomes
    Management Consultant$

    then concatenate all lines
    AbreyCatherine$Ms$Abrey$Catherine$....Y$

    ..yes its a pain
    but this is a one off, if you are unable, unwilling or short on time then despite it being a pain to edit the files it may be a smarter solution for you. remeber when you edit edit a copy of the file not the original, especially if you cannot easily recover it

    Id also reccommend that you include the fieldnames as the first row of the file
    CUSTOMER_REFERENCE$L1_TITLE$L1_SURNAME$.....L1_SMO KE

    however that may fall apart if your records have different field names or different records have different contents. if thats the case then you will almost certainly need to read the file sequentially. looking at your data I think you probably need to do that anyway as the design does not look normalised to me.. but thats down to you and your application
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2007
    Posts
    14

    Red face

    Quote Originally Posted by georgev
    What did it do?
    Please read this: http://www.dbforums.com/showpost.php...84&postcount=3
    I can help you, not do this for you.
    Once you've told me what this code does (here's a clue: ReadLine) - can you think of how this is going to help you reach your goal?
    EDIT: Actually, I can do this for you - but not for free
    by looking at the file once in the very begining i understood that how its going to work.....but the thing is that i do know codding part...

    if you can help me...on that...and get me proper solution ...if you intend to help some one...if your life...

    regards,
    om

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh for goodness sake.
    The code I gave you cycles through the file and reads every line one at a time. This will allow you to write a script to trim the line up to (and including) the equals sign, leaving you with just the value. Store each value into a variable and then run an INSERT INTO sql statement with the result.

    Anything else?
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Alternatively, you could use the same piece of code I wrote to write the data into a new text file but adding a dollar sign to the end of each line programmatically.
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2007
    Posts
    14
    Quote Originally Posted by georgev
    Oh for goodness sake.
    The code I gave you cycles through the file and reads every line one at a time. This will allow you to write a script to trim the line up to (and including) the equals sign, leaving you with just the value. Store each value into a variable and then run an INSERT INTO sql statement with the result.

    Anything else?
    if you could get me a piece of code for the same...then i hope that it will help me a lot...

    regards...om
    Last edited by ontljoshi; 04-24-07 at 10:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •