• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

export to excel result unreadable

Explorer ,
Jun 18, 2016 Jun 18, 2016

Copy link to clipboard

Copied

I'm exporting a monthly credit card statement to excel (xlsx) format. Part of the resulting converted file look fine. The part that has the detail transactions appears in the proper column formatting but the actual dates, amounts, and descriptions are not readable. It's as if the underlying data is encoded in a format that excel does not understand...for example, it might be in 2-byte encoding. Any idea whether I can tell the export to use a different encoding OR is it an option that has to be handled within excel OR is it a but in EXPORT?

Views

8.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jun 22, 2016 Jun 22, 2016

Obviously, the fault is in the specific PDF file, and you are doing all that you can do. Good look.

Votes

Translate

Translate
Adobe Employee ,
Jun 18, 2016 Jun 18, 2016

Copy link to clipboard

Copied

Hi cellist_01

Could you please let us know the Adobe service you are using to export PDF to excel format.

If possible could you please also share the screenshot of the PDF and the excel file you get after exporting PDF.

Regards,

Meenakshi

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 18, 2016 Jun 18, 2016

Copy link to clipboard

Copied

Meenakshi,

Thanks for replying.

The service is called Adobe Export PDF and I am accessing it via Adobe Acrobat Reader DC Tools.

In my original post, I showed an example of a line from the pdf and the corresponding line from the excel file. I copy-and-pasted the lines into the text of my question. Now that I go back and look, I see that the example lines were lost. (They

showed while I was composing the text of the question!)

I won't be able to show the entire pdf, as it contains sensitive information but I can attach a screenshot of some of the problem lines.

Phil

credit card screenshot of lines from excel.JPGcredit card screenshot of lines from pdf.JPG

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 18, 2016 Jun 18, 2016

Copy link to clipboard

Copied

Here are the excel lines again with the rows resized to show the text more clearly.

credit card screenshot of lines from excel-resized rows.JPG

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 18, 2016 Jun 18, 2016

Copy link to clipboard

Copied

Without further information, my guess is that the author of the file used a font that isn't in your system and that wasn't properly embedded in the document.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 19, 2016 Jun 19, 2016

Copy link to clipboard

Copied

The file is created by the credit card company. I'll see if I can find out more about the specifications of the file and report back what I find.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 21, 2016 Jun 21, 2016

Copy link to clipboard

Copied

I've contacted the credit card company (bank). The pdf is created by software from TargetStream StreamEDS. I asked them: What is the character encoding? ans: we don't know. Is the file encrypted? ans: if it is encrypted then you would need a password. (in other words, "we don't know."

I've contacted TargetStream. They disavowed any knowledge of this particular file and suggested I contact the bank.

Looking at the data with a hex editor, it seems that the encoding might be EBCDIC. I'm trying to find a tool that will convert it to ascii, which is what EXCEL needs.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 22, 2016 Jun 22, 2016

Copy link to clipboard

Copied

Obviously, the fault is in the specific PDF file, and you are doing all that you can do. Good look.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 24, 2016 Jun 24, 2016

Copy link to clipboard

Copied

@Claudio

Do you know how to submit an enhancement request to Adobe?

I'm sure the conversion process pdf->excel is complicated, but it seems that since Adobe understands the internal structure of the pdf far better than any third-party, they could find a way to identify areas of the converted file that are based on images rather than text. If so, it would seem that their process could reserve a column of the output for a flag that would basically say "this(these) row(s) is(are) rendered from an image using OCR and it (they) may not be readable as text." Then, the user could decide whether to remove them from the excel file. It might even be an option that the end user chooses when requesting the conversion.

In my case, I want to be able to automate (at my end) as much as possible, the process of coming up with a useful spreadsheet. For other bank statements that I deal with, I currently use a process that goes something like this:

     1~ excel-file-from-adobe-export ->

          2~ saveas csv ->

               3~ perl-extract-useful-data-as-csv ->

                   4~ open-csv-in-excel ->

                         5~ manually-process-excel-file

Best case scenario, steps 2,3,4 are automated.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 29, 2016 Jun 29, 2016

Copy link to clipboard

Copied

@claudio I spoke with the credit card company (bank) technical support again.

They provide two types of statement PDFs.

The one I've been discussing here is specifically designed to make it more accessible on a mobile device. Some of the data contains information that will enable the mobile device user to use voice and speaker to access the statement. The reason for the EBCDIC encoding is to enable interaction with the file using both the Adobe Reader software and other (non-Adobe) reader software.

The other type of statement is for non-mobile-device use and is not encoded in EBCDIC. I have no problem converting that type of file (to EXCEL) on my PC with the Adobe Export service.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 29, 2016 Jun 29, 2016

Copy link to clipboard

Copied

Thanks for all the extra information. Suggestions for improvements are best posted here: Feature Request/Bug Report Form

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 02, 2016 Aug 02, 2016

Copy link to clipboard

Copied

send to where? I am having the same problem with BofA statements and will gladly send you thge files if I only knew wehere to send them...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 28, 2016 Sep 28, 2016

Copy link to clipboard

Copied

I just bought Adobe's Export PDF for a year to make my data entry and sorting easier and I've been struggling with this for more than an hour. The BOA statements come out in Greek. Did you ever find an answer to your problem?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Jul 18, 2017 Jul 18, 2017

Copy link to clipboard

Copied

After spending an entire day trying to figure this out and thinking there was an issue with my system fonts, I found a work around.

I'm on a mac but I imagine the equivalent steps would work on a PC.

I open my bank statement as a pdf and take a screen shot (command+shift+4) of just the sections I want (my Bank of America statement has sections with logos, etc.) and save those to my desktop. I then open each one in Preview or your image viewer and export them as a pdf, giving each one a name like Feb page1, Feb page 2, etc, so I know the correct order they appeared on the statement. I then combine the new pdfs in Acrobat, making sure pages are turned the same way and in the correct order. Then take the new combined file pdf and export it as an excel file...voila, works like a charm and saved so much time.

Best,

George

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 16, 2017 Sep 16, 2017

Copy link to clipboard

Copied

LATEST

Found a solution,

1 - Open the PDF file you wish to convert

2 - Use File > Print (Using Microsoft Print as PDF instead of your actual printer)

3 - Save a copy of the PDF and name as needed (I just name it 1.pdf for January, 2.pdf for February, etc.)

4 - Open your new file with Adobe Acrobat

5 - Convert to xlsx

This should result in a legible copy of your statement. Using Print as PDF will also allow you convert locked pdfs to Excel format. I had an issue with a statement that was locked or perhaps in a view-only state and using Print as PDF allowed me to convert this to excel for data extraction.

Hope this helps someone!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources