Deleting leading and trailing rows after/before import (1 Viewer)

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Hi,
I receive multiple excel documents daily for customer sales management and am trying to automate the cleanup and put it in a format that is useful. My shipping report has the same leading rows everyday but as I ship product the trailing rows grow. What is the best way to clean this up in Access 2016? I get up to 6 different spreadsheets and cleaning them up manually takes too much time. What is the best practice here? Import it and then clean it or vice versa. I am a beginner and appreciate any guidance.
Thanks for your help in advance.
Cheers,
Razzbarry
 

plog

Banishment Pending
Local time
Today, 10:14
Joined
May 11, 2011
Messages
11,611
You really haven't provided much information. What's the purpose of this other than "put into a format that is useful"? Give us the big picture--pretend we are elementary students and its career day--explain what you do.

Then give us some details. Specifically, how is this data used. You've posted in the table section of an Access forum, so I assume you have a table you want to put it this into? Then what? Just store it or report on it? An example of the spreadsheet would be helpful as well.
 

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Yes I was trying to stay higher level.
My reports have different information that are linked by order number or customer name sometimes including promise and actual dates.
What I need is to be able to basically see the history by supplier or customer or fiscal quarter so I have an instant snapshot. The reports come individually and I find myself jumping between several reports and two different systems to get all the information i need. An example, my boss calls me and asks about my to date shipping, backlog and bookings on any given day in the quarter. I would have to jump between at least three reports which all have irrelevant data in the first few rows before it gets to the actually column header. I was discussing forecast with my customer and he asked about historic information which I couldn't provide while sitting there.

After all the relevant data there is typically a sum of revenue and gross profit dollars and then more blank rows ending with notes. I have pasted an example but removed customer data.
Begin paste
Page 1 of 1 10/3/2018
Items Shipped by Sales Person / Product Line Post Split

3:03:02 AM

Division Customer # Bill To Name Invoice # Invoice Date PL Name Item # Qty Shipped Split FQ Sales Post Split GP Post Split GP % SP Code and Name End Customer

$127,047.00 $159,200.00


"Note:
1. This report is reflecting sales numbers per sales person in order to measure sales person performance / commission.
2. It is not a Company / Division sales report. Sales person codes are broken into 3 options (Inside, Outside, Single) so the Division / company wide numbers are doubled.
3. The report includes only commissionable, shipped products & services.
4. The report does not include discounts.
Source: KS-CommClac"






Date Range Selected
10/1/2018 From:
10/31/2018 To:

End Paste
The data I want is from the header to just above the totals with no blank rows.
If I relate the customer # on the bookings, backlog and Shipping I can get a snapshot of all three in a jiffy. Since i receive an updated copy of each of these at the beginning of the next day there is a lot of cleanup to get to useful information.
I have a macro now that pulls a couple of these reports in with the formatted data which I do manually. I would like to do that automatically and seek feedback on the best method. I have more plans but this is a big hurdle for me. I hope that paints a better picture.
Thanks for your input.
Razzbarry
 

plog

Banishment Pending
Local time
Today, 10:14
Joined
May 11, 2011
Messages
11,611
If I had multiple files in the above format to get data into Access I would write a parsing program/script (https://en.wikipedia.org/wiki/Parsing#Computer_languages). Essentially you read in the whole file as a string, then you move through that string to various markers that denote where data begins and ends.

If done with VBA you can then insert the actual data into a table with the proper end structure. There are examples and tutorials all over online, but for your specific files; its going to require you yourself actually write the script.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Sep 12, 2006
Messages
15,613
let's say you have a file with rows designated HEADER, and for each header you have multiple LINES.

Now you can't use a simple import to load a whole file like this, as the columns won't align correctly. Even if you could, you often find that the lines are related to the headers they follow, and there is no cross-matching data to connect the lines to the header. The table you get in access is not guaranteed to be in the same order as your text file.

therefore the only way to do this is by reading the file a line at a time. Not too hard one you get used to the technique.


Code:
open file
while not end of file
    read record
    if header then process header
    if line then process line
end while
close file
 

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Hi Plog and Dave,
Thank you for your responses.
A question for Plog:
I looked everywhere I could think of with different variations of parsing and scripts but didn't have any success. Could you lead me to one example please? I am sure it is just the terminology which I am still learning.
Dave,
Are you running that script prior to the import or after? I will name the file the same every time as it is updated daily and replace as necessary in my table. I use simply shipping report.xlsx, backlogreport.xlsx, and so forth.
Thank you all for your help. I am definitely new to access and VBA so pretend I am a school kid when explaining your suggestions because experiential I am.
I have been watching(over and over) some online tutorials through Lynda in hopes of gaining some experience but I haven't found what i need to get me going.
 

plog

Banishment Pending
Local time
Today, 10:14
Joined
May 11, 2011
Messages
11,611
I just wrote one in php to extract physician data from a specific website:

Code:
<?php


$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $URL);
curl_setopt($ch, CURLOPT_HEADER, false);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$str_Scrape=curl_exec($ch);
curl_close($ch);
// retrieves html of $URL and puts into $sr_Scrape
 
