Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108709 > unrolled thread

Working from data converted from a pdf file

Back to article view | Back to microsoft.public.excel.programming

Contents

  Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 08:23 -0700
    Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 12:21 -0400
      Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 11:13 -0700
        Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 15:07 -0400
          Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 12:17 -0700
            Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 15:26 -0400
    Re: Working from data converted from a pdf file Claus Busch <claus_busch@t-online.de> - 2016-04-06 18:24 +0200
      Re: Working from data converted from a pdf file Claus Busch <claus_busch@t-online.de> - 2016-04-06 19:57 +0200
        Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 11:23 -0700
          Re: Working from data converted from a pdf file Claus Busch <claus_busch@t-online.de> - 2016-04-06 20:25 +0200
            Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 11:39 -0700
              Re: Working from data converted from a pdf file Claus Busch <claus_busch@t-online.de> - 2016-04-06 20:53 +0200
                Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 12:17 -0700
                  Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 12:27 -0700
                    Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 15:39 -0400
                      Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 12:46 -0700
                        Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 12:49 -0700
                          Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 16:05 -0400
                            Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 13:17 -0700
                              Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 16:30 -0400
                              Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 16:35 -0400
                                Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 13:51 -0700
                                  Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 17:12 -0400
                                    Re: Working from data converted from a pdf file B Roberson <jbr7655@gmail.com> - 2016-04-06 14:32 -0700
                                      Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 17:38 -0400
                                  Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 17:24 -0400
                                  Re: Working from data converted from a pdf file GS <gs@v.invalid> - 2016-04-06 17:35 -0400

Page 1 of 2  [1] 2  Next page →

#108709 — Working from data converted from a pdf file

040480-00	HOLLON #26-1@  OK  WOODWARD									
09/15 G	1562	1.22	2.49  3,895.79	3,649.51	1.0000000 WI	3,895.79				3,173.26
							TOKER	-3.42 FEXMPP	-476.25	
							TOKEX	-3.25		
							TOKMR	-0.24		
							TOKPR	-239.37		
09/15 G	-1562	1.22	.0000  -3,895.06	-3,614.57	1.0000000 WI	-3,895.06				-3,138.32
							TOKER	3.90 FEXMPP	476.25	
							TOKEX	3.70		
							TOKMR	0.24		
							TOKPR	272.65		

The above data is an example of how a converted pdf file gets parsed as if gets converted and is resaved as an xlsx file. The next 6 or 7 rows are basically a record that has to be joined together to form one row in a table. I can do that one with =match formulas. The problem comes with the second occurrence starting with "09/15 G -1562" and this group continues on the next 6 or 7 rows below that. 

The problem I am asking for help on has to do with the top line identifier information. I need to be able to create a formula for the 2nd record say from rows 9 to 15 to also pull the identifier found on row 1 to also associate this line with it as well as with the first row.

The simplified table below is what I am after. The headers were created as desired and are not part of this particular file I brought in but are part of the table structure I am looking to create

Sales Month	Meter	Well Name	Mcfs	Gross
09/15	        040480	HOLLON #26-1    1562    3,895.79
09/15	        040480	HOLLON #26-1    -1562  -3,614.57
		

Can this sort of thing be done with combinations of index and match, or is it easier to create a looping marco? Either way, there are thousands of such rows, and the top record information on this page will be changing in some variation but it will not be in a predictable patter, and sometimes that record contained in meter and well name in the example table above will not show up consistently in the same excel column when it gets parsed by pdf converter,

Can someone get me started on this please?

Thanks,


Bruce

[toc] | [next] | [standalone]

#108710

Firstly, which PDF converter are you using?
Is the 1st indented row ("TOK?") placed in col "C"?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108713

I think it is nuance and its on our company webapp page.

On Wednesday, April 6, 2016 at 11:21:55 AM UTC-5, GS wrote:
> Firstly, which PDF converter are you using?
> Is the 1st indented row ("TOK?") placed in col "C"?
> 
> -- 
> Garry
> 
> Free usenet access at http://www.eternal-september.org
> Classic VB Users Regroup!
>   comp.lang.basic.visual.misc
>   microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108718

> I think it is nuance and its on our company webapp page.
>
> On Wednesday, April 6, 2016 at 11:21:55 AM UTC-5, GS wrote:
>> Firstly, which PDF converter are you using?
>> Is the 1st indented row ("TOK?") placed in col "C"?
>> 
>> -- 
>> Garry
>> 
>> Free usenet access at http://www.eternal-september.org
>> Classic VB Users Regroup!
>>   comp.lang.basic.visual.misc
>>   microsoft.public.vb.general.discussion

