Export to excel

[plaforte]

Hello, I am using PG 4.0.800.572 with Windows 8.0

My Db is 70,000 messages. I need to do a lot of work and categorizing on subsets of 500...1000 messages. The best for me would be to do that in excel where  I can easily add columns for categories and I am not familiar with databases at all.

I tried two free softwares for exporting to excel. One is SqLite Manager (a firefox addon) and the other SqLite Expert Professional. They both worked for me and I could export easily to excel but the problem is with the "content" field of the messages. This one is BLOB type and won't transfer properly to excel. I tried to change the type but no success, I got a bunch of chinese characters. Is there a workaround this problem that does not involve deep database knowledge ?

Thank you
Pierre

[Wilson Logan]

Hi Pierre,

The 'content' field of group message is held as hex.

In your Excel spread sheet you need to convert the hex to a decimal number and then that number to an ASCII character.

=CHAR(HEX2DEC(A1))

where A1 is the cell you want to convert.

Create a new column and have this formula in each row.

Does that make sense for you?

Cheers,

Wilson.

[plaforte]

Thank you Wilson, I think we are close to the solution.

I added the column G with your formula but it gives errors #NUM! or #VALUE!

I thought it could be the formatting of column G so I tried to format as text or general but no change.

Here is a link to my file with 10 records https://www.dropbox.com/s/3zi8abgqyhw0brb/testJuly5saturday.xlsx

Any idea how to address this ?

Thank you
Pierre

[Wilson Logan]

Hi Pierre,

Ah, right... the formula I gave you would work for one hexadecimal number but not for a string of them.

Excel tries to convert the string of hexadecimal numbers into a decimal value before conversion into characters but as the string of hexadecimal converts to a decimal number several times the age of the universe measured in seconds, excel does not like this.

I have looked on the internet and there is a formula quoted for excel that is supposed to do this but later posters say that it does not work (Google "Excel convert long hex string to ascii").

I see lots of people asking how to do this and no one offering an answer.

I think the best thing is to convert the hexadecimal string directly into ascii while it is still in a .CSV output format.

This means that you export the .db3 file from SQLite Manager as a .CSV file and then use that file as an input to a program written in VB or Qbasic or something that converts just the hex string field to ascii and then you use the translated file as an input to Excel.

I will see if I can find something suitable.

Cheers,

Wilson.

[plaforte]

Thank you for your search Wilson, I will look in this direction of the .csv file. And I guess with some googling that I can manage to make a VBA module in excel to do as you say. Now that I know what to look for that should be feasible. Let me know if you find any further "cooked" solution.

Best regards
Pierre

[Wilson Logan]

Hi Pierre,

Actually, while looking for a solution I came across a user that is already doing what you require. I have emailed them to ask what their solution is.

Cheers,

Wilson.

[Wilson Logan]

Hi Pierre,

I have been told that they're using a custom Delphi XE6 program that does the conversion but also whole load of other things they specifically need.

I recommend you look for a VB solution.

Cheers,

Wilson.

[BMaverick]

Pierre,

The PGOffline program allows for export to the CSV format in a text file.

Next start Excel.  Load the csv text file.  A pop-up will appear.  Use the keyboard character pipe symbol as the divider.  pipe = | on the keyboard.  Excel will import all the contents.  I had found during the import to change the columns from GENERAL to TEXT for all the cells.  It makes this so much easier. 

As folks look for ways out of the Yahoo Groups with 10 of thousands of archived messages, the PGO is a life saver.   Glad Wilson and other gave the hint when exporting with the word 'pipe'.  I only knew then it was the special character to get the EXPORT into a spreadsheet of Excel or OpenOffice Calc.  Both will read in the Export with the pipe.  :D

Should others need this info too, I offer it as a BIG help as Yahoo is shutting down much of the content and services in the groups.

Bret