while (strpos($str_Scrape, "doctor-locator-search/profile")!==false)
  // records start with a div tag of the above type, will process as long as that text still exists in $str_Scrape  	
	{$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "doctor-locator-search/profile"));
	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "_trackEvent"));
	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, ">")+1);
  // truncates $str_Scrape so that it now begins at the beginning of doctor name

	$str_Record="\"".substr($str_Scrape, 0, strpos($str_Scrape, "</a>"));
  // extracts doctor name

	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "semibold")+10);
	$str_Record.="\", \"".substr($str_Scrape, 0, strpos($str_Scrape, "</span>"));
 // extracts business name

	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "<br />")+6);
	$str_Record.="\", \"".substr($str_Scrape, 0, strpos($str_Scrape, "<br/>"));
  // extracts address line

	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "<br/>")+6);
	$str_Record.="\", \"".substr($str_Scrape, 0, strpos($str_Scrape, "<br/>"));
  // extracts city/state/zip

	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "_trackEvent"));
	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, ">")+1);
	$str_Record.="\", \"".substr($str_Scrape, 0, strpos($str_Scrape, "</a>"));
  // extracts phone

	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, "Website"));
	$str_Scrape=substr($str_Scrape, strpos($str_Scrape, ">")+1);
	$str_Record.="\", \"".substr($str_Scrape, 0, strpos($str_Scrape, "<"))."\"";
// extracts website

	echo "<br>".$str_Record;
// spits out record onto screen	
	}

?>
 

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Wow.... impressive and way over my head. I am going to have to step back and reassess. I know less about PHP than I do VBA. If you simpler examples it would be greatly appreciated. Thanks for sharing.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
26,999
The way I might approach it if parsing isn't your thing is to import the spreadsheets into an Access table manually (once for each differently formatted spreadsheet) and do so into an Access table that has ALL SHORT TEXT fields. You define the staging table once, then you erase it (DELETE * FROM STAGING;) before each use. Note that when doing this, you open yourself up to the need to do compact & repair on whereever you put the staging table, though there ARE solutions for this.

OK, so you have a look for each case. You identify the "markers" where a header will drop something recognizable and unique into each field of the table. Perhaps it will be some common word that never appears in that same position anywhere else in the table. Perhaps it will be some kind of punctuation. Perhaps it will be that due to spacing, the first field is blank. There will probably be several such "markers" for each table.

So now you write a bunch of DELETE queries to tackle the records for each marker. Like DELETE * FROM STAGING WHERE LEN(NZ(FIELD1,"")) = 0; and other similar simple action queries. Eventually, you have cleaned out everything that you didn't want.

Now you can do the INSERT INTO realtable (real-field-names-list) SELECT CLNG( FIELD1 ), CDBL( FIELD2 ), FIELD3, etc. etc. FROM STAGING ;. The key, of course, is to do the manual import once up front to verify the markers that tell you that the record is one you don't want to keep.

This is a "divide and conquer" strategy in that you divide up the KINDS of markers and take them one at a time. When you are finished, what is left should be what you wanted. THEN you convert it to the right data types or you reformat it some kind of way. But take it in small steps.
 

June7

AWF VIP
Local time
Today, 07:14
Joined
Mar 9, 2014
Messages
5,423
I would attempt to write code for you but need more info. It's hard to tell where some of those header fields begin and end. Is the last field really [SP Code and Name End Customer]? It would be nice to see some truly representative data. Does data have spaces? For instance, does [Bill To Name] have values like "John James Doe". Attach a txt file to post.

Consistency is critical when parsing text and without distinct "markers" to identify records and fields, can be virtually impossible. Files that are comma separated or fixed width columns are fairly easy to parse. The example is more 'free-form' and will not be easy. Do all of the Excel files follow the same structure?