Ok, thanks! This tells me what was used as the col delimiter. While 
your sample data 'appears' tab delimited, it's better to be sure. Of 
course, this means nothing if the data is already imported into the 
worksheet. It means a lot if the data is stored in a text file rather 
than PDF because that way it can be read into an array, parsed as 
desired, then 'dumped' into a worksheet; -all quite easily. That means 
the file that your PDF was created from might be a better source if 
available!!!

Meanwhile, I'll also take a look at your sample file as your data 
layout is similar to how my menu-builder routine reads in its data. In 
this case working code for this already exists and just needs tweaking 
to suit your usage!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108719

> Ok, thanks! This tells me what was used as the col delimiter. While 
> your sample data 'appears' tab delimited, it's better to be sure. Of 
> course, this means nothing if the data is already imported into the 
> worksheet. It means a lot if the data is stored in a text file rather 
> than PDF because that way it can be read into an array, parsed as 
> desired, then 'dumped' into a worksheet; -all quite easily. That means 
> the file that your PDF was created from might be a better source if 
> available!!!
> 
> Meanwhile, I'll also take a look at your sample file as your data 
> layout is similar to how my menu-builder routine reads in its data. In 
> this case working code for this already exists and just needs tweaking 
> to suit your usage!
> 
> -- 
> Garry
Garry and Claus, here is my link. https://www.dropbox.com/s/aguedei0jqymk4p/Check%20Detail%20March%2031%2C%202016.xlsm?dl=0

Sheet1 contains all the data that nuance parsed just the way it parsed it, pretty messed up but then it looks like the check stub does. 

On sheet2, I just put in 4 records so you could see where I was getting the information starting at the top of sheet1.

I also included the total of the check at the bottom of sheet2 as it appears on the sheet1. What makes this really messy is that it has 92 pages in here of pdf data it had been printed out as shown. That is a lot of mess to process manually, so that is why I am trying to help out an accountant in our office by tabling it so it can be mapped into where it needs to go.

Bruce

[toc] | [prev] | [next] | [standalone]

#108721

>
>
>> Ok, thanks! This tells me what was used as the col delimiter. While 
>> your sample data 'appears' tab delimited, it's better to be sure. Of 
>> course, this means nothing if the data is already imported into the 
>> worksheet. It means a lot if the data is stored in a text file 
>> rather  than PDF because that way it can be read into an array, 
>> parsed as  desired, then 'dumped' into a worksheet; -all quite 
>> easily. That means  the file that your PDF was created from might be 
>> a better source if  available!!!
>> 
>> Meanwhile, I'll also take a look at your sample file as your data 
>> layout is similar to how my menu-builder routine reads in its data. 
>> In  this case working code for this already exists and just needs 
>> tweaking  to suit your usage!
>> 
>> -- 
>> Garry
> Garry and Claus, here is my link. 
> https://www.dropbox.com/s/aguedei0jqymk4p/Check%20Detail%20March%2031%2C%202016.xlsm?dl=0
>
> Sheet1 contains all the data that nuance parsed just the way it 
> parsed it, pretty messed up but then it looks like the check stub 
> does. 
>
> On sheet2, I just put in 4 records so you could see where I was 
> getting the information starting at the top of sheet1.
>
> I also included the total of the check at the bottom of sheet2 as it 
> appears on the sheet1. What makes this really messy is that it has 92 
> pages in here of pdf data it had been printed out as shown. That is a 
> lot of mess to process manually, so that is why I am trying to help 
> out an accountant in our office by tabling it so it can be mapped 
> into where it needs to go.
>
> Bruce

Who creates the PDF?
Can you inquire if the data is available as a text file?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108711

Hi Bruce,

Am Wed, 6 Apr 2016 08:23:17 -0700 (PDT) schrieb B Roberson:

> Sales Month	Meter	Well Name	Mcfs	Gross
> 09/15	        040480	HOLLON #26-1    1562    3,895.79
> 09/15	        040480	HOLLON #26-1    -1562  -3,614.57

in your expected output you have in row 1 below Gross the value that is
behind WI in your source table. In row 2 you have a value that is in
front of WI.
Which value is correct?


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108712

Hi Bruce,

Am Wed, 6 Apr 2016 18:24:27 +0200 schrieb Claus Busch:

> in your expected output you have in row 1 below Gross the value that is
> behind WI in your source table. In row 2 you have a value that is in
> front of WI.
> Which value is correct?

I hope iseparated your data into the correct columns. Your data in
Sheet1.
Try:

