How to take data from rows, and make new columns with it??

[toddjsaunders]

Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.


What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.

It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...

Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!

The fields:

Phone: [phone]
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:

thank you thank you thank you thank you thank you !!

[ADezii]

Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.


What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.

It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...

Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!

The fields:

Phone: [phone]
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:

thank you thank you thank you thank you thank you !!

It's not as simple as you think it is but I will work on it when I get the chance.

[ADezii]

It's not as simple as you think it is but I will work on it when I get the chance.

The following code has been thoroughly tested and is completely functional. It does, however, rely on the exact specifications provided by you. Any deviation from these specs will cause the code to fail. All Fields in tblVisitorInput have been defined as (TEXT 50), except [ID], in order to eliminate the possibility of data mismatch errors during the Append operation. This is something that you can experiment with later.

__1. Create tblVisitorInput exactly as shown. This Table will contain exactly 14 Fields, 13 of which correspond to the 13 line segments of your Text file. Depending on the Line Numbers, data will be appended to the appropriate Fields once the Field Maximum (13) has been reached. Here are the Fields, they are all defined as (TEXT 50) except [ID] which is AutoNumber/Primary Key:

ID

Phone

Submit

addy_line_1

addy_line_2

badge

city

email

homegroup

name

program

state

volunteer

zip

__2. Here is the code that does the dirty work. Let me know if this is what you wanted and how it worked out:

Dim strMyLine As String, lngLineNumber As Long, strFieldValue As String
Dim MyDB As Database, MyRS As Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblVisitorInput", dbOpenDynaset)
 
MyRS.AddNew
 
'specify your own Path to the Text File
Open "C:\Dell\Visitor Input.txt" For Input As #1
 
Do While Not EOF(1)
  lngLineNumber = lngLineNumber + 1     'increment Line Number counter
  Line Input #1, strMyLine   'Read line into variable
   'Strip the Field Name, Colon, and Space from the line
   strFieldValue = Right$(strMyLine, Len(strMyLine) - (InStr(strMyLine, ":") + 1))
    Select Case lngLineNumber Mod 13    '13 Fields - 14th starts a New Record
      Case 1    'Line 1 - Field1 in tblVisitorInput
        MyRS![Phone] = strFieldValue
      Case 2    'Line 2 - Field2 in tblVisitorInput
        MyRS![Submit] = strFieldValue
      Case 3    'Line 3 - Field3 in tblVisitorInput
        MyRS![addy_line_1] = strFieldValue
      Case 4    'you should get the idea by now
        MyRS![addy_line_2] = strFieldValue
      Case 5
        MyRS![badge] = strFieldValue
      Case 6
        MyRS![city] = strFieldValue
      Case 7
        MyRS![email] = strFieldValue
      Case 8
        MyRS![homegroup] = strFieldValue
      Case 9
        MyRS![Name] = strFieldValue
      Case 10
        MyRS![program] = strFieldValue
      Case 11
        MyRS![State] = strFieldValue
      Case 12
        MyRS![volunteer] = strFieldValue
      Case 0    'Last Field for Current Record
        MyRS![zip] = strFieldValue
        MyRS.Update
        MyRS.AddNew
      Case Else
    End Select
Loop
 
Close #1
MyRS.Close

[toddjsaunders]

Ok, so you might get a laugh out of this one...

Thanks for the code... but where do i put it to make it work?

Is this something I can use in Access?

Thanks!!

[ADezii]

Ok, so you might get a laugh out of this one...

Thanks for the code... but where do i put it to make it work?

Is this something I can use in Access?

Thanks!!

Where you place the code is irrelevant, the absolut Path to Visitor Input.txt is.

[toddjsaunders]

Sorry to bother you again, and thanks for your help--

So do I put the code somewhere in Access? Total beginner here!

[ADezii]

Sorry to bother you again, and thanks for your help--

So do I put the code somewhere in Access? Total beginner here!

the easiest location would be in the Click() Event of a Command Button on an Access Form.

[toddjsaunders]

Thank you so much for your help!

It is coming along nicely... just one issue now:

Not all the fields are required on the form, so in the text file, some of the fields are blank.

Because of this, the output in the new tblVisitorInput has multiple records instead of only one.

When every field is filled, it works perfectly, filling each field in one record..

Any way to correct for this in the code?

Thanks!

[toddjsaunders]

Playing around with it more... the code doesn't like blank fields... is there an easy way to populate a blank field with an 'X', or perhaps the word "Blank"...


Thanks again!

[ADezii]

Playing around with it more... the code doesn't like blank fields... is there an easy way to populate a blank field with an 'X', or perhaps the word "Blank"...


Thanks again!

If a Critical Field is blank, and you do not wish to append the data into your Table, you can check for this condition in the code and not append the data. Please be more specific as to what you are looking for.