Where do these reports you get come from? If these are produced internally, couldn't you coordinate with the other entity to provide you data in a friendlier structure? You say "My shipping report" and "as I ship"? Do you have any control over how these reports are generated?

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
 
Last edited:

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Hi June 7,
Thanks for the offer. I have attached to images to give a better idea of the spreadsheet format. I changed data to protect the innocent and well my job. There are two images. Backlog and shipping. Of course I deleted a bunch of lines so it would fit in the image and still be readable. There is a blank row for some reason in the shipping reports on row 7 and 9 plus cells are merged. So either I need to be able to import and clean up the rows or somehow automate before I import it. The backlog report changes as orders are shipped as does the shipping report. When I book an order, the booking file is updated and hence the backlog report is updated the next day. At the beginning of the month the booking and shipping report start from scratch. I keep a spreadsheet which I will import into Access that sales from day 1 so I can see customer trends, gross profit dollars of all suppliers and individual suppliers or supplier parts. But of course I want to calculate my commission on the fly so I can catch possible shipping errors like perhaps a debit hasn't been applied. My goal is to easily put all the reports into a dashboard and be able to export by customer, product line, or some time frame like month to date or quarter. I would appreciate any examples. Right now I get requests three or four times a month to provide supplier or customer data and all the manipulation takes too long. I need to knock on doors not clean spreadsheets to get Momma those new shoes. Thanks a bunch!
Razzbarry
shipping.jpg

backlog image.jpg
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 19, 2013
Messages
16,553
in the shipping image, the rows you want to import can be identified where column F is numeric, the same for the backlog file, but this time column E.

So the easy way to do this is to link the file - no headers, then use a query along the lines of


INSERT INTO destTable (fieldnames)
SELECT fieldnames
FROM linkedTable
WHERE isnumeric(F6)

change F6 to F5 for the other table
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Sep 12, 2006
Messages
15,613
Hi Plog and Dave,
Thank you for your responses.
A question for Plog:
I looked everywhere I could think of with different variations of parsing and scripts but didn't have any success. Could you lead me to one example please? I am sure it is just the terminology which I am still learning.
Dave,
Are you running that script prior to the import or after? I will name the file the same every time as it is updated daily and replace as necessary in my table. I use simply shipping report.xlsx, backlogreport.xlsx, and so forth.
Thank you all for your help. I am definitely new to access and VBA so pretend I am a school kid when explaining your suggestions because experiential I am.
I have been watching(over and over) some online tutorials through Lynda in hopes of gaining some experience but I haven't found what i need to get me going.

This is instead of the import.

The problem is that docmd.transferspreadsheet, or docmd.transfertext is no good unless every row in the importfile has the same format.

When this isn't the case, you have to read the file in code, a line at a time, and write a "bespoke" import manager to deal with the rows.
 

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Hi,
Thank you for all the advice. I haven't been able to get the formatting down so I have attached an example of what I receive on a daily basis. I left all the format alone and changed the data. If you want to write something I would really appreciate it. Given these two have minor differences I am hoping I can follow what you do and adapt to the other spreadsheets I receive.
Thanks again everyone for your help. I must say I am learning bits and pieces and am hoping to pull them all together. Thanks for your patience.
Razzbarry
 

Attachments

  • Backlog report example.xls
    70.5 KB · Views: 100
  • Shipping report example.xls
    47.5 KB · Views: 84

CJ_London

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 19, 2013
Messages
16,553
@Razzbarry - appreciate the vote of thanks, but would be helpful to know if you have tried the code I suggested and if so, what, if any, errors did you get.
 

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
@Razzbarry - appreciate the vote of thanks, but would be helpful to know if you have tried the code I suggested and if so, what, if any, errors did you get.

Unfortunately I haven't got that far. I did read it but I didn't understand it so I need to step back and educate myself. any tips are welcome?
Thanks,
Razzbarry
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 19, 2013
Messages
16,553
see attached as example. I imported the xls file rather than linking to it as an example
 

Attachments

  • xlimp.accdb
    884 KB · Views: 89

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
Wow CJ....just wow. That was so simple yet so profound. I will apply it in my database and advise. I can't thank you enough.
Is there any way to keep the headers?
 
Last edited:

Razzbarry

Registered User.
Local time
Today, 09:14
Joined
Sep 28, 2018
Messages
28
I tried like"*" in field5 and it works to take out leading and trailing rows with unwanted data. Thanks again!
 

Users who are viewing this thread

Top Bottom