Parsing for Access standardization

[KellyJo]

Hello! I have an Excel file that I need to get into Access. Problem?: I need to "parse" portions of it. What I have is as follows:

Roach, Richard 555-55-5555 01-6500-3000

Date In Out In Out
Data Data Data Data Data
Data Data Data Data Data
.
.
.

I need to pull only the Name (Roach, Richard) from the first line and then all of the data in the "table" below it.

Is there a way to do this? PLEASE don't tell me to give up. I know I'm a novice, but I'm desperate. Thank you so much!

KellyJo

 

[MichaelRed]

In the generic sense, it is certainly possible. An approach, which I think would work reasonably for a novice, would be to generate a calculateds field (column) in Excel which appended the name part of the first record (row) to each succeding record -up to the next (new) name record, continuing this through all of the records. Then, copy the VALUE of this new column to another column, deleting the 'calcluated' value column and all the Name records. This should, based on your 'sample' provide a crude, but eminiently useable spreadsheet which could be directly imported into Ms. A.





MichaelRed

 

[KellyJo]

That's an excellent suggestion. I thought of it also, but decided against it because it would require a ton of manual work on my part. I am looking for a way to automate this process as I will have 850 records every time I run it.

How would I write the formula for this? Is just a basic concatenation? I got that far, but then to do the fill down and next record part would take all manual entry unless you have an idea of a formula (or macro) that would help me with this. Thanks!

KellyJo

 

[MichaelRed]

As a Programmer, I would at least look at a code soloution. In this vein, I can only come up with a few approaches.

First, a series of queries on the "raw" spreadsheet:
The overall complexity of this may be greatly influenced by the nature and reliability of the spreadsheet content. i.e. IF the data is highly relilable, some great deal of error checking may be avoided. IF all of the data segments (rows) are numeric (or the first data elementy in each row is Numeric and ALWAYS present (even if zero), then the names and data rows are easily seperated, otherwise, each row requires examiniiation (parsing) based on what can be known to be the characteristics.

Assuming the optimial situation. Simple queries can easily return (independently) all the records (rows) with the Names and the data based only on the first character /Field/Colimn([Not] IsNumeric(...).

Given the set of values which represent the names, add a Field (column) to hold the name values to the recordset. Note that the actual column to use for this may need to be determined dynamically, depending on the regularity of the data.

Insert the name value in the [NewName] column of each Name record.

Starting at the "top", get the name from the [NewName]column and loop till the row is at the end of the record set (last (empty?) row, replacing the [NewName] value whenever the column:row is not empty.

Start all over again at the top, deleting all records (rows) which are "name: records (rows). Note: These should be DELETED, not just erased or emptied.

close the spreadsheet

Use transfer text to import the cleansed spreadsheet.

Of course, this is somewhat incomplete (crude?), as it is -at best- a loose description of a process, Totally ignoring even pseudo code and the nicities of opening and closing instnaces of applications (or even which app this is written in). Perhaps, given a reasonable sample of realistic data a routine to fully automate the thought could be done in a few hours





MichaelRed