Data Manipulation

[21Outlaw]

I have said data file:
http://public.silvergsx.net/sample data.txt

I need to take each set of data and append the e-mail address and date (but not the time) in the same row as the Policy, Number and Phone. I would do it by hand but this just some sample data as there is well over 100,000 records that need to have this data appended to.

I've tried Access, Excel and a combination of the two without any luck. I'm looking for something automated so the fields will populate themselves using some type of formula or function.

I already have all the Policies, Numbers and Phones in tables in access but can't find an easy way to append the correct e-mail address and date since there are multiple "sets" of data in each table.

 

[InfiniteLurker]

Do you wanna pay somebody to do this for you?

If the data file is perfectly consistent with space between lines etc., all you need to do is write a macro (in Word, or whatever has a macro recorder) to copy the email address and date and paste it into each of the successive rows that require it... lather, rinse, repeat...

hope this helps...

 

[21Outlaw]

I don't want to have to manually paste it into every field.

I want it to check for data in the previous field/record and/or check for no data in the following field/record so it won't insert any unnecessary data. I would like to use this as an append query or a formula to paste into an entire column in Excel.

 

[InfiniteLurker]

With the macro, once you've recorded it with the first set of rows, all you do is run it and it will do all the work for you as it rips through the file, you wouldn't need to paste it into every field... But, like I said, if the records aren't consistent (e.g. 1 row to copy from and 3 to paste to for each set), it won't work.

 

[21Outlaw]

Yea, the sets of records range from 1000-10000+

 

[InfiniteLurker]

The number of rows overall does not matter. For instance - your sample text file looks like:

me@yahoo.com 08/04/2005 01:40 AM
---Policy No--- -- Phone No ----------------
123457888 7777 , 9182616100
490814390 PR , 9185438888
249050987 A1 , 9062354321
140395781 PR , 8548942341

you@yahoo.com 08/09/2005 01:40 AM
---Policy No--- -- Phone No ----------------
123457888 7777 , 9182616100
490814390 PR , 9185438888
249050987 A1 , 9062354321
140395781 PR , 8548942341


If each group of records follows the same pattern, 1 email address & date row followed by the column header row, followed by 4 data rows, then using a macro WILL work and will be very easy and quick. However, if your data looks more like this overall:

me@yahoo.com 08/04/2005 01:40 AM
---Policy No--- -- Phone No ----------------
123457888 7777 , 9182616100
140395781 PR , 8548942341

you@yahoo.com 08/09/2005 01:40 AM
---Policy No--- -- Phone No ----------------
123457888 7777 , 9182616100
490814390 PR , 9185438888
249050987 A1 , 9062354321
140395781 PR , 8548942341

you2@yahoo.com 08/09/2005 01:40 AM
---Policy No--- -- Phone No ----------------
123457888 7777 , 9182616100
490814390 PR , 9185438888
249050987 A1 , 9062354321
140395781 PR , 8548942341
123457888 7777 , 9182616100
490814390 PR , 9185438888
249050987 A1 , 9062354321
140395781 PR , 8548942341


Then a simple macro will not work, and you'll need something to handle a little more logic...

 

[21Outlaw]

Exactly. Glad to see you understand now. Sorry if I was a little vague in my original post.