By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

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

P: 5
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: 213-555-1212
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 !!
Mar 10 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,627
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: 213-555-1212
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.
Mar 10 '07 #2

ADezii
Expert 5K+
P: 8,627
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strMyLine As String, lngLineNumber As Long, strFieldValue As String
  2. Dim MyDB As Database, MyRS As Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("tblVisitorInput", dbOpenDynaset)
  6.  
  7. MyRS.AddNew
  8.  
  9. 'specify your own Path to the Text File
  10. Open "C:\Dell\Visitor Input.txt" For Input As #1
  11.  
  12. Do While Not EOF(1)
  13.   lngLineNumber = lngLineNumber + 1     'increment Line Number counter
  14.   Line Input #1, strMyLine   'Read line into variable
  15.    'Strip the Field Name, Colon, and Space from the line
  16.    strFieldValue = Right$(strMyLine, Len(strMyLine) - (InStr(strMyLine, ":") + 1))
  17.     Select Case lngLineNumber Mod 13    '13 Fields - 14th starts a New Record
  18.       Case 1    'Line 1 - Field1 in tblVisitorInput
  19.         MyRS![Phone] = strFieldValue
  20.       Case 2    'Line 2 - Field2 in tblVisitorInput
  21.         MyRS![Submit] = strFieldValue
  22.       Case 3    'Line 3 - Field3 in tblVisitorInput
  23.         MyRS![addy_line_1] = strFieldValue
  24.       Case 4    'you should get the idea by now
  25.         MyRS![addy_line_2] = strFieldValue
  26.       Case 5
  27.         MyRS![badge] = strFieldValue
  28.       Case 6
  29.         MyRS![city] = strFieldValue
  30.       Case 7
  31.         MyRS![email] = strFieldValue
  32.       Case 8
  33.         MyRS![homegroup] = strFieldValue
  34.       Case 9
  35.         MyRS![Name] = strFieldValue
  36.       Case 10
  37.         MyRS![program] = strFieldValue
  38.       Case 11
  39.         MyRS![State] = strFieldValue
  40.       Case 12
  41.         MyRS![volunteer] = strFieldValue
  42.       Case 0    'Last Field for Current Record
  43.         MyRS![zip] = strFieldValue
  44.         MyRS.Update
  45.         MyRS.AddNew
  46.       Case Else
  47.     End Select
  48. Loop
  49.  
  50. Close #1
  51. MyRS.Close
Mar 11 '07 #3

P: 5
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!!
Apr 19 '07 #4

ADezii
Expert 5K+
P: 8,627
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.
Apr 19 '07 #5

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

So do I put the code somewhere in Access? Total beginner here!
Apr 19 '07 #6

ADezii
Expert 5K+
P: 8,627
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.
Apr 19 '07 #7

P: 5
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!
Apr 20 '07 #8

P: 5
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!
Apr 21 '07 #9

ADezii
Expert 5K+
P: 8,627
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.
Apr 21 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.