Sub NewTable()
Dim varData As Variant, varOut() As Variant
Dim Cnt As Long, LRow As Long, i As Long, n As Long
Dim WName As String, Meter As String
Dim myRng As Range

With Sheets("Sheet1")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set myRng = .Range("A1:A" & LRow)
    Cnt = Application.Evaluate("=SumProduct((MID(" _
        & "Sheet1!" & myRng.Address & ",3,1)=""/"")*1)")
    varData = .Range("A1:G" & LRow)
    ReDim Preserve varOut(Cnt - 1, 4)
    For i = LBound(varData) To UBound(varData)
        If InStr(varData(i, 2), "@") Then
            Meter = Split(varData(i, 1), "-")(0)
            WName = Split(varData(i, 2), "@")(0)
        End If
        If InStr(varData(i, 1), "/") Then
            varOut(n, 0) = Split(varData(i, 1), " ")(0)
            varOut(n, 1) = Meter
            varOut(n, 2) = WName
            varOut(n, 3) = varData(i, 2)
            varOut(n, 4) = varData(i, 7)
            n = n + 1
        End If
    Next
End With
Sheets("Sheet2").Range("A2").Resize(Cnt, 5) = varOut
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108714

> I hope iseparated your data into the correct columns. Your data in
> Sheet1.
> Try:
> 
> Sub NewTable()
> Dim varData As Variant, varOut() As Variant
> Dim Cnt As Long, LRow As Long, i As Long, n As Long
> Dim WName As String, Meter As String
> Dim myRng As Range
> 
> With Sheets("Sheet1")
>     LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
>     Set myRng = .Range("A1:A" & LRow)
>     Cnt = Application.Evaluate("=SumProduct((MID(" _
>         & "Sheet1!" & myRng.Address & ",3,1)=""/"")*1)")
>     varData = .Range("A1:G" & LRow)
>     ReDim Preserve varOut(Cnt - 1, 4)
>     For i = LBound(varData) To UBound(varData)
>         If InStr(varData(i, 2), "@") Then
>             Meter = Split(varData(i, 1), "-")(0)
>             WName = Split(varData(i, 2), "@")(0)
>         End If
>         If InStr(varData(i, 1), "/") Then
>             varOut(n, 0) = Split(varData(i, 1), " ")(0)
>             varOut(n, 1) = Meter
>             varOut(n, 2) = WName
>             varOut(n, 3) = varData(i, 2)
>             varOut(n, 4) = varData(i, 7)
>             n = n + 1
>         End If
>     Next
> End With
> Sheets("Sheet2").Range("A2").Resize(Cnt, 5) = varOut
> End Sub

Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.

Thanks for your help...

Bruce

[toc] | [prev] | [next] | [standalone]

#108715

Hi Bruce,

Am Wed, 6 Apr 2016 11:23:47 -0700 (PDT) schrieb B Roberson:

> Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.

if you need further help, please upload a workbook with example data and
the expected output.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108716

Apparently I am going to need some more help. I will post back here once I figure out how to upload. It looks like I need to post a link out of dropbox to you can access it?

I loaded this macro into the full file which has some 4,700 rows. The reason I can't tell exactly what is happening is it appears this look goes through all the rows before it copies anything to the sheet2. I stepped through a lot of the iterations of the loop and it passes through those just fine. 

I guess what is happening is when the loop ceased to find anymore rows, it threw up an error message when it encountered the following line.

varOut(n, 0) = Split(varData(i, 1), " ")(0)

After that, it doesn't write anything. Am I close on my guess and would it just need something like an onerror statement to get it to pass on down to where it writes what it did? If I can figure that out, maybe I can build on it to add more fields.

On Wednesday, April 6, 2016 at 1:26:06 PM UTC-5, Claus Busch wrote:
> Hi Bruce,
> 
> Am Wed, 6 Apr 2016 11:23:47 -0700 (PDT) schrieb B Roberson:
> 
> > Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.
> 
> if you need further help, please upload a workbook with example data and
> the expected output.
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108717

Hi Bruce,

Am Wed, 6 Apr 2016 11:39:47 -0700 (PDT) schrieb B Roberson:

