Topic: Converting exported .pdf to .csv (1 of 10), Read 19 times
Conf: Converting, Translating
From: Peter Rejto
Date: Saturday, November 22, 2008 02:28 AM

Hello,

I have downloaded the 2,000 US Census data as a .pdf file.
Using the Adobe Select Tool I got data like,

Alabama 4,461,130 7

In other words, the fields are separated by spaces.

Then, I converted these data into a .csv file.
Actually, Vedit was quite helpful. This is what I did. First, replaced the thousand delimeter by, say, the backward quote. Second, replaced the space by comma. This seemed to do the job.

I noticed that Vedit does have a rather sophisticated conversion macro, that I can call from the {Edit, Convert} menu. I have a hunch that this conversion macro would automate what I have done manually. I just do not know.


Actually, all I need is to import these data into Excel.
May be the .csv format is not the best way to do it. After replacing the Thousand delimeter by, backward quote, I had to change it again in Excel.


Thanks,

-peter

 

Topic: Re: Converting exported .pdf to .csv (2 of 10), Read 18 times
Conf: Converting, Translating
From: Christian Ziemski
Date: Saturday, November 22, 2008 11:07 AM

On 22.11.2008 08:34 in vtech-convert Peter Rejto wrote:
>
> Using the Adobe Select Tool I got data like,
>
> Alabama 4,461,130 7
>
> In other words, the fields are separated by spaces.
>
> Then, I converted these data into a .csv file. Actually, Vedit was
> quite helpful. This is what I did. First, replaced the thousand
> delimeter by, say, the backward quote. Second, replaced the space by
> comma. This seemed to do the job.
>
> [...]
>
> Actually, all I need is to import these data into Excel. May be the
> .csv format is not the best way to do it. After replacing the
> Thousand delimeter by, backward quote, I had to change it again in
> Excel.

I would recommend to completely remove the thousand delimiters from the
.csv file before importing it into Excel.

Then in Excel you can configure how to _display_ those numbers.

Christian

 

Topic: Re: Converting exported .pdf to .csv (3 of 10), Read 18 times
Conf: Converting, Translating
From: Peter Rejto
Date: Saturday, November 22, 2008 12:46 PM

On 11/22/2008 11:07:25 AM, Christian Ziemski wrote:

>I would recommend to completely remove
>the thousand delimiters from the
>.csv file before importing it into
>Excel.
>
>Then in Excel you can configure how to
>_display_ those numbers.

Christian,

Thank you very much! Now that you have pointed it out I wonder how could I miss it in the first place?

So, my conversion job is really simple:
Step 1: remove coma-s.
Step 2: replace space by comma.

Hence the Vedit conversion macro is an overkill.I have a hunch that it also contains my problem as a super simple special case. In case you or another Vedit pro has some time, I would appreciate knowing how to choose the parameters in the conversion macro for my job.

Thanks again,

-peter

 

Topic: Re: Converting exported .pdf to .csv (4 of 10), Read 17 times
Conf: Converting, Translating
From: Christian Ziemski
Date: Saturday, November 22, 2008 02:24 PM

On 22.11.2008 19:05 in vtech-convert Peter Rejto wrote:
>
> On 11/22/2008 11:07:25 AM, Christian Ziemski wrote:
>
>> I would recommend to completely remove the thousand delimiters from
>> the .csv file before importing it into Excel.
>>
>> Then in Excel you can configure how to _display_ those numbers.
>
> Christian,
>
> Thank you very much! Now that you have pointed it out I wonder how
> could I miss it in the first place?
>
> So, my conversion job is really simple:
> Step 1: remove coma-s.
> Step 2: replace space by comma.
>
> Hence the Vedit conversion macro is an overkill.I have a hunch that
> it also contains my problem as a super simple special case. In case
> you or another Vedit pro has some time, I would appreciate knowing
> how to choose the parameters in the conversion macro for my job.

Peter:

That conversion macro isn't appropriate in your case.

It converts fixed-length data files to .csv format.
Your data is variable length and needs another approach.

In your description above you already "coded" it!

Replace(",","", BEGIN+ALL+NOERR) // Step 1
Replace("|W",",", BEGIN+ALL+NOERR) // Step 2
File_Save_As("|(PATHNAME).csv") // save as .csv

That's it.


Christian

 

Topic: Re: Converting exported .pdf to .csv (5 of 10), Read 18 times
Conf: Converting, Translating
From: Christian Ziemski
Date: Saturday, November 22, 2008 04:51 PM

>> Alabama 4,461,130 7
>
> Replace(",","", BEGIN+ALL+NOERR) // Step 1
> Replace("|W",",", BEGIN+ALL+NOERR) // Step 2
> File_Save_As("|(PATHNAME).csv") // save as .csv

Oops:

Of course that simple version only works as long as there are no spaces
in the fields.
Like in "New York" ...

Then it's a bit more complicated.


Christian

 

Topic: Re: Converting exported .pdf to .csv (6 of 10), Read 19 times
Conf: Converting, Translating
From: Peter Rejto
Date: Saturday, November 22, 2008 06:33 PM