> Apparently I am going to need some more help. I will post back here once I figure out how to upload. It looks like I need to post a link out of dropbox to you can access it?
> 
> I loaded this macro into the full file which has some 4,700 rows. The reason I can't tell exactly what is happening is it appears this look goes through all the rows before it copies anything to the sheet2. I stepped through a lot of the iterations of the loop and it passes through those just fine. 
> 
> I guess what is happening is when the loop ceased to find anymore rows, it threw up an error message when it encountered the following line.
> 
> varOut(n, 0) = Split(varData(i, 1), " ")(0)
> 
> After that, it doesn't write anything. Am I close on my guess and would it just need something like an onerror statement to get it to pass on down to where it writes what it did? If I can figure that out, maybe I can build on it to add more fields.

yes, upload the workbook to DropBox and post the link here.
This error occurs if there is no other char in the column with the date.
Therefore it is necessary to see the data.
The macro works into the memory and writes the data in Sheet2 when all
data is done. If it would write row after row it would be slow.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108720

See notes below to Garry, here is the link.

https://www.dropbox.com/s/aguedei0jqymk4p/Check%20Detail%20March%2031%2C%202016.xlsm?dl=0


On Wednesday, April 6, 2016 at 1:53:10 PM UTC-5, Claus Busch wrote:
> Hi Bruce,
> 
> Am Wed, 6 Apr 2016 11:39:47 -0700 (PDT) schrieb B Roberson:
> 
> > Apparently I am going to need some more help. I will post back here once I figure out how to upload. It looks like I need to post a link out of dropbox to you can access it?
> > 
> > I loaded this macro into the full file which has some 4,700 rows. The reason I can't tell exactly what is happening is it appears this look goes through all the rows before it copies anything to the sheet2. I stepped through a lot of the iterations of the loop and it passes through those just fine. 
> > 
> > I guess what is happening is when the loop ceased to find anymore rows, it threw up an error message when it encountered the following line.
> > 
> > varOut(n, 0) = Split(varData(i, 1), " ")(0)
> > 
> > After that, it doesn't write anything. Am I close on my guess and would it just need something like an onerror statement to get it to pass on down to where it writes what it did? If I can figure that out, maybe I can build on it to add more fields.
> 
> yes, upload the workbook to DropBox and post the link here.
> This error occurs if there is no other char in the column with the date.
> Therefore it is necessary to see the data.
> The macro works into the memory and writes the data in Sheet2 when all
> data is done. If it would write row after row it would be slow.
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108722

The other thing I forgot to mention about the drop file.... I went back and found that I had already stripped out the top row which had the meter and well name parsed together with some other header data. I copied the first record values out, being the meter number and the well name, and then deleted that header file. That probably doesn't matter in the scheme of things here though. I just thought I would mention it.

[toc] | [prev] | [next] | [standalone]

#108723

> The other thing I forgot to mention about the drop file.... I went 
> back and found that I had already stripped out the top row which had 
> the meter and well name parsed together with some other header data. 
> I copied the first record values out, being the meter number and the 
> well name, and then deleted that header file. That probably doesn't 
> matter in the scheme of things here though. I just thought I would 
> mention it.

Well we need that so it reflects *exactly* how the data layout is in 
order to properly code for it!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108724

> Garry
> 
Here is the link to the file as I got it out of Nuance

https://www.dropbox.com/s/3otad74nd75fx4i/Check%20Detail%20March%2031%2C%202016.pdf.xlsx?dl=0

Nuance in this case wouldn't find any headings for me to cut those out, and of course those are just extra stuff to sift through.

[toc] | [prev] | [next] | [standalone]

#108725

One of the big challenges to me is that the well names tend to parse across multiple columns as you can see if you look down through there.

Bruce

[toc] | [prev] | [next] | [standalone]

#108726

> One of the big challenges to me is that the well names tend to parse 
> across multiple columns as you can see if you look down through 
> there.
>
> Bruce

Yes.., rather messy output IMO!

Can you put together 2 comma delimited strings; 1 for the original 
order of headings, and 1 for those same headings ordered how you want 
them.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108727

https://www.dropbox.com/s/xre6rh9f6gugt4l/Delimited%20file%20of%20headers.csv?dl=0

That is the csv file of the way I layed out how I wanted it. I can't find much of a pattern for how it was in the pdf file

[toc] | [prev] | [next] | [standalone]

#108728

> https://www.dropbox.com/s/xre6rh9f6gugt4l/Delimited%20file%20of%20headers.csv?dl=0
>
> That is the csv file of the way I layed out how I wanted it. I can't 
> find much of a pattern for how it was in the pdf file

There has to be a pattern in the source for proper coding or for a data 
map to work. How is the PDF created? It must be a report generated by 
some app that collects the data. Output from that app should be 
consistent so find out if the report can be written/saved to a text 
file rather than 'printed' to a PDF.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web