On 11/22/2008 4:51:14 PM, Christian Ziemski wrote:
>>> Alabama 4,461,130 7
>>
>> Replace(",","", BEGIN+ALL+NOERR) // Step 1
>> Replace("|W",",", BEGIN+ALL+NOERR) // Step 2
>> File_Save_As("|(PATHNAME).csv") // save as .csv
>
>Oops:
>
>Of course that simple version
>only works as long as there
>are no spaces
>in the fields.
>Like in "New York" ...
>
>Then it's a bit more
>complicated.

Christian,

I was glad to learn that the case of "New York" is a bit more complicated. In other words, I can come back to this problem later. After importing the .csv file into excel I did it by hand. So, Vedit did 90% of the conversion job for me and that is great!

As you know, I do not have a programming background.So, I can not figure out something like,

>That conversion macro isn't appropriate
>in your case.
>
>It converts fixed-length data files to
>.csv format.
>Your data is variable length and needs
>another approach.

Thanks again

-peter.

 

Topic: Re: Converting exported .pdf to .csv (7 of 10), Read 17 times
Conf: Converting, Translating
From: Christian Ziemski
Date: Sunday, November 23, 2008 05:22 AM

On 23.11.2008 00:36 in vtech-convert Peter Rejto wrote:
>
> I was glad to learn that the case of "New York" is a bit more
> complicated. In other words, I can come back to this problem later.

Glad? ;-)

Here a possible solution.

(It is a bit special for your list of state names,
so don't use it as is for other conversions
without checking!)


      // remove all commas

      // (usually too brutal, but for the given data it works)

      Replace(",", "", BEGIN+ALL+NOERR)

      

      // replace all spaces by commas as field delimiter

      // (including spaces within state names ...)

      Replace("|W", ",", BEGIN+ALL+NOERR)

      

      // restore names with spaces by replacing back comma with space

      Replace("([^0-9]),([^0-9])", "\1 \2", BEGIN+ALL+NOERR+REGEXP)

      

      // save as .csv

      File_Save_As("|(PATH_ONLY)/|(FILE_ONLY).csv")


> After importing the .csv file into excel I did it by hand. So, Vedit
> did 90% of the conversion job for me and that is great!
>
> As you know, I do not have a programming background.So, I can not
> figure out something like,
>
>> That conversion macro isn't appropriate in your case.
>>
>> It converts fixed-length data files to .csv format. Your data is
>> variable length and needs another approach.

Vedit's help describes the conversion macro in detail, with examples.
The topic is named: "Converting 'Flat' Data Files to/from CSV"

Perhaps it's not really easy to find...

Use the menu {Edit, Convert}, then press F1 while the conversion submenu
is diplayed. In the description of "Flat to CSV" there is a link.

or:

Help - Index - Search - "CSV"


Christian

 

Topic: Re: Converting exported .pdf to .csv (8 of 10), Read 25 times, 2 File Attachments
Conf: Converting, Translating
From: Christian Ziemski
Date: Sunday, November 23, 2008 08:28 AM

In the conversion macro flat-csv.vdm I added a help button.

It's not perfect, but may be a first step.

(The resulting help page needs another click on a link,
but it's the best entry in the help file I found to jump to.

[Edit: New version: The help button is now better integrated in the dialog flow.]

The modified macro and a diff file (for technically interested) are
attached.


Christian

  FLAT-CSV(1).VDM (52KB)
  FLAT-CSV.VDM.DIFF (2KB)

 

Topic: Re: Converting exported .pdf to .csv (9 of 10), Read 18 times, 2 File Attachments
Conf: Converting, Translating
From: Christian Ziemski
Date: Sunday, November 23, 2008 08:47 AM

PS:

And here the "other" macro csv-flat.vdm with the new help button.

The button is now better integrated in the dialog flow.
(I fixed that in flat-csv.vdm in my earlier message too.)


Christian

  CSV-FLAT.VDM.DIFF (2KB)
  CSV-FLAT.VDM (42KB)

 

Topic: Re: Converting exported .pdf to .csv (10 of 10), Read 15 times
Conf: Converting, Translating
From: Peter Rejto
Date: Monday, November 24, 2008 02:36 AM

On 11/23/2008 5:22:33 AM, Christian Ziemski wrote:
>On 23.11.2008 00:36 in vtech-convert


>Vedit's help describes the conversion
>macro in detail, with examples.
>The topic is named: "Converting 'Flat'
>Data Files to/from CSV"
>
>Perhaps it's not really easy to find...
>
>....
>or:
>
>Help - Index - Search - "CSV"
>
>
Christian,

This is exactly that I have missed.Now let me tell you what I did. I went into the Vedit Help file and looked under the topic, Supplied Macros. I did not find the conversion macro and I gave up.


Now,

>Use the menu {Edit, Convert}, then press
>F1 while the conversion submenu
>is diplayed. In the description of "Flat
>to CSV" there is a link.
>

This is really neat! In other words, if I need help with a menu item then try F1, before trying to guess the topic.

Thanks,

-peter